patternsqlMinor
MySQL hogging memory
Viewed 0 times
mysqlmemoryhogging
Problem
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:
It has grown as large as 50 GB once and by thus has significantly outgrown the data set itself:
Usually, I am able to free ~ 3 GB by issuing
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 ahe
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 mysqldIt 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 GUsually, 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 ahe
Solution
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%';Code Snippets
SHOW GLOBAL VARIABLES LIKE '%table_definition_cache%';Context
StackExchange Database Administrators Q#40413, answer score: 3
Revisions (0)
No revisions yet.