What's the STATUS value of Threads_running? If it really is "~60-100 connection threads", then there could be any of a few temp allocations for the queries. Some allocations are per-subquery.
5.6 has a lot of new tricks for optimizing certain subqueries -- such as testing out all possible indexes, then creating the optimal one. Is the system I/O bound? Or CPU bound? Or neither? I recommend you optimize the queries. Provide us with EXPLAIN for the query you see most often in SHOW PROCESSLIST, together with SHOW TABLE STATUS and SHOW CREATE TABLE. The solution may be as easy as adding an index or turning a subquery into a JOIN. Granted, that would not help to nail down the suspected memory leak. > -----Original Message----- > From: Ilya Kazakevich [mailto:ilya.kazakev...@jetbrains.com] > Sent: Friday, April 19, 2013 2:40 PM > To: 'Denis Jedig'; mysql@lists.mysql.com > Subject: RE: Troubleshoot excessive memory usage in InnoDB > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
