Содержание

Настройка и оптимизация MySQL и MariaDB

MySQL и MariaDB являются наиболее широко используемыми системами управления реляционными базами данных (RDMS), когда речь идет о хостинге веб-сайтов и системах CMS, таких как Joomla, WordPress, Drupal и Typo 3.

Содержание

  1. Храните данные MySQL в отдельных разделах
  2. Установите максимальное количество соединений MySQL
  3. Включить журнал медленных запросов MySQL
  4. Установите максимальный пакет, разрешенный MySQL
  5. Настройка емкости временной таблицы
  6. Настройте максимальный объем таблицы памяти
  7. Отключение обратного поиска DNS для MySQL
  8. Избегайте использования свопинга в MySQL
  9. Увеличение размера буферного пула InnoDB
  10. Работа с размером кэша запросов
  11. Проверка неиспользуемых соединений
  12. Восстановление базы данных MySQL
  13. Проверка производительности MySQL/MariaDB с помощью инструментов тестирования

Храните данные MySQL в отдельных разделах

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

Установите максимальное количество соединений MySQL

MySQL/MariaDB использует инструкцию max_connections, которая определяет, сколько одновременных соединений в данный момент разрешено на сервере. Слишком большое количество соединений приводит к большому потреблению памяти, а также к высокой загрузке процессора. Для небольших сайтов количество соединений может быть определено в 100-200, а для крупных может потребоваться 500-800 и более. Значение max_connections можно динамически изменять с помощью SQL-запроса.

mysql -u root -p
mysql> set global max_connections=200;

Включить журнал медленных запросов MySQL

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

slow-query-log=1
slow-query-log-file= /var/lib/mysql/mysql-slow-query.log
long-query-time=1

Где первая переменная включает журнал медленных запросов

Вторая переменная определяет каталог файла журнала

Третья переменная определяет время выполнения запроса MySQL.

Перезапустите службу MySQL/MariaDB и проследите за журналом

systemctl restart mysql
systemctl restart mariadb
 tail -f /var/lib/mysql/mysql-slow-query.log

Установите максимальный пакет, разрешенный MySQL

В MySQL данные разбиваются на пакеты. Max_allowed_packet определяет максимальный размер пакетов, которые могут быть отправлены. Установка слишком низкого значения max_allowed_packet может привести к слишком медленному выполнению запроса. Рекомендуется устанавливать значение packet равным размеру самого большого пакета.

Настройка емкости временной таблицы

Tmp_table_size — это максимальное пространство, используемое для таблицы встроенной памяти.

Если размер таблицы превышает указанный предел, она будет преобразована в таблицу MyISAM на диске. В MySQL/MariaDB вы можете добавить следующие переменные в конфигурационный файл для настройки временного размера таблицы. Рекомендуется установить это значение на сервере 64M на Гб памяти.

[mysqld]

tmp_table_size=64M

Перезапустите службу mysql

systemctl restart mysql
systemctl restart mariadb

Настройте максимальный объем таблицы памяти

Max_heap_table_size — это переменная, используемая в MySQL для настройки максимального объема таблицы памяти. Размер максимальной емкости таблицы памяти должен быть таким же, как и емкость временной таблицы, чтобы избежать записи на диск. Рекомендуется установить это значение на сервере равным 64M на ГБ памяти. Добавьте следующую строку в конфигурационный файл MySQL и перезапустите службу.

[mysqld]
max_heap_table_size=64M

 

Чтобы применить изменения, перезапустите сервер базы данных.

systemctl restart mysql
systemctl restart mariadb

Отключение обратного поиска DNS для MySQL

При получении нового соединения MySQL/MariaDB выполняет поиск DNS для определения IP-адреса пользователя. Это может вызвать задержку, если конфигурация DNS недействительна или есть проблемы с DNS-сервером. Чтобы отключить поиск DNS, добавьте следующую строку в конфигурационный файл MySQL и перезапустите службу MySQL.

[mysqld]
skip-name-resolve

Перезапустите службу:

systemctl restart mysql
systemctl restart mariadb

Избегайте использования свопинга в MySQL

Ядро Linux перемещает часть памяти в специальный раздел диска, называемый «swap» пространством, когда в системе заканчивается физическая память. В этом случае система записывает информацию на диск, а не освобождает часть памяти. Поскольку системная память быстрее дискового хранилища, рекомендуется отключить своппинг. Отключить своппинг можно с помощью следующей команды.

sysctl -w vm.swappiness=0

Увеличение размера буферного пула InnoDB

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

Пример.

Системная память = 4 ГБ

Размер буферного пула = 3,2 ГБ

Добавьте следующую строку в конфигурационный файл MySQL и перезапустите службу

[mysqld].
Innodb_buffer_pool_size=3.2G

Перезапустите базу данных:

systemctl restart mysql
systemctl restart mariadb

Работа с размером кэша запросов

query_cache_size был удален в MySQL 8.

Директива Query cache в MySQL/MariaDB используется для кэширования всех запросов, которые постоянно повторяются с одними и теми же данными. Для небольших сайтов рекомендуется установить значение 64MB и со временем увеличивать его. Увеличивать размер кэша запросов до гигабайтов не рекомендуется, так как это может ухудшить производительность базы данных. Добавьте следующую строку в файл my.cnf.

[mysqld]
query_cache_size=64M

Проверка неиспользуемых соединений

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

mysqladmin processlist -u root -p | grep "Sleep"

Запрос выведет список процессов, которые находятся в состоянии сна. Как правило, в PHP это событие может произойти при использовании mysql_pconnect. Это открывает соединение с MySQL, выполняет запросы, снимает аутентификацию и оставляет соединение открытым. С помощью директивы wait_timeout простаивающие соединения могут быть прерваны.

Значение по умолчанию для wait_timeout равно 28800 секунд, которое можно уменьшить до минимального значения, например 60 секунд. Добавьте следующую строку в файл my.cnf

[mysqld]
wait_timeout=60

Восстановление базы данных MySQL

Если сервер неожиданно выключается, существует вероятность того, что таблицы в MySQL/MariaDB могут упасть. Существуют и другие возможные причины сбоя таблиц базы данных, например, доступ к базе данных во время выполнения процесса копирования, внезапный сбой файловой системы. На этот случай у нас есть специальный инструмент под названием «mysqlcheck», который проверяет, ремонтирует и оптимизирует все таблицы в базах данных.

Используйте следующую команду для выполнения действий по ремонту и оптимизации.

Для всех баз данных:

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

Для конкретной базы данных:

mysqlcheck -u root -p --auto-repair --check --optimize dbname

Замените dbname на имя вашей базы данных

Проверка производительности MySQL/MariaDB с помощью инструментов тестирования

Наилучшей практикой является регулярная проверка производительности баз данных MySQL/MariaDB. Это позволит легко получить отчет о производительности и точки улучшения. Существует множество инструментов, среди которых лучшим является mysqltuner.

Выполните следующую команду, чтобы загрузить инструмент

wget https://github.com/major/MySQLTuner-perl/tarball/master

Разархивируйте файл

tar xvzf master

Перейдите в каталог проекта и выполните следующий скрипт.

cd major-MySQLTuner-perl-7aa57fa
 ./mysqltuner.pl

Про использование mysqltuner можно почитать в статье «Оптимизация работы MySQL»

Оптимизация MySQL с помощью mysqltuner

Оптимизация MySQL становится необходимой когда замечена нагрузка на систему, создаваемая сервисом и задержки в работе сайтов.

Основной конфигурационный файл сервиса располагается по пути /etc/mysql/my.cnf.

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

include.


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

За счет оптимизации MySQL можно добиться максимальной производительности и скорости работы для проекта.

За исходные условия принимаются:

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

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

Подбор параметров можно проводить вручную или с помощью скрипта MySQLTuner. Второй вариант проще и используется чаще. Будем разбирать его.

Чтобы воспользоваться скриптом нужно есть скачать

cd /tmp && wget http://mysqltuner.pl/ -O mysqltuner.pl

Потом скрипт можно запускать (под пользователем root)

perl mysqltuner. pl

Скрипт выдаст большой объем данных — укажет всё что он проверяет, в конце будут рекомендации, которые можно использовать для оптимизации MySQL, пример приведен на скриншоте


Чтобы результат был корректным сервис MySQL должен проработать после последнего перезапуска не менее 24 часов.

Если условие не выполняется mysqltuner выдаст предупреждение об этом.

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

Рекомендации mysqltuner касающиеся настроек на машине, взятой для демонстрации:

skip-name-resolve=1
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
performance_schema=ON
key_buffer_size (~ 24M)
innodb_log_file_size should be (=16M) if possible, so InnoDB total log file size equals 25% of buffer pool size.

Рассмотрим параметры и добавим их, но перед этим покажем как проходит процесс проверки текущих значений MySQL.


Как проверить текущие значения параметров MySQL

Чтобы увидеть текущие значения используемые службой, нужно зайти в консоль MySQL с реквизитами пользоватя root.

Подключившись по SSH выполняем

Пароль вводится сразу после -p, без пробела.

Оказавшись в консоли сервера баз данных значение любого параметра можно посмотреть выполнив


show variables like ‘имя_параметра’;

Пример приведен на скриншоте. Все численные значения выводятся в байтах.

Определение п
араметров и их значений при оптимизации MySQL:

1)

skip-name-resolve=1

MySQL использует DNS и разрешает домены в ip адреса формируя кэш имен хостов.

Отключение этого механизма за счет установления значения 1 опции отключает данный механизм. Минус только в том, что при создании пользователя MySQL нельзя будет использовать домены, только ip адреса. Домены при создании пользователей практически никогда не используются так что в 99% случаев можно смело отключать DNS и эффект будет только позитивный.

2)

query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)

Параметры отвечают за кэш запросов, первый определяет выделенное количество памяти. Второй — query_cache_type сообщает службе какие данные кэшировать, со значением 0 не будет кэшироваться ничего, со значением 1 будут кэшироваться все запросы кроме SELECT SQL_NO_CACHE, со значением 2 будут кэшироваться все запросы.

query_cache_limit это ограничение в Мб на кэш для одного запроса.

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

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

3)

performance_schema=ON

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

4)

key_buffer_size (~ 24M)

key_buffer_size буфер для индексов таблиц MyISAM. В данном случае скрипт рекомендуем значение 24Мб. Параметр очень важен для баз с преобладанием таблиц с движком MyISAM, о том как выяснить какой движок используется и сменить его рассказано в статье

5)

innodb_log_file_size

Рекомендуемое значение (=16M) (if possible, so InnoDB total log file size equals 25% of buffer_pool_size)

В данном случае скрипт ничего не вывел касательно самого параметра buffer_pool_size. Он является самым важным для таблиц с движком InnoDB, он определяет объем выдееннной под них оперативной памяти. Значение innodb_log_file_size всегда должно быть равным 25% от buffer_pool_size

Редактирование настроек

Параметры определены, теперь нужно их добавить, перезапустить службу и оптимизация MySQL будет завершена. Находим в конфигурационных файлах сервиса файл с секцией [mysqld], на взятой для демонстрации машине это /etc/mysql/mariadb.conf.d/50-server.cnf

Открываем файл на редактирование и добавляем параметры


Сохраняем файл, перезапускаем службу, убеждаемся, что она запустилась

Если служба не запустилась нужно проверять лог, обычно путь к нему /var/log/mysql/error.log

Там будут записи если, например, в именах параметров опечатки.

На этом всё, оптимизация MySQL с помощью скрипта mysqltuner выполнена. В реальных системах список параметров и значения могут быть другими, процесс аналогичен. По кадому параметру проверяется текущее значение, выставляется новое, если непонятно за что отвечает параметр по нему ищется информация в документации.

Перед внесением любых изменений нужно делать копии конфигурационных файлов.

Ручная оптимизация выполняется точно так же, но значения параметров выбираются на основе анализа запросов и текущих значений параметров. По каждому параметру есть информация в документации MySQL (пример).

Что ещё можно сделать

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

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

Как оптимизировать производительность базы данных MySQL

By Staff Contributor, 11 июня 2020 г.

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

Оптимизация базы данных MySQL — непростой процесс, но он жизненно важен для поддержания производительности приложений и предоставления услуг. В этом руководстве мы рассмотрим 10 советов по оптимизации производительности вашей базы данных MySQL — от ознакомления с вашей рабочей нагрузкой до внедрения программного обеспечения, такого как SolarWinds 9.0007 ® Анализатор производительности базы данных (DPA) или монитор производительности базы данных SolarWinds (DPM).

Как оптимизировать базу данных MySQL

    1. Поймите свою рабочую нагрузку
    2. Оптимизировать запросы
    3. Не используйте MySQL в качестве очереди
    4. Мониторинг основных ресурсов
    5. Результаты фильтрации
    6. Оптимизация подзапросов
    7. Распознавание проблем с масштабируемостью
    8. Запрос кэша
    9. Проверка запросов на разбиение на страницы
    10. Автоматизировать настройку


Есть несколько советов по оптимизации производительности базы данных MySQL; некоторые из них могут быть техническими, а другие более высокого уровня. Ниже приведены мои подходы «наибольшая отдача от затраченных средств», но есть много других, более мелких оптимизаций, на которые вы также можете обратить внимание, если хотите получить действительно техническую информацию.

Совет 1. Оцените свою рабочую нагрузку

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

Самый простой способ профилировать или определить базовый уровень производительности вашей базы данных — это использовать инструмент, подобный описанному в совете 10.

Совет 2. Оптимизация запросов

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

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

Совет 3. Не используйте MySQL в качестве очереди

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

Совет 4. Мониторинг основных ресурсов

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

  1. ЦП
  2. Память
  3. Диск
  4. Сеть

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

Совет 5. Фильтрация результатов

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

Совет 6. Оптимизация подзапросов

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

Совет 7. Выявление проблем с масштабируемостью

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

Совет 8. Запросите кэш

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

Если вы используете приложения с разбиением на страницы, иногда они группируются и сортируются таким образом, что индексы не используются или не могут использоваться. Это создает много работы для сервера. Вы можете создавать оптимизации, показывая только ссылку на следующую страницу, а не ссылки на все доступные страницы.

Совет 10. Автоматизация настройки

Для автоматизации производительности базы данных MySQL и настройки конфигурации я рекомендую два ключевых инструмента: анализатор производительности базы данных SolarWinds (DPA) и монитор производительности базы данных SolarWinds (DPM). Оба они полезны для управления производительностью базы данных MySQL и изменениями конфигурации и могут автоматизировать большую часть процесса настройки производительности, так что вы можете освободить время для других вещей.

DPA — это локальное решение, обеспечивающее поддержку кроссплатформенной базы данных. Он способен отслеживать, анализировать и настраивать производительность SQL-запросов, а также обеспечивает обнаружение аномалий с помощью машинного обучения. Он также обеспечивает отслеживание использования ресурсов базы данных, таких как четыре основных ресурса, упомянутых выше, и отслеживает тенденции рабочей нагрузки и изменения оптимизации. Это поможет вам увидеть, не вызвали ли какие-либо изменения проблемы. DPA также можно интегрировать с другими продуктами SolarWinds через Orion 9.0007 ® Платформа.

DPM представляет собой платформу «программное обеспечение как услуга» с пользовательским веб-интерфейсом. Это помогает администраторам отслеживать производительность и обнаруживать проблемы с базой данных. Последовательный сбор данных позволяет легко выяснить, в чем кроется основная причина проблемы. DPA также включает надежные инструменты оптимизации производительности и настройки, поэтому вы можете отслеживать SQL, ожидания, приложения, клиентские машины и пользователей, среди прочего.

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

Настройка производительности является важной частью обслуживания базы данных MySQL. Помимо ознакомления с вашей базой данных и принятия ручных мер по оптимизации производительности, вы получите большую пользу от использования инструмента для автоматизации необходимых процессов. Администраторам, которые ищут локальное программное обеспечение, я рекомендую SolarWinds DPA, а тем, кто ищет решение SaaS, следует рассмотреть SolarWinds DPM. Бесплатные пробные версии доступны как для DPA, так и для DPM.

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

Best Practices for MySQL Performance Tuning в 2023 году

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

Настройка производительности MySQL: для чего используется MySQL?

MySQL — это система управления реляционными базами данных, построенная на языке SQL (язык структурированных запросов). Эта система управления реляционными базами данных (RDBMS) используется для различных задач, включая хранение данных, электронную коммерцию и ведение журналов.

  • Система управления базами данных, такая как MySQL, необходима для добавления, доступа и обработки данных, содержащихся в компьютерной базе данных.
  • Он может содержать что угодно, от отдельной части информации до всего ассортимента товаров онлайн-бизнеса.
  • Можно разрабатывать веб-сайты, взаимодействующие в режиме реального времени с базой данных MySQL, чтобы быстро отображать классифицированную и доступную для поиска информацию пользователю веб-сайта с помощью языка сценариев, такого как PHP или Perl.

Настройка производительности MySQL: узнайте, где MySQL тратит больше всего времени на ваш запрос и почему.

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

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

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

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

  • Узнайте, ограничена ли медленная работа MySQL определенным запросом, пакетным процессом или базой данных или затронуты ли и другие сетевые ресурсы
  • Другой причиной проблем с производительностью SQL может быть неправильное создание индекса или доступ к ошибочному индексу из определенных запросов.
  • Выбор медленной стратегии выполнения может снизить производительность.
  • Запуск одного запроса за раз может пройти гладко, но необходимо определить, не ухудшает ли производительность сервера выполнение нескольких запросов MySQL одновременно.
  • Если пользователь испытывает низкую производительность при работе с компонентами базы данных, системный монитор может оказаться весьма полезным. Производительность как базы данных, так и компонентов, не являющихся базой данных, можно проверить с помощью системного монитора.
  • Специальные SQL-запросы выполняются вне хранимой процедуры: хранимые процедуры почти всегда обеспечивают более высокую производительность, поскольку MySQL может кэшировать свои планы выполнения; Специальные запросы следует по возможности преобразовывать в хранимые процедуры.
  • В планах выполнения длительные поиски или поиск с интенсивным использованием ЦП. Действия сканирования таблицы указывают на отсутствие соответствующего индекса; таким образом, внедрение индекса для устранения сканирования таблицы может оказать немедленное положительное влияние на производительность.
  • Низкая производительность MySQL также может быть вызвана запросами со значительным количеством объединений. Соединения требуют времени, и хотя MySQL Server, очевидно, оборудован для управления ими, большое количество соединений может значительно снизить производительность. Ограничьте количество объединений до семи в качестве общего эмпирического правила; если у вас есть больше, вам, возможно, придется начать искать способы сократить.
  • Запрос, который все время выполняется медленно. Это запрос, который можно улучшить, переписав его. Запрос, который время от времени выполняется медленно, скорее всего, вызван внешними переменными, такими как блокировки или конкуренция за ресурсы.

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

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

Настройка производительности MySQL: всегда обновляйте MySQL до последней версии

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

Каждая новая выпущенная версия MySQL обеспечивает значительное улучшение производительности и возможностей по сравнению с предыдущими версиями. Самый важный совет — используйте последнюю версию MySQL. MySQL 8.0 включает словарь данных транзакций, а также возможность хранить информацию, связанную с операциями DDL, в одной отдельной атомарной транзакции.

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

Если вы получили значительные преимущества от использования MariaDB или Percona по сравнению с обычным MySQL, поделитесь своим опытом. Они оба являются отличным выбором.

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

Чтобы повысить производительность MySQL, вы измените аппаратные и программные переменные на системном уровне.

Память

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

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

Дисковое пространство

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

Сеть

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

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

Скорость вашего процессора показывает, насколько быстро работает ваша система. Команда top покажет вам, как процессор и оперативная память используются каждым процессом — другими словами, как используются ваши ресурсы. Чтобы увидеть, как используются ваши ресурсы, используйте команду Linux top.

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

Настройка производительности MySQL: оптимизация производительности программного обеспечения

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

Что касается программного обеспечения, настройка производительности MySQL включает в себя:

Настройка параметров сервера MySQL, Оптимизация производительности запросов MySQL, Настройка индексов MySQL, Обновление до механизма хранения MySQL InnoDB

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

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

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

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

Настройка производительности MySQL: никогда не используйте MySQL в качестве очереди

Очереди могут оказать непосредственное влияние на скорость базы данных и могут войти в базы данных вашего приложения без вашего ведома. Например, если вы создаете статус для определенного элемента, чтобы «соответствующий процесс» мог получить к нему доступ, вы непреднамеренно создаете очередь. Что он делает, так это добавляет дополнительное время загрузки для доступа к ресурсу без видимой цели.

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

Настройка производительности MySQL: используйте новейшие разработки баз данных (используйте InnoDB, а не MyISAM)

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

  • Последняя версия InnoDB предлагает более продвинутую функциональность и включает механизм оптимизации, в то время как MyISAM имеет менее эффективную структуру базы данных, в то время как
  • InnoDB использует кластеризованный индекс и хранит данные на страницах, которые хранятся в последовательных физических блоках.
  • Если значение слишком велико для страницы, InnoDB перемещает его в другое место перед индексацией.
  • Функция InnoDB хранит соответствующие данные в одном и том же месте на устройстве хранения, что означает, что фактическому жесткому диску требуется меньше времени для доступа к данным.
  • MySQL 5.5 и более поздние версии используют механизм InnoDB, что обеспечивает более высокие требования к параллелизму и ссылочной целостности.
  • InnoDB обеспечивает превосходное восстановление после сбоев.
  • Блокировка на уровне строки доступна в InnoDB, но MyISAM может выполнять блокировку только на уровне всей таблицы.
  • Начиная с MySQL 5.6, InnoDB, как и MyISAM, теперь предоставляет индексы поиска FULLTEXT.
  • Транзакции, внешние ключи и ограничения отношений поддерживаются InnoDB, но не MyISAM.

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

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

Tuning-primer

Эта утилита немного старше и была создана для MySQL 5.5–5.7. Он может проверить вашу базу данных и порекомендовать изменения конфигурации для повышения производительности. Например, если ваша система обрабатывает запросы недостаточно быстро, чтобы кэш оставался чистым, может быть предложено увеличить параметр размера кэша запросов.

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

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

phpMyAdmin Advisor

Анализирует вашу базу данных и дает рекомендации, как и две другие утилиты. Если вы уже используете phpMyAdmin, Советник — это полезный инструмент, который вы можете использовать с графическим интерфейсом.

MySQL Enterprise Monitor (MEM)

Это официальный инструмент мониторинга MySQL от Oracle. MEM, как и другие обсуждаемые здесь методы, в основном полагается на схему производительности MySQL для получения показателей. Этот инструмент может отслеживать ошибки, медленные запросы, задержку, максимальное количество подключений, состояние диска (подкачка), состояние репликации и т. д. для работы над конкретным запросом.

Средство мониторинга и управления Percona (PMM)

PMM — это набор инструментов для мониторинга с открытым исходным кодом. Он включает в себя предварительно созданные информационные панели, которые помогут вам понять производительность вашей системы MySQL. PMM извлекает метрики MySQL с помощью Prometheus и инструмента MySQLD Exporter. Экспортеру Mysqld требуется доступ к вашим экземплярам MySQL, чтобы запрашивать их.

Инструмент профилирования кода для настройки производительности MySQL

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

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

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

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

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

Настройка производительности MySQL: сбор метрик и оповещение

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

Например, когда-нибудь может возникнуть странная проблема, и вы будете рады, что у вас есть возможность указать на график и показать изменение рабочей нагрузки сервера.

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

Настройка производительности MySQL: кэш запросов в MySQL

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

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

В базах данных часто используется структура SELECT, WHERE. Они отвечают за фильтрацию, извлечение и анализ результатов. Создание крошечного набора индексов для связанных таблиц — это удобная структура, которая позволяет направлять запрос к индексу для ускорения его выполнения. Основная цель индексации:

  1. Индексы позволяют серверу находить группы соседних строк, а не отдельные строки.
  2. Индексы позволяют серверу избежать сортировки, читая строки в желаемом порядке.
  3. Индексы позволяют серверу выполнять полные запросы, основанные только на индексе, без необходимости вообще запрашивать таблицу.

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

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

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

No related posts.