Оптимизация в mysql


Содержание

Оптимизация сервера MySQL с помощью утилиты MySQL Tuner

Оптимизация сервера MySQL с помощью специальной утилиты mysqltuner.

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

Сперва потребуется конфигурационный файл my.cnf , более подходящий для нашего сервера.

По умолчанию примеры расположены в директории /usr/share/mysql .

Выбираем конфигурационный файл исходя из наших потребностей:

my-small.cnf — для систем с объемом памяти менее 64Mb, где MySQL используется редко или в незначительной степени;

medium.cnf — под MySQL может выделяться до 64Мb памяти (для маломощных VDS/VPS);

my-large.cnf — для систем с оперативной памятью от 512Мb;

my-huge.cnf — для систем с оперативной памятью 1-2Gb;

Выбираем одну из наиболее подходящих нам конфигураций и переписываем её вместо текущей my.cnf .

Приступим к оптимизации с помощью утилиты MySQL Tuner

Скачиваем утилиту

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

Очень важно обратить внимание на строку

Количество текущих соединений — 16, количество возможных соединений — 151. Параметр 151 — это параметр использующийся по умолчанию. Обычно этого значения маловато, именно из-за него появляются сообщения вида “Too many active connections”. Увеличим этот параметр до 300.
В секции [mysqld] в конфигурационном файле /etc/my.cnf

Еще одним, наиболее важным параметром увеличения производительности MySQL является key_buffer_size . Параметр определяет размер общего для всех пользовательских процессов буфера индексных блоков MyISAM таблиц. Обычно устанавливается в пределах 30-40% от общей выделенной под MySQL оперативной памяти.

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

Рекомендации

1) Ставим MyISAM по умолчанию и отключаем ненужные нам вещи:

2) Привязываем базу для использования только на нашем сервере:

3) Выставляем необходимые максимальные ограничения:

4) Устанавливаем общий буфер, кэш для запросов и максимальный размер для временных таблиц:

5) Максимальное число соединений к базе и ожидающих их процессов:

6) Буферы на каждый процесс (большие значения типа 1Mb и выше для нас это явно перебор).

7) Служебный сортировочный буфер (память постоянно не занимает) и кэш открытых таблиц.

8) Необходимые таймауты, чтобы отсекать долгие ожидания.

9) Установка кодировки UTF-8:

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

Обратите внимание на “1.7M per thread (151 max threads)”. Увеличив количество max_connections в 3 раза, сильно увеличится использование оперативной памяти. Поэтому не стоит ставить max_connections про запас. Посмотрите свои текущие метрики, сколько соединений бывает в пике, прибавьте к этому 10-20%.

Оптимизация MySQL (просто о сложном)

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

Делайте запросы MySQL удобными для кэширования

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

Дело в том, что в первом запросе была использована функция CURDATE(), особенность её работы не позволяет помещать результаты запроса в кэш. Значение даты можно предварительно записать в строку запроса, это позволит исключить использование функции CURDATE() в запросе.
По аналогии есть и другие функции, которые не кэшируются самим сервером MySQL, среди них RAND(), NOW() а так же другие функции, результат которых недетерминирован.

Просмотрите как выполняется ваш запрос с помощью синтаксиса EXPLAIN

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

Когда вам нужна одна запись, выставляйте LIMIT 1

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

Индексируйте поля по которым производится поиск

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

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

Индексируйте поля по которым объединяются таблицы

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

Найдите альтернативу вместо ORDER BY RAND()

Использование рандомной сортировки действительно является весьма удобным, и об этом такого же мнения многие начинающие программисты. Однако тут есть подводные камни, и очень весомые, используя подобный метод выборки в своих запросах, вы оставляете узкое место в производительности. Здесь же рекомендуется прибегнуть к дополнительному коду вместо использования ORDER BY RAND(), в качестве альтернативы, чтобы избавиться от слабого места в производительности, которое напомнит о себе при увеличении объема данных.

Используйте выборку конкретных полей, вместо SELECT *

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

Добавляйте поле ID для всех таблиц

Каждая таблица в хорошем её исполнении должна иметь поле id типа INT, которое является первичным ключом (PRIMARY_KEY), и AUTO_INCREMENT. Кроме того, для поля нужно указать параметр UNSIGNED, который означает то, что значение всегда будет положительным.
В MySQL есть внутренние операции, которые могут использовать первичный ключ, это играет роль для сложных конфигураций баз данных, таких как кластеры, распараллеливание, и т.д.
Кроме того, если есть несколько таблиц, и необходимо выполнить объединенный запрос, то тут ID таблиц окажется весьма кстати.

ENUM как альтернатива VARCHAR

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

Используйте значение NOT NULL вместо NULL

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

Пользуйтесь Prepared Statements

Prepared Statements (подготовленные выражения, их так же называют связываемыми переменными) — это часть функциональности SQL-баз данных, предназначенная для отделения данных запроса и собственно выполняемого SQL-запроса. Его использование имеет преимущества в плане безопасности, и производительности. Фильтруя значения данных, добавляемых в запрос, prepared statements таким образом защищает базу данных от SQL инъекций. Разумеется, делать подобные проверки можно и в ручную, однако в этом случае есть вероятность допустить ошибку из-за невнимательности. Последние версии MySQL компилируют prepared statements в бинарную форму, это позволяет повысить эффективность его работы. Выполняя множество однотипных запросов в приложении, MySQL будет разбирать запрос только один раз. На первых этапах prepared statements не имело возможности кэшироваться в MySQL, это являлось веской причиной для его игнорирования и отсутсвия желания использовать в своих проектах. Однако начиная с версии MySQL 5.1 ситуация кардинально поменялась.

Пользуйтесь mysql_unbuffered_query

mysql_unbuffered_query() посылает запрос MySQL query без автоматической обработки и буферизации её результата, в отличие от функции mysql_query(). Это позволяет сохранить достаточно большое количество памяти для SQL-запросов, возвращающих большое количество данных. Кроме того, вы можете начать работу с полученными данными сразу после того, как первый ряд был получен: вам не приходится ждать до конца SQL-запроса.

Вы можете хранить IP-адреса в поле с типом INT (UNSIGNED)

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

Используйте статичные таблицы

Статичная таблица это обычная таблица в базе, за исключеним того, что каждое поле в таблице имеет фиксированный размер. Если в таблице есть колонки, не фиксированной длины, к примеру, это могут быть: VARCHAR, TEXT, BLOB, она перестает быть статичной, и будет обрабатываться MySQL немного иначе. Статичные таблицы, или их можно ещё назвать таблицами фиксированного размера работают быстрее не статичных. Записи из таких таблицах будут просматриваться быстрее, при необходимости выбора нужной строки MySQL быстро вычислит её позицию. Если поле имеет не фиксированный размер, то в этом случае поиск производиться по индексу. Есть и другие плюсы использования статических таблиц, дело в том, что эти таблицы проще кэшируются, а так же восстанавливаются после падения базы данных.

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

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

Разделяйте объемные запросы INSERT и DELETE

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

Стремитесь использовать поля небольшого размера

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

Выбирайте тип таблиц под свои задачи

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

Оптимизация в mysql

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

Сегодня идет много разговоров о Dig Data и других новых технологиях. NoSQL и облачные решения это супер, но много популярного софта (такого как WordPress, phpBB, Drupal) до сих пор работает на MySQL. Миграция на новейшие решения может вылиться не только в изменении конфигурации на серверах. К тому же, эффективность MySQL до сих пор на уровне, особенно версия Percona.

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


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

Для кого эта статья?

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

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

Делать бэкапы MySQL просто, используя утилиту mysqldump:

Вы можете узнать больше о mysqldump .

Что делает запрос медленным?

Вот общий список факторов, влияющих на скорость выполнения запросов и нагрузки сервера:

  • индексы таблиц;
  • условие WHERE(и использования внутренних функций MySQL, например, таких как IF или DATE);
  • сортировка по ORDER BY;
  • частое повторение одинаковых запросов;
  • тип механизма хранения данных (InnoDB, MyISAM, Memory, Blackhole);
  • не использование версии Percona;
  • конфигурации сервера ( my.cnf / my.ini );
  • большие выдачи данных (более 1000 строк);
  • нестойкое соединение;
  • распределенная или кластерная конфигурация;
  • слабое проектирование таблиц.

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

Что такое индексы?

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

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

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

Уменьшаем частое повторение одинаковых запросов

Наиболее быстрый и эффективный способ, который я нашел для этого — это создание хранилища запросов и результатов их выполнения с помощью Memcached или Redis. С Memcache вы можете легко положить в кэш результат выполнения вашего запроса, например, следующим образом:

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

Примечание: Допишите p: в начале аргумента хоста MySQLi для создания постоянного соединения.

Распределенная или кластерная конфигурация

Когда данных становится все больше, и скорость вашего сервиса идет под уклон, паника может овладеть вами. Быстрым решением может стать распределения ресурсов (sharding). Однако я не рекомендую делать это, если вы не обладаете хорошим опытом, поскольку распределение по своей сути делает структуры данных сложнейшими.

Слабое проектирование таблиц

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

Обеспечение правильного проектирования для нужного использования является первостепенным в создании вашего приложения. Храните различные данные в различных таблицах (например, категории и статьи) и убедитесь, что отношения к другу (many to one) и один ко многим (one to many) могут быть легко связаны с идентификаторами (ID). Использование FOREIGN KEY в MySQL идеально подходит для хранения каскадных данных в таблицах.

При создании таблицы помните следующее:

  • Создавайте эффективные таблицы для решения ваших задач, а не заполняйте таблицы лишними данными и связями.
  • Не ожидайте от MySQL выполнения вашей бизнес логики или програмности — данные должны быть готовы к вставке строки вашей скриптовым языком. Например, если вам нужно отсортировать список в случайном порядке, сделайте это в массиве PHP, не используя ORDER BY из арсенала MySQL.
  • Используйте индексные типы UNIQUE для уникальных наборов данных и применяйте ON DUPLICATE KEY UPDATE, чтобы хранить дату обновленной, например, для того, чтобы знать, когда строка была в последний раз изменена.
  • Используйте тип данных INT для сохранения целых чисел. Если вы не укажете размер типа данных, MySQL сделает это за вас.

Основы оптимизации

Для эффективной оптимизации мы должны применять три подхода к вашему приложению:

  1. Анализ (логирование медленных запросов, изучение системы, анализ запросов и проектирование базы данных)
  2. Требования к исполнению (сколько пользователей)
  3. Ограничения технологий (скорость железа, неправильное использование MySQL)

Анализ может быть сделан несколькими путями. Сначала мы рассмотрим наиболее очевидные способы, чтобы заглянуть под капот вашей MySQL, в котором выполняются запросы. Самый первый инструмент оптимизации в вашем арсенале это EXPLAIN. Если добавить этот оператор перед вашим запросом по SELECT, результат запроса будет таким:

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

Колонка possible_keys покажет индексы, в которые MySQL имел доступ, чтобы выполнить запрос. Иногда нужно назначить индексы, чтобы запрос выполнялся быстрее. Колонка Extra покажет, были ли использованы дополнительные WHEREили ORDER BY. Наиболее важно обратить внимание, есть ли Using Filesort в выводе.

Что делает Using Filesort, указано в справке MySQL:

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

Лишний проход замедлит ваше приложение, этого нужно избегать, чего бы это ни стоило. Другой критический результат Extra, который мы должны избегать — это Using temporary. Он говорит о том, что MySQL пришлось создать временную таблицу для выполнения запроса. Очевидно, это ужасное использования MySQL. В таком случае результат запроса должен быть сохранен в Redis или Memcache и не выполняться пользователями лишний раз.

Чтобы избежать проблемы с Using Filesort мы должны увериться, что MySQL использует INDEX. Сейчас указано несколько ключей в possible_keys, из которых можно выбирать, но MySQL может выбрать только один индекс для финального запроса. Также индексы могут быть составлены из нескольких колонок, также вы можете ввести подсказки (хинты) для оптимизатора MySQL, указывая на индексы, что вы создали.

Илон Маск рекомендует:  option в HTML

Оптимизатор MySQL будет использовать статистику, основанную на запросах таблиц, чтобы выбрать лучший индекс для выполнения запроса. Он действует достаточно просто, основываясь на встроенной статистической логике, поэтому имея несколько вариантов, не всегда делает правильный выбор без помощи хинтинга. Чтобы убедиться, что был использован правильный (или неправильный) ключ, воспользуйтесь ключевым словам FORCE INDEX, USE INDEX и IGNORE INDEX в вашем запросе. Вы можете прочитать больше о хинтинге индексов в справке MySQL .

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

В дополнение к EXPLAIN существует ключевое слово DESCRIBE. Вместе с DESCRIBE можно просматривать информацию из таблицы следующим образом:

Для добавления индексов в MySQL надо использовать синтаксис CREATE INDEX. Есть несколько видов индексов. FULLTEXT Применяется для полнотекстового поиска, а UNIQUE — для хранения уникальных данных.

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

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

Индексы имеют большое влияние на скорость выполнения запросов. Только назначения главного уникального ключа недостаточно — композитные ключи являются реальной областью применения в настройке MySQL, что иногда требует некоторых A/B проверок с использованием EXPLAIN.

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

Как только мы создали ключ на основе колонки username, в котором хранится имя пользователя и колонки active типа ENUM, определяющий, активен ли его аккаунт. Теперь все оптимизировано для запроса, который будет использовать WHERE для поиска валидного имени пользователя с активным аккаунтом (active = 1).

Насколько быстра ваша MySQL?

Включим профилирование, чтобы подробнее рассмотреть MySQL запросы. Это можно сделать, выполнив команду set profiling=1, после чего для просмотра результата надо выполнить show profiles.

Если вы используете PDO, выполните следующий код:

То же самое можно сделать с помощью mysqli:

Это вернет вам профилированные данные, содержащие время выполнения запроса во втором элементе ассоциативного массива.

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

Полный аудит работы базы вашего сайта

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

Чтобы включить логирование в MySQL 5.1.6 используйте глобальную переменную log_slow_queries, также вы можете отметить файл для логирования с помощью переменной slow_query_log_file. Это можно сделать, выполнив следующий запрос:

Также это можно указать в файлах конфигурации /etc/my.cnf или my.ini вашего сервера.

После внесения изменений не забудьте перезагрузить MySQL сервер необходимой командой, например service mysql restart, если вы используете Linux.

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

Переменная long_query_time определяет количество секунд, после которых выполнение запроса считается медленным. Значение это 10, а минимум это 0. Также можно указать миллисекунды, используя дробь; сейчас я указал одну секунду. И теперь каждый запрос, который будет выполняться дольше 1 секунды, записывается в логи в таблице.

Логирование будет вестись в таблицах mysql.slow_log и mysql.general_log вашей MySQL базы данных. Чтобы выключить логирование, измените log_output на NONE.

Логирование на рабочем сервере

На рабочем сервере, который обслуживает клиентов, лучше применять логирование только на короткий период и для мониторинга нагрузки, чтобы не создавать лишней нагрузки. Если ваш сервис перегружен и необходимо безотлагательное вмешательство, попробуйте выделить проблему, выполнив SHOW PROCESSLIST, или обратитесь к таблице information_schema.PROCESSLIST, выполнив SELECT * FROM information_schema.PROCESSLIST;.

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

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

Логирование множества запросов

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

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

Горячий и холодный кэш

Количество запросов и нагрузка сервера имеет сильное влияние на исполнение, также может повлиять на время выполнения запросов. При разработке вы должны взять за правило, что выполнение каждого запроса должно быть не более доли миллисекунды (0.0xx или быстрее) на свободном сервере.

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

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

Исправление медленных запросов

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

Если вы пока не нашли медленного запроса, проверьте настройки long_query_time, если вы пользуетесь этим методом логирования. Иначе, проверив все ваши запросы профилирования (set profiling=1), составьте список запросов, отнимают больше времени, чем доля миллисекунд (0.000x секунд) и начнем из них.

Вот шесть самых распространенных проблем, которые я находил, оптимизируя MySQL запросы:


ORDER BY и filesort

Предотвращение filesort иногда невозможно из-за выражения ORDER BY. Для оптимизации сохраните результат в Memcache, или выполните сортировку в логике вашего приложения.

Использование ORDER BY вместе с WHERE и LEFT JOIN

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

Применение ORDER BY по временным колонками

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

Игнорирование индекса FULLTEXT

Использование LIKE это самый лучший способ сделать полнотекстовый поиск медленным.

Беспричинный выбор большого количества строк

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

Чрезмерное использование JOIN вместо создания композитных таблиц или представления

Когда в одном запросе вы пользуетесь больше чем тремя-четырьмя операторами LEFT JOIN, спросите себя: все ли здесь верно? Продолжайте, если у вас есть на то веская причина, например — запрос используется не часто для вывода в панели администратора, или результат вывода может быть сохранен в кэше. Если же вам нужно выполнять запрос с большим количеством операций объединения таблиц, тогда лучше задуматься о создании композитных таблиц из необходимых столбиков или использовать представления.

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

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

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

Оптимизируйте запросы для кэша запросов

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

Причина в том, что в первом запросе используется функция CURDATE(). Это относиться ко всем функциям, подобным NOW(), RAND() и другим, результат которых недетерминирован. Если результат функции может измениться, то MySQL не кэширует такой запрос. В данном примере это можно предотвратить вычислением даты до выполнения запроса.

Используйте EXPLAIN для ваших запросов SELECT

Используя EXPLAIN, вы можете посмотреть, как именно MySQL выполняет ваш запрос. Это может помочь вам избавиться от слабых мест производительности и других проблем в вашем запросе или в структуре таблиц.
Результат EXPLAIN покажет вам, какие используются индексы, как выбираются и сортируются таблицы и т.д.
Возьмите ваш SELECT запрос (он может быть сложным, с объединениями) и добавьте в начало ключевое слово EXPLAIN. Для этого вы можете использовать phpmyadmin. В результате вы получите очень интересную таблицу. Для примера, пусть я забыл добавить индекс в таблицу, которая участвует в объединении:

После добавления индекса для поля group_id:

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

LIMIT 1, когда нужна единственная строка

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

Индексируйте поля, по которым ищите

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

Как вы можете заметить, это правило также применимо для части строк, например — «last_name LIKE ‘a%’». При поиске с начала строки, MySQL использует индекс этого столбца.
Вы так же должны понимать, что это не сработает для регулярных выражений. Например, когда вы ищите слово (т.е. «WHERE post_content LIKE ‘%apple%’»), то от обычного индекса не будет никакого толку. Лучше будет использовать полнотекстовый поиск или создать вашу собственную систему индексации.

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

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

Не используйте ORDER BY RAND() для больших таблиц

(Имеется в виду выборка единственной строки. Примечание переводчика)

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

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

query(«SELECT MAX(id) FROM `table`»));
$result_set = $mysqli->query(«SELECT * FROM `table` WHERE ` «);
?>

Избегайте SELECT *

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

Старайтесь всегда создать поле ID

В каждой таблице нужно поле id, которое будет PRIMARY KEY, AUTO_INCREMENT, а так же иметь тип INT. Так же неплохо, чтобы оно было UNSIGNED, т.к. вряд ли у идентификатора будут отрицательные значения.
Даже если в вашей таблице пользователей есть уникальное поле username, не делаете его основным ключом. Использование поля VARCHAR, как основного ключа, очень медлительно. Да и структура вашего кода, относящаяся к пользователям, будет гораздо лучше, если у каждого пользователя будет свой внутренний идентификатор.
Есть так же и внутренние операции MySQL, использующие первичный ключ. И это становиться очень важно для более сложных конфигураций базы данных (кластеры, распараллеливание и т.д.)
Исключение из этого правила составляют «таблицы ассоциаций», используемые для связи «многие-ко-многим» между 2 таблицами. Например, таблица «posts_tags», содержит 2 поля: post_id, tag_id, который используется для объединения между двумя таблицами «Posts» и «Tags». Эта таблица будет иметь первичный ключ составленный из 2 полей.

Используйте NOT NULL, если это возможно

Если есть особые причины использовать NULL — используйте его. Но перед этим спросите себя — есть ли разница между пустой строкой и NULL (для INT — 0 или NULL). Если таких причин нет, используйте NOT NULL.
NULL занимает больше места и, к тому же, усложняет сравнения с таким полем. Избегайте его, если это возможно. Тем не менее, бывают веские причины использовать NULL, это не всегда плохо.
Из документации MySQL:
«Столбцы NULL занимают больше места в записи, из-за необходимости отмечать, что это NULL значение. Для таблиц MyISAM, каждое поле с NULL занимает 1 дополнительный бит, который округляется до ближайшего байта».

Подготовленные выражения

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

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

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

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

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

Для использования подготовленных выражений в PHP можно использовать расширение mysqli или PDO.

Небуферизованные запросы

Обычно, делая запрос, скрипт останавливается и ждет результата его выполнения. Вы можете изменить это, используя небуферизованные запросы.
Хорошее описание есть в документации функции mysql_unbuffered_query():

«mysql_unbuffered_query() отправляет SQL-запрос в MySQL, не извлекая и не автоматически буферизуя результирующие ряды, как это делает mysql_query(). С одной стороны, это сохраняет значительное количество памяти для SQL-запросов, дающих большие результирующие наборы. С другой стороны, вы можете начать работу с результирующим набором срезу после получения первого ряда: вам не нужно ожидать выполнения полного SQL-запроса»

Однако есть определенные ограничения. Вам придется считывать все записи или вызывать mysql_free_result() прежде, чем вы сможете выполнить другой запрос. Так же вы не можете использовать mysql_num_rows() или mysql_data_seek() для результата функции.

Хранение IP в UNSIGNED INT

Многие программисты хранят IP адреса в поле типа VARCHAR(15), не зная что можно хранить его в целочисленном виде. INT занимает 4 байта и имеет фиксированный размер поля.
Убедитесь, что используете UNSIGNED INT, т.к. IP можно записать как 32 битное беззнаковое число.
Используйте в запросе INET_ATON() для конвертирования IP адреса в число, и INET_NTOA() для обратного преобразования. Такие же, такие функции есть и в PHP — ip2long() и long2ip() (в php эти функции могут вернуть и отрицательные значения. замечание от хабраюзера The_Lion).

Таблицы фиксированного размера (статичные) — быстрее

Если каждая колонка в таблице имеет фиксированный размер, то такая таблица называется «статичной» или «фиксированного размера». Пример колонок не фиксированной длины: VARCHAR, TEXT, BLOB. Если включить в таблицу такое поле, она перестанет быть фиксированной и будет обрабатываться MySQL по-другому.
Использование таких таблицы увеличит эффективность, т.к. MySQL может просматривать записи в них быстрее. Когда надо выбрать нужную строку таблицы, MySQL может очень быстро вычислить ее позицию. Если размер записи не фиксирован, ее поиск происходит по индексу.
Так же эти таблицы проще кэшировать и восстанавливать после падения базы. Например, если перевести VARCHAR(20) в CHAR(20), запись будет занимать 20 байтов, вне зависимости от ее реального содержания.
Используя метод «вертикального разделения», вы можете вынести столбцы с переменной длиной строки в отдельную таблицу.

Вертикальное разделение

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

Разделяйте большие запросы DELETE и INSERT

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

Выбирайте правильный тип таблицы

Два основных типа таблиц — MyISAM и InnoDB, у каждого есть свои плюсы и минусы.
MyISAM хорошо считывает из таблиц большое количество данных, но он плох для записи. Даже если вы изменяете всего одну строку, блокируется вся таблица, и ни один процесс не может ничего из нее прочитать. MyISAM очень быстро выполняет запросы типа SELECT COUNT(*).
У InnoDB более сложный механизм хранения данных, и он может быть медленнее, чем MyISAM, для маленьких приложений. Но он поддерживает блокировку строк, что более эффективно при масштабировании. Так же поддерживаются некоторые дополнительные функции, такие операции как транзакции.
Подробнее:
MyISAM Storage Engine
InnoDB Storage Engine

Оптимизация в mysql

Для запуска магазина плагинов мы использовали WooCommerce и плагин WooCommerce Software Subscriptions. Суть запроса – получение всех подписок покупателя по его номеру. В WooCommerce довольно сложная модель данных. Хотя заказ и хранится в виде кастомного типа поста, но id покупателя не хранится в post_author, а является частью post мета данных. Также есть несколько подключений к пользовательским таблицам, созданным плагином подписки на ПО. Давайте более подробно разберем запрос.

MySQL твой друг

В MySQL есть полезное выражение DESCRIBE, с помощью которого можно выводить информацию о структуре таблицы (ее колонки, типы данных, значения по умолчанию). Если выполнить DESCRIBE wp_postmeta;, то вы увидите:

Круто, но вы, возможно, уже знаете об этом. Но знали ли вы, что префикс DESCRIBE можно использовать на SELECT, INSERT, UPDATE, REPLACE и DELETE? Более широко известен синоним EXPLAIN, который даст нам подробную информацию о том, как будет выполняться выражение.

Результат для медленного запроса:

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

Самая главная колонка type, в ней описывается способ объединения таблиц. Если стоит ALL, значит MySQL читает всю таблицу с диска, увеличивает скорость чтения/записи и перекладывает загрузку на CPU. Процесс называется полное сканирование таблицы (более подробно позже).

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

Explain дает больше информации, чем мы можем оптимизировать. Например, таблица pm2 (wp_postmeta) говорит нам, что мы используем Using filesort, так как хотим, чтобы результаты хранились с помощью выражения ORDER BY. Если бы мы еще группировали запрос, это добавило бы еще больше нагрузки на выполнение.

Визуальное расследование

MySQL Workbench – еще один хороший бесплатный инструмент для расследований. Для баз данных MySQL 5.6 и выше результат EXPLAIN можно вывести в виде JSON, а MySQL Workbench превращает этот JSON в визуальный план выполнения выражения:


Он автоматически рисует предупреждения по проблемам, окрашивая части запроса, которые будут долго выполняться. Мы сразу видим, что присоединение к таблице wp_woocommerce_software_licences (алиас |) добавляет проблем.

Решение

Часть запроса выполняет полное сканирование таблицы, чего следует избегать, так как она использует колонку без индекса order_id для объединения между таблицами wp_woocommerce_software_licences и wp_posts. Распространенная проблема в медленных запросах. Ее очень легко решить.

Индексы

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

Илон Маск рекомендует:  Праздники военных РФ. Даты и описание

Нам удалось сэкономить 5 секунд запроса простым добавлением индекса, круто!

Знайте свой запрос

Проанализируйте запрос – join за join, подзапрос за подзапросом. Запрос делает то, что не должен? Его можно оптимизировать?

В нашем случае мы подключаем таблицу лицензий к таблице постов с помощью order_id, постоянно ограничивая выражение типами постов shop_order. Это необходимо для обеспечения целостности данных, чтобы убедиться, что мы используем только правильные записи заказов. На самом деле эта часть запроса устарела. Мы знаем, что безопаснее, чтобы в строке лицензии ПО в таблице был order_id, относящийся к заказу WooCommerce в таблице постов, поскольку это применяется в коде плагина PHP. Давайте удалим join и посмотрим, улучшит ли это запрос:

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

Кэшируйте все!

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

Query Monitor выяснил, что наш запрос запускается 4 раза за одну загрузку страницы. Хотя и хорошо кэшировать MySQL запросы, но дублировать считывания из базы данных в одном запросе точно не стоит. Статическое кэширование в коде PHP – простой и крайне эффективный способ решения проблемы. Вы получаете результат запроса из базы данных при первом запросе и храните результат в статическом свойстве класса. В последующих вызовах будет возвращаться результат из статического свойства:

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

Мыслите нестандартно

Можно предпринять и другие способы для ускорения выполнения запросов, в которых нужно чуть больше, чем просто поправить запрос или добавить индекс. Одна из самых медленных частей нашего запроса – процесс объединения таблиц для перехода от id покупателя к id товара, и это необходимо делать для каждого покупателя. А что если сделать все объединения за раз, чтобы получать данные о покупателе тогда, когда это необходимо?

Вы можете денормализовать данные, создав таблицы, в которой хранятся данные лицензии, а также id пользователя и товара для всех лицензий, а также запрос к конкретному покупателю. Понадобится пересобрать таблицу с помощью MySQL triggers на INSERT/UPDATE/DELETE для таблицы лицензий (или других в зависимости от изменения данных), но это значительно повысит производительность запроса данных.

Если несколько join замедляют запрос, можно ускорить его, разбив на 2 и более выражения, после чего выполнять их отдельно в PHP, собирать и фильтровать результаты в коде. Laravel делает что-то похожее в жадной загрузке в Eloquent.

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

Результаты

С помощью этих подходов по оптимизации запросов нам удалось ускорить наш запрос с 8 до 2 секунд, а также снизить количество вызовов с 4 до 1. Время запросов записывалось на версии для разработки, в продакшн оно было бы меньше.

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

Автор: Iain Poulson

Источник: https://www.tutorialspoint.com/

Редакция: Команда webformyself.

Отзывы и обзоры хостинга

Страницы сайта генерируются медленно? Возникают ошибки 502 bad gateway и 504 gateway timeout? Хостер говорит, что сайт создает слишком большую нагрузку на процессор? Скорее всего, проблемы связаны с базой данных. В этой статье рассмотрим вопросы оптимизации производительности MySQL.

Как понять, что дело именно в MySQL

Если сайт работает на популярной CMS, то можно воспользоваться отчетом по SQL-запросам, выполняемым при генерации страницы. Например, в Drupal такой отчет доступен в модуле Devel, в Joomla – в режиме отладки, в WordPress – в расширении Debug bar. Если специальных инструментов нет, то можно до и после выполнения каждого SQL-запроса вызвать PHP-функцию microtime() и посчитать разность.

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

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

С чего начать оптимизацию

Итак, вы определили, какие запросы выполняются при генерации страницы. Дальше возможны варианты:

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

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

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

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

Кеш запросов

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

По умолчанию кеширование выключено. Включить его можно, добавив в конфигурационный файл my.cnf строчку вида query_cache_size = 64M . Через переменную query_cache_size задается размер оперативной памяти, выделяемой под кеш, в данном случае — 64 мегабайта.

Теперь нужно перезапустить MySQL. Сделать это можно из некоторых панелей управления (в ISPmanager: Management tools — Services), либо по SSH из командной строки примерно так:
/usr/local/etc/rc.d/mysql-server stop
/usr/local/etc/rc.d/mysql-server start

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

Есть еще несколько переменных для настройки кеша:

  • query_cache_type задает режим работы кеша, когда query_cache_size установлен больше нуля. Допустимые значения query_cache_type: 0 или OFF — кеширование выключено; 1 или ON — кеширование включено для всех выражений, кроме начинающихся с SELECT SQL_NO_CACHE; 2 или DEMAND — кеширование включено только для запросов, начинающихся с SELECT SQL_CACHE.
  • query_cache_limit – максимально допустимый размер, при котором результат выполнения запроса будет сохранен в кеше.
  • query_cache_min_res_unit – минимальный размер блоков памяти, выделяемых под кеш. По умолчанию 4 Кб. Если у вас много результатов значительно меньшего объема, query_cache_min_res_unit можно понизить, чтобы память использовалась эффективнее. Подходящее значение можно рассчитать по формуле (query_cache_size — Qcache_free_memory) / Qcache_queries_in_cache.

Пример my.cnf для небольшого VPS:
query_cache_size = 64M
query_cache_limit = 2M
query_cache_type = 1
query_cache_min_res_unit = 2K

Посмотреть текущее состояние кеша можно в phpMyAdmin на вкладке Status, либо из командной строки:

  • Qcache_free_blocks – количество свободных блоков в кеше.
  • Qcache_free_memory – объем свободной ОЗУ, отведенной под кеш.
  • Qcache_hits – количество запросов, результаты которых были взяты из кеша.
  • Qcache_inserts – количество запросов, которые были добавлены в кеш.
  • Qcache_lowmem_prunes – количество запросов, которые были удалены из кеша из-за нехватки памяти.
  • Qcache_not_cached – количество запросов, которые не были записаны в кеш (с SQL_NO_CACHE или некешируемые по другим причинам).
  • Qcache_queries_in_cache – количество запросов, которые находятся в кеше.
  • Qcache_total_blocks – общее количество блоков.

Долю закешированных запросов от их общего числа можно посчитать по формуле Qcache_hits / (Com_select + Qcache_hits). Степень использования кеша — Qcache_hits / Qcache_inserts.

О нюансах работы кеша MySQL можно почитать на mysqlperformanceblog.com (англ.)

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

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

Результат работы оператора EXPLAIN

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

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

  • производится поиск в части WHERE
  • соединяются таблицы при JOIN
  • сортируются и группируются записи при ORDER BY и GROUP BY
  • производится поиск MIN() и MAX()

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

Разбирая вывод EXPLAIN, обратите особое внимание на столбцы

  • type (значение ALL — плохо)
  • key (NULL — плохо)
  • ref (NULL — плохо)
  • extra (Using filesort, Using temporary, Using where — плохо)

Описание всех значений и пример оптимизации запроса можно посмотреть в документации.

Добавить индексы можно из phpMyAdmin или с помощью запросов вида ALTER TABLE table_name ADD INDEX index_name (column_name)

Журнал медленных запросов

Если определить тяжелые запросы «на глаз» не получается, нужно собрать более обширную статистику. В этом поможет журнал медленных запросов (slow query log).

Для включения журнала в MySQL, начиная с версии 5.1.29, задайте переменной slow_query_log значение 1 или ON; для отключения журнала — 0 или OFF. В более старых версиях используется log-slow-queries = /var/db/mysql/slow_queries.log (путь можно задать другой).

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

Пара полезных дополнительных настроек:

  • log-queries-not-using-indexes – запись в журнал запросов, не использующих индексы.
  • slow_query_log_file – имя файла журнала. По умолчанию host_name-slow.log

Пример для записи в журнал всех запросов, выполняющихся дольше 50 миллисекунд:
slow_query_log = 1
slow_query_log_file = /var/db/mysql/slow_queries.log
long_query_time = 0.05
log-queries-not-using-indexes = 1


Пример для старых версий MySQL, все запросы дольше 1 секунды:
log-slow-queries = /var/db/mysql/slow_queries.log
long_query_time = 1

Для анализа журнала используются утилиты mysqldumpslow, mysqlsla и mysql_slow_log_filter. Они парсят журнал и выводят агрегированную информацию о медленных запросах.

mysqldumpslow – утилита из состава MySQL. Вызывается таким образом: mysqldumpslow [параметры] [файл_журнала . ] . Пример:

Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log
Count: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1

Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t2 select * from t1 limit N

Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
insert into t1 select * from t1

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

Некоторые параметры mysqldumpslow:

  • -t N – отображать только первые N запросов.
  • -g pattern — анализировать только запросы, которые соответствуют шаблону (как grep).
  • -s sort_type — как сортировать вывод. Значения sort_type: t или at — сортировать по суммарному или среднему времени выполнения запросов, c — по количеству выполненных запросов данного типа.

mysqlsla – еще одна утилита для анализа логов MySQL с аналогичной функциональностью. Пример использования:

mysqlsla -lt slow /tmp/slow_queries.log

Подробности в документации.

mysql_slow_log_filter
— perl-скрипт с похожей функциональностью. Пример использования:

tail –f mysql-slow.log | mysql_slow_log_filter –T 0.5 –R 1000

Эта команда в реальном времени покажет запросы, выполняющиеся дольше 0,5 секунды или сканирующие больше 1000 строк.

Выявленные медленные запросы дальше можно оптимизировать, используя EXPLAIN и индексы.

Вторая часть статьи будет посвящена тонкой настройке MySQL. Материал находится в разработке.

Как ускорить работу 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 my.cnf

Основной конфигурационный файл

Анализ логов сервера баз данных и использование инструментов диагностики производительности

Цели оптимизации:

  1. Достижение стабильной работы сервера
  2. Экономия на модернизации аппаратной части под растущие потребности проекта.

Когда веб проекты разрастаются и текущая конфигурация сервера перестает выдерживать нагрузки — нужно увеличивать объем RAM и выделать проекту больше ресурсов процессора CPU.

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

По умолчанию конфигурация mysql подразумевает следующие значения параметров

[mysqld]
set-variable = max_connections=500
safe-show-databases

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

Значение max_connections, как правило, ниже 500 устанавливать не стоит. Это объясняется тем, что стандартное количество child процессов (child process) для Apache — 512. Если все они будут использоваться, а в /etc/mysql/my.cnf будет задано значение менее 500 — часть пользователей будут видеть при обращении к ресурсу ошибки.

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

Дефолтные настройки во всех случаях нужно менять, без задаваемых непосредственно настроек сервер баз данных не сможет узнать как много оперативной памяти и ресурсов процессора используется. Работа серовера с 256Мб и 16Гб RAM сильно отличаются, для того чтобы обеспечить максимальную производительность MySQL нужна тонкая настройка параметров.

Переменные

MySQL CLI:

SSH (root)

mysqladmin variables или mysqladmin var

mysqladmin variables | grep name-of-variable

Параметры кэширования запросов, которые могут задаваться в my.cnf


thread_cache_size (default 0)

количество потоков, которое сервер должен кэшировать для повторного использования, хорошее значение для начала — 4


table_cache/tables_open_cache(default 64)

количество открытых таблиц для всех потоков


query_cache_limit (default 1M)

максимальный размер запроса который может быть помещен к кеш


query_cache_size (default 0)

поскольку здесь 0, следующий параметр работать не будет
4-8М хорошие значения, количество памяти для кэширования запросов


query_cache_type (default 1=on)

0=off, 2=on if needed

Буферы MySQL

key_buffer_size (default 8M)

буфер для индексов таблиц MYISAM, 24-48М нормальные значения


read_buffer_size (default 128K)

последовательный буфер потоков


join_buffer_size (default 128K)

используется под JOIN без индексов, лучшим решением (вместо использования параметра) является ораганизация базы с джойнами которые могут быть проиндексированы — если такой возможности нет джойны ускоряются параметром join_buffer_size

Другие переменные

connect_timeout (MySQL pre-5.1.23: default 5, MySQL 5.1.23+: default 10)
количество секунд по просшествии которых сервер баз данных будет выдавать ошибку, при активном веб-сервере значение можно уменьшать чтобы увеличить скорость работы, на медленной машине — можно увеличивать

max_connect_errors
(default 10)
максимальное количество единовременных соединений с сервером баз данных с хоста запрос блокируется если он прерывается запросами с того же хоста до момента окончания обработки запроса)
блокируются навсегда, очистить можно только из командной оболочки MySQL:

В случае атаки на сервер нужно уменьшать (5) чтобы отсекать попытки соединения, при большой активности веб-сервера можно увеличивать

max_allowed_packet (default 1M)

максимальный размер пакета, при подключении

tmp_table_size (system-specific default)

16М — довольно много
максимальный размер памяти выделяемой под хранение временных данных

Переменные, использование которых не требуется

thread_concurrency
используется только на Solaris, на linux mysql 5.5+ при использовании переменной MySQL не сможет запуститься

innodb_thread_concurrency

похожая на предыдущую переменная, является, тем не менее, совсем другой и относится к innodb

skip-locking
сейчас параметр называется skip-external-locking (по умолчанию используется начиная с MySQL 4.0 — непосредственное указание в конфиге не требуется)

Логирование MySQL

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

Без логирования единственным способом отслеживать состояние MySQL был бы мониторинг.

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

Error-логи

По умолчанию включены

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

Slow MySQL queries

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

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

Логирование включается путем раскомментирования соответствующих директив к my.cnf

Включение ведения логов в MySQL 5.0:

Логи медленных запросов:

Илон Маск рекомендует:  Таблицы tables (нет в html 2 0)

В большинстве случаев на файлы логов устанавливаются права 600 и владелец mysql:mysql

Включение ведения логов в MySQL 5.1.29+:

Логи медленных запросов:

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

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

просмотр логов atop позволит определить причины неполадок на сервере в принципе, если нагрзку создает MySQL — вывод утилиты укажет на это

предоставляет полную информацию о процессах MySQL

Образцы конфигурационных файлов MySQL

Найти их можно в каталоге /usr/share/mysql

my-huge.cnf
my-large.cnf
my.small.cnf
my-innodb-heavy-4G.cnf
my-medium.cnf

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

Также в шаблонах по умолчанию применяются thread_concurrency, что означает невозможность запуска MySQL с шаблонных конфигом в принципе

Инструменты диагностики MySQL

Общая оптимизация

mysqltuner.pl

Сервер баз данных должен работать минимум 24 часа до запуска данной утилиты

mysqlidxchk

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

chmod 755 mysqlidxchk* mysqlidxchk

./mysqlidxchk —general /var/lib/mysql/ general.log

Парсинг лог-файлов


mysqlsla

Определяет на основании анализа логов медленных запросов и общих логов пользователей которые используют базу больше всего (в процентном отношении)

Использование (логирование должно быть включено)

./mysqlsla -lt general /var/lib/mysql/general.log

Формирование отчетов о статусе

mysqlreport

Утилита интерпретирует вывод SHOW STATUS и составляет на основании собранной статистики отчет о работе MySQL

Исопльзование
./mysqlreport

Инструмент довольно сложен в использовании и предполагает хорошие знания MySQL

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

Настройка MySQL

Основное конфигурирование MySQL осуществляется с помощью файла /etc/my.cnf или /etc/mysql/my.cnf

Указание кодировок и collation

В секцию [mysqld] добавим строки:

character-set-server=utf8
collation-server=utf8_general_ci
init-connect=»SET NAMES utf8″

  • character-set-server — кодировка для всего сервера;
  • collation-server — порядок символов и строк на основе алфавитного порядка и классов эквивалентности;
  • init-connect — строка, выполняемая для каждого клиента при соединении.

Ограничение количества, таймауты и источники соединений

bind-address=localhost
# Отключаем определение доменного имени для IP-адресов
skip-name-resolve
# Максимальное количество соединений
max_connections = 250
# Отключаем использованием symbolic-links
symbolic-links=0
# Таймауты
interactive_timeout=60
wait_timeout=60

Увеличение числа открытых файлов

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

Проверим текущие опции:

Внесем требуемые лимиты в /etc/security/limits.conf

Динамически изменим текущие лимиты:

Проверим soft limit:

Текущие лимиты в MySQL проверим SQL-запросом:

SHOW VARIABLES LIKE ‘%open_files%’

Оптимизация MySQL для MyISAM

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

Буферы

Основными параметрами являются key_buffer_size (буфер для работы с ключами и индексами) и sort_buffer (буфер для сортировки).

key_buffer_size = 64M
sort_buffer_size = 32M

При наличии 16Гб памяти и более, рекомендуется увеличить key_buffer_size до 128M-256M. Если Вы не используете MyISAM таблицы, рекомендуется установить размер key_buffer_size в 32Мб для хранения индексов временных таблиц.

Кэш запросов указывается в опции query_cache_size, ограничение на кэшируемый элемент в query_cache_limit, кэш открытых таблиц в table_open_cache.


table_open_cache = 2048
query_cache_limit = 2M
query_cache_size = 128M
query_cache_type = 1
thread_cache_size = 16

max_heap_table_size = 128M
tmp_table_size = 128M

Будьте внимательны при установке завышенного значения query_cache_size, т.к. это может привести к ожиданию блокировок (Be careful not to set the size of the cache too large. Due to the need for threads to lock the cache during updates, you may see lock contention issues with a very large cache). Мы не рекомендуем устанавливать значение больше 256M.

Параметр thread_cache_size указывает количество тредов (threads), уходящих в кеш при отключении клиента. При новом подключении тред не создается, а берется из кеша, что позволяет экономить ресурсы при больших нагрузках. При наличии 32Гб памяти и более рекомендуем увеличить thread_cache_size до 32, table_open_cache в диапазон 4096-8192, query_cache_size до 256M.

Перенос временных файлов MySQL в память

Проверяем наличие /dev/shm:

Настройки размещаются в /etc/fstab , рекомендуем указать размер, например, 1G:

none /dev/shm tmpfs defaults,size=1G 0 0

Если внесли изменения, то перемонтируем:

mount -o remount /dev/shm

В конфигурационном файле указываем:

В случае, если используется Apparmor, то внесите используемый путь (/dev/shm или /run/mysql) в конфигурационный файл /etc/apparmor.d/usr.sbin.mysqld, например:

service apparmor restart

Оптимизация MySQL для InnoDB

Стандартно все таблицы и индексы хранятся в одном файле, мы рекомендуем использовать опцию innodb_file_per_table для установки хранения каждой таблицы в отдельном файле. Дополнительно необходимо корректно рассчитать параметр innodb_open_files, до версии MySQL 5.6.6 он устанавливался в значение 300, с версии MySQL 5.6.6 рассчитывается автоматически и имеет значение по умолчанию -1.

Значение innodb_open_files и table_open_cache рассчитывается как количество таблиц во всех базах, умноженное на 2, ориентировочно рекомендуем устанавливать обе опции в 4096 или 8192.

innodb_file_per_table = 1
table_open_cache = 4096
innodb_open_files = 4096

При использовании только InnoDB часть опций требует корректировки:

key_buffer_size = 32M
max_allowed_packet = 1M
sort_buffer_size = 32M
read_buffer_size = 256K
read_rnd_buffer_size = 1M
thread_stack = 128K
query_cache_limit = 1M
query_cache_size = 0
query_cache_type = 1
thread_cache_size = 32
max_heap_table_size = 128M
tmp_table_size = 128M

Обратите внимание, при работе с InnoDB мы отключаем query_cache_size установкой его значения в 0, исключающее лишние действия по работе с кэшем, что особенно важно при активной работе с большими объемами данных. С версии MySQL 5.6 query_cache_size отключен, а с версии 8.0 удален.

Важнейшей настройкой MySQL при работе с InnoDB является innodb_buffer_pool_size, устанавливается по принципу «чем больше, тем лучше». Рекомендуется выделять до 70-80% оперативной памяти сервера под innodb_buffer_pool_size. Не забудьте провести расчет использования памяти Apache и дополнительным программным обеспечением для исключения сваливания системы в swap. Для сервера с 16Гб устанавливается в диапазоне 10-12G и разделяем его на 4 секции, для сервера с 32Гб устанавливаем значение в диапазоне 20-24G и разделяем его на 8 секций, :

innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 4

Установка большого размера innodb_log_file_size может привести к увеличению быстродействия, при этом увеличится время восстановления данных, выберите от 258M до 1G.

innodb_log_file_size = 256M
innodb_log_buffer_size = 32M

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

Количество потоков ввода/вывода файлов в InnoDB задается опциями innodb_read_io_threads, innodb_write_io_threads, обычно этому параметру присваивается значение 4 или 8, на быстрых SSD-дисках установите в 16. Значение innodb_thread_concurrency установите в количество ядер * 2.

innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_thread_concurrency = 32

Для ускорения работы с INFORMATION_SCHEMA, SHOW TABLE STATUS или SHOW INDEX отключим обновление статистики при выполнении таких операций, с версии MySQL 5.6.6 innodb_stats_on_metadata отключено по умолчанию:

Для версии MySQL 5.7+ укажите дополнительные опции:

performance_schema = OFF
skip-log-bin
sync_binlog = 0

После применения изменений перезагрузите MySQL:

service mysqld restart

Динамическое изменение размера innodb_buffer_pool_size

С версии MySQL 5.7.5 допускается динамическое изменение размера innodb_buffer_pool_size, однако необходимо помнить, что размер innodb_buffer_pool_size должен быть пропорционален innodb_buffer_pool_chunk_size.

Запросим размер innodb_buffer_pool_chunk_size:

Получим значение, например, 128 мегабайт. Нам требуется динамически установить размер innodb_buffer_pool_size в 16G, соответсвенно 16G / 128M = 125, установим значение:

SET GLOBAL innodb_buffer_pool_size = (SELECT @@innodb_buffer_pool_chunk_size) * 125

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

Три наиболее распространенных вопроса, связанных с производительностью базы данных MySQL, ко­торые мы получаем в консалтинговой практике, — оптимально ли работает сервер, почему конкретный запрос не выполняется достаточно быстро, а также как устранить таинственные перебои в работе, которые пользователи обычно называют «стопор», «захламление» и «зависание». Данная и последующие статьи даст прямые ответы на эти три типа во­просов. Мы перечислим инструменты и методы, которые помогут повысить общую рабочую нагрузку сервера, ускорить выполнение одного запроса, устранить непо­ладки и решить трудновыявляемые проблемы, вызванные неизвестным фактором и неизвестно в чем проявляющиеся.

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

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

Попросите десять человек дать определение термина «производительность», и вы, скорее всего, получите десять различных ответов, в которых будут использованы такие термины, как «запросов в секунду», «загрузка процессора», «масштабируемость» и т. д. Обычно это не вызывает проблем, поскольку люди по-разному понимают производи­тельность в зависимости от контекста, однако в этой серии блогов мы будем использовать формальное определение. Оно гласит: производительность измеряется временем, требующимся для выполнения задачи. Другими словами, производительность — это время отклика системы. Это очень важный подход. Мы измеряем производи­тельность задачами и временем, а не ресурсами. Цель сервера базы данных состоит в выполнении SQL-выражений, поэтому интересующие нас задачи — это запросы или выражения, в основном SELECT , UPDATE , INSERT и

Теперь зададим еще один риторический вопрос: что такое оптимизация? Мы по­дробнее ответим на него позже, а пока договоримся, что оптимизация производитель­ности — это работа, направленная на максимальное сокращение времени отклика для данной

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

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

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

Мы заметили, что многие люди, пытаясь что-то оптимизировать, тратят большую часть своего времени на изменения и очень мало — на измерения. Мы же, на­против, стремимся провести большую часть своего времени — возможно, свыше 90 %, — измеряя, где именно задерживается отклик. Если мы не получили ответа, то, возможно, не выполнили измерения правильно или в полном объеме. При сборе полных и должных образом подобранных метрик работы сервера проблемы про­изводительности обычно выплывают на поверхность и решение сразу становится очевидным. Однако измерение может оказаться сложной задачей само по себе, и, кроме того, иногда непонятно, что делать с полученными результатами: выяснить с помощью измерений, где затрачивается время, еще не означает понять, почему это происходит.

Мы упомянули о правильно проведенных измерениях, но что это значит? Правильно проведенное измерение — это измерение, которое затрагивает только ту деятель­ность, которую вы хотите оптимизировать. Как правило, лишнее попадает в изме­рения в двух случаях:

  • измерения начинаются и заканчиваются не вовремя;
  • деятельность измеряется в совокупности, а не по интересующим нас фрагментам.

Например, распространенной ошибкой является изучение поведения всего сервера при медленном выполнении запроса. Если запрос выполняется медленно, лучше всего измерить только его, а не весь сервер. И следует выполнять измерение от на­чала до конца запроса, а не до или после.

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

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

Как узнать, верны ли измерения

Измерения очень важны, однако не ошибочны ли они? На самом деле измерения всегда ошибочны. Результат измерения величины — это не то же самое, что сама ве­личина. Ошибки в результатах могут быть не настолько большими, чтобы это было важным, и тем не менее это ошибки. Поэтому задаваемый вопрос должен звучать так: «Насколько ошибочными являются измерения?» Эта проблема детально рассматри­вается в других книгах, поэтому мы не будем здесь на ней останавливаться. Просто будьте в курсе, что вы работаете с результатами измерений, а не с фактическими ве­личинами. Очень часто результаты измерений могут оказаться беспорядочными или двусмысленными, что также может привести к неправильным выводам.

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

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

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

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

Чтобы это стало понятнее, посмотрим на реальный профиль рабочей нагрузки всего сервера, который показывает типы запросов, на выполнение которых сервер тратит свое время. Это самое общее представление о времени отклика, другие варианты мы покажем позже. Далее приведен результат работы инструмента pt-query-digest из пакета Percona Toolkit, который является преемником mak-query-digest из пакета Maatkit. Чтобы не отвлекаться на посторонние моменты, мы немного упростили результат, включив в него только первые несколько типов запросов:

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

Фактически мы обсудим два вида профилирования: профилирование времени выпол­нения и анализ ожидания. Профилирование времени выполнения показывает, какие задачи потребляют больше всего времени, а анализ ожидания — на каком этапе задачи застревают или блокируются на самое продолжительное время.

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

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

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

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

Например, в Percona Server 5.0 журнал медленных запросов может выявить некото­рые из наиболее важных причин низкой производительности, такие как ожидание ввода/вывода на диск или блокировки на уровне строк. Если в журнале отобража­ется 9,6 секунды ожидания ввода/вывода на диск для десятисекундного запроса, не имеет смысла выяснять, на что пришлись оставшиеся 4 % времени отклика. Ввод/ вывод на диск, безусловно, является самой важной проблемой.

Интерпретация профиля

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

  • Полезность запросов. Отчет не показывает автоматически, какие запросы стоят того, чтобы их оптимизировать. Это возвращает нас к значению оптимизации. Если вы прочитаете книгу Кэри Миллсапа, то получите намного больше полез­ной информации ло этой теме, но мы повторим два важных нюанса. Во-первых, некоторые задачи не стоит оптимизировать, поскольку время их выполнения составляет незначительную долю от общего времени работы. Согласно закону Амдаля, запрос, который потребляет всего 5 % от общего времени отклика, мо­жет ускорить общее время работы только на 5 % независимо от того, насколько быстрее вы его выполните. Во-вторых, если оптимизация задачи стоит 1000 дол­ларов, а бизнес в итоге не получит никаких дополнительных денег, это значит, что вы просто деоптимизировали бизнес на 1000 долларов. Таким образом, оптимизацию необходимо прекратить, если стоимость улучшения превышает получаемую выгоду.
  • Выбросы. Задачи могут нуждаться в оптимизации, даже если они не оказываются в верхней части профиля. Возможно, медленное выполнение некоторой задачи может быть неприемлемо для пользователей, несмотря на то что эта задача вы­полняется не настолько часто, чтобы занимать существенную долю в общем времени отклика.

Например, если в результате измерений вы получите общее время работы процес­сора 10 секунд, а составление вашего профиля подзадач требует 9,7 секунд, тогда потерянное время составляет 300 миллисекунд. Это может быть признаком того, что вы не все измеряете, либо может оказаться неизбежным из-за ошибок округления и самих затрат на измерения. Если инструмент показывает потерянное время, следует обратить на него внимание. Возможно, вы упускаете из виду что-то важное. Если отчет его не показывает, вы должны обратить на это внимание и запомнить (или за­писать), какой информации вам не хватает. В нашем примере профиля потерянное время не показано, однако это просто недостаток использованного инструмента.

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

Хорошие инструменты могут помочь, автоматически показывая эту информацию. Фактически утилита pt-query-digest включает многие из этих показателей в свой профиль и в подробный отчет, который она выдает. В нашем примере профиль су­щественно упрощен для того, чтобы сосредоточиться на самом важном — сортировке задач по убыванию затраченного времени. Далее в этой серии статей мы приведем примеры более подробного и полезного отчета о профилировании.

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

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL