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 
