An installation of MySQL 5.6.10 on a virtualized Ubuntu 12.04 is exhibiting massive memory hogging. The mysqld process claims the entire available memory within a couple of hours of uptime and forces the host to swap:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
16229 mysql 20 0 26.8g 21g 8736 S 42 93.4 37:23.22 mysqld
It has grown as large as 50 GB once and by thus has significantly outgrown the data set itself:
Current InnoDB index space = 5.25 G
Current InnoDB data space = 23.07 G
Usually, I am able to free ~ 3 GB by issuing FLUSH TABLES, although it is considerably faster to just kill -9 the mysql process, have it re-started and have recovery run for InnoDB. The tables used are nearly exclusively InnoDB, the innodb_buffer_pool_size has been set to 5 GB (after setting it to 16 GB quickly depleted the available physical memory and swapped out more than 18 GB of it).
While the system was swapping, I could observe rather high numbers for "swap out" counters (vmstat is showing ~1k pages/second during bursts) and hardly anything at all swapped back in (few dozens of pages per minute). I first suspected memory leakage but have not found anything supporting this hypothesis so far.
SHOW INNODB STATUS indicates that the buffer pool is only partially filled:
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 5365825536; in additional pool allocated 0
Dictionary memory allocated 2558496
Buffer pool size 320000
Free buffers 173229
Database pages 142239
Old database pages 52663
Modified db pages 344
Pending reads 1
Pending writes: LRU 0, flush list 1 single page 0
Pages made young 34, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 141851, created 387, written 41126
81.16 reads/s, 0.00 creates/s, 0.39 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 142239, unzip_LRU len: 0
I/O sum[0]:cur[464], unzip sum[0]:cur[0]
The server has a total of 80-90 connections most of which are reported to be in "Sleep" state by SHOW PROCESSLIST.
The memory-sensitive options set are
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
max_connections = 1000
innodb_file_format = Barracuda
innodb_buffer_pool_size = 5000M
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
query_cache_limit = 1M
query_cache_size = 256M
join_buffer_size = 256k
tmp_table_size = 2M
max_heap_table_size = 64M
The tuning-primer.sh script calculates sane values for memory usage:
MEMORY USAGE
Max Memory Ever Allocated : 5.27 G
Configured Max Per-thread Buffers : 1.92 G
Configured Max Global Buffers : 5.15 G
Configured Max Memory Limit : 7.07 G
Physical Memory : 22.98 G
Max memory limit seem to be within acceptable norms
Binlog is enabled and the host has a replication slave attached to it (although results were not all that different at the time this has not been the case). Innodb_file_per_table is enabled by default in 5.6 and the databases are hosting a total of ~ 1,300 tables.
What means do I have to identify the possible causes for the apparently unlimited growth?
After reading "How MySQL uses memory" I had the suspicion that temporary tables might be the culprit. If they are not being released correctly for whatever reason, they could accumulate pretty quickly. The application querying the database issues a lot of nested, complicated queries, so temporary tables would be heavily in use according to the referenced docs. I tried checking if killing / resetting existing (idle) connections would significantly reduce memory usage when mysqld has reached ~20 GB - it would not, so this is either not related to connection states or the memory is leaking from there in a way which would be unaffected by closing the connection.
How would I verify if in-memory temporary tables are occupying a significant amount of memory? The STATUS variables and the INFORMATION_SCHEMA do not seem to have this information.
MySQL's memory usage appears hard to debug - the counters available seem not to account for the larger part of the usage I am seeing. I might be missing something, though.
I also have a MyISAM-based replication slave attached to the InnoDB master taking similar (read-only) loads - it does not show any signs of excessive memory usage (mysqld RSS is continuously < 1GB) , so the problem appears to be specific to the InnoDB configuration.
my.cnfinto the question.