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

First, you should know that once MySQL uses memory, it never frees it. It keeps it around for the next query.

Here's my query for figuring out the maximum amount of memory my InnoDB DB will use:

SELECT ( @@key_buffer_size
       + @@query_cache_size
       + @@innodb_buffer_pool_size
       + @@innodb_log_buffer_size
       + @@max_allowed_packet
       + @@max_connections * ( @@read_buffer_size
       + @@read_rnd_buffer_size
       + @@sort_buffer_size
       + @@join_buffer_size
       + @@binlog_cache_size
       + 2*@@net_buffer_length
       + @@thread_stack
       + @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;

To see a breakdown of how much memory is being used for different things:

-- To see a breakdown of what's using how much memory:
SELECT @@key_buffer_size/(1024*1024) as `key_buffer_size_IN_MB`,
       @@query_cache_size/(1024*1024) as 'query_cache_size_IN_MB',
       @@innodb_buffer_pool_size/(1024*1024) as 'innodb_buffer_pool_size_IN_MB',
       @@innodb_log_buffer_size/(1024*1024) as 'innodb_log_buffer_size_IN_MB',
       @@max_connections*@@read_buffer_size/(1024*1024) as '@@read_buffer_size_IN_MB',
       @@max_connections*@@read_rnd_buffer_size/(1024*1024) as 'read_rnd_buffer_size_IN_MB',
       @@max_connections*@@sort_buffer_size/(1024*1024) as 'sort_buffer_size_IN_MB',
       @@max_connections*@@join_buffer_size/(1024*1024) as 'join_buffer_size_IN_MB',
       @@max_connections*@@binlog_cache_size/(1024*1024) as 'binlog_cache_size_IN_MB',
       @@max_connections*@@thread_stack/(1024*1024) as 'thread_stack_IN_MB',
       @@max_connections*@@tmp_table_size/(1024*1024) as 'tmp_table_size_IN_MB',
       @@max_connections*@@net_buffer_length*2/(1024*1024) as 'net_buffer_size_IN_MB'
       ;