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:d...@syneticon.net] >Sent: Saturday, April 20, 2013 1:17 AM >To: mysql@lists.mysql.com >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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
