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
Best Answer
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% .