mjhjmtu

I'm trying to figure out what's using my RAM. I did some checks and it looks like that mysqld is causing high RAM usage. The output of free -g, htop, top, showprocesslist; isn't much helping.

free -g

 total used free shared buffers cached
 Mem: 125 120 5 0 1 101
 -/+ buffers/cache: 17 108
 Swap: 0 0 0

If I'm right, this shows that I have only got 5 GB of RAM free out of 125 GB.

htop

1 [||| 4.6%] 9 [ 0.0%] 17 [|| 1.4%] 25 [ 0.0%]
 2 [||||||||||||||||||||||| 44.4%] 10 [ 0.0%] 18 [ 0.0%] 26 [| 0.5%]
 3 [||||||| 12.4%] 11 [| 0.5%] 19 [ 0.0%] 27 [ 0.0%]
 4 [||||| 7.5%] 12 [ 0.0%] 20 [ 0.0%] 28 [ 0.0%]
 5 [|||||||| 14.2%] 13 [ 0.0%] 21 [ 0.0%] 29 [ 0.0%]
 6 [|||| 4.8%] 14 [ 0.0%] 22 [| 0.5%] 30 [ 0.0%]
 7 [ 0.0%] 15 [| 0.5%] 23 [|| 2.4%] 31 [ 0.0%]
 8 [| 0.5%] 16 [| 0.9%] 24 [|||| 5.6%] 32 [ 0.0%]
 Mem[||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||17367/128886MB] Tasks: 93, 90 thr; 2 running
 Swp[ 0/1021MB] Load average: 1.01 0.96 0.97
 Uptime: 18 days, 05:43:05

 PID USER PRI NI VIRT RES SHR S CPU% MEM% TIME+ Command
27655 mysql 20 0 66.8G 6166M 11220 S 69.2 4.8 5:25.06 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
28300 mysql 20 0 66.8G 6166M 11220 R 33.4 4.8 0:51.03 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27743 mysql 20 0 66.8G 6166M 11220 S 35.8 4.8 1:26.46 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
28064 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 1:33.48 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
28007 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 1:27.01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27727 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.44 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27692 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27697 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.03 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27725 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.39 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27724 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27691 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27693 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.11 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27694 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.06 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27695 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.10 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27696 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.08 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27698 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27699 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27700 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27726 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
27739 mysql 20 0 66.8G 6166M 11220 S 0.0 4.8 0:00.00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysql
 8395 clamav 20 0 799M 534M 24264 S 0.0 0.4 0:00.00 /usr/sbin/clamd --foreground=true
 8266 clamav 20 0 799M 534M 24264 S 0.0 0.4 9:07.15 /usr/sbin/clamd --foreground=true
12977 amavis 20 0 316M 120M 8448 S 0.0 0.1 0:01.46 /usr/sbin/amavisd-new (ch10-avail)
 8190 amavis 20 0 316M 120M 8448 S 0.0 0.1 0:01.64 /usr/sbin/amavisd-new (ch11-avail)
24641 amavis 20 0 242M 116M 5692 S 0.0 0.1 0:11.26 /usr/sbin/amavisd-new (master)

htop output indicates that mysqld is causing the high RAM usage, doesn't it?

top

 top - 18:12:15 up 18 days, 5:55, 1 user, load average: 0.98, 1.00, 1.00
Tasks: 475 total, 1 running, 474 sleeping, 0 stopped, 0 zombie
%Cpu(s): 2.7 us, 0.2 sy, 0.0 ni, 97.1 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem: 13198007+total, 12738310+used, 4596968 free, 1927068 buffers
KiB Swap: 1046520 total, 0 used, 1046520 free. 10698358+cached Mem

 PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
27655 mysql 20 0 66.840g 6.662g 11220 S 62.3 5.3 12:50.24 mysqld
22288 web1 20 0 367640 75964 52492 S 29.2 0.1 6:55.78 php-cgi
16897 www-data 20 0 2067372 28632 7380 S 1.0 0.0 7:20.03 apache2
29318 root 20 0 26512 3440 2576 R 0.7 0.0 0:01.49 top
31235 root 20 0 775232 12736 5984 S 0.3 0.0 16:57.05 fail2ban-server
 1 root 20 0 177980 6764 3256 S 0.0 0.0 0:55.74 systemd
 2 root 20 0 0 0 0 S 0.0 0.0 0:00.13 kthreadd
 3 root 20 0 0 0 0 S 0.0 0.0 0:04.97 ksoftirqd/0
 5 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H
 6 root 20 0 0 0 0 S 0.0 0.0 2:56.16 kworker/u64:0
 8 root 20 0 0 0 0 S 0.0 0.0 16:31.60 rcu_sched
 9 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh
 10 root rt 0 0 0 0 S 0.0 0.0 0:00.95 migration/0

But then top shows that mysqld is only using 5.3% of RAM.

show processlist;

Kill Kill 734 phpmyadmin localhost NULL Sleep 0 NULL
Kill Kill 735 root localhost NULL Query 0 NULL SHOW PROCESSLIST

show processlist; is empty, which is true, as there is only one website under development without any traffic.

mysqltuner.pl

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 24m 14s (175K q [120.627 qps], 1K conn, TX: 2G, RX: 37M)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory : 125.9G
[--] Max MySQL memory : 62.4G
[--] Other process memory: 1.1G
[--] Total buffers: 62.0G global + 2.7M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 62.0G (49.29% of installed RAM)
[OK] Maximum possible memory usage: 62.4G (49.60% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/175K)
[OK] Highest usage of available connections: 2% (4/151)
[OK] Aborted connections: 0.42% (5/1182)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 1% (988 temp sorts / 57K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 81% (13K on disk / 16K total)
[OK] Thread cache hit rate: 99% (4 created / 1K connections)
[OK] Table cache hit rate: 96% (209 open / 216 opened)
[OK] Open file limit used: 21% (225/1K)
[OK] Table locks acquired immediately: 100% (204K immediate / 204K locks)

(...)

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
 Control warning line(s) into /var/log/mysql/error.log file
 Control error line(s) into /var/log/mysql/error.log file
 MySQL started within last 24 hours - recommendations may be inaccurate
 Enable the slow query log to troubleshoot bad queries
 Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
 When making adjustments, make tmp_table_size/max_heap_table_size equal
 Reduce your SELECT DISTINCT queries which have no LIMIT clause
 Consider installing Sys schema from https://github.com/mysql/mysql-sys
 Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: bit.ly/2wgkDvS
Variables to adjust:
 tmp_table_size (> 16M)
 max_heap_table_size (> 16M)
 innodb_log_file_size should be (=7G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

mysql error.log is empty. Any ideas what is going on?

As for RAM, from your free command output, you have got free+buffers+cached -> 5+1+101 = 107 GB.

So in reality your programs are using, again from free output 120GB RAM used - 107 GB free+buffers+cached = 13GB

Buffers and cache can be reclaimed any time by the kernel for other needs and usually grow over time; however as they grow they also can shrink when memory is at premium.

As you can also see, both in free, ps and mysqltuneroutput, MySQL RAM usage seems to be mostly buffers.

A great tool I picked up during my work with MySQL and memory usage/performance can be found at the following link.

http://www.omh.cc/mycnf/

This link is nothing more than a convenient calculator tool which takes your my.cnf file's contents and calculates the maximum memory MySQL will use. Some of your key variables from the link above to pay attention to are the following.

• max_allowed_packet


• Total (per session)


• innodb_buffer_pool_size


• key_buffer_size


• Total Memory Needed (for X connection)

This should give you an idea of what kind of memory your server's MySQL/MariaDB service would need at a maximum. Not every server will use the amount the server is configured for and this calculation should only be a determiner of whether or not your server could potentially run out of memory by your MySQL configuration. Your web server, and possibly PHP settings, pose a much more significant threat to a server running out of memory. Many servers won't need above a 2 MB max_allowed_packet, and unless handling huge requests, may not ever need more than 16 MB; you would want to base such a setting off of your application's needs.

In general, when I worked as a systems administrator, we aimed for MySQL services to consume no more than 25% of a server's available memory at a maximum in order to have a better likelihood of avoiding out of memory scenarios. This tool was what I often turned to for quick calculations of how much memory I could expect a busy MySQL server to utilize.

Once more, you could create a bash script to perform this same calculation. I provide the link as an example of how analyzing a my.cnf file could help determine what is using a lot of memory. The configuration could help you determine if it is the number of sessions or perhaps the cached memory. As mentioned by Martin Smith, cache can be reclaimed at anytime, but if we are going to configure our server and don't need to tell MySQL to cache as much as it could, then let's not tell it to do so. A funny link for explaining this is the following.

https://www.linuxatemyram.com/

One helpful way of determining if your connections are using a lot of memory is to run the following at the CLI.

mysql -e "SHOW GLOBAL STATUS;"|grep Max_used_

This will tell you how many MySQL connections have been utilized by your MySQL service's current running session. (This variable is reset if you restart MySQL.) So if you are configured to allow 150 connections, but the highest your MySQL service has ever reached is 80—and the service hasn't been restarted for a while and you aren't expecting any spikes in connections—you could reconfigure your my.cnf to allow for better performance for 80 connections instead of balancing your my.cnf around a 150 max_connections configuration. If your server doesn't need any more caching, for instance, then there may not be any need to reconfigure as once more, this is more about a maximum usage scenario.

This may not tell you what is using all of your RAM, but it can help you create a target point for your server to better control how many resources MySQL is allowed to consume and avoid an oomkiller/out of memory event.