MySQL 使用了太多内存，并且从未释放其中的一些。
尽管 show full processlist 显示所有线程都处于休眠状态，但内存使用率过高。我还看到一些 mysql 命令（通过 htop）运行了 36 小时。
我的服务器是专用的，具有以下规格：
- Intel(R) Xeon(R) CPU E5-2620 v2 @ 2.10GHz（4 核）
- 16 GB 内存
- CentOS 版本 6.5（最终版）
- mysql 版本 14.14 发行版 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
mysql报告
__ 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
答案1
我以前遇到过这些问题，我发现这不是 SQL，而是 Apache 线程之一，通常会陷入无限循环。可能有很多原因，例如尝试访问不存在的位置或死锁。要查看是否是这种情况...请输入“top”并查看所有进程。有很多方法可以调试该特定进程/线程。
1.) 使用 gdb。2.) 重新启动 apache（或 nginx）并打开所有进程，然后转到各种函数调用，查看哪一个触发内存使用量从 .1 到 99.9%。