我有一个便宜的VPS 128Mb RAM和256Mb突发。 MySQL启动并保留大约110Mb，但使用不超过20Mb。 我的VPS控制面板显示，我使用127Mb（我也运行nginx和sphinx），我知道，它显示保留的内存，但是当我达到128Mb，我的VPS每4小时自动重新启动。 所以我想强制MySQL预留更less的RAM。 我怎样才能做到这一点？ 我做了一些与my.conf的调整，但没有太多的帮助。
顶级输出：
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1 root 15 0 2156 668 572 S 0.0 0.3 0:00.03 init 11311 root 15 0 11212 356 228 S 0.0 0.1 0:00.00 vzctl 11312 root 18 0 3712 1484 1248 S 0.0 0.6 0:00.01 bash 11347 root 18 0 2284 916 732 R 0.0 0.3 0:00.00 top 13978 root 17 -4 2248 552 344 S 0.0 0.2 0:00.00 udevd 14262 root 15 0 1812 564 472 S 0.0 0.2 0:00.03 syslogd 14293 sphinx 15 0 11816 1172 672 S 0.0 0.4 0:00.07 searchd 14305 root 25 0 7192 1036 636 S 0.0 0.4 0:00.00 sshd 14321 root 25 0 2832 836 668 S 0.0 0.3 0:00.00 xinetd 15389 root 18 0 3708 1300 1132 S 0.0 0.5 0:00.00 mysqld_safe 15441 mysql 15 0 113m 16m 4440 S 0.0 6.4 0:00.15 mysqld 15489 root 21 0 13056 1456 340 S 0.0 0.6 0:00.00 nginx 15490 nginx 18 0 13328 2388 992 S 0.0 0.9 0:00.06 nginx 15507 nginx 25 0 19520 5888 4244 S 0.0 2.2 0:00.00 php-cgi 15508 nginx 18 0 19636 4876 2748 S 0.0 1.9 0:00.12 php-cgi 15509 nginx 15 0 19668 4872 2716 S 0.0 1.9 0:00.11 php-cgi 15518 root 18 0 4492 1116 568 S 0.0 0.4 0:00.01 crond MySQL调谐器：
>> MySQLTuner 1.0.1 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password: -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.77 [OK] Operating on 32-bit architecture with less than 2GB RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in InnoDB tables: 1M (Tables: 1) [OK] Total fragmented tables: 0 -------- Performance Metrics ------------------------------------------------- [--] Up for: 38m 43s (37 q [0.016 qps], 20 conn, TX: 4M, RX: 3K) [--] Reads / Writes: 100% / 0% [--] Total buffers: 28.1M global + 832.0K per thread (100 max threads) [OK] Maximum possible memory usage: 109.4M (42% of installed RAM) [OK] Slow queries: 0% (0/37) [OK] Highest usage of available connections: 1% (1/100) [OK] Key buffer size / total MyISAM indexes: 128.0K/64.0K [OK] Query cache efficiency: 42.1% (8 cached / 19 selects) [OK] Query cache prunes per day: 0 [!!] Temporary tables created on disk: 27% (3 on disk / 11 total) [!!] Thread cache is disabled [OK] Table cache hit rate: 57% (8 open / 14 opened) [OK] Open file limit used: 1% (12/1K) [OK] Table locks acquired immediately: 100% (22 immediate / 22 locks) [!!] Connections aborted: 10% [OK] InnoDB data size / buffer pool: 1.5M/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Set thread_cache_size to 4 as a starting value Your applications are not closing MySQL connections properly Variables to adjust: tmp_table_size (> 32M) max_heap_table_size (> 16M) thread_cache_size (start at 4)
我想如果我做MySQLtuner所说的话，MySQL会使用更多的RAM。
在Linux上有几种可用的机制来为进程提供可用的内存。
在较新的系统上，您可以使用cgroup，如果可用，那可能是最好的解决scheme。 （使用libcgroup软件包中的工具自动将mysql守护进程分类到memory.limit_in_bytes = 64M;的内存控制组memory.limit_in_bytes = 64M;或者任何其他的。
在较老的系统上，如果一个脚本正在启动mysql守护进程，那么可以简单地使用ulimit来限制可用内存 – 在守护进程实际启动的上方的脚本中放置一个ulimit -v 65536语句。
这就是说，用现代软件，这是真正推动你可以摆脱。 你可能只需要比你更多的RAM。 一个选项可能是切换到SQLite而不是MySQL – 那么你根本没有一个单独的守护进程。
使用调优 引擎或MySQLTuner 。
# wget http://mysqltuner.pl/mysqltuner.pl --2011-01-31 06:49:46-- http://mysqltuner.pl/mysqltuner.pl Resolving mysqltuner.pl... 174.143.142.58 Connecting to mysqltuner.pl|174.143.142.58|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 39054 (38K) [text/plain] Saving to: `mysqltuner.pl' 100%[==============================================================================================================================>] 39,054 54.6K/s in 0.7s 2011-01-31 06:49:49 (54.6 KB/s) - `mysqltuner.pl' saved [39054/39054] # perl mysqltuner.pl >> MySQLTuner 1.0.1 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password:
设置max_connections = 15
128 MB对于nginx，php，Sphinx和MySQL来说太less了。
尽量不要使用InnoDB，但是MyISAM。 将skip-innodb添加到my.cnf会减less大量的内存