Как и все реляционные базы данных, MySQL может оказаться «хитрым зверьком», который может мгновенно «застыть на полпути», оставив ваши приложения в беде и подведя к опасной черте ваш бизнесе или бизнес ваших клиентов.
По правде говоря, в основе большинства проблем производительности MySQL лежат распространенные ошибки. Чтобы ваш сервер MySQL гудел как паровоз на максимальной скорости, обеспечивая стабильную и соответствующую вашим задачам производительность, важно устранить эти ошибки, которые часто скрываются в некоторых тонкостях вашей рабочей нагрузки сервера или в ловушке, скрытой в конфигурационных файлах СУБД.
К счастью, многие проблемы с производительностью MySQL, как правило, имеют аналогичные решения, что позволяет устранить неполадки и настроить MySQL на целевую задачу.
Вот 10 советов, которые позволят Вам получить отличную производительность базы данных MySQL. Данные советы охватывает как сферу администрирования сервера, так и нюансы разработки приложений под СУБД MySQL (т. е. зону ответственности программиста).
Совет по настройке производительности MySQL № 1: профиль вашей рабочей нагрузки
Лучший способ понять, на что ваш сервер тратит свое драгоценное время, - это профиль рабочей нагрузки сервера. Профилируя свою рабочую нагрузку, вы можете просмотреть наиболее дорогие запросы для дальнейшей настройки. Здесь время является наиболее важной метрикой, потому что, когда вы выдаете запрос на сервер, вы ни о чем не заботитесь так сильно, как о наискорейшем времени его завершения.
Лучший способ профилировать рабочую нагрузку - это инструмент, такой как анализатор запросов MySQL Enterprise Monitor или утилита pt-query-digest из набора инструментов Percona Toolkit. Эти инструменты захватывают запросы, выполняемые сервером, и возвращают таблицу задач, отсортированную по уменьшению порядка времени отклика, мгновенно поднимая самые дорогие и трудоемкие задачи вверх. Поэтому Вы можете сразу видеть, на чем сосредоточить свои усилия.
Инструменты профилирования рабочей нагрузки объединяют одинаковые запросы вместе, позволяя вам видеть медленные запросы, а также быстрые, но выполняемые очень часто, что также может заставить сервер «просесть».
Совет по настройке производительности MySQL № 2: Понимание четырех основных ресурсов для оптимизации
Для работы серверу баз данных необходимы четыре основных ресурса: процессор, память, диск и сеть. Если какой-либо из них слабый, неустойчивый или перегруженный, то сервер базы данных, скорее всего, будет работать плохо.
Понимание основных ресурсов важно в двух конкретных областях: выбор оборудования и устранение неполадок.
Выбирая аппаратные средства для MySQL сервера, обеспечьте наличие хороших компонентов. Так же важно, сбалансировать их достаточно хорошо друг с другом. Часто организации выбирают серверы с быстрыми процессорами и дисками, но с маленьким объемом памяти. В некоторых случаях добавление памяти является дешевым способом увеличения производительности на порядки, особенно при нагрузках, связанных с диском. Это может показаться противоречивым, но во многих случаях диски перенапряжены, потому что недостаточно памяти для хранения рабочего набора данных сервера, в следствие чего происходить кеширование данных на диски.
Еще один хороший пример этого баланса относится к процессорам. В большинстве случаев MySQL будет хорошо работать с быстрыми процессорами, потому что каждый запрос работает в одном потоке и не может быть распараллелен между процессорами. Много ядер и много потоков в процессоре - это очень хорошо, но еще лучше, когда каждое конкретное ядро было бы быстрым! Важно не ошибиться при выборе процессора. Купить многоядерный, но с медленными потоками или 1-2 ядерный, но с высокой тактовой частотой каждого ядра? Это определяется конкретными Ваши задачами, которые будут выполнятся на сервере. Совет по оптимизации №1 MySQL вам в помощь, как говорится (проанализируйте Ваши приложения и задачи перед покупкой).
Когда дело доходит до устранения неполадок, проверьте производительность и использование всех четырех ресурсов, тщательно следя за тем, чтобы определить, работают ли все эти компоненты плохо или просто просят делают слишком много работы (аппаратная часть перегружена и требует модернизации). Эти знания помогут быстро решить Ваши проблемы. Вы удивитесь, как часто «бутылочное горлышко» находится именно в несбалансированной конфигурации аппаратной части сервера!
Совет по производительности MySQL № 3: не используйте MySQL в качестве очереди
Очереди (Queues) и шаблоны доступа, похожие на очереди, могут проникнуть в ваше приложение, не зная об этом. Например, если вы в приложении (коде) задаете изменение статуса элемента, тогда конкретный рабочий процесс должен его «захватить», прежде чем его обработать, вы невольно создаете очередь. Обычным примером является маркировка писем как неотправленных, отправка их, а затем их маркировка как отправленные.
Очереди вызывают проблемы по двум основным причинам: они сериализуют вашу рабочую нагрузку, предотвращая параллельное выполнение задач, и они часто приводят к тому, что таблица, содержащая незавершенную работу, а также исторические/ретроспективные данные (historical data ) из заданий, которые были обработаны давно. Оба добавляют латентность к приложению и загружаются в базу данных MySQL.
Совет по настройке производительности MySQL № 4: сначала делайте менее трудозатратную работу
Отличный способ оптимизации MySQL - сначала сделать дешевую, неточную работу, а затем сложную и точную работу над меньшим результирующим набором данных.
Например, предположим, что вы ищете что-то в пределах данного радиуса географической точки. Первым инструментом во множестве инструментов программистов является формула большого круга (Haversine) для вычисления расстояния по поверхности сферы. Проблема с этим методом заключается в том, что для формулы требуется много тригонометрических операций, которые очень интенсивно работают в ЦП. Расчеты с большим кругом, как правило, выполняются медленно и ускоряют загрузку процессора.
Перед тем, как применить формулу с большим кругом, уменьшите свои записи до небольшого подмножества всего и обрезайте полученный набор с точным кругом. Квадрат, содержащий круг (точно или неточно), является простым способом сделать это. Таким образом, мир за пределами площади никогда не попадает во все эти дорогостоящие триггерные функции.
Совет по настройке и оптимизации MySQL № 5: помните о двух смертельных ловушках масштабируемости
Масштабируемость не так расплывчата, как вы можете подумать. На самом деле существуют точные математические определения масштабируемости, которые выражаются в виде уравнений. Эти уравнения подчеркивают, почему системы не масштабируются так, как должны.
Возьмите Универсальный Закон о Масштабируемости, определение, которое удобно выражать и количественно оценивать характеристики масштабируемости системы. Он объясняет проблемы масштабирования с точки зрения двух фундаментальных затрат: сериализации (serialization) и перекрестных помех (crosstalk).
Параллельные процессы, которые должны останавливаться на сериализацию данных, по своей природе ограничены в их масштабируемости. Аналогично, если параллельные процессы должны постоянно общаться друг с другом, чтобы скоординировать свою работу, они ограничивают друг друга.
Избегайте сериализации и перекрестных помех, и ваше приложение будет масштабироваться намного лучше. Что это означает в контексте настройки производительности MySQL? Это может проявляться в различных аспектах, но в некоторых примерах можно избежать эксклюзивных блокировок для строк. Очереди ( см. пункт № 3) имеют тенденцию плохо масштабироваться по этой причине.
Совет по оптимизации MySQL № 6: не уделяйте слишком много внимания настройке
Администраторы баз данных, как правило, тратят огромное количество времени на настройки конфигураций. Результат, как правило, не является большим улучшением и иногда может быть даже очень опасным. Я видел много «оптимизированных» серверов, которые постоянно терпели крах, закончились нехваткой памяти и плохо работали, когда рабочая нагрузка стала немного более интенсивной.
Настройки по умолчанию в конфигурационных файлах MySQL, идут по принципу «одна общая конфигурация подходит для всех задач», и хоть такой подход достаточно устарели, но вам не нужно настраивать все подряд. Лучше правильно настроить базовые параметры и изменять другие настройки только в случае необходимости. В большинстве случаев вы можете получить 95% максимальной производительности сервера, установив примерно 10 параметров. Несколько ситуаций, когда это не применяется, - это крайностные случаи, уникальные для ваших конкретных обстоятельств.
В большинстве случаев серверные инструменты настройки (например, MySqlTuner) не рекомендуется использовать, поскольку они, как правило, дают рекомендации не имеющие смысла для конкретных случаев. У некоторых даже есть опасные, неточные рекомендации, закодированные в них, такие как отношение кэш-памяти и формулы потребления памяти. Они никогда не были правы, и с течением времени они стали еще менее эффективными.
Помните, что установочный дистрибутив базы данных MySQL и MariaDb идет с комплектом предустановленных конфигурационных файлов: my-small.cnf, my-medium.cnf, my-large.cnf и my-huge.cnf в зависимости от предполагаемых рабочих нагрузок и имеющихся аппаратных мощностей вашего сервера. Соответственно, для малых, средних, больших и очень больших систем. Выберете один из вариантов в соответствии с Вашими задачами и возможностями. Это дефолтные настройки перекроют от 80 до 95% максимально возможных результатов по оптимизации и настройки производительности.
Совет по оптимизированию и настройке MySQL № 7: следите за запросами пагинации
Приложения, которые имеют склонность к пагинации, как правило, «нагибают» сервер по полной.
Показывая вам страницу результатов со ссылкой для перехода на следующую страницу, эти приложения обычно группируются и сортируются способами, которые не могут использовать индексы, и они используют инструкции LIMIT и offset, которые заставляют сервер выполнять большую работу генерируя, а затем отбрасывая строки.
Источники для оптимизации часто можно найти в самом пользовательском интерфейсе. Вместо того, чтобы показывать точное количество страниц в результатах и ссылки на каждую страницу отдельно, вы можете просто показать ссылку на следующую страницу. Вы также можете запретить людям переходить на страницы слишком далеко от первой страницы.
На стороне запроса вместо использования LIMIT и offset вы можете выбрать еще одну строку, чем вам нужно, и когда пользователь нажимает ссылку «следующая страница», вы можете назначить эту конечную строку в качестве отправной точки для следующего набора результатов. Например, если пользователь просмотрел страницу со строками с 101 по 120, вы также должны сделать выборку и строки 121, а чтобы вывести следующую страницу, вы запросите сервер для строк больше или равных 121, предел 21.
Совет по настройке производительности MySQL № 8: неистово накапливайте статистику, но не увлекайтесь чрезмерными оповещениями
Мониторинг и оповещение необходимы, но что происходит с типичной системой мониторинга? Он начинает отправлять ложные срабатывания, а системные администраторы настраивают правила фильтрации электронной почты, чтобы остановить весь этот спам. Вскоре ваша система мониторинга станет совершенно бесполезной.
Мы предлагаем воспринимать мониторинг в двух разрезах. Во-первых, как можно подробнее фиксировать показатели и оповещения. Очень важно фиксировать и сохранять все показатели, которые позволяет сделать сервер. Когда-нибудь возникнет странная проблема, и вам несомненно потребуется возможность проанализировать статистику и график изменения рабочей нагрузки сервера.
Во-вторых, есть тенденция настраивать систему мониторинга на слишком много сигналов оповещения. Мы настоятельно предлагаем этого не делать. Системы мониторинга часто предупреждают о таких вещах, как коэффициент попадания в буфер или количество временных таблиц, созданных за секунду. Проблема в том, что для такого отношения нет хорошего порога чувствительности. Правильный порог отличается не только от сервера к серверу, но и от часа к часу с изменением рабочей нагрузки.
В результате, необходимо осторожно реагировать на статистику мониторинга и только на условиях, которые указывают на определенную, действующую проблему. Низкий коэффициент попадания в буфер не подлежит действию и не указывает на реальную проблему, но сервер, который не отвечает на попытку подключения, является реальной проблемой, которая должна быть решена.
Совет по производительности MySQL № 9: Изучите три правила индексирования
Индексирование, вероятно, является самой непонятой темой в базах данных, потому что существует множество способов запутаться в том, как работают индексы и как их использует сервер. Требуется много усилий, чтобы действительно понять, что происходит.
Индексы, если они правильно разработаны, служат для трех важных целей на сервере базы данных:
- Индексы позволяют серверу находить группы соседних строк вместо отдельных строк. Многие считают, что целью индекса является поиск отдельных строк, но поиск отдельных строк приводит к случайным операциям с дисками, что происходит медленно. Гораздо лучше найти группы строк, все или большинство из которых интересны, чем поиск строк по одиночке.
- Индексы позволяют избежать сортировки сервера, читая строки в нужном порядке. Сортировка является дорогостоящей. Чтение строк в желаемом порядке происходит намного быстрее.
- Индексы позволяют серверу удовлетворять целые запросы только из индекса, избегая необходимости вообще обращаться к таблице. Это свойство известно по-разному как индекс покрытия (covering index) или запрос только по индексу (index-only query).
Если вы можете разработать свои индексы и запросы для использования этих трех возможностей, вы можете сделать свои запросы на несколько порядков быстрее!
Совет по производительности MySQL № 10: используйте опыт ваших коллег
Не пытайтесь идти в одиночку. Если вы решаете проблему и делаете то, что кажется логичным и разумным для вас, это здорово. И это возможно работает в 19 разах из 20. В 21-ый же раз вы можете загнать себя в тупик, выход из которого будет очень дорогостоящим и трудоемким. При этом вы будите думать, что применяемые Вами меры имеют большой смысл и шансы на успех.
Посетите несколько ресурсов, связанных с MySQL (например, наш портал), и найдите информацию, выходящую за рамки инструментов и руководств по устранению неполадок. Есть очень знающие люди, скрывающиеся в списках рассылок, форумов, не сайтах в формате Вопрос-ответ (Q & A) и т. д. Конференции, выставки и местные групповые мероприятия предоставляют ценные возможности для получения информации и построения отношений с коллегами, которые могут помочь вам в решении многих задач.
