Тестирование производительности запросов в mysql

Я пытаюсь настроить script, который будет проверять производительность запросов на сервере mysql разработки. Вот более подробная информация:

У меня есть root-доступ.
Я единственный пользователь, обращающийся к серверу
В наибольшей степени заинтересованы в производительности InnoDB
Запросы, которые я оптимизирую, - это в основном поисковые запросы (SELECT ... LIKE '%xy%')

Я хочу создать надежную среду тестирования для измерения скорости одного запроса, не зависящего от других переменных.

До сих пор я использовал SQL_NO_CACHE, но иногда результаты таких тестов также показывают поведение кэширования - выполнение намного дольше для выполнения на первом и меньше времени на последующие прогоны.

Если кто-то может подробно объяснить это поведение, я могу использовать SQL_NO_CACHE; Я полагаю, что это может быть связано с кэшем файловой системы и/или кэшированием индексов, используемых для выполнения запроса, как объясняет . Мне непонятно, когда Buffer Pool и Key Buffer становятся недействительными или как они могут мешать тестированию.

Итак, за исключением перезапуска сервера mysql, как бы вы рекомендовали настроить среду, которая была бы надежной при определении того, будет ли один запрос работать лучше, чем другой?

Ответ 1

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

Некоторые вещи, которые могут быть полезны для этого:

rows в 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 - ваш друг.

Ответ 2

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

Ответ 3

Как предполагает связанная статья, используйте FLUSH TABLES между тестовыми запусками до reset столько, сколько вы можете (особенно кеш запросов).

Не следует ли в ваших тестах учитывать, что InnoDB сам будет иметь разные состояния во время фактической работы, так что вы заинтересованы в совокупной производительности в течение нескольких испытаний? Как "реально" будет ваше тестирование производительности, если вы хотите reset InnoDB для каждого испытания? Запрос, который вы отклоняете, потому что он работает плохо сразу после перезагрузки, может быть самым лучшим запросом после того, как InnoDB немного разогрелся.

Если бы я был вами, я бы сосредоточился на том, что оптимизатор запросов делает отдельно от производительности InnoDB. Там много написано о том, как настроить InnoDB, но это помогает иметь хорошие запросы для начала.

Вы также можете попробовать измерить производительность с помощью эквивалентных таблиц MyISAM, где FLUSH TABLES действительно будет reset вам в основном идентичной начальной точке.

Вы пытались полностью отключить кеширование запросов? Даже с SQL_NO_CACHE существует около 3% -ного штрафа, в котором есть кеш запросов.

Ответ 4

Считаете ли вы использование Maatkit? Одна из возможностей, с которой я немного знакома, заключается в том, чтобы захватить сетевые данные MySQL с помощью tcpdump и обработать дамп с помощью mk-query-digest. Этот инструмент позволяет вам отображать мелкие детализированные сведения о каждом запросе. Но есть целая куча других инструментов, которые облегчают анализ запросов.

Ответ 5

Вы можете попробовать workbench mysql, я подумал, что у него есть монитор операторов sql, чтобы вы могли видеть, насколько это быстро и почему оно быстро

Ответ 6

Полные текстовые запросы на InnoDB медленны (LIKE "% 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, не слепо запускайте оптимизирующие запросы).