MySQL High Ram Usage?

Question

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?

Answer 1

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.

Answer 2

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.