10 важных советов по производительности MySQL

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

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

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

Вот 10 советов по увеличению производительности MySQL.

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

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

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

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

Совет по производительности MySQL № 2: изучите четыре основных ресурса

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

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

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

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

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

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

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

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

Совет по производительности MySQL №4: сначала фильтруйте результаты по самым дешевым

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Совет по производительности MySQL № 8. Сохраняйте статистику с готовностью, неохотно предупреждая

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

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

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

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

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

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

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

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

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

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

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

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

Тем, кто ищет инструменты, дополняющие эти советы, вы можете воспользоваться мастером настройки Percona для MySQL, советником по запросам Percona для MySQL и подключаемыми модулями Percona Monitoring. (Примечание. Для доступа к этим первым двум ссылкам вам потребуется создать учетную запись Percona. Это бесплатно.) Мастер настройки может помочь вам создать базовый файл my.cnf для нового сервера, который превосходит образцы файлов, поставляемых с сервер. Советчик по запросам проанализирует ваш SQL, чтобы помочь обнаружить потенциально плохие шаблоны, такие как запросы с разбивкой на страницы (№ 7). Плагины Percona Monitoring - это набор плагинов для мониторинга и построения графиков, которые помогут вам быстро сохранять статистику и предупреждать неохотно (№ 8). Все эти инструменты находятся в свободном доступе.