1

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
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

-1

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.

  • RAM:12G engine:innodb Max_used_connections:80 Open_tables:497 – nyn Mar 17 '16 at 5:51
  • I added some more notes. Sorry, these may not address your original question, but they are important settings. – Rick James Mar 17 '16 at 6:46
  • Thank you for your answers. Maybe overkill, but using 800 partitions.We chased down our problem to glibc. I added more notes as an answer. – nyn Mar 17 '16 at 7:53
  • 800 partitions in one table will have performance problems. I can't say what kind of problem or how much problem; it depends on type of partitioning, certain caches, etc. – Rick James Mar 17 '16 at 23:54
  • What is being cached to table_cache? While monitoring table_cache, I noticed that only few kilobytes is used for a non-partitioned table, while 10MB - 20MB is used for a single table with 800 partitions. – nyn Mar 18 '16 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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