List:General Discussion« Previous MessageNext Message »
From:Ilya Kazakevich Date:April 19 2013 9:39pm
Subject:RE: Troubleshoot excessive memory usage in InnoDB
View as plain text  
Hello,

Try to use "tuning-primer.sh": this scripts reads your variables  and prints
memory size you need for that.

Here is example of its output:
MEMORY USAGE
Max Memory Ever Allocated : 2.86 G
Configured Max Per-thread Buffers : 1.80 G
Configured Max Global Buffers : 2.10 G
Configured Max Memory Limit : 3.91 G
Physical Memory : 5.82 G

I am not sure if it works correctly with 5.6


Ilya

>-----Original Message-----
>From: Denis Jedig [mailto:dj@stripped]
>Sent: Saturday, April 20, 2013 1:17 AM
>To: mysql@stripped
>Subject: Troubleshoot excessive memory usage in InnoDB
>
>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.
>
>http://dev.mysql.com/doc/refman/5.6/en/memory-use.html 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
><http://dba.stackexchange.com/questions/40413> which I will
>update with further information as I get it.
>
>Any hints on how to hunt the resource hog greatly appreciated,
>--
>Denis Jedig
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql

Thread
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