[MySQL, Администрирование баз данных] Оптимизация mysql комплексная

Ответить на тему

Автор

Сообщение

news_bot ^®

Стаж: 6 лет 9 месяцев
Сообщений: 27286

news_bot ^® написал(а)
29-Янв-2021 04:30

Цитировать

Доброго времени суток, уважаемые хабровчане.
Сегодня речь пойдет опять и снова про 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="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

Не будем заниматься бездумной подстановкой, а пройдемся по параметрам 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 калькулятором, который подскажет основные параметры и позволит не выходить за пределы доступной памяти — как-никак всё упирается в неё:
Спасибо за внимание. Присоединяйтесь к обсуждению.,
===========
Источник:
habr.com
===========
Похожие новости:

Теги для поиска: #_mysql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_mysql, #_optimizatsija_mysql (оптимизация mysql), #_mysql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
)

Профиль ЛС

Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 23-Ноя 03:00
Часовой пояс: UTC + 5