Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What does mysqltuner.pl (github.com/rackerhacker/MySQLTuner-perl) tell you about your server configuration? –  Chris Apr 26 '13 at 12:00
    
Please post the entire my.cnf into the question. –  RolandoMySQLDBA Apr 29 '13 at 14:47
    
Please test with 5.6.11. There are many serious bugs fixed in this second GA release, it is possible that memory leaks too. –  Laurynas Biveinis May 2 '13 at 17:59
    
Have you figured it out? I just upgraded a relatively big database to 5.6 and I'm having the same problem, the memory usage will grow until the machine runs out of memory or I restart the database. –  Aleksandar Ivanisevic Sep 19 '13 at 17:51
    
@AleksandarIvanisevic Nope, I still can see the effect with the pre-current GA release 5.6.12. Apparently, the issue only arises with complex queries using multiple tables. –  syneticon-dj Sep 19 '13 at 20:08

2 Answers 2

A bug filed here (http://bugs.mysql.com/bug.php?id=68287) looks like it is potentially relevant - check what table_definition_cache is set to by running:

SHOW GLOBAL VARIABLES LIKE '%table_definition_cache%';
share|improve this answer
up vote 0 down vote accepted

Testing with the current GA release MySQL 5.6.19 revealed that the issue is gone.

Reading through the release notes of intermediate releases, I noted this particular note in 5.6.14 which I suspect to be related to our problem since it explicitly refers to subquery clauses which are in excessiveuse in our case:

For some statements, memory leaks could result when the optimizer removed unneeded subquery clauses. (Bug #16807641)

References: This bug is a regression of Bug #15875919.

Oracle unfortunately is not publishing bugs, so this is all the technical detail we can get on the issue.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.