Проверение производительности запросов в mysql
Я пытаюсь установить сценарий, который проверил бы производительность запросов на разработке mysql сервер. Вот больше деталей:
- У меня есть корневой доступ
- Я - единственный пользователь, получающий доступ к серверу
- Главным образом заинтересованный работой InnoDB
- Запросы, которые я оптимизирую, являются главным образом поисковыми запросами (
SELECT ... LIKE '%xy%')
То, что я хочу сделать, должно создать надежную тестовую среду для измерения скорости единого запроса, лишенного зависимостей от других переменных.
До настоящего времени я использовал SQL_NO_CACHE, но иногда результаты таких тестов также показывают кэширующееся поведение - взятие намного дольше для выполнения на первом показе и взятии меньшего количества времени на последующих выполнениях.
Если кто-то может объяснить это поведение в полных деталях, я мог бы придерживаться использования SQL_NO_CACHE; Я действительно полагаю, что это могло бы произойти из-за кэша файловой системы, и/или кэширование индексов раньше выполняло запрос, как это сообщение объясняет. Мне не ясно, когда Пул буферов и Ключевой Буфер делаются недействительным или как они могли бы вмешаться в тестирование.
Так, за исключением перезапуска mysql сервер, как Вы рекомендовали бы установить среду, которая будет надежна в определении, если один запрос выполнит лучше затем другой?
6 ответов
Предполагая, что вы не можете оптимизировать саму операцию LIKE, вы должны попытаться оптимизировать базовый запрос без минимизации количества строк, которые должны быть проверены.
Некоторые вещи, которые могут быть для этого полезны:
строк столбец в EXPLAIN SELECT ... результат. Затем,
mysql> set profiling=1;
mysql> select sql_no_cache * from mytable;
...
mysql> show profile;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000063 |
| Opening tables | 0.000009 |
| System lock | 0.000002 |
| Table lock | 0.000005 |
| init | 0.000012 |
| optimizing | 0.000002 |
| statistics | 0.000007 |
| preparing | 0.000005 |
| executing | 0.000001 |
| Sending data | 0.001309 |
| end | 0.000003 |
| query end | 0.000001 |
| freeing items | 0.000016 |
| logging slow query | 0.000001 |
| cleaning up | 0.000001 |
+--------------------+----------+
15 rows in set (0.00 sec)
Затем,
mysql> FLUSH STATUS;
mysql> select sql_no_cache * from mytable;
...
mysql> SHOW SESSION STATUS LIKE 'Select%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 1 |
+------------------------+-------+
5 rows in set (0.00 sec)
И еще одно интересное значение - last_query_cost , которое показывает, насколько дорого оптимизатор оценил запрос (значение - это количество случайных чтений страницы):
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 2635.399000 |
+-----------------+-------------+
1 row in set (0.00 sec)
Документация MySQL ваш друг.
Вы можете попробовать mysql workbench, я думал, что у него есть монитор операторов sql, чтобы вы могли видеть, насколько он быстр и почему
Как предлагается в связанной статье, используйте FLUSH TABLES между тестовыми запусками для максимального сброса (особенно кеша запросов).
Разве ваше тестирование не должно принимать во внимание, что InnoDB сам по себе будет иметь разные состояния во время реальной работы, так что вас интересует совокупная производительность по нескольким испытаниям? Насколько «реальным» будет ваше тестирование производительности, если вы хотите перезагружать InnoDB для каждой пробной версии? Запрос, который вы отклоняете, потому что он плохо работает сразу после перезапуска, может оказаться лучшим запросом после того, как InnoDB немного нагреется.
На вашем месте я бы сосредоточился на том, что оптимизатор запросов делает отдельно от производительности InnoDB. О том, как настроить InnoDB, много написано, но для начала полезно иметь хорошие запросы.
Вы также можете попробовать измерить производительность с помощью эквивалентных таблиц MyISAM, где FLUSH TABLES действительно вернет вас к практически идентичной начальной точке.
Вы пробовали полностью отключить кеширование запросов? Даже с SQL_NO_CACHE, простое включение кеша запросов приводит к штрафу примерно в 3%.
Рассматривали ли вы использование Maatkit ? Одна из его возможностей, с которой я немного знаком, - это захват сетевых данных MySQL с помощью tcpdump и обработка дампа с помощью mk-query-digest . Этот инструмент позволяет отображать некоторые подробные сведения о каждом запросе. Но есть целый ряд других инструментов, которые должны упростить анализ запросов.
Полнотекстовые запросы в InnoDB выполняются медленно (НАПРАВЛЯЕМЫЕ операторы "% query%"), вы ничего не можете сделать для их оптимизации. Решения варьируются от передачи этой конкретной таблицы, которую вы запрашиваете, в MyISAM, чтобы вы могли создавать полнотекстовые индексы (которые innoDB не поддерживает), до денормализации строки в индексы с возможностью поиска (не рекомендуется), Doctrine ORM предоставляет простой пример того, как это архивировать: http://www.doctrine-project.org/documentation/manual/1_1/nl/behaviors:core-behaviors:searchable "Правильным" решением вашей проблемы было бы индексирование информация, которую вы используете для полнотекстового поиска, с помощью таких решений, как Sphinx Search или Apache Solr.
Как было сказано ранее, вы должны учитывать состояние кеша при сравнении результатов, так как заполненный кеш дает чрезвычайно производительные запросы. Вы должны учитывать процент попаданий в кеш для конкретного запроса, даже если это дорогостоящий запрос, если у него коэффициент попадания в кеш 99%, средняя производительность будет очень высокой.
Тонкая настройка запросов - не серебряная пуля, вы можете усложнить свое приложение ради оптимизации, которая в целом в производственной среде незначительна.
Учитывайте свою рабочую нагрузку, устраняйте частые, неэффективные запросы (используйте slow_query_log в mysql, не начинайте вслепую оптимизацию запросов).
Цитируется по этой странице: Опции SQL_NO_CACHE влияют на кэширование результатов запроса в кэше запросов. Если ваша таблица довольно маленькая, то возможно, что сама таблица уже кэшируется. Поскольку вы просто избегаете кэширования результатов, а не таблицы, вы иногда получаете описанное поведение. Поэтому, как сказано в других сообщениях, вам следует промывать ваши таблицы между запросами.