Тюнинг MySQL в Unix/Linux
Запуск MySQL c оптимальными настройками для конкретных сервисов помогает сбалансировать работу при большой нагрузки на сервер и предотвращает замедление сервера.
Тюнинг база данных — экспансивная тема, и это руководство «Тюнинг MySQL в Unix/Linux» поможет оптимизировать mysql. На моем готовом примере, покажу как это можно сделать.
Утилиты
Для того, чтобы определить, если ваша база данных MySQL должна быть изменена, то лучше всего посмотреть, как ваши ресурсы выполняются в настоящее время. Это может быть выполнено с командой:
# echo [PID] [MEM] [PATH] && ps aux | awk '{print $2, $4, $11}' | sort -k2rn | head -n 20
и
# ps -eo pcpu,pid,user,args | sort -k 1 -r | head -20
MySQLTuner
Скрипт MySQLTuner оценивает вашу настройку MySQL, а затем выводит предложения для повышения производительности и стабильности вашего сервера.
Скачиваем MySQLTuner:
# cd /usr/local/src && wget http://mysqltuner.pl/ -O mysqltuner.pl
Запустите скрипт:
# perl /usr/local/src/mysqltuner.pl
It outputs your results:
>> MySQLTuner 1.4.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 Please enter your MySQL administrative logiroot Please enter your MySQL administrative password: [!!] Currently running unsupported MySQL version 10.0.19-MariaDB [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM [--] Data in MyISAM tables: 40M (Tables: 8) [--] Data in InnoDB tables: 87M (Tables: 432) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52) [--] Data in MEMORY tables: 5M (Tables: 6) [!!] Total fragmented tables: 26 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 12d 17h 25m 17s (12M q [11.016 qps], 157K conn, TX: 130B, RX: 5B) [--] Reads / Writes: 69% / 31% [--] Total buffers: 1.1G global + 11.3M per thread (80 max threads) [OK] Maximum possible memory usage: 2.0G (32% of installed RAM) [OK] Slow queries: 0% (3/12M) [OK] Highest usage of available connections: 63% (51/80) [OK] Key buffer size / total MyISAM indexes: 32.0M/949.0K [OK] Key buffer hit rate: 100.0% (5M cached / 2K reads) [OK] Query cache efficiency: 26.1% (2M cached / 10M selects) [!!] Query cache prunes per day: 17574 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 134K sorts) [!!] Temporary tables created on disk: 90% (350K on disk / 387K total) [OK] Thread cache hit rate: 95% (6K created / 157K connections) [!!] Table cache hit rate: 0% (128 open / 16K opened) [OK] Open file limit used: 0% (11/8K) [OK] Table locks acquired immediately: 99% (8M immediate / 8M locks) [!!] Connections aborted: 24% [OK] InnoDB buffer pool / data size: 512.0M/87.3M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Your applications are not closing MySQL connections properly Variables to adjust: query_cache_size (> 32M) table_open_cache (> 128)
MySQLTuner предлагает улучшение относительно ваших баз данных.
Тюнинг MySQL
MySQLTuner является одним из самых безопасных способов улучшить производительность базы данных.
Файл, который вы изменение находится в /etc/mysql/my.cnf.
ПРИМЕЧАНИЕ! Перед обновлением конфигурации MySQL, рекомендую создать резервную копию файла my.cnf:
# cp /etc/mysql/my.cnf ~/my.cnf.backup
Лучшие делайте небольшие изменения чтобы контролировать сервер после каждого изменения. После каждого изменения перезапустить MySQL:
На Debian/Ubuntu:
# service mysql restart
На CentOS/Fedora:
# /etc/init.d/mysqld restart
Параметры.
key_buffer
Изменение «key_buffer» влияет на выделение памяти для MySQL, может существенно ускорить базы данных, если у вас есть свободное память на сервере. Размер «key_buffer» как правило, должны занять не более 25% от системной памяти при использовании таблицы c MyISAM, и до 70% для InnoDB. Если значение слишком высокое, то ресурсы тратятся впустую. Согласно документации MySQL, сервера с 256 МБ оперативной памяти (или больше) -рекомендуется устанавливать 64M , если сервера имеют 128 Мб ОЗУ и меньше, то используется 16M, значения по умолчанию.
max_allowed_packet
Максимальный размер пакета который может быть отправлен. Если вы знаете, что ваш сервер MySQL будет выполнять обработку больших пакетов, то лучше поднять этот параметр. Если это значение установлено слишком маленьким, вы получите сообщение об ошибке в лог-журнале.
thread_stack
Это значение содержит размер стека для каждого потока. MySQL считает значения переменной thread_stack по умолчанию, достаточной для нормального использования; Однако, если возникает ошибка, относящаяся к thread_stack, то данный параметр может быть увеличен.
thread_cache_size
Если thread_cache_size -выключен (установлен в 0), то все новые соединения, создают нужен новый поток, созданный для них и когда соединения завершается, то нить разрушается. В противном случае, это значение задает количество неиспользованных потоков для сохранения в кэше, пока они не должны быть использованы для соединения. Вообще этот параметр мало влияет на производительность, если вы не получаете сотни соединений в минуту, при которых время это значение должно быть поднято для больших соединений которые делаются на кэшированных потоков.
max_connections
Устанавливает максимальное количество одновременных подключений. Лучше рассмотреть максимальное количество подключений. Обратите внимание, что это не означает, что это будет максимальное количество пользователей на вашем сайте в одно время, но это максимальное количество пользователей, делающих запросы одновременно.
table_cache
должен быть больше, чем значение open_tables. Чтобы определить это значение использования:
SHOW STATUS LIKE 'open%';
Вот некоторые решения.
Тюнинг выдал рекомендацию:
Run OPTIMIZE TABLE to defragment tables for better performance
Чтобы это исправить, используем:
$ mysqlcheck -uroot -p --optimize --all-databases
Вот и все, тема «Тюнинг MySQL в Unix/Linux» завершена.