MySQL is using too much memory, and never freeing it

Question

MySQL is using too much memory, and never freeing some of it.

Despite show full processlist show that all threads are aslept, memory usage is too high. Also I see that some mysql commands (via htop) are running for 36 hs.

My server is a dedicated one, with the following specifications:

• Intel(R) Xeon(R) CPU E5-2620 v2 @ 2.10GHz (4 cores)
• 16 GB RAM
• CentOS release 6.5 (Final)
• mysql Ver 14.14 Distrib 5.1.71

/etc/my.cnf:

[mysqld]
max_connect_errors = 800
max_allowed_packet = 64M

federated

max_connections = 300

skip-name-resolve
skip-host-cache

key_buffer_size=1G
thread_cache=16

table_cache=2048
key_buffer=1024M
myisam_repair_threads=2
sort_buffer_size=4M
read_buffer_size=2M
join_buffer_size=2M
query_cache_size=2048M
query_cache_limit=8M
read_rnd_buffer_size=1M
myisam_sort_buffer_size=64M

symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

mysqlreport

__ Key _________________________________________________________________
Buffer used   134.39M of   1.00G  %Used:  13.12
  Current     289.81M            %Usage:  28.30
Write hit      99.93%
Read hit      100.00%

__ Questions ___________________________________________________________
Total          70.65M   203.7/s
  DMS          39.84M   114.9/s  %Total:  56.39
  QC Hits      28.63M    82.5/s           40.52
  Com_          3.68M    10.6/s            5.21
  -Unknown      2.29M     6.6/s            3.24
  COM_QUIT    791.42k     2.3/s            1.12
Slow 10 s         243     0.0/s            0.00  %DMS:   0.00  Log: OFF
DMS            39.84M   114.9/s           56.39
  REPLACE      29.80M    85.9/s           42.18         74.81
  SELECT        9.51M    27.4/s           13.46         23.88
  INSERT      391.36k     1.1/s            0.55          0.98
  UPDATE      123.07k     0.4/s            0.17          0.31
  DELETE       10.32k     0.0/s            0.01          0.03
Com_            3.68M    10.6/s            5.21
  admin_comma   2.42M     7.0/s            3.42
  change_db   965.13k     2.8/s            1.37
  set_option  177.48k     0.5/s            0.25

__ SELECT and Sort _____________________________________________________
Scan          683.92k     2.0/s %SELECT:   7.19
Range         152.16k     0.4/s            1.60
Full join     142.13k     0.4/s            1.49
Range check    10.77k     0.0/s            0.11
Full rng join   2.10k     0.0/s            0.02
Sort scan      53.07M   153.0/s
Sort range      3.02M     8.7/s
Sort mrg pass   1.56k     0.0/s

__ Query Cache _________________________________________________________
Memory usage  122.83M of   2.00G  %Used:   6.00
Block Fragmnt  12.61%
Hits           28.63M    82.5/s
Inserts         9.20M    26.5/s
Insrt:Prune   9.20M:1    26.5/s
Hit:Insert     3.11:1

__ Table Locks _________________________________________________________
Waited         78.39k     0.2/s  %Total:   0.14
Immediate      55.41M   159.8/s

__ Tables ______________________________________________________________
Open             1545 of 2048    %Cache:  75.44
Opened          2.11k     0.0/s

__ Connections _________________________________________________________
Max used          171 of  300      %Max:  57.00
Total         791.74k     2.3/s

__ Created Temp ________________________________________________________
Disk table     63.96k     0.2/s
Table           2.69M     7.8/s    Size:  16.0M
File            3.12k     0.0/s

__ Threads _____________________________________________________________
Running             2 of  136
Cached             11 of   16      %Hit:  99.90
Created           805     0.0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients           629     0.0/s
Connects            4     0.0/s

__ Bytes _______________________________________________________________
Sent           99.96G  288.2k/s
Received       17.70G   51.0k/s

__ InnoDB Buffer Pool __________________________________________________
Usage           8.00M of   8.00M  %Used: 100.00
Read hit       98.23%
Pages
  Free              0            %Total:   0.00
  Data            501                     97.85 %Drty:   0.00
  Misc             11                      2.15
  Latched                                  0.00
Reads           9.54G   27.5k/s
  From file   168.38M   485.5/s            1.77
  Ahead Rnd   4625963    13.3/s
  Ahead Sql  22657091    65.3/s
Writes          4.03M    11.6/s
Flushes       212.50k     0.6/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits               1     0.0/s
Current             0
Time acquiring
  Total           927 ms
  Average         927 ms
  Max             927 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads       212.56M   612.9/s
  Writes      286.44k     0.8/s
  fsync       118.77k     0.3/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created       8.34k     0.0/s
  Read        539.47M    1.6k/s
  Written     212.50k     0.6/s

Rows
  Deleted      10.37k     0.0/s
  Inserted    312.27k     0.9/s
  Read          5.89G   17.0k/s
  Updated      43.99k     0.1/s

Answer 1

I have encountered these issues previously and I discovered that its not SQL but its one of the apache threads that usually gets stuck in infinite loop. Their may be many reasons such as trying to access a location that doesn't exists or dead locks. To see if that's the case ... type "top" and see all the processes. their are many ways to debug that particular process/thread.

1.) Use gdb. 2.) Restart your apache (or nginx) and open all the processes and then go to various function calls and see which one triggers memory usage from .1 to 99.9% .