List:General Discussion« Previous MessageNext Message »
From:Denis Jedig Date:April 19 2013 9:17pm
Subject:Troubleshoot excessive memory usage in InnoDB
View as plain text  
Hi all.

In a specific MySQL installation of 5.6.10 using InnoDB tables, I 
am observing unusual memory consumption patterns. The memory 
usage is growing constantly - even beyond the physical memory 
limits. The entire on-disk storage is 41 GB (uncompressed), yet 
memory usage is happily growing to values larger than 50 GB.

The databases mainly experience read load with complex queries 
and subSELECTs running ~60-100 connection threads.

Although the docs state that "there should be no memory leaks", 
this case certainly looks like one at first glance. suggests 
that temporary in-memory tables would be used  for this purpose 
so I suspected unfreed temporary tables to be the culprit. But 
memory usage growth rates did not change significantly even after 
lowering tmp_table_size to 2M (from 64M). Also, I have been 
unable to find a way to determine the size of in-memory temporary 
tables at any given time.

Some of the STATUS counters:

| Com_select                                    | 424614      |
| Com_update                                    | 3444        |
| Created_tmp_disk_tables                       | 1716        |
| Created_tmp_files                             | 43          |
| Created_tmp_tables                            | 4002        |
| Uptime                                        | 5112        |

The total number of tables over all databases is 1370. my.cnf 
contains the following memory-related values:

max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
max_connections        = 1000
innodb_buffer_pool_size = 5000M
innodb_log_file_size    = 256M
innodb_flush_method     = O_DIRECT
query_cache_limit       = 1M
query_cache_size        = 256M
join_buffer_size        = 256k
tmp_table_size          = 2M
max_heap_table_size     = 64M
read_buffer_size        = 1M
ft_min_word_len = 3
open_files_limit        = 10000

A replication slave of this very host is running 5.6.10 with 
MyISAM tables and the mysqld process does not exceed 1 GB in 
memory utilization even after several hours of operation under 
similar load.

I have posted a question to 
<> which I will 
update with further information as I get it.

Any hints on how to hunt the resource hog greatly appreciated,
Denis Jedig
Troubleshoot excessive memory usage in InnoDBDenis Jedig19 Apr
  • RE: Troubleshoot excessive memory usage in InnoDBIlya Kazakevich19 Apr
    • RE: Troubleshoot excessive memory usage in InnoDBRick James19 Apr
    • Re: Troubleshoot excessive memory usage in InnoDBDenis Jedig20 Apr
      • RE: Troubleshoot excessive memory usage in InnoDBRick James29 Apr