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

Светлана Комарова

Светлана Комарова

Автор статьи. Системный администратор, Oracle DBA. Информационные технологии, интернет, телеком. Подробнее.

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


Оглавление статьи[Показать]


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

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

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

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

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

А если бы вы считали, что оптимизация производительности связана с увеличением количества запросов в секунду, то подумали бы об оптимизации пропускной способ­ности. Увеличение пропускной способности можно рассматривать как побочный эффект оптимизации производительности. Оптимизация запросов позволяет сер­веру выполнять больше запросов в секунду, поскольку, когда сервер оптимизирован, для выполнения каждого из них требуется меньше времени. (Единица измерения пропускной способности — это запросы за время, то есть величина, обратная произ­водительности, с точки зрения нашего определения.)

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

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

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

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

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

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

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

 

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

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

 

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

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

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

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

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

Rank Response time    Calls R/Call Item
==== ================ ===== ====== =======
   1 11256.3618 68.1% 78069 0.1442 SELECT InvitesNew
   2  2029.4730 12.3% 14415 0.1408 SELECT StatusUpdate
   3  1345.3445  8.1%  3520 0.3822 SHOW STATUS

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

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

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

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

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

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

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

 

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

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

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

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

  • Скрытые подробности. Профиль ничего не говорит о распределении времени от­клика. Опираться на средние значения опасно, поскольку они скрывают инфор­мацию и не являются хорошим индикатором состояния объекта целиком. Петр любит повторять, что информация о средней температуре по больнице не несет смысловой нагрузки. Что бы случилось, если бы элемент № 1 в показанном ранее отчете состоял из двух запросов с односекундным временем отклика и 12 771 за­проса с временем отклика в десятки микросекунд? У нас нет никакого способа выяснить, с какой ситуацией мы имеем дело в данном случае. Чтобы принять правильное решение о месте сосредоточения усилий, вам нужна дополнительная информация о 12 773 запросах, упакованных в эту единственную строчку профиля. Особенно полезно иметь более полную информацию о времени отклика, такую как гистограммы, процентили, стандартное отклонение и индекс рассеяния.

 

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

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

 

Вас заинтересует / Intresting for you:

Модель развития базы данных My...
Модель развития базы данных My... 1410 просмотров Ирина Светлова Thu, 10 Jan 2019, 12:29:03
Транзакции в базе данных MySQL
Транзакции в базе данных MySQL 21430 просмотров Ирина Светлова Mon, 07 Jan 2019, 05:18:23
Выбор оптимальных типов данных...
Выбор оптимальных типов данных... 9728 просмотров Валерий Павлюков Sun, 27 Oct 2019, 15:24:19
Преимущества использования  ба...
Преимущества использования ба... 3426 просмотров Дэйзи ак-Макарова Tue, 03 Aug 2021, 19:11:52
Войдите чтобы комментировать