List:General Discussion« Previous MessageNext Message »
From:Rick James Date:April 19 2013 10:26pm
Subject:RE: Troubleshoot excessive memory usage in InnoDB
View as plain text  
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.Kazakevich@stripped]
> Sent: Friday, April 19, 2013 2:40 PM
> To: 'Denis Jedig'; mysql@stripped
> 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: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
> 
> 
> --
> 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