Contiunous rise of mysqld memory consumption

Question

Mysqld memory consumption rises forever and never seems to be freed. It starts out about 6GB but gradually rises to around 10GB in a few weeks, and of the 10GB only 4GB and 50MB is being used by the buffer pool and the dictionary respectively when checking innodb status.

This is with MySQL 5.6.16 on a server with 12GB memory. A few of the tables are partioned and there are roughly 8000 idb files. Also one table is created each day.

I have tried to FLUSH TABLES with no success. The tables are closed but the memory does not get freed at all. In fact, more memory gets consumed.

Why is the memory being consumed? And are there any known issues with memory not being freed when using partioned tables?

my.cnf

query_cache_size = 512M
query_cache_limit = 16M
max_allowed_packet = 16M
table_open_cache = 1024
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 2MB
myisam_sort_buffer_size = 1M
max_connections = 1024
thread_cache = 1024
tmp_table_size = 16M
max_heap_table_size = 16M
wait_timeout = 20
join_buffer_size = 256KB
thread_cache_size = 50
table_definition_cache = 400
key_buffer_size = 256M

Answer 1

We tracked it down to glibc. With glibc-2.12-1.149.el6_6.4.x86_64, memory is not fully freed when closing tables.

It seems that under high concurrent connections, accessing partitioned tables increases Open_tables which increases memory usage, however memory is not fully freed when the table is closed using glibc.

Newer glibcs faced the same problem as well. Switching to jemalloc-3.6.0-1.el6.x86_64 has resolved our problem.

Test environment
CentOS release 6.6 (Final)
kernel 2.6.32-504.el6.x86_64
mysql-5.6.16

physical ram 2G
bufferpool 256M
other default

memory usage with gblic-2.12-1.166.el6_7.7.x86_64
1st round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.3G
"flush table" -> 1.0G

2nd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.6G
"flush table" -> 1.2G

3rd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.6G
"flush table" -> 1.4G

memory usage with jemalloc-3.6.0-1.el6.x86_6
1st round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.3G
"flush table" -> 582M

2nd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.3G
"flush table" -> 583M

3rd round
mysqlslap concurrent connection: 70, accessed partions: 800 -> 1.2G
"flush table" -> 584M

Answer 2

8000 idb files ... Also one table is created each day.

Not a good design. Please provide details so we can discuss viable alternatives.

How many of those 8000 are you using on a daily basis?

query_cache_size = 512M

Over about 50M, the QC causes spikes of poor performance.

How much RAM do you have?

Are you using InnoDB? Or MyISAM? Or Both?

What is the STATUS value of Max_used_connections?

For only InnoDB on 12GB machine, key_buffer_size=20M and innodb_buffer_pool_size=8G.

You mentioned "partitioning"; how many partitions in any table? About 50 is a practical upper limit; much more than that and performance suffers noticeably.