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

10 апреля 2008, 23:27

Автор: Григорий Рубцов [rgbeast]

Кролик надел очки.
— С чего начинать, Ваше Величество? — спросил он.
— Начни с начала, — важно ответил Король, — продолжай, пока не дойдешь до конца.

Льюис Кэрролл. Приключения Алисы в стране чудес.

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

Нужна ли оптимизация MySQL?

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

• Наличие свободного места на диске в рамках дисковой квоты.
• Отсутствие зацикливания в веб-приложении.
• Время отклика вашего web-сервера (ping).
• В случае виртуального хостинга, отсутсвие внешних замедляющих факторов (один из способов это проверить — остановить приложение и выполнить простейший запрос SELECT 2+2; в консоли mysql на сервере. Такой запрос должен выполниться за несколько сотых секунды).
• Отсутствие задержки при подключении к mysql-серверу с сервера веб-приложения. Если такая задержка присутствует, это может быть связано с неправильной работой системы доменных имен (DNS).

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

Путь оптимизации

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

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

1. Анализ медленных запросов к базе данных. На этой стадии анализируется журнал медленных запросов (slow query log) и текущий поток запросов к базе (увидеть выполняемые в настоящий момент запросы к базе можно, выполнив команду SHOW FULL PROCESSLIST в консоли MySQL).
2. Добавление ключевых полей, оптимизация запросов, оптимизация типов колонок, выбор оптимального механизма хранения (storage engine). На этой ступени устраняются наиболее распространенные причины медленности запросов - отсутствие ключей, неоптимальный выбор типа колонки и неоптимальная форма запроса.
3. Переход на виртуальный выделенный сервер (VDS) или на выделенный физический сервер (если это еще не сделано). Время разработчика дорого, поэтому переход на более производительный сервер часто оптимальное решение (бывает, без этого и не обойтись). Кроме того, без прав суперпользователя невозможна оптимизация конфигурации MySQL, а на VDS невозможно гарантировать стабильную производительность, так как приложения других пользователей VDS могут блокировать доступ к диску.
4. Оптимизация конфигурации MySQL и параметров операционной системы. На этой стадии настраиваются кэши и буферы MySQL, исходя из потребности приложений. Настраиваются другие параметры MySQL и некоторые параметры операционной системы. См. также статью «Использование кэшей индексов».
5. Оптимизация структуры базы данных. На этой стадии производится необходимая нормализация или денормализация базы данных. Стадия наиболее трудоемкая в силу того, что изменение структуры данных может потребовать внесения значительных изменений в код приложений, использующих базу данных. К этому методу обычно прибегают на стадии проектирования приложения или если все, описанные выше методы, не дали результата.

Серебряной пули нет

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

© Все права на данную статью принадлежат порталу webew.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в печатных изданиях допускается только с разрешения редакции.