Mysqltuner – оптимизация MySQL
Mysqltuner — это perl-скрипт, который анализирует статистику работы Mysql и выдает свои рекомендации по оптимизации настроек Mysql сервера.
# yum -y install mysqltuner
Запускаем:
# mysqltuner --user root --pass rootpassword
Особое внимание стоит уделить строкам, помеченным символами [!!] и секции Recommendations.
Параметры указанные после строки Variables to adjust нужно изменить в файле my.cnf, в соответствии с рекомендациями Mysqltuner. Если указанного параметра нет в файле my.cnf, то его следует дописать.
-------- 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 When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** tmp_table_size (> 64M) max_heap_table_size (> 64M)
Согласно рекомендациям правим:
/etc/my.cnf
После внесения изменений в файл my.cnf нужно перезагрузить Mysql-сервер:
# systemctl restart mariadb
После изменения конфигурации Mysql-сервер должен проработать минимум 24 часа без перезагрузок. Затем, можно снова запустить утилиту Mysqltuner и проанализировать вывод статистики. Таким образом, можно привести конфигурационный файл my.cnf и работу Mysql-сервера, соответственно, к оптимальному состоянию.
Мой пример конфига.
# uname -rs Linux 3.10.0-862.11.6.el7.x86_64
# free -m total used free shared buff/cache available Mem: 2811 2086 91 9 633 540 Swap: 2047 0 2047
# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd ## WordPress/Owncloud max_connections = 64 wait_timeout = 22222 interactive_timeout = 22222 table_open_cache = 800 open_files_limit = 65535 thread_cache_size = 32 thread_concurrency = 8 query_cache_type = 1 query_cache_size = 32M query_cache_limit = 1M key_buffer_size = 24M key_cache_division_limit=70 join_buffer_size = 512M tmp_table_size = 128M max_heap_table_size = 128M # INNODB innodb_buffer_pool_size = 128M innodb_flush_log_at_trx_commit = 2 innodb_file_per_table = 1 innodb_buffer_pool_size = 512M innodb_data_file_path = ibdata1:10M:autoextend:max:3999M innodb_flush_method = O_DIRECT # MYISAM myisam_sort_buffer_size = 32M #Logging slow_query_log = 1 slow_query_log_file = /var/log/mariadb/mysql-slow.log [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/my.cnf.d
# mysqltuner Please enter your MySQL administrative login: root Please enter your MySQL administrative password: >> MySQLTuner 1.6.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.56-MariaDB [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 42K (Tables: 10) [--] Data in InnoDB tables: 39M (Tables: 87) [!!] Total fragmented tables: 3 -------- Security Recommendations ------------------------------------------- [OK] There is no anonymous account in all database users [OK] All database users have passwords assigned [!!] There is not basic password file list ! -------- Performance Metrics ------------------------------------------------- [--] Up for: 44m 53s (43K q [16.302 qps], 748 conn, TX: 34M, RX: 8M) [--] Reads / Writes: 87% / 13% [--] Binary logging is disabled [--] Total buffers: 840.0M global + 514.7M per thread (64 max threads) [!!] Maximum reached memory usage: 16.4G (597.34% of installed RAM) [!!] Maximum possible memory usage: 33.0G (1201.42% of installed RAM) [OK] Slow queries: 0% (0/43K) [OK] Highest usage of available connections: 48% (31/64) [OK] Aborted connections: 0.53% (4/748) [OK] Query cache efficiency: 40.8% (24K cached / 59K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 627 sorts) [!!] Temporary tables created on disk: 26% (18 on disk / 67 total) [OK] Thread cache hit rate: 95% (31 created / 748 connections) [OK] Table cache hit rate: 122% (140 open / 114 opened) [OK] Open file limit used: 7% (72/1K) [OK] Table locks acquired immediately: 100% (16K immediate / 16K locks) -------- MyISAM Metrics ----------------------------------------------------- [!!] Key buffer used: 18.7% (4M used / 25M cache) [OK] Key buffer size / total MyISAM indexes: 24.0M/136.0K [!!] Read Key buffer hit rate: 75.0% (32 cached / 8 reads) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 512.0M/39.7M [OK] InnoDB buffer pool instances: 1 [!!] InnoDB Used buffer: 5.93% (1943 used/ 32767 total) [OK] InnoDB Read buffer efficiency: 99.50% (358717 hits/ 360535 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 4616 writes) -------- AriaDB Metrics ----------------------------------------------------- [--] AriaDB is disabled. -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- 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 When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** tmp_table_size (> 128M) max_heap_table_size (> 128M)