J'ai récemment eu des problèmes de plantage suite à un manque de mémoire. (Mon VPS a 256M au total)
J'essaie de régler MySQL à l'aide de mysqltuner.pl, et j'obtiens les résultats suivants :
\-------- General Statistics --------------------------------------------------
\[--\] Skipped version check for MySQLTuner script
\[OK\] Currently running supported MySQL version 5.0.51a-3ubuntu5.4-log
\[OK\] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
\[--\] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
\[--\] Data in MyISAM tables: 114M (Tables: 454)
\[!!\] Total fragmented tables: 34
-------- Performance Metrics -------------------------------------------------
\[--\] Up for: 40s (570 q \[14.250 qps\], 23 conn, TX: 154K, RX: 23K)
\[--\] Reads / Writes: 100% / 0%
\[--\] Total buffers: 338.0M global + 2.7M per thread (20 max threads)
\[!!\] Maximum possible memory usage: 392.9M (153% of installed RAM)
\[OK\] Slow queries: 0% (5/570)
\[OK\] Highest usage of available connections: 15% (3/20)
\[!!\] Key buffer size / total MyISAM indexes: 8.0M/9.4M
\[!!\] Key buffer hit rate: 57.1% (7 cached / 3 reads)
\[OK\] Query cache efficiency: 21.9% (7 cached / 32 selects)
\[OK\] Query cache prunes per day: 0
\[OK\] Sorts requiring temporary tables: 0% (0 temp sorts / 1 sorts)
\[OK\] Temporary tables created on disk: 0% (0 on disk / 32 total)
\[OK\] Thread cache hit rate: 86% (3 created / 23 connections)
\[OK\] Table cache hit rate: 26% (128 open / 484 opened)
\[OK\] Open file limit used: 25% (259/1K)
\[OK\] Table locks acquired immediately: 100% (492 immediate / 492 locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
\*\*\* MySQL's maximum memory usage is dangerously high \*\*\*
\*\*\* Add RAM before increasing MySQL buffer variables \*\*\*
key\_buffer\_size (> 9.4M)Mais je suis un peu confus sur la façon de réduire l'utilisation maximale de la mémoire ? Il semble qu'elle soit basée sur key_buffer et max_connections, mais il doit y avoir autre chose d'impliqué aussi ?
mon.cnf :
key\_buffer = 8M
max\_allowed\_packet = 12M
thread\_stack = 128K
thread\_cache\_size = 8
max\_connections = 20
table\_cache = 128
tmp\_table\_size = 256M
max\_heap\_table\_size = 256M
join\_buffer\_size = 256K
query\_cache\_limit = 8M
query\_cache\_size = 64MJ'ai essayé de lire des articles sur le réglage de MySQL, mais ils semblent destinés à des personnes qui savent déjà ce qu'elles font ! Toute aide serait la bienvenue. Merci.
