Оптимизация mysql: Оптимизация mysql комплексная / Хабр

Содержание

Как оптимизировать базы MySQL/MariaDB | Сеть без проблем

В этой статье мы будем исследовать некоторые методы сжатия таблицы / базы данных и дефрагментации в MySQL / MariaDB, что поможет вам сэкономить место на диске , база данных расположена на.

Базы данных крупных проектов со временем безмерно разрастаются, и всегда возникает вопрос, что с ними делать. Есть несколько способов решить проблему. Вы можете уменьшить объем данных в базе данных, удалив старую информацию, разделив базу данных на более мелкие, увеличив размер диска на сервере или сжав / сжав таблицы.

Еще один важный аспект функционирования базы данных — необходимость время от времени дефрагментировать таблицы и базы данных для повышения их производительности.

Сжатие и оптимизация таблиц InnoDB

Файлы ibdata1 и ib_log

Большинство проектов с таблицами InnoDB имеют проблемы с большими файлами ibdata1 и ib_log. В большинстве случаев это связано с неправильной конфигурацией MySQL/MariaDB или архитектурой БД. Вся информация из таблиц InnoDB хранится в файле ibdata1, пространство которого само не используется. Я предпочитаю хранить данные таблицы в отдельных файлах ibd*. Для этого добавьте в my.cnf следующую строку:

innodb_file_per_table

или

innodb_file_per_table = 1

Если ваш сервер настроен и у вас есть продуктивные базы данных с таблицами InnoDB, сделайте следующее:

Сделайте резервную копию всех баз данных на вашем сервере (кроме mysql и performance_schema). Вы можете получить дамп базы данных с помощью этой команды:
# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
После создания резервной копии базы данных остановите сервер mysql/mariadb;
Измените настройки в my.cfg;
Удалите файлы ibdata1 и ib_log;
Запустите демон mysql/mariadb;
Восстановить все базы из резервной копии:
# mysql -u [username] –p[password] [database_name] < [dump_file. sql]

После этого все таблицы InnoDB будут храниться в отдельных файлах, и ibdata1 перестанет экспоненциально расти.

Сжатие таблиц InnoDB

Вы можете сжимать таблицы с текстовыми данными / данными BLOB и экономить довольно много места на диске.

У меня есть база данных innodb_test, содержащая таблицы, которые потенциально могут быть сжаты, и поэтому я могу освободить место на диске. Прежде чем что-либо делать, я рекомендую сделать резервную копию всех баз данных. Подключитесь к серверу mysql:

# mysql -u root -p

Выберите нужную базу данных в консоли mysql:

# use innodb_test;

Чтобы отобразить список таблиц и их размеры, используйте следующий запрос:

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)"
FROM information_schema.TABLES
WHERE table_schema = "innodb_test"
ORDER BY (data_length + index_length) DESC;

Где innodb_test — имя вашей базы данных.

Некоторые таблицы могут быть сжаты. Возьмем для примера таблицу b_crm_event_relations. Запустите этот запрос:

mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;

После его запуска вы можете увидеть, что размер таблицы уменьшился с 26 МБ до 11 МБ из-за сжатия.

Сжимая таблицы, вы можете сэкономить много дискового пространства на вашем хосте. Однако при работе со сжатыми таблицами нагрузка на процессор возрастает. Используйте сжатие для таблиц db, если у вас нет проблем с ресурсами процессора, но есть проблема с дисковым пространством.

Сжатие таблиц MyISAM в MySQL / MariDB

Для сжатия таблиц Myisam используйте специальный запрос в консоли сервера вместо консоли mysql. Чтобы сжать таблицу, запустите следующее:

# myisampack -b /var/lib/mysql/test/modx_session

Где /var/lib/mysql/test/modx_session — это путь к вашей таблице. К сожалению, у меня не было большой таблицы и пришлось сжимать маленькие, но результат все равно можно было увидеть (файл был сжат с 25 МБ до 18 МБ):

# du -sh modx_session. MYD

25M modx_session.MYD

# myisampack -b /var/lib/mysql/test/modx_session

Compressing /var/lib/mysql/test/modx_session.MYD: (4933 records)
— Calculating statistics
— Compressing file
29.84%
Remember to run myisamchk -rq on compressed tables

# du -sh modx_session.MYD

18M modx_session.MYD

Я использовал в команде ключ -b. Когда вы добавляете его, таблица создается перед сжатием и помечается меткой OLD:

# ls -la modx_session.OLD

-rw-r----- 1 mysql mysql 25550000 Dec 17 15:20 modx_session.OLD

# du -sh modx_session.OLD

25M modx_session.OLD

Оптимизация таблиц и баз данных в MySQL и MariaDB

Для оптимизации таблиц и баз данных рекомендуется их дефрагментировать. Убедитесь, что в базе данных есть таблицы, требующие дефрагментации.

Откройте консоль MySQL, выберите базу данных и выполните этот запрос:

select table_name, round(data_length/1024/1024) as data_length_mb,
round(data_free/1024/1024) as data_free_mb from 
information_schema. tables where round(data_free/1024/1024) > 50 order
by data_free_mb;

Таким образом, вы отобразите все таблицы с не менее 50 МБ неиспользуемого пространства:

+-------------------------------+----------------+--------------+
| TABLE_NAME | data_length_mb | data_free_mb |
+-------------------------------+----------------+--------------+
| b_disk_deleted_log_v2 | 402 | 64 |
| b_crm_timeline_bind | 827 | 150 |
| b_disk_object_path | 980 | 72 |

data_length_mb — общий размер стола

data_free_mb — неиспользуемое место в столе

Это таблицы, которые мы можем дефрагментировать. Проверьте, сколько места они занимают на диске:

# ls -lh /var/lib/mysql/innodb_test/ | grep b_

-rw-r ----- 1 mysql mysql 402M 17 октября, 12:12 b_disk_deleted_log_v2.MYD
-rw-r ----- 1 mysql mysql 828M 17 октября 13:23 b_crm_timeline_bind.MYD
-rw-r ----- 1 mysql mysql 981M 17 октября, 11:54 b_disk_object_path. MYD

Чтобы оптимизировать эти таблицы, выполните следующую команду в консоли mysql:

# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;

После успешной дефрагментации вы увидите следующий результат:

+ ------------------------------- + ---------------- + -------------- +
| TABLE_NAME | data_length_mb | data_free_mb |
+ ------------------------------- + ---------------- + -------------- +
| b_disk_deleted_log_v2 | 74 | 0 |
| b_crm_timeline_bind | 115 | 0 |
| b_disk_object_path | 201 | 0 |

Как видите, data_free_mb теперь равно 0, а размер таблицы значительно уменьшился (в 3-4 раза).

Вы также можете запустить дефрагментацию, используя mysqlcheck в консоли сервера:

# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file

Где innodb_test ваша база данных и b_workflow_file название таблицы.

Чтобы оптимизировать все таблицы в базе данных, запустите эту команду в консоли сервера:

# mysqlcheck -o innodb_test -u root -p

Где innodb_test — имя базы данных

Или запустите оптимизацию всех баз на сервере:

# mysqlcheck -o --all-databases -u root -p

Если вы проверите размер базы данных до и после оптимизации, вы увидите, что общий размер уменьшился:

# du -sh

2,5 г

# mysqlcheck -o innodb_test -u root -p

innodb_test. b_admin_notify
note : Table does not support optimize, doing recreate + analyze instead
status : OK
innodb_test.b_admin_notify_lang
note : Table does not support optimize, doing recreate + analyze instead
status : OK
innodb_test.b_adv_banner
note : Table does not support optimize, doing recreate + analyze instead
status : OK

# du -sh

1,7 г

Таким образом, чтобы сэкономить место на вашем сервере, вы можете время от времени оптимизировать и сжимать свои таблицы и базы данных MySQL/MariDB. Не забудьте создать резервную копию базы данных перед выполнением любой работы по оптимизации.

Насколько публикация полезна?

Нажмите на звезду, чтобы оценить!

Средняя оценка / 5. Количество оценок:

Оценок пока нет. Поставьте оценку первым.

Статьи по теме:

Оптимизация производительности MySQL — Losst

MySQL — это одна из самых популярных реляционных систем управления базами данных, которая используется для обеспечения большинства веб-сайтов в интернете. От скорости записи и получения данных из таблиц зависит скорость работы сайта, в целом, так как, если на один запрос будет уходить больше секунды, то это будет тормозить работу php, а в следствии скоро накопиться столько запросов, что сервер не сможет их обработать.

В сегодняшней статье мы поговорим о том, как выполняется оптимизация производительности mysql. Какие программы для этого лучше использовать и как это работает.

Содержание статьи:

Скорость работы MySQL

Оптимизация без аналитики бессмысленна. Перед тем как переходить к оптимизации давайте посмотрим как работает база данных сейчас, есть ли запросы, которые выполняются очень медленно. Все настройки вашего сервиса mysql находятся в файле /etc/my.cnf. Чтобы включить отображение медленных запросов добавьте такие строки в my.cnf, в секцию [mysqld]:

log-slow-queries=/var/log/mariadb/slow_queries.log long_query_time=5

Здесь первая строка включает запись лога медленных запросов, вторая указывает, что минимальное время запроса для внесения его в этот лог — две секунды. Еще можно включить в лог запросы, которые не используют индексы:

log-queries-not-using-indexes=1

Но это уже необязательно для проверки скорости и используется больше для отладки кода и правильности создания таблиц. Дальше перезапустите сервер баз данных и посмотрите лог:

systemctl restart mariadb

tail -f /var/log/mariadb/slow-queries.log

Мы можем видеть, что есть запросы, которые выполняются больше, чем 10 секунд. Это, например, запрос

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Можно его выполнить отдельно, в консоли mysql:

Здесь тоже измеряется время, и мы видим результат — три секунды. Это очень много. И еще ничего, если такие запросы приходят редко, если ваш сайт постоянно под нагрузкой, то тремя секундами вы не отделаетесь, количество необработанных запросов будет расти, а скорость ответа увеличиваться до нескольких минут. Можно пойти двумя путями — оптимизировать код, убрать сложные запросы, или же нужна оптимизация mysql на сервере.

Оптимизация MySQL

Конфигурация MySQL достаточно сложная, но, к счастью, вам не нужно в нее сильно углубляться. Есть специальный скрипт под названием MySQLTunner, который анализирует работу MySQL и дает советы какие параметры нужно изменить и какие значения для них установить. Скрипт поддерживает большинство версий MariaDB, MySQL и Percona XtraDB. Нам понадобится загрузить три файла с помощью wget:

wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent. com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

Первый из них — это сам скрипт, написанный на Perl, второй и третий — база данных простых паролей и уязвимостей. Они позволяют обнаружить проблемы с безопасностью. Дальше можно переходить к тестированию. Я использую сервер с настройками mysql по умолчанию, установленными панелью управления VestaCP.

perl ./mysqltuner.pl

Буквально за несколько минут скрипт выдаст полную статистику по работе MySQL. Количеству запросов, занимаемому объему памяти и эффективности работы буферов. Вы можете ознакомиться со всем этим, чтобы лучше понять в чем причина проблем. Проблемные места обозначены красными восклицательными знаками. Например, здесь мы видим, что размер буфера движка таблиц InnoDB (InnoDB buffer pool) намного меньше, чем должен быть для оптимальной работы:

Кроме того, в самом конце вывода утилита предоставит список рекомендаций как исправить ситуацию. Мы рассмотрим все сообщения утилиты из этого примера и почему нужно использовать именно их, а не другие.

Все параметры нужно добавлять в /etc/my.cnf. Еще раз замечу, что вы не копируете статью, а смотрите что вам выдала утилита. Начнем с query-cache.

query_cache_size=0 query_cache_type=0 query_cache_limit=1M

Скрипт рекомендует отключить кэш запросов. Query Cache — это кэш вызовов SELECT. Когда базе данных отправляется запрос, она выполняет его и сохраняет сам запрос и результат в этом кэше. И все бы ничего, но при использовании его вместе с InnoDB при любом изменении совпадающих данных кэш будет перестраиваться, что влечет за собой потерю производительности. И чем больше объем кэша, тем больше потери. Кроме того при обновлении кэша могут возникать блокировки запросов. Таким образом, если данные часто пишутся в базу данных — его надежнее отключить.

tmp_table_size=16M max_heap_table_size=16M

Оба параметра устанавливают размер памяти, которая используется для внутренних временных таблиц MySQL. Утилита рекомендует использовать объем больше 16 мегабайт, просто установите это ваше значение для обоих переменных, если у вас достаточно памяти, то можно выделить 32 или даже 64. Но важно чтобы оба значения совпадали, иначе будет использоваться минимальное.

thread_cache_size=16

Этот параметр отвечает за количество потоков, которые будут закэшированны. После того, как работа с подключением будет завершена, база данных не разорвет его, а закэширует, если количество кэшированных потоков не превышает ограничение. Утилита рекомендует больше четырех, например, 16.

skip-name-resolve=1

Указывает, что не нужно пытаться определить доменное имя для подключений извне. Ускоряет работу, так как не тратится время на DNS запросы.

innodb_buffer_pool_size=800M

Этот параметр определяет размер буфера InnoDB в оперативной памяти, от этого размера очень сильно зависит скорость выполнения запросов. Значение зависит от размера ваших таблиц и количества данных в них. Если памяти недостаточно, запросы будут обрабатываться дольше. У меня используется стандартный объем 128, а нужно больше 652.

innodb_log_file_size=200M

Размер файла лога innodb должен составлять 25% от размера буфера. В случае 800 мегабайт это будет 200М. Но тут есть одна проблема. Чтобы изменить размер лога нужно выполнить несколько действий. Поскольку мы изменили все нужные параметры перейдем к перезагрузке сервера. Для нашего лога нужно остановить сервис:

systemctl stop mariadb

Затем переместите файлы лога в /tmp:

mv /var/lib/mysql/ib_logfile[01] /tmp

И запустите сервис:

systemctl start mariadb

Когда размер лога меняется сервис видит поврежденный лог, выдает ошибку и не запускается. Поэтому сначала нужно удалить старый. После этого смотрите есть ли сообщения об ошибках:

systemctl status mariadb

Тестирование результата

Готово оптимизация базы данных mysql завершена, теперь тестируем тот же запрос через клиент mysql:

mysql

> USE база_данных;
> SELECT option_name, option_value FROM wpfc_options WHERE autoload = 'yes';

Первый раз он выполняется долго, может даже дольше чем обычно, но все последующие разы буквально мгновенно. Результат с более 3 секунд до 0,15. А если брать статистику из slow-log, то от более 12. Если в выводе утилиты для вас были предложены и другие оптимизации, то их тоже стоит применить.

Выводы

Как видите, оптимизация mysql это достаточно просто благодаря такому скрипту, но, в то же время, такая операция может очень сильно помочь, особенно высоконагруженным проектам. Еще лучше ускорить работу может только оптимизация запросов mysql. Не забывайте время от времени проверять параметры, чтобы быть уверенным что все в порядке. Если у вас остались вопросы, спрашивайте в комментариях!

На завершение лекция про производительность MySQL от Percona:

советов по настройке производительности MySQL (которые работают в 2022 году)

MySQL, будучи самой популярной системой управления реляционными базами данных, все еще время от времени требует оптимизации. Более того, в случае
больших и сложных наборов данных регулярные действия по оптимизации необходимы для правильной работы системы.

Оптимизация производительности MySQL обычно включает настройку, профилирование и мониторинг производительности на нескольких уровнях. Чтобы настроить производительность MySQL, вы
не обязательно иметь обширный опыт и глубокое понимание SQL.

В этой статье мы познакомим вас с основными методами настройки производительности, чтобы вы могли обеспечить стабильность, надежность и скорость
ваши приложения, управляемые базой данных.

Проверьте рекомендуемые требования к аппаратному и программному обеспечению для MySQL

Первое, что нужно сделать, особенно если вы владелец слабого ПК, это проверить оптимальные аппаратные и программные требования для MySQL,
поскольку аппаратные ограничения могут существенно повлиять на производительность.

Минимальные аппаратные требования к серверу баз данных MySQL (для версий 5.7–8.0):

Процессор 1 ГГц
512 МБ ОЗУ
Место на жестком диске в зависимости от размера базы данных

Также стоит упомянуть, что лучше использовать самую последнюю официальную версию MySQL, если это возможно.

Оптимизация использования памяти, диска и процессора

На аппаратном уровне вы можете предпринять ряд действий для улучшения аппаратных и программных ресурсов.

Место на диске
Если вы используете традиционный жесткий диск (HDD) и хотите повысить производительность, вам следует подумать о переходе на SSD.
Официальная документация MySQL явно не определяет параметры дискового пространства или памяти, необходимые для эффективной работы сервера MySQL, поскольку
в первую очередь они зависят от размера потенциальной базы данных или баз данных. Тем не менее, было бы неплохо контролировать производительность вашего диска, используя
sar и iostat средства повышения производительности системы, например. Если использование диска значительно превышает использование других ресурсов, вам следует
определенно добавьте больше памяти или обновитесь до более быстрой.

ОЗУ
Нехватка памяти также может серьезно повлиять на производительность базы данных. Это может показаться банальным, но если на вашем сервере регулярно не хватает памяти и
Производительность RAM Disk не удовлетворяет, стоит добавить больше памяти.
Когда у вас заканчивается оперативная память, сервер MySQL кэширует физическую память, что снижает производительность. Таким образом, оптимизация памяти MySQL чрезвычайно важна.

ЦП
Оптимизация использования процессора MySQL должна начинаться с тщательного анализа процессов MySQL, происходящих на вашем компьютере, и требуемого процента использования процессора.
Обновление ЦП недешево, однако, если это узкое место, обновление будет необходимо.

Интернет-соединение
Сеть является важной частью инфраструктуры MySQL, и важно отслеживать и анализировать сетевой трафик, чтобы убедиться, что у вас достаточно ресурсов.
для управления вашими рабочими нагрузками. Убедитесь, что у вас хорошее и стабильное подключение к Интернету для правильной работы сервера MySQL.

Инструменты для настройки производительности программного обеспечения

Как мы уже упоминали, вы можете оптимизировать производительность MySQL на аппаратном и программном уровнях. Давайте теперь посмотрим на настройку производительности программного обеспечения MySQL.

Настройка производительности MySQL с точки зрения программного обеспечения включает в себя настройку параметров сервера MySQL, повышение производительности запросов MySQL, настройку MySQL
индексы, переход на движок хранения MySQL InnoDB и т. д. Рассмотрим все это подробно.

Использование индекса MySQL для повышения производительности

Правильная индексация для повышения производительности непроста и требует определенного уровня знаний, однако это одно из лучших улучшений производительности, которое вы можете сделать для повышения производительности.
ваша база данных.

MySQL использует индексы в качестве книжного указателя или дорожной карты для быстрого поиска значений для заданного запроса. Без индексов MySQL будет сканировать всю таблицу построчно, чтобы найти нужные данные.
Таким образом, оптимизация индекса направлена на ускорение извлечения данных. Индексы невидимы для пользователей и содержат информацию о том, где хранятся фактические данные. Это также стоит
отметив, что длина индекса MySQL для таблиц InnoDB имеет ограничения в зависимости от формата строки.

Индексы MySQL чрезвычайно полезны для больших наборов данных, и настройка индекса — это правильное решение, если ваша база данных быстро растет. Индексы особенно полезны для следующих
операции: поиск строк, соответствующих предложению WHERE, получение данных с помощью JOIN, сортировка и группировка данных с помощью ORDER BY и GROUP BY.

Так почему бы тогда не вставить как можно больше индексов? Это было бы плохой идеей — ненужные индексы занимают место и тратят время системы, не говоря уже о том, что
они также увеличивают стоимость запросов, поскольку необходимо обновлять индексы. Таким образом, вы должны найти правильный баланс для достижения оптимального использования индекса MySQL.

Повышение производительности с помощью InnoDB

Одним из первых советов по настройке для тех, у кого большая нагрузка на базу данных, будет попытка переключиться на InnoDB с механизма хранения MyISAM.
Имея кластеризованный индекс с данными на страницах и последовательными физическими блоками, InnoDB имеет лучшую производительность для больших объемов данных по сравнению с MyISAM.

InnoDB также может похвастаться богатым набором переменных и дополнительных параметров, которые можно настроить для дальнейшего повышения производительности MySQL. Параметры производительности InnoDB
более обширны, и поэтому существует больше способов настроить InnoDB для повышения производительности по сравнению с настройкой MyISAM.

Оптимизация запросов MySQL

Теперь давайте посмотрим, как оптимизировать запрос MySQL для повышения производительности и скорости. Для тех, кто хочет улучшить запросы MySQL, это было бы
рекомендуется следовать следующим методам оптимизации.

Добавьте индексы к столбцам, используемым в предложениях WHERE, ORDER BY и GROUP BY
Таким образом, вы повысите производительность запросов MySQL, поскольку сервер MySQL будет извлекать результаты из базы данных значительно быстрее.

Укажите необходимые столбцы в операторах SELECT
Старайтесь избегать использования SELECT * FROM, так как он извлекает все столбцы таблицы, что создает дополнительную нагрузку на сервер и снижает его производительность.
Возьмите за правило всегда указывать столбцы в операторах SELECT.

Используйте DISTINCT и UNION с осторожностью
Еще один хороший совет по настройке запросов — использовать операторы DISTINCT и UNION только в случае необходимости, поскольку запросы с ними приводят к накладным расходам на сервер и, как правило, увеличивают нагрузку на сервер.
время отклика. Попробуйте заменить UNION на UNION ALL и DISTINCT на GROUP BY, чтобы повысить эффективность процесса.

Избегайте использования подстановочных знаков в начале шаблонов LIKE
Запросы MySQL с операторами LIKE часто приводят к падению производительности сервера, поэтому их следует использовать осторожно. MySQL не может использовать индексы, когда LIKE
шаблон начинается с подстановочного знака, например, ‘%xyz’, и в этом случае выполняет полное сканирование таблицы. Вы должны помнить об этом при оптимизации запросов MySQL и
попробуйте вместо этого использовать ‘xyz%’, когда это возможно.

Использовать ВНУТРЕННИЕ СОЕДИНЕНИЯ вместо ВНЕШНИХ СОЕДИНЕНИЙ
Используйте OUTER JOIN только при необходимости. MySQL выполняет гораздо больше работы по получению результатов для ВНЕШНИХ СОЕДИНЕНИЙ по сравнению с ВНУТРЕННИМИ СОЕДИНЕНИЯМИ. Рекомендуем проверить работоспособность
ваших запросов JOIN, и в случае, если это вас не устраивает, начните преобразовывать ваши ВНЕШНИЕ СОЕДИНЕНИЯ во ВНУТРЕННИЕ СОЕДИНЕНИЯ, когда это возможно.
Оптимизация MySQL JOIN может привести к драматическим последствиям.
улучшение производительности.

Настройка параметров сервера для повышения производительности

Теперь давайте сосредоточимся на том, как оптимизировать параметры сервера MySQL с точки зрения настройки производительности. Для этого вам нужно будет настроить файл конфигурации (my.cnf/my.ini).

innodb_buffer_pool_size
Этот параметр указывает объем памяти, выделенный MySQL для пула буферов InnoDB. Рекомендуемое значение этого параметра составляет 70-80% от доступного.
объем памяти. Чем больше ваши наборы данных, тем больше должно быть значение.

макс_соединение
Этот параметр определяет максимально допустимое количество одновременных клиентских подключений и имеет значение по умолчанию 151. Во избежание получения сообщения «Слишком много подключений»
ошибка, значение может быть увеличено. Однако имейте в виду, что слишком много открытых подключений может повлиять на производительность.

query_cache_size
Этот параметр задает общий объем памяти, выделенный для кэша запросов. Оптимальное значение для него зависит в первую очередь от вашего рабочего случая и должно быть
определяется ориентировочно. Идея состоит в том, чтобы начать с очень малого, например, 10 МБ, а затем постепенно увеличивать его до 100–200 МБ. Настройка query_cache_size,
не забудьте включить кеш запросов (тип кеша запросов ON). Обратите внимание, что большой размер кэша запросов может привести к серьезному снижению производительности.

innodb_io_capacity
Этот параметр указывает количество операций ввода-вывода в секунду, разрешенное для задач, выполняемых в фоновом режиме, и имеет значение по умолчанию 200. Как правило,
значение около 100 подходит для жестких дисков среднего уровня, тогда как для более быстрых и современных устройств хранения данных более высокие значения будут выгодны.

innodb_log_file_size
Этот параметр указывает размер в байтах для каждого файла журнала повторов MySQL в группе журналов и имеет значение по умолчанию 134 217 728 (около 128 МБ). innodb_log_files_in_group
Параметр, в свою очередь, указывает количество лог-файлов в лог-группе и имеет значение по умолчанию 2.
Если значение innodb_log_file_size мало для вашей рабочей нагрузки, а ваше приложение интенсивно записывает,
мы рекомендуем увеличить его. Однако слишком большое значение innodb_log_file_size увеличит время восстановления после сбоя. Так что вам придется найти его оптимальный размер.

Профилирование MySQL и оптимизация запросов с помощью

dbForge Studio для MySQL

dbForge Studio для MySQL поставляется с расширенным профилировщиком MySQL, который позволяет собирать максимально полную статистику о выполненных запросах,
обнаружение медленных запросов и устранение проблем с производительностью любого рода.

С помощью инструмента настройки производительности dbForge MySQL вы можете:

Оптимизация запросов с помощью плана EXPLAIN
Статистика сеанса мониторинга
Сравнение результатов профилирования запросов
Определить самые дорогие запросы

Правила оптимизации производительности MySQL

Всегда проверяйте результат своих усилий по оптимизации в тестовой среде
Никогда не оптимизируйте без сравнительного анализа
Меняйте только одну вещь за раз
Добавьте мониторинг производительности в свою повседневную жизнь
Задокументировать результаты

Настройка производительности MySQL: советы, сценарии и инструменты

23 апреля 2022 г. by Хейден Джеймс , в блоге Linux

Распространенные ошибки конфигурации MySQL могут привести к серьезным проблемам с производительностью. Если вы неправильно настроите хотя бы один из многих параметров конфигурации, это может снизить производительность. Конечно, производительность MySQL часто связана с эффективностью ваших запросов MySQL. Важно убедиться, что проблемы с производительностью не связаны с плохо написанными запросами MySQL. Вы можете использовать журнал медленных запросов MySQL, log_queries_not_using_indexes или инструменты APM, которые предлагают мониторинг производительности MySQL, такие как Datadog, AppOptics, New Relic и другие инструменты мониторинга.

Настройка MySQL — довольно обширная тема. Сегодня я не буду пытаться размещать здесь какие-либо рекомендуемые строки конфигурации, значения или настройки. Будьте очень осторожны со статьями, основанными на рекомендуемых настройках. В этом посте предполагается, что вы уже оптимизировали свои запросы и теперь ищете рекомендации по выбору лучших параметров конфигурации производительности (например, my. cnf) для MySQL. Это может сильно различаться в каждом конкретном случае, так как очень мало универсальных советов. Поэтому советы являются дополнительными ссылками на популярные бесплатные скрипты и инструменты настройки MySQL.

Будьте в курсе последних версий сервера MySQL

С каждой выпущенной новой версией MySQL происходят существенные улучшения производительности и возможностей по сравнению с предыдущими версиями. Так что самым важным советом будет апгрейд, апгрейд, апгрейд. Взгляните на некоторые сравнения производительности версий здесь.

Если вам нужны дополнительные функции или гибкость, возможно, вы уже используете MariaDB или Percona, расширенные замены для MySQL Server. Если вы заметили заметные улучшения в использовании MariaDB или Percona по сравнению со стандартным MySQL, поделитесь своим опытом ниже. Они оба отличные варианты.

Выбор механизма хранения MySQL

Это просто, используйте InnoDB и по возможности избегайте MyISAM. По этим причинам:

Версии MySQL 5.5 и выше перешли на движок InnoDB, чтобы обеспечить ограничения ссылочной целостности и более высокий параллелизм.
InnoDB имеет лучшее восстановление после сбоя.
InnoDB имеет блокировку на уровне строк; MyISAM может выполнять только полную блокировку на уровне таблицы.
Как и MyISAM, InnoDB теперь имеет индексы поиска FULLTEXT, начиная с MySQL 5.6
InnoDB поддерживает транзакции, внешние ключи и ограничения отношений; MyISAM нет.

Сценарии настройки производительности MySQL

Вы не можете заменить профессиональную настройку MySQL сценариями. Сценарии служат важными руководствами, иногда точными, но в большинстве случаев нечеткими руководствами, которые решат только самые серьезные неправильно настроенные параметры. Используйте их в качестве отправной точки. Это означает, что прежде чем вы обратитесь к профессионалу для настройки MySQL, используйте эти сценарии настройки, чтобы, по крайней мере, у вас не было так называемой неудобной конфигурации в вашем файле my. cnf. Например, для параметра join_buffer_size установлено значение 4 ГБ, если общий размер БД меньше 1 ГБ.

Теперь рассмотрим популярные скрипты и инструменты для настройки производительности MySQL: MySqlTuner, Tuning-Primer, MySQLreport, Percona Toolkit и phpMyAdmin Advisor.

MySQLTuner

Этот скрипт, написанный на Perl, поможет вам настроить MySQL и даст рекомендации по повышению производительности и стабильности.

MySQLTuner поддерживает Galera Cluster, TokuDB, Performance schema, метрики ОС Linux, InnoDB, MyISAM, Aria и т. д. — MySQLTuner на Github.

Тюнинг-праймер

Этот сценарий берет информацию из команд «ПОКАЗАТЬ СОСТОЯНИЕ, КАК…» и «ПОКАЗАТЬ ПЕРЕМЕННЫЕ, КАК…», чтобы дать разумные рекомендации по настройке переменных сервера.

Исходный скрипт больше не обновляется. Я использовал эту версию Tuning-primer на Github, полностью поддерживающую MariaDB.

Набор инструментов Percona

Percona Toolkit — это набор передовых инструментов командной строки с открытым исходным кодом, разработанных для выполнения различных задач MySQL, которые слишком сложны или сложны для выполнения вручную, освобождая ваших администраторов баз данных для работы, которая помогает вам достичь ваших бизнес-целей.

Полезные инструменты включают: pt-align, pt-archiver, pt-config-diff, pt-deadlock-logger, pt-diskstats, pt-duplicate-key-checker, pt-fifo-split, pt-find, pt-fingerprint , pt-fk-error-logger, pt-heartbeat, pt-index-usage, pt-ioprofile, pt-kill, pt-mext, pt-mongodb-query-digest, pt-mongodb-summary, pt-mysql-summary , pt-online-schema-change, pt-pg-summary, pt-pmp, pt-query-digest, pt-secure-collect, pt-show-grants, pt-sift, pt-slave-delay, pt-slave -find, pt-slave-restart, pt-stalk, pt-summary, pt-table-checksum, pt-table-sync, pt-table-usage, pt-upgrade, pt-variable-advisor и pt-visual-explain .

Советник phpMyAdmin

Система Advisor предоставляет рекомендации по серверным переменным, анализируя переменные состояния MySQL.

phpMyAdmin — это бесплатный программный инструмент, написанный на PHP и предназначенный для администрирования MySQL через Интернет. Посетите: PHPMyAdmin.

Mysqlотчет

Mysqlreport преобразует значения из SHOW STATUS в удобный для чтения отчет, который обеспечивает глубокое понимание того, насколько хорошо работает MySQL.