Mysql – Contiunous rise of thesqld memory consumption

memoryMySQLmysql-5.6

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

Best Answer

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

Related Solutions

MySQL Replication: Insert generating VERY huge temporary tables

Algorithmically, it is doing what your colleague says. But, do you see what it is doing ???

It is generating 10,000 temp tables each containing 1 row after traversing 317 million rows through in the InnoDB internal index. Each temp table is a complete regeneration of the rowids in sernumbers_results_2009 table along with executing handler_read_prev commands internally to sort the data by an index scan from the back of the internal rowid index. Also, please remember you are dealing with InnoDB. Who knows what Multiversioning (via MVCC) is going on so that the INSERT is completed without interference and with rollback capabilities.

Is there any reason why this query wouldn't work for you ???

INSERT INTO sernumbers_results_2009
SELECT * FROM sernumbers_results
ORDER BY rowid DESC LIMIT 10000;

This will definitely generate one temp table.

Give it a Try !!!

MySQL database drop insanely slow

I hate the checking permissions issue.

You may have to disable key checks before the DROP DATABASE

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.