MySQL excessive memory usage

Question

I have an installation of MySQL which allocates a lot more memory than I expected. I'd like to understand where it has gone to fix the root cause.

To estimate the RAM usage I use this formula:

key_buffer_size + query_cache_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + Max_used_connections * (read_buffer_size + read_rnd_buffer_size + sort_buffer_size+ join_buffer_size+ binlog_cache_size + thread_stack + tmp_table_size)

This formula produces around 5.3 GB of estimated allocation. Instead, the RAM used by MySQL continues to grow and, after a couple of days of activity, it goes well over 9 GB (writing heavily into swap).

What am I forgetting? How can I understand who is eating the remaining RAM?

A few more pieces of information.

I'm running MySQL CE 5.6.17 on RHEL6.3 64 bits. My server has 6 GB RAM and 8GB swap space. I have constantly around 150 alive connections to MySQL but I'm keeping max_connections to higher values to accommodate peaks and foresee some more traffic on this machine. I'm only using InnoDB. My application uses pooled connections to the DB. The producers open connections and release them to the pool when they are done. I have measured that I always have several parallel requests, and pooling speeds up a little bit connections creation (so faster queries). The pool (DBCP) auto-tunes a reasonable number of alive connections and it's usually around 100-150. I have already tried:

• killing the connections does not free RAM
• FLUSH TABLES does not free RAM

Following my config:

---- my.cnf -----
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 1M
table_open_cache = 256
key_buffer_size = 128M
sort_buffer_size = 524288
read_buffer_size = 131072
read_rnd_buffer_size = 524288
myisam_sort_buffer_size = 524288
tmp_table_size=10M
performance_schema=0
thread_cache_size = 50
query_cache_type = OFF
query_cache_size = 0
server-id = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

innodb_data_home_dir = "/data/mysqldata"
innodb_log_group_home_dir = "/data1/mysqllog"
innodb_buffer_pool_size = 3000M
innodb_buffer_pool_instances = 3
innodb_log_file_size = 750M
innodb_flush_log_at_trx_commit = 2
datadir = "/data/mysqldata"
innodb_flush_method=O_DIRECT

max_connections = 1650

slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 10

autocommit=1
init-connect='SET NAMES utf8'
character-set-server = utf8

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
default-character-set=utf8

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
---------

This is the memory allocated to mysql after few days of activity:

[root@G-VelocityDB ~]# ps aux | grep mysql
root      1963  0.0  0.0 108332  1492 ?        S    May09   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/data/mysqldata --pid-file=/data/mysqldata/G-VelocityDB.pid
mysql     2583 80.4 88.3 9816560 8949704 ?     Sl   May09 3538:34 /usr/sbin/mysqld --basedir=/usr --datadir=/data/mysqldata --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/data/mysqldata/G-VelocityDB.err --pid-file=/data/mysqldata/G-VelocityDB.pid --socket=/var/lib/mysql/mysql.sock --port=3306
root     21450  0.0  0.0 103244   852 pts/1    S+   08:51   0:00 grep mysql

# cat /proc/2583/status
Name:   mysqld
State:  S (sleeping)
Tgid:   2583
Pid:    2583
PPid:   1963
TracerPid:      0
Uid:    496     496     496     496
Gid:    493     493     493     493
Utrace: 0
FDSize: 512
Groups: 493
VmPeak:  9882096 kB
VmSize:  9816560 kB
VmLck:         0 kB
VmHWM:   8950684 kB
VmRSS:   8949652 kB
VmData:  9771648 kB
VmStk:        88 kB
VmExe:     11684 kB
VmLib:      3928 kB
VmPTE:     17996 kB
VmSwap:        0 kB
Threads:        161
SigQ:   0/78996
SigPnd: 0000000000000000
ShdPnd: 0000000000000000
SigBlk: 0000000000087007
SigIgn: 0000000000001006
SigCgt: 00000001800066e9
CapInh: 0000000000000000
CapPrm: 0000000000000000
CapEff: 0000000000000000
CapBnd: ffffffffffffffff
Cpus_allowed:   3
Cpus_allowed_list:      0-1
Mems_allowed:   00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000001
Mems_allowed_list:      0
voluntary_ctxt_switches:        15496
nonvoluntary_ctxt_switches:     337

Some more info on my server:

GLOBAL STATUS:
Aborted_clients............ 237
Aborted_connects............. 10
Binlog_cache_disk_use.......... 0
Binlog_cache_use............. 0
Binlog_stmt_cache_disk_use......... 0
Binlog_stmt_cache_use.......... 0
Bytes_received............. 4194923507
Bytes_sent............. 11895871264
Com_admin_commands........... 455
Com_assign_to_keycache........... 0
Com_alter_db............. 0
Com_alter_db_upgrade........... 0
Com_alter_event............ 0
Com_alter_function........... 0
Com_alter_procedure.......... 0
Com_alter_server............. 0
Com_alter_table............ 0
Com_alter_tablespace........... 0
Com_alter_user............. 0
Com_analyze............ 0
Com_begin.............. 2
Com_binlog............. 0
Com_call_procedure........... 4868660
Com_change_db............ 8
Com_change_master............ 0
Com_check.............. 0
Com_checksum............. 0
Com_create_db............ 0
Com_create_event............. 0
Com_create_function.......... 0
Com_create_index............. 0
Com_create_procedure........... 0
Com_create_server............ 0
Com_create_table............. 0
Com_create_trigger........... 0
Com_create_udf............. 0
Com_create_user............ 0
Com_create_view............ 0
Com_dealloc_sql............ 0
Com_delete............. 19742
Com_delete_multi............. 0
Com_do............... 0
Com_drop_db............ 0
Com_drop_event............. 0
Com_drop_function............ 0
Com_drop_index............. 0
Com_drop_procedure........... 0
Com_drop_server............ 0
Com_drop_table............. 0
Com_drop_trigger............. 0
Com_drop_user............ 0
Com_drop_view............ 0
Com_empty_query............ 0
Com_execute_sql............ 0
Com_flush.............. 0
Com_get_diagnostics.......... 0
Com_grant.............. 0
Com_ha_close............. 0
Com_ha_open............ 0
Com_ha_read............ 0
Com_help............... 57
Com_insert............. 7149329
Com_insert_select............ 44
Com_install_plugin........... 0
Com_kill............... 0
Com_load............... 0
Com_lock_tables............ 0
Com_optimize............. 0
Com_preload_keys............. 0
Com_prepare_sql............ 0
Com_purge.............. 0
Com_purge_before_date.......... 0
Com_release_savepoint.......... 0
Com_rename_table............. 0
Com_rename_user............ 0
Com_repair............. 0
Com_replace............ 0
Com_replace_select........... 0
Com_reset.............. 0
Com_resignal............. 0
Com_revoke............. 0
Com_revoke_all............. 0
Com_rollback............. 6366209
Com_rollback_to_savepoint.......... 0
Com_savepoint............ 0
Com_select............. 16223159
Com_set_option............. 13471816
Com_signal............. 0
Com_show_binlog_events........... 0
Com_show_binlogs............. 0
Com_show_charsets............ 0
Com_show_collations.......... 632
Com_show_create_db........... 0
Com_show_create_event.......... 0
Com_show_create_func........... 0
Com_show_create_proc........... 0
Com_show_create_table.......... 0
Com_show_create_trigger.......... 0
Com_show_databases........... 21
Com_show_engine_logs........... 0
Com_show_engine_mutex.......... 0
Com_show_engine_status........... 0
Com_show_events............ 0
Com_show_errors............ 0
Com_show_fields............ 101
Com_show_function_code........... 0
Com_show_function_status........... 3
Com_show_grants............ 0
Com_show_keys............ 9
Com_show_master_status........... 0
Com_show_open_tables........... 0
Com_show_plugins............. 2
Com_show_privileges.......... 0
Com_show_procedure_code.......... 0
Com_show_procedure_status.......... 3
Com_show_processlist........... 0
Com_show_profile............. 0
Com_show_profiles............ 0
Com_show_relaylog_events........... 0
Com_show_slave_hosts........... 0
Com_show_slave_status.......... 2
Com_show_status............ 805
Com_show_storage_engines........... 0
Com_show_table_status.......... 0
Com_show_tables............ 13
Com_show_triggers............ 0
Com_show_variables........... 667
Com_show_warnings............ 0
Com_slave_start............ 0
Com_slave_stop............. 0
Com_stmt_close............. 0
Com_stmt_execute............. 0
Com_stmt_fetch............. 0
Com_stmt_prepare............. 0
Com_stmt_reprepare........... 0
Com_stmt_reset............. 0
Com_stmt_send_long_data.......... 0
Com_truncate............. 0
Com_uninstall_plugin........... 0
Com_unlock_tables............ 0
Com_update............. 168248
Com_update_multi............. 0
Com_xa_commit............ 0
Com_xa_end............. 0
Com_xa_prepare............. 0
Com_xa_recover............. 0
Com_xa_rollback............ 0
Com_xa_start............. 0
Compression............ OFF
Connection_errors_accept........... 0
Connection_errors_internal......... 0
Connection_errors_max_connections........ 0
Connection_errors_peer_address......... 0
Connection_errors_select........... 0
Connection_errors_tcpwrap.......... 0
Connections............ 670
Created_tmp_disk_tables.......... 838
Created_tmp_files............ 6
Created_tmp_tables........... 5214
Delayed_errors............. 0
Delayed_insert_threads........... 0
Delayed_writes............. 0
Flush_commands............. 1
Handler_commit............. 30081211
Handler_delete............. 6300384
Handler_discover............. 0
Handler_external_lock.......... 67018766
Handler_mrr_init............. 0
Handler_prepare............ 0
Handler_read_first........... 11247573
Handler_read_key............. 22140615
Handler_read_last............ 0
Handler_read_next............ 1099116268
Handler_read_prev............ 26294847
Handler_read_rnd............. 38722
Handler_read_rnd_next.......... 22705321
Handler_rollback............. 60
Handler_savepoint............ 0
Handler_savepoint_rollback......... 0
Handler_update............. 167974
Handler_write............ 7852897
Innodb_buffer_pool_dump_status......... not started
Innodb_buffer_pool_load_status......... not started
Innodb_buffer_pool_pages_data........ 186890
Innodb_buffer_pool_bytes_data........ 3062005760
Innodb_buffer_pool_pages_dirty......... 6539
Innodb_buffer_pool_bytes_dirty......... 107134976
Innodb_buffer_pool_pages_flushed......... 584944
Innodb_buffer_pool_pages_free........ 3072
Innodb_buffer_pool_pages_misc........ 2035
Innodb_buffer_pool_pages_total......... 191997
Innodb_buffer_pool_read_ahead_rnd........ 0
Innodb_buffer_pool_read_ahead........ 129315
Innodb_buffer_pool_read_ahead_evicted...... 0
Innodb_buffer_pool_read_requests......... 531706692
Innodb_buffer_pool_reads........... 215710
Innodb_buffer_pool_wait_free......... 0
Innodb_buffer_pool_write_requests........ 84850509
Innodb_data_fsyncs........... 445789
Innodb_data_pending_fsyncs......... 0
Innodb_data_pending_reads.......... 0
Innodb_data_pending_writes......... 0
Innodb_data_read............. 5930536960
Innodb_data_reads............ 379282
Innodb_data_writes........... 3707043
Innodb_data_written.......... 24355757056
Innodb_dblwr_pages_written......... 584944
Innodb_dblwr_writes.......... 16463
Innodb_have_atomic_builtins........ ON
Innodb_log_waits............. 0
Innodb_log_write_requests.......... 8093999
Innodb_log_writes............ 3091626
Innodb_os_log_fsyncs........... 168887
Innodb_os_log_pending_fsyncs......... 0
Innodb_os_log_pending_writes......... 0
Innodb_os_log_written.......... 5187414528
Innodb_page_size............. 16384
Innodb_pages_created........... 76352
Innodb_pages_read............ 358104
Innodb_pages_written........... 584944
Innodb_row_lock_current_waits........ 0
Innodb_row_lock_time........... 17326
Innodb_row_lock_time_avg........... 18
Innodb_row_lock_time_max........... 1027
Innodb_row_lock_waits.......... 946
Innodb_rows_deleted.......... 6300384
Innodb_rows_inserted........... 7149273
Innodb_rows_read............. 1140478060
Innodb_rows_updated.......... 164739
Innodb_num_open_files.......... 300
Innodb_truncated_status_writes......... 0
Innodb_available_undo_logs......... 128
Key_blocks_not_flushed........... 0
Key_blocks_unused............ 107146
Key_blocks_used............ 25
Key_read_requests............ 811
Key_reads.............. 25
Key_write_requests........... 0
Key_writes............. 0
Last_query_cost............ 0.000000
Last_query_partial_plans........... 0
Max_used_connections........... 172
Not_flushed_delayed_rows........... 0
Open_files............. 11
Open_streams............. 0
Open_table_definitions........... 69
Open_tables............ 256
Opened_files............. 3701
Opened_table_definitions........... 69
Opened_tables............ 308

Prepared_stmt_count.......... 0

Queries.............. 54874967
Questions.............. 49932690
Select_full_join............. 1196
Select_full_range_join........... 0
Select_range............. 494248
Select_range_check........... 0
Select_scan............ 9551157

Slow_launch_threads.......... 0
Slow_queries............. 63
Sort_merge_passes............ 0
Sort_range............. 1
Sort_rows.............. 45942
Sort_scan.............. 2550

Table_locks_immediate.......... 26010899
Table_locks_waited........... 0
Table_open_cache_hits.......... 24039437
Table_open_cache_misses.......... 308
Table_open_cache_overflows......... 45
Tc_log_max_pages_used.......... 0
Tc_log_page_size............. 0
Tc_log_page_waits............ 0
Threads_cached............. 47
Threads_connected............ 102
Threads_created............ 282
Threads_running............ 1
Uptime............... 171023
Uptime_since_flush_status.......... 171023
Com_commit............. 6536509


SHOW ENGINE INNODB STATUS;
Per second averages calculated from the last 17 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 268650 srv_active, 0 srv_shutdown, 12135 srv_idle
srv_master_thread log flush and writes: 280785
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1688081
OS WAIT ARRAY INFO: signal count 957822
Mutex spin waits 1873319, rounds 44188913, OS waits 1295453
RW-shared spins 469687, rounds 12569665, OS waits 334361
RW-excl spins 35863, rounds 1100713, OS waits 24644
Spin rounds per wait: 23.59 mutex, 26.76 RW-shared, 30.69 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-05-07 17:22:56 7f0d94a1a700
*** (1) TRANSACTION:
TRANSACTION 1119978000, ACTIVE 0 sec starting index read
mysql tables in use 10, locked 10
LOCK WAIT 7 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 69, OS thread handle 0x7f0d91c0d700, query id 73382628 g-velocity.abodata.domain 10.0.2.180 platone updating
DELETE  FROM PacketDay PARTITION(p1730,p1731,p1732,p1733,p1734,p1735,p1736,p1737,p1738,p1739)  WHERE XXXX=4 AND StartTime<1399388476900 LIMIT 50000
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5831 page no 27 n bits 224 index `PRIMARY` of table `ViewDb`.`PacketDay` /* Partition `p1732` */ trx id 1119978000 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 17; compact format; info bits 32
 0: len 8; hex 80000145cf7ab51d; asc    E z  ;;
 1: len 4; hex 80000e94; asc     ;;
 2: len 6; hex 000042c1820e; asc   B   ;;
 3: len 7; hex 65000480151812; asc e      ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 8; hex 80000145cf7ab51d; asc    E z  ;;
 6: len 8; hex 8000000000000000; asc         ;;
 7: len 4; hex 80000004; asc     ;;
 8: len 4; hex 80000003; asc     ;;
 9: SQL NULL;
 10: len 8; hex 0000000000001c40; asc        @;;
 11: len 0; hex ; asc ;;
 12: len 1; hex 30; asc 0;;
 13: len 2; hex 8001; asc   ;;
 14: len 4; hex 80000000; asc     ;;
 15: len 8; hex 80000145cf7ab3b6; asc    E z  ;;
 16: len 24; hex 5b303d4f66662c20313d52696768742c20323d4c6566745d; asc [0=Off, 1=Right, 2=Left];;

*** (2) TRANSACTION:
TRANSACTION 1119977998, ACTIVE 0 sec updating or deleting
mysql tables in use 10, locked 10
32 lock struct(s), heap size 6544, 6 row lock(s), undo log entries 1
MySQL thread id 36, OS thread handle 0x7f0d94a1a700, query id 73382652 g-velocity.abodata.domain 10.0.2.180 platone updating
DELETE  FROM PacketDay PARTITION(p1730,p1731,p1732,p1733,p1734,p1735,p1736,p1737,p1738,p1739)  WHERE XXXX=5 AND StartTime<1399388476871 LIMIT 50000
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5831 page no 27 n bits 224 index `PRIMARY` of table `ViewDb`.`PacketDay` /* Partition `p1732` */ trx id 1119977998 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 17; compact format; info bits 32
 0: len 8; hex 80000145cf7ab51d; asc    E z  ;;
 1: len 4; hex 80000e94; asc     ;;
 2: len 6; hex 000042c1820e; asc   B   ;;
 3: len 7; hex 65000480151812; asc e      ;;
 4: len 4; hex 80000005; asc     ;;
 5: len 8; hex 80000145cf7ab51d; asc    E z  ;;
 6: len 8; hex 8000000000000000; asc         ;;
 7: len 4; hex 80000004; asc     ;;
 8: len 4; hex 80000003; asc     ;;
 9: SQL NULL;
 10: len 8; hex 0000000000001c40; asc        @;;
 11: len 0; hex ; asc ;;
 12: len 1; hex 30; asc 0;;
 13: len 2; hex 8001; asc   ;;
 14: len 4; hex 80000000; asc     ;;
 15: len 8; hex 80000145cf7ab3b6; asc    E z  ;;
 16: len 24; hex 5b303d4f66662c20313d52696768742c20323d4c6566745d; asc [0=Off, 1=Right, 2=Left];;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5831 page no 28 n bits 824 index `idx_XXXX_YYYY` of table `AAAA`.`PacketDay` /* Partition `p1732` */ trx id 1119977998 lock_mode X locks rec but not gap waiting
Record lock, heap no 686 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 8; hex 80000145cf7ab51d; asc    E z  ;;
 2: len 4; hex 80000e94; asc     ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 1136364858
Purge done for trx's n:o < 1136364106 undo n:o < 0 state: running but idle
History list length 984
LIST OF TRANSACTIONS FOR EACH SESSION:
[...]
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1320822 OS file reads, 8503653 OS file writes, 2583777 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.12 writes/s, 0.06 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 67353, seg size 67355, 5035 merges
merged operations:
 insert 65890, delete mark 110773, delete 319
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 6225523, node heap has 1792 buffer(s)
0.06 hash searches/s, 0.94 non-hash searches/s
---
LOG
---
Log sequence number 291212776237
Log flushed up to   291212776237
Pages flushed up to 291114806316
Last checkpoint at  291114806316
0 pending log writes, 0 pending chkp writes
4968792 log i/o's done, 0.12 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 3219456000; in additional pool allocated 0
Dictionary memory allocated 36167844
Buffer pool size   191997
Free buffers       3072
Database pages     187133
Old database pages 69022
Modified db pages  8213
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 653753, not young 38903623
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1310744, created 130762, written 3414584
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 187133, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   63999
Free buffers       1024
Database pages     62402
Old database pages 23015
Modified db pages  2482
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 197235, not young 11921942
0.00 youngs/s, 0.00 non-youngs/s
Pages read 423522, created 40807, written 1078968
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 62402, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   63999
Free buffers       1024
Database pages     62366
Old database pages 23006
Modified db pages  2909
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 219800, not young 13508215
0.00 youngs/s, 0.00 non-youngs/s
Pages read 439314, created 44996, written 1162507
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 62366, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   63999
Free buffers       1024
Database pages     62365
Old database pages 23001
Modified db pages  2822
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 236718, not young 13473466
0.00 youngs/s, 0.00 non-youngs/s
Pages read 447908, created 44959, written 1173109
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 62365, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
7 read views open inside InnoDB
Main thread process no. 5965, id 139696323659520, state: sleeping
Number of rows inserted 13664081, updated 230718, deleted 8001095, read 31517028476
0.06 inserts/s, 0.06 updates/s, 0.00 deletes/s, 43.59 reads/s


select sum(data_length+index_length) from information_schema.tables where engine='memory';
Result: '0'

As suggested, I have run with and without partitioning enabled on my server (I have 5 tables with 2000 partitions each), and seems that disabling partitioning helps in some way. But still I have not clear the reason, and I cannot eliminate partitioning at all.

As suggested, I have run some basic test running mysql under valgrind for leak detection: http://pastebin.com/W3ZMAGY4

And massif: http://pastebin.com/LweQHn3M These tests are executed with MySQL default configuration.

If I'm reading the output correctly, no relevant leak is found and I have a huge memory consumption per connection (500 MB each connection).

Is that correct? Any idea why it grows so much?

Answer 1

The InnoDB data dictionary can grow without bounds as you open many tables, beyond innodb_additional_mem_pool_size, and it often does grow huge if you have thousands of tables. This would be independent of the number of connections.

I've seen other people report that MySQL 5.6 has a lot of memory usage, but we haven't tracked it down definitively. One would have to run mysqld under valgrind to track memory growth.

MySQL 5.7 is still under development, but they are creating new PERFORMANCE_SCHEMA instruments to track memory usage.

---

If the memory reported by the InnoDB data dictionary alone doesn't account for the memory growth, I suggest reading the following bug reports to see if they apply in your case:

• Bug #72322 Query to I_S.tables and I_S.columns leads to huge memory usage
• Bug #68514 5.6 huge memory usage with max_connections parameter
• Bug #57480 Memory leak when having 256+ tables