Очень часто возникает вопрос “Как оптимизировать MySQL?”.
Ниже я опишу простой способ как это можно сделать с помощью скрипта mysqltuner.pl
И так качаем и запускаем:
cd /usr/local/src/ wget mysqltuner.pl chmod 755 mysqltuner.pl ./mysqltuner.pl |
Результат будет примерно таковым
>> MySQLTuner 1.2.0 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [!!] Successfully authenticated with no password - SECURITY RISK! -------- 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 [!!] InnoDB is enabled but isn't being used [!!] BDB is enabled but isn't being used [OK] Total fragmented tables: 0 -------- Security Recommendations ------------------------------------------- [!!] User '@cpe-109-108-243-16.enet.vn.ua' has no password set. [!!] User '@localhost' has no password set. [!!] User '[email protected]' has no password set. [!!] User '[email protected]' has no password set. [!!] User 'root@localhost' has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 5h 31m 19s (10 q [0.001 qps], 6 conn, TX: 7K, RX: 457) [--] Reads / Writes: 100% / 0% [--] Total buffers: 34.0M global + 2.7M per thread (100 max threads) [OK] Maximum possible memory usage: 302.7M (60% of installed RAM) [OK] Slow queries: 0% (0/10) [OK] Highest usage of available connections: 1% (1/100) [OK] Key buffer size / total MyISAM indexes: 8.0M/67.0K [!!] Query cache is disabled [OK] Temporary tables created on disk: 0% (0 on disk / 2 total) [!!] Thread cache is disabled [OK] Table cache hit rate: 50% (6 open / 12 opened) [OK] Open file limit used: 1% (12/1K) [OK] Table locks acquired immediately: 100% (12 immediate / 12 locks) -------- Recommendations ----------------------------------------------------- General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB Add skip-bdb to MySQL configuration to disable BDB MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries Set thread_cache_size to 4 as a starting value Variables to adjust: query_cache_size (>= 8M) thread_cache_size (start at 4) |
и теперь просто добавляем параметры которые вывелись здесь “Variables to adjust:” (у меня это query_cache_size и thread_cache_size) в /etc/my.cnf где-то под строкой [mysqld].
vi /etc/my.cnf query_cache_size = 16M thread_cache_size = 4 |
далее сохраняем и рестартуем MySQL
service mysqld restart |
или
service mysql restart |
