28.01.2021 22:41
Ivan_krilov79 18 3400 Источник
Доброго времени суток, уважаемые хабровчане.

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

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