Практическая оптимизация MySQL: измерять, чтобы ускорять

10 лучших приемов для оптимизации работы с MySQL

1. LIMIT 1 Когда нужно извлечь из таблицы уникальную строку

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

В таких случаях, использование метода LIMIT 1 может существенно увеличить производительность:

2. Оптимизация работы с базой с помощью обработки кэша запросов

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

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

3. Индексация полей поиска

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

Как вы понимаете, это правило также распространяется на часть строки поиска: такую как «l ast_name LIKE ‘% ‘». Когда поиск производится по началу строки, MySQL может использовать для этого столбца индексацию.

Вы также должны понимать, какие виды запросов не могут использовать обычные индексы. Например, при поиске слова (например, «WHERE post_content LIKE ‘%tomato%’»), применение обычного индекса вам ничего не даст. В таком случае лучше будет использовать поиск MySQL на полное соответствие или создать свой собственный индекс.

4. Индексирование и использование столбцов одинакового типа при объединении

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

Кроме того, столбцы, которые объединяются, должны быть одинакового типа. Например, если вы объединяете столбец типа DECIMAL из одной таблицы и столбец типа INT из другой, MySQL не сможет использовать по крайней мере один из индексов.

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

5. По возможности не используйте запросы типа SELECT *

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

6. Пожалуйста, не используйте метод сортировки ORDER BY RAND()

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

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

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

Таким образом, вы выберете меньшее количество результатов поиска, после чего сможете применить метод LIMIT, описанный в пункте 1.

7. Используйте столбцы типа ENUM вместо VARCHAR

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

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

Существует даже возможность задать сценарий, при котором MySQL будет «предлагать» изменить структуру таблицы. Когда у вас есть поле типа VARCHAR, система может автоматически рекомендовать изменить формат столбца на ENUM. Это можно сделать с помощью вызова функции PROCEDURE ANALYSE() .

Используйте для хранения IP-адресов поля типа UNSIGNED INT

Многие разработчики создают для этих целей поля типа VARCHAR (15) , в то время как IP-адреса можно было бы хранить в базе в виде десятичных чисел. Поля типа INT предоставляют возможность хранить до 4 байта информации, и при этом для них можно задать фиксированный размер поля.

Вы должны удостовериться, что ваши колонки имеют формат UNSIGNED INT , поскольку IP-адрес задается 32-мя битами.

В запросах можно использовать параметр INET_ATON () для преобразования IP-адресов в десятичные числа, и INET_NTOA () — наоборот. PHP имеет и другие аналогичные функции long2ip () и ip2long () .

8. Вертикальное секционирование (разделение)

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

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

Таким образом, ваша основная таблица пользователей заметно уменьшится в размерах. А как вы знаете, меньшие таблицы, обрабатываются быстрее.

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

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

9. Меньшие столбцы – быстрее

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

В MySQL Docs прописан ряд требований к хранению разных типов данных. Если ожидается, что таблица не будет содержать слишком большое количество записей, то нет причин хранить первичный ключ в полях типа INT, MEDIUMINT, SMALLINT , а в отдельных случаях даже TINYINT . Если в формате даты вам не нужны составляющие времени (часы : минуты), то используйте поля типа DATE вместо DATETIME.

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

10. Правильно выбирайте движок

Существует два основных движка баз данных MySQL: MyISAM и InnoDB . У каждого есть свои преимущества и недостатки.
MyISAM хорош для работы с «тяжелыми» приложениями, но хуже справляется с обработкой данных, если в базе большое количество записей.

Даже если вы обновляете всего одно поле одной из строк, вся таблица может быть заблокирована, и никакой другой процесс не сможет получить к ней доступ, пока не завершится этот запрос. В то же время MyISAM очень быстр в обработке запросов типа SELECT COUNT (*) .

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

Данная публикация представляет собой перевод статьи « 10 MySQL Best Practices for Optimization » , подготовленной дружной командой проекта Интернет-технологии.ру

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

От скорости работы баз данных (БД) зависит быстрота отклика сайта. Ведь замедленная обработка запросов влияет на 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] добавляется следующий запрос:

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

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

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

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

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

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

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

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

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

Чтобы скрипт работал и показывал текущие проблемы, необходимо загрузить три файла через wget :

Первый файл — это скрипт написан на 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 позволяет выполнять 3 типа объединений записей в двух таблицах ( table_A и table_B ):

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

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

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

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

Далее необходимо ввести пароль и 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 минут.

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

Любой успешный проект рано или поздно сталкивается с проблемой роста. Число посетителей веб-сайта увеличивается, веб-сервер обрабатывает бóльшее количество соединений, растёт поток запросов к базе данных. В определённый момент времени отзывчивость сайта снижается: страницы загружаются медленнее, что, согласно многочисленным исследованиям, влияет на конверсию (пример подобного исследования – http://www.webperformancetoday.com/2014/04/09/web-page-speed-affect-conversions-infographic/).

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

В первую очередь следует выяснить характер нагрузки на диски. В этом поможет утилита iostat. В Ubuntu она устанавливается с пакетом sysstat:

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

Как ускорить чтение

Допустим, диски загружены запросами на чтение. Что можно сделать, чтобы ускорить отдачу данных? Закэшировать данные в памяти. MySQL предоставляет возможность использования разных хранилищ, или движков (storage engines), для доступа к данным, поэтому подход к кэшированию разный. Рассмотрим два наиболее популярных движка: MyISAM и InnoDB.

Движок InnoDB имеет встроенный кэш для данных и индексов – так называемый Buffer Pool. Его размер регулируется переменной innodb_buffer_pool_size. В идеале размер Buffer Pool должен быть как минимум такого объёма, чтобы в нём полностью можно было разместить все данные и индексы плюс 30%-60% от их размера. Дополнительная память используется для служебных нужд и Insert Buffer, а также для обеспечения запаса памяти на будущее. Переменная innodb_buffer_pool_size – не динамическая, поэтому после её изменения в конфигурационном файле потребуется перезапуск MySQL.

Движок MyISAM не имеет кэша для данных. Но мы по-прежнему можем ускорить чтения из таблиц MyISAM. Дело в том, что ядро Linux кэширует все прочитанные файлы в области оперативной памяти, которая называется pagecache. Разумеется, файлы с таблицами MyISAM также попадают в этот кэш. Объём pagecache можно узнать из вывода команды free:

Максимальной производительности чтения можно добиться, если объём pagecache равен объёму данных MyISAM.

По умолчанию под pagecache выделяется почти вся незанятая процессами память, поэтому увеличить его объём можно лишь установкой дополнительных планок RAM. Однако память – недорогой по сравнению с ЦПУ и дисками ресурс, при этом эффект от увеличения кэша может привести к значительному увеличению производительности. Ниже представлен график %iowait – доли времени, в течение которого ЦПУ ожидает ввода/вывода. График снят с рабочего нагруженного сервера. Думаю, комментарии здесь излишни.

Как ускорить запись

Увеличить производительность MySQL при большом объёме записи можно с помощью тонкой настройки параметров сервера.

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

Однако если datadir MySQL расположен на аппаратном RAID-массиве, то есть возможность задействовать для такой буферизации NVRAM-кэш RAID-контроллера, что намного эффективнее. Следует только убедиться, что контроллер оснащён BBU (Battery Backup Unit) – отдельным источником питания для кэша. При внезапном отключении электропитания у контроллера должно быть время, чтобы сбросить содержимое кэша на диски, иначе данные в массиве останутся в неконсистентном состоянии.

При задействовании кэша RAID-контроллера повысить производительность операций записи в БД можно, отключив ненужную буферизацию на уровне операционной системы. Для этого требуется выставить переменную MySQL innodb_flush_method в значение O_DIRECT, после чего перезагрузить систему управления базы данных. Снизить нагрузку на диски также может изменение переменной innodb_flush_log_at_trx_commit. Для соответствия требованиям ACID движок InnoDB хранит логи транзакций, или redo-логи, в которые записываются все запросы на изменение данных. Эти логи используются в процессе восстановления после аварийного останова системы управления базами данных.

Значение по умолчанию (1) предполагает, что буфер redo-логов, расположенный в памяти InnoDB, записывается на диск после каждого коммита транзакции. Это наиболее безопасный режим работы, обеспечивающий сохранность каждой транзакции даже в случае “падения” сервера. Можно выставить innodb_flush_log_at_trx_commit в значение 2, тогда логи будут записываться также после каждого коммита, но fsync() – сброс данных на диск – будет выполняться лишь раз в секунду (начиная с версии MySQL 5.6.6 этот интервал определяется переменной innodb_flush_log_at_timeout). Аварийное завершение работы СУБД не приведёт к потере транзакций, однако отключение самого сервера может привести к потере последней секунды транзакций. Значение 0 подразумевает ещё более быстрый режим записи – данные и записываются, и синхронизируются раз в секунду, безотносительно коммитов транзакций. Однако innodb_flush_log_at_trx_commit=0 может привести к потере транзакций даже при падении процесса. Администратору базы данных нужно сделать выбор исходя из текущей нагрузки и бизнес-требований.

Оптимизировать дисковые операции записи помогает правильный выбор размера redo-логов. Для этого есть несложное правило. Достаточно замерить объём данных, который записан в лог за одну минуту. Эту операцию нужно выполнять в момент дневной пиковой нагрузки:

Из примера видно, что за минуту в лог InnoDB записывается 2,44 Мб данных. Объём лога следует подбирать таким образом, чтобы в него умещался объём данных за час. В таком случае у InnoDB будет достаточно времени, чтобы изменить порядок запросов на ввод/вывод для достижения последовательной записи. В нашем примере за один час через redo-логи проходит 150 Мб данных, поэтому переменную innodb_log_file_size следует выставить в значение не менее 75M. Если объём лога выбрать слишком большим, то увеличится время InnoDB Crash Recovery, что увеличит даунтайм при аварийном перезапуске (стоит отметить, что в MySQL 5.5 время Crash Recovery зависит от размера InnoDB-лога в меньшей степени).

Вывод

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

Как ускорить Mysql. Чек лист работ

У Вас тормозит сайт. Вы не можете качественно отдать контент. Абоненты не могут нормально работать с закрытыми частями сайта. Служба поддержки получает звонки от разгневаных пользователей. Кто виноват и что делать? Ответ как правило в следующем:

Оставим рефакторинг кода на откуп программистам и посмотрим, как можно ускорить mysql сервер. Все написаное ниже верно и для mariadb

Настройка mysql

1. Установить последнюю версию mysql
2. Запустить mysqltunner и дать ему поработать 24+ часов. Выполнить его рекомендации
3. Настроить query_cache_size, query_cache_limit
4. Для движка MyISAM настроить key_buffer_size
5. Для движка ARIA настроить aria-pagecache-buffer-size (только для mariadb)
6. Значение innodb_buffer_pool_size должно быть 70-80% от обьема RAM
7. Для innodb-таблиц установить innodb_file_per_table=1. Для изменений потребуется сдампить и удалить (переименовать) базу. Накатить базу из дампа по-новой
8. Посмотреть на параметр innodb_flush_log_at_trx_commit. Возможно имеет смысл отключить сброс данных на диск
9. Выяснить, что используется чаще SELECT или UPDATE. Если SELECT то low-priority-updates=1, если UPDATE то low-priority-updates=0. Есть смысл сделать два инстанса mysql на разных сокетах/портах и разделить базы по приоритету использования SELECT/UPDATE
10. Возможно потребуется увеличить wait_timeout если у конечного пользователя наблюдаются ошибки соединения
11. Для временных таблиц использовать tmpfs вместо дисковой фс
12. Если возможно, для обмена данными приложения и БД использовать сокет. Такой обмен работает быстрее, чем обмен через tcp-соединение

Настройка БД

1. Запустить mytop либо mtop и отследить медленные запросы
2. Включить slow.log. Оптимизировать медленные запросы. Использовать EXPLAIN
3. Проверить индексы в таблицах. Там, где используется поиск однозначно должен быть индекс
4. Установить правильные тип и размер полей. Это уменьшит размер таблицы
5. Использовать партиционирование если в таблице есть старые данные, которые нужны, но к ним редко обращаются
6. Регулярный optimize table таблиц с движком ARIA/MyISAM
7. Пробовать persistant connection в базу. Возможно полегчает, возможно нет. Это индивидуально

Железо и система

1. Много памяти, чем больше — тем лучше. База любит память, дать ей разумный максимум, который только возможен
2. Хороший, многоядерный сервер
3. Дисковая система в raid10
4. Мониторинг (munin, zabbix и тд). Важно понимать, чем занят сервер
5. Кастомизация ядра и настройки sysctl.conf, limits.conf
6. Отключить лишнее, максимально уменьшить работу с диском
7. Если у Вас хайлоад-проект, то запланировать внедрение репликации на другой сервер

Помнить, что mysql с параметрами по-умолчанию не оптимизирован под высокие нагрузки. Оптимизация БД это комплекс работ. Каждое соединение, каждый байт — считаются

Александр Черных
системный администратор

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

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

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

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

Вот 10 советов, которые позволят Вам получить отличную производительность базы данных MySQL. Данные советы охватывает как сферу администрирования сервера, так и нюансы разработки приложений под СУБД MySQL (т. е. зону ответственности программиста).

Совет по настройке производительности MySQL № 1: профиль вашей рабочей нагрузки

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

Лучший способ профилировать рабочую нагрузку – это инструмент, такой как анализатор запросов MySQL Enterprise Monitor или утилита pt-query-digest из набора инструментов Percona Toolkit. Эти инструменты захватывают запросы, выполняемые сервером, и возвращают таблицу задач, отсортированную по уменьшению порядка времени отклика, мгновенно поднимая самые дорогие и трудоемкие задачи вверх. Поэтому Вы можете сразу видеть, на чем сосредоточить свои усилия.

Инструменты профилирования рабочей нагрузки объединяют одинаковые запросы вместе, позволяя вам видеть медленные запросы, а также быстрые, но выполняемые очень часто, что также может заставить сервер «просесть».

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

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

Понимание основных ресурсов важно в двух конкретных областях: выбор оборудования и устранение неполадок.

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

Еще один хороший пример этого баланса относится к процессорам. В большинстве случаев MySQL будет хорошо работать с быстрыми процессорами, потому что каждый запрос работает в одном потоке и не может быть распараллелен между процессорами. Много ядер и много потоков в процессоре – это очень хорошо, но еще лучше, когда каждое конкретное ядро было бы быстрым! Важно не ошибиться при выборе процессора. Купить многоядерный, но с медленными потоками или 1-2 ядерный, но с высокой тактовой частотой каждого ядра? Это определяется конкретными Ваши задачами, которые будут выполнятся на сервере. Совет по оптимизации №1 MySQL вам в помощь, как говорится (проанализируйте Ваши приложения и задачи перед покупкой).

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

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

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

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

Совет по настройке производительности MySQL № 4: сначала делайте менее трудозатратную работу

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

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

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

Совет по настройке и оптимизации MySQL № 5: помните о двух смертельных ловушках масштабируемости

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

Возьмите Универсальный Закон о Масштабируемости, определение, которое удобно выражать и количественно оценивать характеристики масштабируемости системы. Он объясняет проблемы масштабирования с точки зрения двух фундаментальных затрат: сериализации (serialization) и перекрестных помех (crosstalk).

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

Избегайте сериализации и перекрестных помех, и ваше приложение будет масштабироваться намного лучше. Что это означает в контексте настройки производительности MySQL? Это может проявляться в различных аспектах, но в некоторых примерах можно избежать эксклюзивных блокировок для строк. Очереди ( см. пункт № 3) имеют тенденцию плохо масштабироваться по этой причине.

Совет по оптимизации MySQL № 6: не уделяйте слишком много внимания настройке

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

Настройки по умолчанию в конфигурационных файлах MySQL, идут по принципу «одна общая конфигурация подходит для всех задач», и хоть такой подход достаточно устарели, но вам не нужно настраивать все подряд. Лучше правильно настроить базовые параметры и изменять другие настройки только в случае необходимости. В большинстве случаев вы можете получить 95% максимальной производительности сервера, установив примерно 10 параметров. Несколько ситуаций, когда это не применяется, – это крайностные случаи, уникальные для ваших конкретных обстоятельств.

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

Помните, что установочный дистрибутив базы данных MySQL и MariaDb идет с комплектом предустановленных конфигурационных файлов: my-small.cnf, my-medium.cnf, my-large.cnf и my-huge.cnf в зависимости от предполагаемых рабочих нагрузок и имеющихся аппаратных мощностей вашего сервера. Соответственно, для малых, средних, больших и очень больших систем. Выберете один из вариантов в соответствии с Вашими задачами и возможностями. Это дефолтные настройки перекроют от 80 до 95% максимально возможных результатов по оптимизации и настройки производительности.

Совет по оптимизированию и настройке MySQL № 7: следите за запросами пагинации

Приложения, которые имеют склонность к пагинации, как правило, «нагибают» сервер по полной.

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

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

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

Совет по настройке производительности MySQL № 8: неистово накапливайте статистику, но не увлекайтесь чрезмерными оповещениями

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

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

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

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

Совет по производительности MySQL № 9: Изучите три правила индексирования

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

Индексы, если они правильно разработаны, служат для трех важных целей на сервере базы данных:

1. Индексы позволяют серверу находить группы соседних строк вместо отдельных строк. Многие считают, что целью индекса является поиск отдельных строк, но поиск отдельных строк приводит к случайным операциям с дисками, что происходит медленно. Гораздо лучше найти группы строк, все или большинство из которых интересны, чем поиск строк по одиночке.
2. Индексы позволяют избежать сортировки сервера, читая строки в нужном порядке. Сортировка является дорогостоящей. Чтение строк в желаемом порядке происходит намного быстрее.
3. Индексы позволяют серверу удовлетворять целые запросы только из индекса, избегая необходимости вообще обращаться к таблице. Это свойство известно по-разному как индекс покрытия (covering index) или запрос только по индексу (index-only query).

Если вы можете разработать свои индексы и запросы для использования этих трех возможностей, вы можете сделать свои запросы на несколько порядков быстрее!

Совет по производительности MySQL № 10: используйте опыт ваших коллег

Не пытайтесь идти в одиночку. Если вы решаете проблему и делаете то, что кажется логичным и разумным для вас, это здорово. И это возможно работает в 19 разах из 20. В 21-ый же раз вы можете загнать себя в тупик, выход из которого будет очень дорогостоящим и трудоемким. При этом вы будите думать, что применяемые Вами меры имеют большой смысл и шансы на успех.

Посетите несколько ресурсов, связанных с MySQL (например, наш портал), и найдите информацию, выходящую за рамки инструментов и руководств по устранению неполадок. Есть очень знающие люди, скрывающиеся в списках рассылок, форумов, не сайтах в формате Вопрос-ответ (Q & A) и т. д. Конференции, выставки и местные групповые мероприятия предоставляют ценные возможности для получения информации и построения отношений с коллегами, которые могут помочь вам в решении многих задач.

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

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

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

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

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

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

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

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.

Буквально за несколько минут скрипт выдаст полную статистику по работе 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 при любом изменении совпадающих данных кэш будет перестраиваться, что влечет за собой потерю производительности. И чем больше объем кэша, тем больше потери. Кроме того при обновлении кэша могут возникать блокировки запросов. Таким образом, если данные часто пишутся в базу данных – его надежнее отключить.

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

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

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

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

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

systemctl stop mariadb

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

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

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

systemctl start mariadb

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

systemctl status mariadb

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

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

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

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

Выводы

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

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