Blogerator.org

× Версия для печати

Окей, поехали. Это первая часть цикла статей «Программное окружение MySQL», которая посвящена теме Инсталляции и анализа SQL-запросов с помощью вспомогательных сервисных Perl-утилит Maatkit.

Установка Maatkit

Скачать последний комплект Maatkit можно на сайте разработчика:

code.google.com/p/maatkit/downloads/list

Качаем последнюю версию и устанавливаем:

wget http://maatkit.googlecode.com/files/maatkit-7540.tar.gz
tar -xvf maatkit-7540.tar.gz
cd maatkit-7540
perl Makefile.PL
make install

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

Анализ SQL-запросов

Maatkit позволяет исследовать эффективность отдельных SQL-запросов на конкретных настройках выбранного сервера, при этом эмпирически выявлять зависимости между нагрузкой и вариантами их построения. Причем, эта задача может решаться двояко: Maatkit может помочь, как оптимизировать конкретные, наиболее «дорогие запросы», так и выполнить тюнинг самого MySQL под используемый на нём тип приложений и выявленные особенности нагрузки. Давайте далее рассмотрим все названные варианты тонкой настройки сервера баз данных уже в терминологии конкретных команд Maatkit.

Утилита mk-query-digest — одна из основных и наиболее развитых в составе пакета. Это, по заверению самих разработчиков, самый настоящий «фреймворк» для анализа лог-файлов и уже обработанных сервером запросов (информация консолидируется из разных источников, таких как slow query log или PROCESSLIST ), и генерации на основании этих данных единого статистического отчета. Утилиту mk-query-digest можно использовать для фильтрации или группировки выполненных запросов по различным критериям, их произвольных повторов или отладки, нахождению самых «дорогих для сервера» запросов.

Ещё один дополнительный плюс — возможность автоматически транслицировать произвольный запрос с диалекта MySQL на диалекты PostgreSQL или даже memcached , а также поддержка анализа логов PostgreSQL. Кроме этого объявлена полная поддержка открытого стандарта Apdex (Application Performance Index), предназначенного для измерения производительности систем и стандартизации подобных исследований и отчетов.

В mk-query-digest очень активно используются концепции событий и атрибутов описания типов запросов, для их фиксации и последующей обработки согласно заданным правилам, полный список которых приведен в документации. В дополнение к этому инструменту поставляется mk-merge-mqd-results  — маленькая, но полезная утилита для склейки в один файл всех отобранных и ранее сохраненных разнородных запросов, сгенерированных через mk-query-digest .

Приведем пример вывода mk-query-digest , при запуске с ключами по-умолчанию:

Следующая логическая связка из утилит — предназначена уже для исследования эффективности настроек самого сервера баз данных. Это, прежде всего, mk-query-profiler , которая запускает SQL-запросы (или любые другие внешние скрипты), замеряя при этом статистику работы и ответную реакцию на эту нагрузку процессов MySQL (связанная с ней mk-profile-compact уплотняет и выравнивает выдачу результатов из указанной утилиты mk-query-profiler , для наиболее наглядного их представления).

Утилиты мелкой россыпью

Следующие утилиты из этой группы:

1. mk-index-usage — считывает запросы из лог-файла и анализирует (через запрос EXPLAIN ) насколько эффективно они используют индексы указанной БД;
2. mk-table-usage — утилита выполняющая чтение истории запросов из лог-файлов и автоматический анализ того, как используются таблицы (составляется граф, с анализом потоков движения данных между таблицами и системой);
3. mk-duplicate-key-checker — находит дублирующиеся индексы и внешние ключи в таблицах MySQL;
4. mk-log-player — позволяет заново, в том числе циклически, проигрывать отдельные последовательности уже обработанных запросов, для нахождения и выявления каких-то закономерностей;
5. mk-upgrade — запускает отобранные запросы сразу на множестве профилируемых серверов MySQL, позволяя наглядно сравнивать их результаты и статистику исполнения, опять же, для выявления странностей или локальных провалов в производительности. Эта утилита также полезна после массового обновления версий установленных MySQL, для начального тестирования их состояния и готовности к дальнейшей производственной эксплуатации;

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

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

# Анализировать все запросы, ранее отобранные в файл slow-query.log
mk-query-advisor /path/to/slow-query.log
# Парсинг всех запросов из стандартного mysql.log
mk-query-advisor --type genlog mysql.log
# Более сложный запрос с использованием tcpdump и mk-query-digest
mk-query-digest --type tcpdump --print --no-report | mk-query-advisor

Поясним логику работы последнего, составного запроса, где мы используем ранее рассмотренную утилиту mk-query-digest , которая в свою очередь, вызывает системную утилиту tcpdump. На основании полученных данных, она парсит сетевые пакеты, выделяя в них клиентский протокол MySQL и извлекая на низком уровне все запросы клиента и ответы сервера баз данных. Далее, результат передаётся по конвейеру на обработку в mk-query-advisor .

Обращаю внимание, что mk-query-advisor позволяет «подслушивать» множество форматов, например, вместо tcpdump можно было бы указать ключ memcached  — уже для контроля протокола memcached.

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

Технически, эта милитаристская команда выполняет считывание всех запросов стоящих в очереди и доступных через SHOW PROCESSLIST , после чего фильтрует их по заранее заданным критериям.

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

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

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

Для тех кто в танке

Соответственно, в зловещем ключе —victims перед запуском следует перечислить список запросов, которые будут находиться «под колпаком» этой утилиты (его можно составить самому или автоматически получить через множество профилирующих утилит Maatkit).

Если же какой-то запрос и его воздействие на базу данных сложно понять, можно воспользоваться помощью mk-visual-explain . Эта утилита обеспечивает вывод вспомогательной информации на любой SQL-запрос, подобно аналогичной команде EXPLAIN из MySQL, только здесь вывод осуществляется в древовидном стиле, в точности отображая алгоритм выполнения этого запроса внутри самого MySQL. mk-visual-explain также может здорово помочь при анализе (или отладке) сложных SQL-запросов, хотя для её использования и требуется определенная квалификация.

Многие находят её «визуальный стиль» ещё более запутывающим, но некоторые профессионалы будут, безусловно, довольны её альтернативным способом объяснения логики работы парсера MySQL.

Например, для следующего запроса:

~

Начало этой серии статей здесь. Следующую часть (продолжение) — читайте вот тут.

Игорь Савчук // Системный Администратор, 2011