[mysql] Небольшой тюнинг и рекомендации для mysql

В статье будет рассмотрено несколько рекомендаций по тюнингу и улучшении работы mysql. Замечу сразу, что численные значения для каждого сервера будут свои (подбираются путём экспериментов) и простое бездумное копирование, может не только ничего не дать, но и напротив – ухудшить производительность.

1) Создание индексов.

В 99% это действительно так. Ещё нужно использовать кеширование индексов (key_buffer). Здесь очень неплохая подборка по работе с индексами

2) Переход на InnoDB.

Этот тип таблиц менее подвержен сбоям (а так же имеет много преимуществ), нежели MyIsam и уже является типом таблиц по умолчанию в версиях 5.5 и выше. Если же нужен полнотекстовый поиск, то используйте дополнительно sphinks.

3) Использовать проверку тюнинг скриптов, benchmarks.

Особое внимание следует уделить тому, что собственно нужно бенчмаркить: либо только mysql либо в целом приложение.

Full-stack benchmarking tools:

– ab (http://httpd.apache.org/docs/2.0/programs/ab.html)
– http_load (http://www.acme.com/software/http_load/)
– JMeter (http://jakarta.apache.org/jmeter/)

Single tools:

– mysqlslap (http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html): симулирует нагрузку на сервер и создаёт отчёт. Входит в состав mysql, начиная с версии 5.1
– MySQL Benchmark Suite (sql-bench) (http://dev.mysql.com/doc/en/mysql-benchmarks.html/) собственный бенчмарк от mysql
– Super Smack (http://vegan.net/tony/supersmack/)
– Database Test Suite (OSDLand hosted on SourceForge at http://sourceforge.net/projects/osdldbt/)
– Percona’s TPCC-MySQL Tool (https://launchpad.net/perconatools)
– sysbench (https://launchpad.net/sysbench)
– maatkit (ниже, пункт 10)

Вот 2 скрипта для тюнинга: mysqltuner.pl (http://mysqltuner.pl/mysqltuner.pl) и tuning-primer.sh (https://launchpad.net/mysql-tuning-primer). Их нужно запустить и следовать рекомендациям отчётов.

Так же можно попробовать прогнать вашу БД sqlmap – тулза для поиска уязвимостей.

4) Просмотр нагрузки в realtime.

Есть 2 замечательные утилиты для этих целей mtop и mytop. С их помощью вы сможете посмотреть в режиме реального времени, какие запросы нагружают БД.

5) Включить логгирование медленных запросов:

log_error=/var/log/mysql/error.log
log_slow_queries=/var/log/mysql/slow.log
long_query_time = 5
log-queries-not-using-indexes

и периодически смотреть лог.

6) Соединение по сокету.

Использовать там, где это возможно соединение по сокету, вместо IP:port

socket=/tmp/mysql.sock
skip-networking

7) skip-name-resolve

Использовать  для skip-name-resolve отключения резолвинга. Этим вы можете выиграть до 20% производительности.

Но будьте внимательны: при включении этой опции localhost тоже не будет резолвится, поэтому если у вас в настройках сайта и в правах используется localhost – замените его на 127.0.0.1. В частности такое наблюдается на Debian Lenny

8) Включение кеширования.

Действительно, это позволяет несколько ускорить работу БД. Включаем кеширование:

query_cache_type = 1

Что бы правильно подобрать параметры query_cache_size, query_cache_limit, а так же остальные параметры кеширования используйте утилиты проверки тюнинга из пунктов 3 и 4.

9) Приоритеты SELECT.

Если у вас запросы SELECT составляют 90% и более всех запросов, то имеет смысл добавить опцию low-priority-updates, которая повышает приоритет запросов select.

10) Отключение неиспользуемых типов хранилищ

Если вы не используете, например, InnoDB или DBD, то их можно отключить. Это также сэкономит немного ресурсов:

skip-bdb
skip-innodb

Примечание

Если вы собрали mysql, например, без хранилища bdb, то при использовании опции skip-bdb получите такое сообщение:

[ERROR] /usr/local/libexec/mysqld: unknown option '--skip-bdb'

Поэтому лучше сначала посмотреть список доступных хранилищ.

ПС. Посмотреть все доступные типы хранилищ можно так:

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

11) Количество потоков

Формула такова

Try number of CPU's*2 for thread_concurrency
thread_cache_size = 8

12) Советы по оптимизации от oracle

Почитать на официальном сайте про оптимизацию http://dev.mysql.com/doc/refman/5.1/en/optimization.html

13) Включаем авто recover для MyIsam

myisam_recover_options=BACKUP

Option	Description
DEFAULT	Recovery without backup, forcing, or quick checking.
OFF	Recovery without backup, forcing, or quick checking.
BACKUP	If the data file was changed during recovery, save a backup of the tbl_name.MYD file as tbl_name-datetime.BAK.
FORCE	Run recovery even if we would lose more than one row from the .MYD file.
QUICK	Do not check the rows in the table if there are not any delete blocks.

14) Хранить каждую таблицу в отдельном файл (InnoDB)

Если размер файла с базой ibdata1 слишком большой, можно включить опцию

innodb_file_per_table=1

но это изменение коснется только новых таблиц.

15) Использование плагина InnoDB вместо встроенного InnoDB.

О том как это сделать, описано здесь http://www.opennet.ru/base/dev/innodb_buffer_size.txt.html

16) Использовать готовые шаблоны my*.cnf

По умолчанию при установке mysql устанавливаются и примеры конфигурационных файлов для различных ситуаций. Во FreeBSD они лежат здесь /usr/share/doc/mysql/:

• my-small.cnf — для систем с малым обьемом памяти (<=64Mb), в которых MySQL используется редко.
• my-medium.cnf — если памяти мало (32-64Mb) или MySQL используется совместно с другими приложениями (например Apache) и памяти около 128Mb.
• my-large.cnf, my-huge.cnf — для систем с большим обьемом памяти (512Mb, 1-2Gb), где MySQL играет главную роль.
• my-innodb-heavy-4G.cnf — 4Gb памяти, InnoDB, MySQL играет главную роль.

17) mysql_secure_installation

После установки (или уже потом) выполнить скрипт mysql_secure_installation. Правда во FreeBSD его нет. Но можно и вручную. Вот список команд:

UPDATE mysql.user SET Password=PASSWORD('NEWROOTPASSWORD') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host!='localhost';
DROP DATABASE test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

18) Компрессия таблиц

В InnoDB есть формат данных называемый Baracuda. Так вот, он поддерживает компрессию. Это позволяет снизить нагрузку на IO (диски) путём использования сжатия. Так же как рекомендация можно использовать размер блока записи 16Кб. Вот пример alter’a:

SET GLOBAL innodb_file_format=BARRACUDA;
ALTER TABLE `t1` ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;

19) MaatKit

Этот набор инструментов из разряда must have. Скачать его можно отсюда. Очень хорошее описание инструментов можно найти здесь

20) Горизонтальное масштабирование.

Есть такой проект http://prestodb.io/ , который позволяет распаралеливать 1 запрос на несколько серверов. Его используют google, facebook, twitter.

Можно так же использовать federated таблицы или партицирование.

21) Другие советы

http://www.percona.com/blog/2014/12/01/faster-restarts-for-mysql-and-percona-server-5-6-21/
http://www.percona.com/blog/2014/11/20/sys-schema-mysql-5-6-5-7/
http://www.percona.com/blog/2014/12/02/tips-from-the-trenches-for-over-extended-mysql-dbas/

22) Safe-Updates Mode

При включении данного режима нельзя выполнить операции Update / Delete без явного указания where/limit. Так сказать, защита от “чайника”. Подробнее