Сегодня речь пойдет опять и снова про mySQL. Разберемся в оптимизации и поговорим про множество параметров сервера.
Давайте приступать.
Начало
Сервер у нас пусть будет на CentOS. Оптимизировать будем методом правки конфига my.cnf .
Настройка некоторых параметров может повысить
производительность БД сервера в несколько раз!
Для начала давайте определимся, что мы вообще оптимизируем т.е сколько каких таблиц на каком движке имеем, какая железка у нас есть и под какие параметры мы будем всё это дело подгонять.
Для этого возьмем htop (как красивый и наглядный инструмент):
yum install htop
Выведем htop :
htop
Получаем нечто такое:
Запишем себе в my.cnf:
# 3 ядра, 4гб оперативной памяти
Теперь давайте узнаем количество таблиц и их типы.
Для этого возьмем mysql tuner:
wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Запустим:
perl mysqltuner.pl
Вывод примерно:
Запишем себе в my.cnf:
# 64M myisam, 770M innoDB
Типовой конфиг обычно рекомендуют какой-то такой:
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english old_passwords = 0 bind-address = 127.0.0.1 skip-external-locking max_allowed_packet = 16M key_buffer_size = 16M innodb_buffer_pool_size = 2048M innodb_file_per_table = 1 innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 0 max_connections = 144 <a href="http://personeltest.ru/aways/ruhighload.com/query_cache_size+%d0%bf%d0%b0%d1%80%d0%b0%d0%bc%d0%b5%d1%82%d1%80+%d0%b2+mysql" target="_blank" style="color: rgb(232, 95, 99);">query_cache_size</a> = 0 slow_query_log = /var/log/mysql/mysql-slow.log long_query_time = 1 expire_logs_days = 10 max_binlog_size = 100M [mysqldump] quick quote-names max_allowed_packet = 16M
Теперь давайте разбираться, что мы будем оптимизировать здесь, зачем, как и почему (особенно почему этих параметров маловато.
Оптимизация и конфиг
Для начала можно пролистать в конец вывода mysql tuner и посмотреть, что же он там рекомендует. В нашем случае это выглядит как-то так:
wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl perl mysqltuner.pl
Не будем заниматься бездумной подстановкой, а пройдемся по параметрам mysql , которые могут нас интересовать в первую очередь. Что к чему:
skip-external-locking, убирает внешнюю блокировку, что быстрее;
skip-name-resolve , позволяет MySQL избегать ответа на запрос DNS при проверке подключения клиентов к серверу MySQL .
Таким образом, сервер MySQL будет использовать только
IP -адреса, а не имена хостов, что немного, но быстрее.
binlog_cache _ size, размер кэша для хранения изменений в двоичном журнале. Задает размер только для кэша транзакций. Сделаем 100M больше не нужно.
innodb_stats_on_metadata = 0 (OFF), для ускорения работы с
INFORMATION_SCHEMA, SHOW TABLE STATUS или SHOW INDEX отключим обновление статистики при выполнении таких операций
quer y _cache_size = 128M и query_сache_type
= 1, кэши запросов. 1 в принципе включен, 128M ограничение. Не
рекомендуется ставить выше 256M , т.к это может привести к блокировке.
Так как у нас большеInnoDB таблиц, то зануляем cache _ size .
С версии MySQL 5.6 query_cache_size отключен, а с версии 8.0 удален
Стандартно все таблицы и индексы хранятся в одном файле, поэтому используем innodb_file_per_table = 1.
Значение innodb_open_files и table_open_cache рекомендуется устанавливать обе опции в 4096 или 8192 . А вообще рассчитывается как количество таблиц во всех базах, умноженное на 2 , ориентировочно.
При работе с InnoDB является важнейшим параметр innodb_buffer_pool_size , он устанавливается по принципу чем больше, тем лучше. Рекомендуется выделять до 70-80% оперативной памяти сервера.
innodb_log_file_size влияет на скорость записи, устанавливает размер лога операций (операции сначала записываются в лог, а потом применяются к данным на диске). Чем больше этот лог, тем быстрее будут работать записи (т.к. их поместится больше в файл лога). Файлов всегда два, а их размер одинаковый. Значением параметра задается размер одного файла.
ВНИМАНИЕ!При изменении параметра innodb_log_file_size остановите MySQL, сделайте резервную копию файлов ib_logfile-n (файлы чаще всего лежат в /var/lib/mysql/), измените значение параметра innodb_log_file_size и запустите MySQL. В результате
MySQL создаст новый лог-файл указанного в конфигурации размера.
Установка большого размера innodb_log_file_size может привести к увеличению быстродействия, но при этом увеличится время восстановления данных, выберите от 256M до 1G .
innodb_log _ buffer_size размер буфера транзакций. Обычно рекомендуется не применять, если не используете BLOB и TEXT больших размеров.
innodb_flush _ method, определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узлов, вы будете выбирать между ODSYNC и ODIRECT, первый параметр быстрее, второй безопаснее.
key_buffer _ size буфер для работы с ключами и индексами, и sort_buffer буфер для сортировки. Если Вы не используете MyISAM таблицы, рекомендуется установить размер key_buffer_size в 32Мб для хранения индексов временных
таблиц.
Параметр thread_cache _ size указывает количество тредов (threads), уходящих в кеш при отключении клиента. При новом подключении тред не создается, а берется из кеша, что позволяет экономить ресурсы при больших нагрузках.
innodb_flush_log_attrx_commit, может повысить пропускную способность записи данных в базу в сотни раз. Он определяет, будет ли Mysql сбрасывать каждую операцию на диск (в файл лога).
innodb_flush_log_at_trx_commit = 1 используется для случаев,
когда сохранность данных это приоритет номер один.
innodb_flush_log_at_trx_commit = 2 для случаев, когда небольшая потеря данных не критична. Есть еще 0 (ноль) самый производительный, но небезопасный вариант.
max_connections если вы получаете ошибки " Too many connections ", эту опцию стоит увеличить. А так большой пользы в оптимизации от неё нет.
Количество потоков ввода/вывода файлов в InnoDB задается опциями innodb_read_io_threads , innodbwrite_io_threads, обычно этому параметру присваивается значение 4 или 8 , на быстрых SSD -дисках установите в 16. Значение innodb_thread_concurrency установите в количество ядер * 2 .
Конфиг получается вот такой:
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english old_passwords = 0 bind-address = 127.0.0.1 skip-external-locking skip-name-resolve binlog_cache_size = 100M thread_cache_size = 32 innodb_stats_on_metadata = OFF query_cache_limit = 1M query_cache_size = 0 query_cache_type = 1 innodb_buffer_pool_size = 3G innodb_log_file_size = 256М innodb_log_buffer_size = 6M innodb_additional_mem_pool_size = 16M innodb_flush_method = O_DSYNC innodb_flush_log_at_trx_commit = 0 innodb_thread_concurrency = 6 innodb_file_per_table = 1 key_buffer_size = 32M tmp_table_size = 64M max_connections = 350 sort_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M join_buffer_size = 8M thread_stack = 1M binlog_cache_size = 8M tmp_table_size = 128M table_open_cache = 2048 [mysqldump] quick quote-names max_allowed_packet = 16M
,Ну и напоследок можно посмотреть рекомендации тюнера и последовать им.
Заключение
Вот такой вот интересный конфиг получился. Если Вам сложно, то на первых порах стоит пользоваться mySQL калькулятором, который подскажет основные параметры и позволит не выходить за пределы доступной памяти как-никак всё упирается в неё:
Спасибо за внимание. Присоединяйтесь к обсуждению.,
