再会，
当 MySQL 开始使用系统中的所有内存时，我遇到了一个问题。MySQL 重启后，内存被释放，但一段时间后又被填满。
内存使用情况图表： http://netmaster.com.ua/files/sys_mem_stat.png
MySQL 和系统设置均未发生任何变化。这种情况大概是在添加 Wordpress 数据库后出现的。服务器仅包含 28 个数据库，除 Wordpress 数据库外，几乎所有数据库都使用 InnoDB。
以下是详细描述：
uname -a
Linux Server 2.6.32-31-server #61-Ubuntu SMP Fri Apr 8 19:44:42 UTC 2011 x86_64 GNU/Linux
mysql --version
mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1
我的cnf
[mysqld]
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
max_allowed_packet = 16M
log_slave_updates = 1
relay_log = mysql-relay-bin
relay-log-purge=1
skip-slave-start
character_set_server = utf8
character_set_client = utf8
bind-address = 0.0.0.0
log_error = /var/log/mysql/error.log
skip-name-resolve
skip-locking
max_connections = 150
open-files-limit = 10240
tmpdir = /dev/shm
query_cache_size = 128M
table_cache = 2048
tmp_table_size = 64M
max_heap_table_size = 64M
thread_stack = 192K
thread_cache_size = 60
join_buffer_size = 64M
query_cache_limit=2M
key_buffer = 50M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_concurrency = 8
long_query_time = 10
log-slow-queries = /var/log/mysql/slow.log
binlog-format = ROW
log-bin = /home/backup/data/mysql-updates/
expire_logs_days = 14
max_binlog_size = 1024M
innodb_file_per_table
innodb_buffer_pool_size = 6G
innodb_additional_mem_pool_size = 20M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method=O_DIRECT
innodb_doublewrite=0
innodb_lock_wait_timeout = 50
innodb_support_xa=0
transaction-isolation = READ-COMMITTED
mysqltuner
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12.10-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 148M (Tables: 308)
[--] Data in InnoDB tables: 33G (Tables: 3514)
[!!] Total fragmented tables: 423
-------- Performance Metrics -------------------------------------------------
[--] Up for: 19h 54m 41s (14M q [202.695 qps], 3K conn, TX: 81B, RX: 28B)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 6.3G global + 70.2M per thread (150 max threads)
[OK] Maximum possible memory usage: 16.6G (52% of installed RAM)
[OK] Slow queries: 0% (348/14M)
[OK] Highest usage of available connections: 50% (76/150)
[OK] Key buffer size / total MyISAM indexes: 50.0M/71.9M
[OK] Key buffer hit rate: 100.0% (234M cached / 1K reads)
[OK] Query cache efficiency: 64.4% (7M cached / 11M selects)
[!!] Query cache prunes per day: 2399978
[OK] Sorts requiring temporary tables: 0% (3 temp sorts / 304K sorts)
[!!] Joins performed without indexes: 136460
[OK] Temporary tables created on disk: 10% (68K on disk / 637K total)
[OK] Thread cache hit rate: 98% (76 created / 3K connections)
[!!] Table cache hit rate: 1% (2K open / 148K opened)
[OK] Open file limit used: 0% (79/10K)
[OK] Table locks acquired immediately: 100% (468M immediate / 468M locks)
[!!] Connections aborted: 12%
[!!] InnoDB data size / buffer pool: 33.6G/6.0G
-------- Recommendations ---------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (> 128M)
join_buffer_size (> 64.0M, or always use indexes with joins)
table_cache (> 2048)
innodb_buffer_pool_size (>= 33G)
mysql报告
Use of uninitialized value $is in multiplication (*) at ./mysqlreport line 829.
Use of uninitialized value in formline at ./mysqlreport line 1227.
MySQL 5.1.41-3ubuntu12. uptime 0 19:59:11 Thu Nov 17 09:01:48 2011
__ Key _________________________________________________________________
Buffer used 1.91M of 50.00M %Used: 3.82
Current 9.35M %Usage: 18.71
Write hit 96.73%
Read hit 100.00%
__ Questions ___________________________________________________________
Total 14.60M 202.9/s
QC Hits 7.58M 105.4/s %Total: 51.94
DMS 5.10M 70.9/s 34.95
Com_ 1.91M 26.6/s 13.09
COM_QUIT 3.99k 0.1/s 0.03
-Unknown 557 0.0/s 0.00
Slow 10 s 360 0.0/s 0.00 %DMS: 0.01 Log: ON
DMS 5.10M 70.9/s 34.95
SELECT 4.21M 58.5/s 28.82 82.46
UPDATE 390.79k 5.4/s 2.68 7.66
INSERT 281.62k 3.9/s 1.93 5.52
DELETE 222.67k 3.1/s 1.52 4.36
REPLACE 0 0/s 0.00 0.00
Com_ 1.91M 26.6/s 13.09
set_option 941.16k 13.1/s 6.45
commit 859.21k 11.9/s 5.88
rollback 62.69k 0.9/s 0.43
__ SELECT and Sort _____________________________________________________
Scan 1.00M 14.0/s %SELECT: 23.87
Range 171.60k 2.4/s 4.08
Full join 137.44k 1.9/s 3.27
Range check 1 0.0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 209.76k 2.9/s
Sort range 95.98k 1.3/s
Sort mrg pass 3 0.0/s
__ Query Cache _________________________________________________________
Memory usage 87.00M of 128.00M %Used: 67.97
Block Fragmnt 7.45%
Hits 7.58M 105.4/s
Inserts 3.40M 47.3/s
Insrt:Prune 1.70:1 19.4/s
Hit:Insert 2.23:1
__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 477.60M 6.6k/s
__ Tables ______________________________________________________________
Open 2048 of 2048 %Cache: 100.00
Opened 160.71k 2.2/s
__ Connections _________________________________________________________
Max used 76 of 150 %Max: 50.67
Total 4.00k 0.1/s
__ Created Temp ________________________________________________________
Disk table 68.62k 1.0/s
Table 572.51k 8.0/s Size: 64.0M
File 22 0.0/s
__ Threads _____________________________________________________________
Running 6 of 59
Cached 17 of 60 %Hit: 98.10
Created 76 0.0/s
Slow 0 0/s
__ Aborted _____________________________________________________________
Clients 14 0.0/s
Connects 486 0.0/s
__ Bytes _______________________________________________________________
Sent 81.80G 1.1M/s
Received 28.58G 397.2k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 6.00G of 6.00G %Used: 100.00
Read hit 99.99%
Pages
Free 0 %Total: 0.00
Data 383.39k 97.50 %Drty: 0.00
Misc 9830 2.50
Latched 0.00
Reads 4.36G 60.6k/s
From file 527.77k 7.3/s 0.01
Ahead Rnd 20752 0.3/s
Ahead Sql 16100 0.2/s
Writes 3.45M 47.9/s
Flushes 465.81k 6.5/s
Wait Free 0 0/s
__ InnoDB Lock _________________________________________________________
Waits 16 0.0/s
Current 0
Time acquiring
Total 2137 ms
Average 133 ms
Max 311 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 604.30k 8.4/s
Writes 465.56k 6.5/s
fsync 252.99k 3.5/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 3.95k 0.1/s
Read 2.51M 34.9/s
Written 465.81k 6.5/s
Rows
Deleted 136.69k 1.9/s
Inserted 270.04k 3.8/s
Read 11.13G 154.7k/s
Updated 240.46k 3.3/s
答案1
有趣的问题，很好地呈现了相关背景。
mysqltuner 正在报告
[确定] 最大可能的内存使用量：16.6G（已安装 RAM 的 52%）
意味着事实并非如此，并且发生了一些非常奇怪的事情，或者内存使用发生在 mysql 以外的其他地方。
我认为 Web 服务器/PHP 是在一台单独的机器上？
我会在重新启动 mysql db 后以及内存已满时拍摄 ps -ef 快照并尝试确定额外内存的去向（可能不是 mysql）。
近 4000 张桌子！真的吗？
服务器仅包含 28 个数据库，除了 Wordpress 数据库外，几乎所有数据库都使用 InnoDB。
那么 Wordpress 需要 3500 张表格吗？
它以占用大量内存而闻名，但我没有意识到它会占用如此大的数据库空间。