Главная

Оптимизация mysql комплексная

вт, 02/02/2021 - 11:25 — Yuriy Kanakov
Сегодня речь пойдет опять и снова про 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

Вывод примерно:

image

Запишем себе в ​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="https://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

image

Не будем заниматься бездумной подстановкой, а пройдемся по параметрам ​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 ​ калькулятором, который подскажет основные параметры и позволит не выходить за пределы доступной памяти — как-никак всё упирается в неё:

Спасибо за внимание. Присоединяйтесь к обсуждению.
  • 16097 просмотров

Сейчас на сайте

Сейчас на сайте 0 пользователей и 0 гостей.