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

Содержание

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

1 ноября 2019

Хостинг

12 282

Время чтения ≈ 11 минут

От скорости работы баз данных (БД) зависит быстрота отклика сайта. Ведь замедленная обработка запросов влияет на PHP, следовательно — накапливается огромное количество операций, с которыми сервер может не справиться.

Управлять данным процессом позволяет использование систем управления базами данных или СУБД. Одной из самых широко применяемых СУБД является MySQL — ПО с открытым исходным кодом, созданное компанией MySQL AB (Oracle) ещё в 1995 году. Оптимизация MySQL позволяет избежать проблем с производительностью сервера и значительно ускорить интернет-ресурс.

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

Зачем оптимизировать работу MySQL

Увеличение скорости обработки и выполнения запросов. Скорость работы сайта прямо пропорционально зависит от времени обработки и выполнения SQL-запроса к базе данных, которое должно быть минимальным.
Предотвращение перегрузки сервера. При перегрузке сервера работа web-ресурса или приложения будет нестабильной. Хостер может заблокировать ресурс, чтобы последний не нарушал работу всего сервера, на котором также работают и другие сайты.
Уменьшение времени ожидания загрузки web-страницы. Когда идет огромное количество SQL-запросов к базе данных, происходит существенное замедление работы сайта, что недопустимо для коммерческого или представительского интернет-ресурса.
Экономия ресурсов хостинга. Если MySQL не оптимизирована, то происходит значительный перерасход использования ресурсов сервера (процессорного времени, оперативной памяти). На этом основании хостер имеет право заблокировать работу ресурса.
Возможность масштабировать ресурс. При расширении сайта будет невозможно обеспечить хорошее качество его работы. Например, арендатор VPS загрузил на сервер интернет-магазин, в котором 100 видов товаров. Через некоторое время бизнес расширяется и появляется возможность предложить потребителю 10000 разновидностей. После загрузки и доработки интернет-магазин начинает работать медленно, постоянно происходят ошибки при помещении товаров в корзину и т. д.

Какие ресурсы желательно оптимизировать

Средне и высоконагруженные сайты с посещаемостью от 10 000 человек в сутки.
Сервера разработчиков веб-пиложений и сервисов.
Интернет-магазины с базой товаров, превышающей 100 единиц.
Высоконагруженные прокси/VPN сервера на основе VPS.
Системы работы с финансами и бухгалтерские приложения (продукты «1 C», ПланФакт, «Моё дело», Livesklad).
Приложения для мониторинга сервисов компьютерных сетей и серверов (Zabbix, Prometheus, Grafana).
Сайты с высокой посещаемостью и регистрациями, превышаемыми 100 пользователей в сутки.
Контент-биржи (Text. ru, Advego, Copylancer).
Видео- и фотохостинговые ресурсы (YouTube, Vimeo, Imgur).
Рекламно-баннерные площадки (myTarget, Adfox).
Финансово-экономические игры с возможностью вывода денег (Your Real Town, Surfer Money).

Скорость работы баз данных

Чтобы оптимизация СУБД MySQL дала результат, нужно начать с анализа работы баз данных. Настройки сервиса содержатся в файле /etc/my.cnf.

С помощью настроек можно проверить, какие запросы выполняются медленно и что можно ускорить. Для этого в раздел [mysqld] добавляется следующий запрос:

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

Информация указана в строчках:

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

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

Чтобы увидеть актуальные данные, сервер перезапускается. Сведения находятся в логе:

# systemctl restart mariadb
# tail -f /var/log/mariadb/slow-queries.log

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

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

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

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

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

Первоначальная настройка

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

Чтобы скрипт работал и показывал текущие проблемы, необходимо загрузить три файла через 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. Два остальных — сведения о простых паролях и уязвимостях, которые позволяют найти проблемы с безопасностью.

Далее необходимо провести тест базы данных. Оптимизация производительности будет основана на выявленных проблемах. Тест запускается скриптом # perl ./mysqltuner.pl. Он выдает полную статистику работы базы. Все проблемные места обозначаются красным восклицательным знаком [!].

Следует помнить, что параметры изменяются согласно рекомендациям, которые выдает утилита. Нельзя бездумно копировать параметры из статьи и применять их к собственной базе данных. В ином случаем можно столкнуться с рядом практических проблем. Например, недостаточным размером буфера движка таблиц (InnoDB buffer pool).

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

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

Рабочие параметры

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

Для MariaDB

Для настройки производительности вручную необходимо выполнить изменения указанных ниже параметров в файле конфигурации наиболее популярной версии MySQL MariaDB — /etc/mysql/conf.d/app.cnf.

tmp_table_size — максимальная величина ОЗУ (оперативной памяти), выделяемая для хранения временных таблиц. Последние создаются при формировании SQL-запросов, которые осуществляют выбор данных из таблицы для дальнейших операций над ними. Значение необходимо выбирать экспериментальным путем, руководствуясь соотношением tmp_table_size = объем_ОЗУ * 0,75 (tmp_table_size = 64 М).
max_heap_table_size — максимальный размер таблицы, которая хранится в ОЗУ. Рекомендуемое значение параметра max_heap_table_size должно быть равным величине tmp_table_size.

query_cache_size — объем ОЗУ, выделяемого под кеш SQL-запросы. Его рекомендуется отключить для проектов с большим количеством записей, т. е. query_cache_size=0.
query_cache_type —параметр, активирующий или деактивирующий службу управления кешем в MySQL. Его рекомендуется отключить, установив query_cache_type в 0, т. е. query_cache_type = 0.

join_buffer_size — значение количества объема ОЗУ, которое выделяется для объединения таблиц баз данных. Рекомендуемое значение 8М.
sort_buffer_size — объем ОЗУ, выделяемый для выполнения операций сортировки данных. Рекомендуется установить значение, равное 10М.
max_connections — опция, определяющее максимальное число соединений с БД, приобретает значение, когда возникает сообщение об ошибке «Too many connections». Повышать это значение следует постепенно, вплоть до исчезновения ошибки.

Для InnoDB

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

innodb_buffer_pool_size — если используются только таблицы InnoDB, необходимо установить максимально возможное значение с учетом технических характеристик системы. Оно должно быть 70-80% от доступной оперативной памяти. Например, при ОЗУ в 32 Гб: innodb_buffer_pool_size = 24G.
innodb_log_file_size — чем выше показатель, тем быстрее работают записи, то есть большее их количество помещается в файл лога. Поскольку файла всегда два, параметр задает значение только для одного. Например, innodb_log_file_size = 512M в сумме даст 1 G.
innodb_log_buffer_size — определяет размер буфера транзакций и изменяется только в том случае, если используются большие поля (TEXT, BLOB). 1М по умолчанию хватает в большинстве случаев.
innodb_flush_log_at_trx_commit — повышает пропускную способность записи данных в базу. Решает, сбрасывает ли MySQL каждую операцию в файл лога. В большинстве случаев подойдет вариант innodb_flush_log_at_trx_commit = 2. Следует учитывать два типа значения, где 1 — сохранность данных играет первую роль, 2 — небольшая потеря данных не критична благодаря дублированию.

Объединение таблиц

Часто проблема при работе с объемными базами данных возникает при выборке — попытке объединения (JOIN) столбцов из разных таблиц.

Чтобы ускорить JOIN больших таблиц MySQL, необходимо убедиться в том, что все столбцы проиндексированы. Скорость операции будет выше, если объединяются столбцы одного типа. При JOIN типов DECIMAL и INT, сервер не сможет использовать как минимум один индекс.

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

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

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

Типы объединений

MySQL позволяет выполнять 3 типа объединений записей в двух таблицах (table_A и table_B):

внутреннее;
левостороннее внешнее;
правостороннее внешнее.

Пример № 1 — внутреннее

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

Чтобы выполнить SQL-запрос, нужно запустить терминал, а затем активировать консоль MySQL командой:

sudo mysql -u имя_пользователя -p

Далее необходимо ввести пароль и SQL-запрос: SELECT * FROM tableA INNER JOIN tableB ON tableA.name = tableB.name, где «name» — имя поля для таблицы А.

Пример № 2 — левостороннее

Суть объединения заключается в миграции данных из таблицы «table_B», которых нет в таблице «table_A», в последнюю. Используется комбинация зарезервированных слов LEFT-OUTER.

SQL-запрос будет иметь такой вид: SELECT * FROM tableA LEFT OUTER JOIN tableB ON tableA.name = tableB.name. Если в «tableA» больше полей, чем в «tableB», то в первую запишутся значения NULL.

Пример № 3 — правостороннее

Главной является таблица «tableB». Для объединения необходимо применить ключевые слова RIGHT-OUTER.

В консоли СУБД нужно набрать SQL-запрос: SELECT * FROM tableB RIGHT OUTER JOIN tableA ON tableA.name = tableB.name. Недостающие поля заполняются константой NULL.

Заключение

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

Автоматическую оптимизацию с помощью скрипта следует обязательно дополнять настройкой производительности в ручном режиме с помощью регулировки основных параметров СУБД. Для повышения эффективности MySQL не менее важна оптимизация работы с выборкой из нескольких объединенных таблиц.

Нужен производительный хостинг для высоконагруженных проектов на MySQL? Выбирайте VPS от Eternalhost — сервер на быстрых SDD, гарантированные ресурсы, запуск за 5 минут.

Оцените материал:

[Всего голосов: 3 Средний: 4/5]

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

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

Содержание

Храните данные MySQL в отдельных разделах
Установите максимальное количество соединений MySQL
Включить журнал медленных запросов MySQL
Установите максимальный пакет, разрешенный MySQL
Настройка емкости временной таблицы
Настройте максимальный объем таблицы памяти
Отключение обратного поиска DNS для MySQL
Избегайте использования свопинга в MySQL
Увеличение размера буферного пула InnoDB
Работа с размером кэша запросов
Проверка неиспользуемых соединений
Восстановление базы данных MySQL
Проверка производительности 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

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. Мониторинг основных ресурсов

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

ЦП
Память
Диск
Сеть

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

Совет 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, что позволит вам выполнять работу более эффективно и обеспечить доступность ваших бизнес-сервисов и приложений.

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

Введение

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

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

Необходимые условия

Система Linux с установленной и работающей MySQL, Centos или Ubuntu
Существующая база данных.

Учетные данные администратора для операционной системы и базы данных.

1. Сбалансируйте четыре основных аппаратных ресурса

Хранилище

Оцените свое хранилище. Если вы используете традиционные жесткие диски (HDD), вы можете перейти на твердотельные накопители (SSD) для повышения производительности.

Используйте инструмент, такой как iotop или sar из пакета sysstat , для мониторинга скорости ввода/вывода вашего диска. Если использование диска намного превышает использование других ресурсов, рассмотрите возможность добавления дополнительного хранилища или перехода на более быстрое хранилище.

Процессор

Процессоры обычно считаются мерой того, насколько быстро работает ваша система. Используйте команду Linux top , чтобы узнать, как используются ваши ресурсы. Обратите внимание на процессы MySQL и требуемый процент использования процессора.

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

Память

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

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

Сеть

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

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

2. Используйте InnoDB, а не MyISAM

MyISAM — это старый стиль базы данных, используемый для некоторых баз данных MySQL. Это менее эффективная структура базы данных. более новый InnoDB поддерживает более продвинутые функции и имеет встроенный механизм оптимизации.

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

3. Используйте последнюю версию MySQL

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

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

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

Настройка производительности SQL — это процесс максимального увеличения скорости выполнения запросов к реляционной базе данных. Задача обычно включает в себя несколько инструментов и методов.

Эти методы включают:

Настройка файлов конфигурации MySQL.
Написание более эффективных запросов к базе данных.
Структурирование базы данных для более эффективного извлечения данных.

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

4. Рассмотрите возможность использования автоматического инструмента повышения производительности

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

Первый тюнинг-грунтовка. Этот инструмент немного старше и предназначен для MySQL 5.5 — 5.7. Он может анализировать вашу базу данных и предлагать настройки для повышения производительности. Например, может предложить поднять query_cache_size , если вам кажется, что ваша система не может обрабатывать запросы достаточно быстро, чтобы кэш оставался чистым.

Вторым инструментом настройки, полезным для большинства современных баз данных SQL, является MySQLTuner. Этот скрипт ( mysqltuner.pl ) написан на Perl. Как и tuning-primer, он анализирует конфигурацию вашей базы данных в поисках узких мест и неэффективностей. В выходных данных показаны метрики и рекомендации:

В верхней части выходных данных вы можете увидеть версию инструмента MySQLTuner и вашей базы данных.

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

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

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

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

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

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

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

Например, база данных OLTP требует быстрых транзакций и эффективной обработки запросов. Выполнение неэффективного запроса блокирует использование базы данных и останавливает обновление информации.

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

6. Используйте индексы там, где это уместно

Многие запросы к базе данных используют подобную структуру:

 SELECT … WHERE

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

7. Функции в предикатах

Избегайте использования функции в предикате запроса. Например:

 SELECT * FROM MYTABLE WHERE UPPER(COL1)='123'Copy

Нотация UPPER создает функцию, которая должна работать во время операции SELECT . Это удваивает работу, выполняемую запросом, и вам следует избегать этого, если это возможно.

8. Избегайте подстановочных знаков % в предикатах

При поиске в текстовых данных подстановочные знаки помогают сделать более широкий поиск. Например, чтобы выбрать все имена, начинающиеся с ch , создайте индекс для столбца имени и выполните:

 SELECT * FROM person WHERE name LIKE "ch%"

Запрос сканирует индексы, что снижает стоимость запроса:

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

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

Способ поиска концов строк состоит в том, чтобы перевернуть строку, проиндексировать перевернутые строки и посмотреть на начальные символы. Размещение подстановочного знака в конце теперь ищет начало перевернутой строки, что делает поиск более эффективным.

9. Укажите столбцы в функции SELECT

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

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

Чтобы суммировать, избегайте использования:

 SELECT * Из таблицы

вместо этого попробуйте:

 SELECT COLMAN1, COLMAN2 Из таблицы

10.

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

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

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

11. GROUP BY вместо SELECT DISTINCT

Запрос SELECT DISTINCT пригодится при попытке избавиться от повторяющихся значений. Однако оператор требует большой вычислительной мощности.

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

id	name	lastName	address	city	state
0	John	Smith	652 Flower Street	Los Angeles	CA
1	Джон	Смит	1215 Ocean Boulevard 5 CA	90802
2	Martha	Matthews	3104 Pico Boulevard	Los Angeles	CA
3	Martha	Jones	2712 Venice Boulevard	Los Angeles	CA

Выполнение следующего запроса возвращает четыре результата:

 SELECT DISTINCT name, address FROM person

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

Чтобы отфильтровать повторяющиеся имена и вернуть адреса, попробуйте использовать оператор GROUP BY :

 SELECT name, address FROM person GROUP BY name

Результат возвращает первое уникальное имя вместе с адресом, что делает утверждение менее двусмысленное. Для группировки по уникальным адресам 9Параметр 0120 GROUP BY просто изменится на адрес и быстрее вернет тот же результат, что и оператор DISTINCT .

Подводя итог, избегайте использования:

 SELECT DISTINCT column1, column2 FROM table

Вместо этого попробуйте использовать:

 SELECT column1, column2 FROM table GROUP BY column1

12. Попробуйте СОЕДИНИТЬ, ГДЕ, UNION, DISTINCT

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

Использование INNER JOIN — это стандартный подход к объединению таблиц. Большинство механизмов баз данных также допускают использование WHERE . Например, следующие два запроса выдают один и тот же результат:

 SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

По сравнению с:

 SELECT * FROM table1, table2 WHERE table1.id = table2.id

Теоретически у них одинаковое время работы.

Выбор использования ПРИСОЕДИНЯЙТЕСЬ или ГДЕ запрос зависит от ядра базы данных. Хотя большинство механизмов имеют одинаковое время выполнения для двух методов, в некоторых системах баз данных один работает быстрее, чем другой.

Примечание: Узнайте больше о MySQL JOINS и о том, как их использовать.

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

13. Используйте функцию EXPLAIN

Современные базы данных MySQL включают функцию EXPLAIN .

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

14. Конфигурация сервера MySQL

Эта конфигурация включает в себя внесение изменений в файл /etc/mysql/my.cnf . Действуйте с осторожностью и вносите незначительные изменения за один раз.

query_cache_size — указывает размер кэша запросов MySQL, ожидающих выполнения. Рекомендуется начинать с небольших значений около 10 МБ, а затем увеличивать их до 100–200 МБ. При слишком большом количестве кешированных запросов вы можете столкнуться с каскадом запросов «Ожидание блокировки кеша». Если ваши запросы продолжают выполнять резервное копирование, лучше использовать EXPLAIN для оценки каждого запроса и поиска способов сделать их более эффективными.

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