List:General Discussion« Previous MessageNext Message »
From:Denis Jedig Date:April 20 2013 9:15am
Subject:Re: Troubleshoot excessive memory usage in InnoDB
View as plain text  
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
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