суббота, 21 июня 2014 г.

Оптимизация MySQL

Заметка из цикла оптимизации веб-сервера на базе apache+nginx+mysql+php.

Первым делом решил заняться именно оптимизацией MySQL.

Стоит заранее сказать, что все нижеуказанные параметры, подходящие для моего сервера, могут не подойти для использования другого, так как они являются сугубо индивидуальными и подобраны в соответствии с характеристиками сервера.

На сервере установлен восьмиядерный процессор Xeon и 24Гб оперативной памяти, на котором работает более 200 сайтов, в основном на Joomla и WordPress.

MySQL версии 5.1

Можно воспользоваться утилитой sysbench до и после оптимизации, для сравнения:
# yum install sysbench
# mysql -uroot -ppassword
> create database dbtest;
> quit

# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=dbtest --mysql-user=root --mysql-password=password --oltp-test-mode=complex --oltp-read-only=off --num-threads=8 --max-requests=0 prepare
# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=dbtest --mysql-user=root --mysql-password=password --oltp-test-mode=complex --oltp-read-only=off --num-threads=8 --max-requests=0 --max-time=180 run

Пройдёмся по нашим БД утилитой MySQLTuner.
Не стоит полность доверять ей, стоит лишь только проанализировать полученные результаты и сделать для себя выводы.
Для запуска скрипта необходимо установить perl.

# curl mysqltuner.ru > tuner.pl

Если нужно отключить запрос пароля для MySQL, это можно сделать так:
# vim /etc/my.cnf
skip-grant-tables
# service mysqld restart

# perl tuner.pl
 >>  MySQLTuner 1.6.13 - Major Hayden <[email protected]>
 >>  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
[!!] Successfully authenticated with no password - SECURITY RISK!
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 123M (Tables: 1704)
[--] Data in InnoDB tables: 77M (Tables: 1140)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (password = '' OR password IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] User 'zaoaes_dizgen@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 17s (16 q [0.941 qps], 8 conn, TX: 23K, RX: 638B)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory     : 15.7G
[--] Max MySQL memory    : 148.7M
[--] Other process memory: 557.1M
[--] Total buffers: 26.0M global + 832.0K per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 26.8M (0.17% of installed RAM)
[OK] Maximum possible memory usage: 148.7M (0.93% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/16)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Aborted connections: 0.00%  (0/8)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache is disabled
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 4 total)
[!!] Table cache hit rate: 16% (1 open / 6 opened)
[OK] Open file limit used: 0% (2/1K)
[OK] Table locks acquired immediately: 100% (6 immediate / 6 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.8% (3K used / 16K cache)
[!!] Key buffer size / total MyISAM indexes: 16.0K/54.2M

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 8.0M/77.5M
[!!] InnoDB Used buffer: 9.96% (51 used/ 512 total)
[OK] InnoDB Read buffer efficiency: 96.70% (1291 hits/ 1335 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Restrict Host for user@% to [email protected]
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (1024) variable 
    should be greater than table_open_cache ( 4)
Variables to adjust:
    query_cache_size (>= 8M)
    table_open_cache (> 4)
    key_buffer_size (> 54.2M)
    innodb_buffer_pool_size (>= 77M) if possible.

Утилита анализирует параметры конфига MySQL, показывает состояние фрагментации баз и рекомендует использование оптимальных значений.
Тут стоит обратить внимание на все пункты, вне зависимости от [!!] или [OK].

Утилита рекомендует нам оптимизировать базы данных.
Это можно сделать так:
# mysqlcheck --auto-repair --optimize --all-databases

Как известно для InnoDB оптимизация не работает, поэтому это сработает только MyISAM-таблиц.


Теперь нужно привести нужные значения параметров в /etc/my.cnf (конфиг не полный, привожу лишь основные изменения):
# vim /etc/mysql/my.cnf
query_cache_size = 16M
query_cache_limit = 2M
table_open_cache = 8192
key_buffer_size = 64M
innodb_buffer_pool_size = 128M
open_files_limit = 8192
skip-grant-tables

Для применения новых настроек, нужно перезагрузить MySQL:
# service mysqld restart

И заново запустить mysqltuner.pl, для сравнения результатов до оптимизации и после:
# perl tuner.pl
 >>  MySQLTuner 1.6.13 - Major Hayden <[email protected]>
 >>  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
[!!] Successfully authenticated with no password - SECURITY RISK!
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 120M (Tables: 1704)
[--] Data in InnoDB tables: 63M (Tables: 1140)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (password = '' OR password IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] User 'zaoaes_dizgen@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2m 49s (4K q [23.775 qps], 187 conn, TX: 10M, RX: 1M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 15.7G
[--] Max MySQL memory    : 348.7M
[--] Other process memory: 627.0M
[--] Total buffers: 226.0M global + 832.0K per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 227.6M (1.42% of installed RAM)
[OK] Maximum possible memory usage: 348.7M (2.17% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/4K)
[OK] Highest usage of available connections: 1% (2/151)
[OK] Aborted connections: 0.00%  (0/187)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache efficiency: 58.7% (2K cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 339 sorts)
[!!] Joins performed without indexes: 1
[OK] Temporary tables created on disk: 20% (54 on disk / 264 total)
[OK] Table cache hit rate: 99% (2K open / 2K opened)
[OK] Open file limit used: 20% (3K/16K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.3% (12M used / 67M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/52.4M
[OK] Read Key buffer hit rate: 99.6% (170K cached / 708 reads)
[!!] Write Key buffer hit rate: 75.0% (32 cached / 8 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/63.8M
[!!] InnoDB Used buffer: 41.83% (3427 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 99.77% (1282526 hits/ 1285531 total)
[!!] InnoDB Write Log efficiency: 35% (7 hits/ 20 total)
[OK] InnoDB log waits: 0.00% (0 waits / 27 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Restrict Host for user@% to [email protected]
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes

Удалить созданную в тесте sysbench базу можно так:
# mysql -uroot -ppassword
> drop database dbtest;
> quit

Полезные ссылки:
Калькулятор, подсчитывает максимальное количество занятой памяти, исходя из значений параметров: jvdev.lv/mysqlmaxmem
Описание параметров MySQL: dev.mysql.com/doc/refman/5.5/en/server-parameters.html
Оптимизация связки nginx, Apache, PHP, MySQL: habr.ru/post/146179/
Оптимизация сервера MySQL. Шаг первый. Утилита mysqltuner: dedicatesupport.com/content/optimizatsiya-servera-mysql-shag-pervyi-utilita-mysqltuner
Онлайн тюнер от Percona: https://tools.percona.com/

Конечно, это не всё, что можно оптимизировать, но и этого не мало. Для более высокой производительности нужно мониторить медленные запросы и оптимизировать их.

Более подробно в документации по MySQL.

4 комментария:

Анонимный комментирует...

Выводы конечно "прекрасны" особенно учитывая время после старта базы "[--] Up for: 6s ...", если уж начали пользоваться советчиками, то читайте хоть что они пишут "MySQL started within last 24 hours - recommendations may be inaccurate", а еще лучше, чтоб база поработала неделю или 2-е (для набора статистики), а потом только делать какие либо выводы. Но уж точно не по 6-ти секундам работы базы.

Amet Umerov комментирует...

Очевидно, что я и это смотрел. Но в заметку это не вошло.

Сергей Якименко комментирует...

Полная бредятина и Ахинея ! Некомпетенция это мягко сказано !

--- Максимальное использование памяти уменьшилось с 152.5G (646%) до 10.3G (43%);

это произошло из за изменения параметра join_buffer_size , так как этот параметр указывается для сессиии ! А у Вас он был 1 GB!

Amet Umerov комментирует...

Именно поэтому стоит использовать тюнер, когда не уверен в том, что изначально неверно настроил MySQL.