Memory leaks are unlikely, but possible. Upgrade to a newer version. Killing threads that say "Sleep" _may_ free up some memory, but unlikely to be more than even 1MB each. It _may_ cause grief for the developers, if they haven't bulletproofed their code enough to handle "lost connection". Mostly that frees up >thread_stack = 192K
Once you have followed the advice in http://mysql.rjweb.org/doc.php/memory there is not much more that an "infrastructure guy" can do. I have roots in many sides of this issue. Once I have tuned a system, I turn to the queries, schema, overall architecture, etc. Some caches act like there is a memory leak. What happens is that they grow as needed, up to some specified limit. This is especially visible for key_buffer_size. Query_cache_size = 256M may be hurting performance; I recommend no more than 50M. (The link explains.) Until the system starts swapping, there should be no problem with the growing memory usage. At that point, performance will tank. The quick fix is to decrease innodb_buffer_pool_size and/or key_buffer_size. If you provide SHOW GLOBAL STATUS and SHOW VARIABLES, I can look for other issues. > -----Original Message----- > From: Denis Jedig [mailto:d...@syneticon.net] > Sent: Saturday, April 20, 2013 2:16 AM > To: mysql@lists.mysql.com > Subject: Re: Troubleshoot excessive memory usage in InnoDB > > 19.04.2013 23:39, Ilya Kazakevich: > > > Try to use "tuning-primer.sh": this scripts reads your variables and > > prints memory size you need for that. > > I tried that. The results are inconspicious: > > MEMORY USAGE > Max Memory Ever Allocated : 5.27 G > Configured Max Per-thread Buffers : 1.92 G Configured Max Global > Buffers : 5.15 G Configured Max Memory Limit : 7.07 G Physical Memory : > 22.98 G Max memory limit seem to be within acceptable norms > > Although the logics behind the "tuning primer" script are rather simple > and I understand predicting the memory usage for MySQL is much harder: > http://www.mysqlperformanceblog.com/2012/03/21/troubleshooting-mysql- > memory-usage/ > > 20.04.2013 00:26, Rick James: > > > 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. > > Usually around 2-4. I also tried checking if killing / resetting > existing (idle) connections would significantly reduce memory usage > when mysqld has reached ~20 GB - it would not, so this is either not > related to connection states or the memory is leaking from there in a > way which would be unaffected by closing the connection. > > > Is the system I/O bound? Or CPU bound? Or neither? > > Neither - the system has plenty of headroom for both. The data working > set easily fits into the RAM, the amount of UPDATEs is negligible > (resulting in < 100 write requests per second for the I/O subsystem). > 1-minute load average is 2-3 under normal > (non-swapping) conditions with 6 CPU cores available. > > > I recommend you optimize the queries. > > I cannot do much about it. I am the infrastructure guy who is "fixing > the obviously broken DBMS". What I still cannot figure out is if the > behavior is due to a misconfiguration or a regression / bug to file. > And MySQL counters are not exactly helping - it is completely opaque to > me where the memory is going. > > -- > 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
