Статьи

Анализ WordPress MySQL Queries с помощью Query Analytics

Апрель 2, 2018
Эта статья была изначально написана Вадимом Ткаченко

Этот блог, MySQLPerformanceBlog.com, работает на WordPress, но мы никогда не задумывались над тем, какие запросы к MySQL используются WordPress. Поэтому в течение пары месяцев мы запускали там агент Query Analytics (часть Percona Cloud Tools ), и теперь интересно взглянуть на запросы. Query Analytics использует отчеты, созданные с помощью pt-query-digest, но он сильно отличается, так как позволяет видеть тенденции и динамику конкретного запроса, в отличие от pt-query-digest, который является всего лишь одним статическим отчетом.

Почему важно изучать вопросы? Я дал вступление в моем предыдущем посте из этой серии.

Так что Query Analytics выдает отчет по самым популярным запросам. Как определить, какой запрос «плохой»?
Одна из метрик, которую я обычно изучаю, это соотношение «проверенных строк» ​​к «отправленным рядам». В рабочей нагрузке OLTP

Я ожидаю, что «Отправленные строки» будут близки к «Проверенные строки», потому что в противном случае это означает, что запрос обрабатывает много строк («проверенных»), которые не используются в окончательном наборе результатов («отправлено»), и это означает, что они потрачены впустую. Циклы процессора и даже ненужные операции ввода-вывода, если строки не находятся в памяти.

Глядя на запросы WordPress, это не займет много времени, чтобы найти один:

Query1

Этот на самом деле выглядит довольно плохо … Он проверяет до ~ 186000 строк, чтобы вернуть 0 или, в лучшем случае, 1 строку.

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

SELECT comment_ID FROM wp_comments WHERE comment_post_ID = '154' AND comment_parent = '0' AND comment_approved != 'trash' AND ( comment_author = 'poloralphlauren.redhillrecords' OR comment_author_email = 'spam@gmail.com' ) AND comment_content = 'Probabilities are in case you are like the ma spam jorityof people nowadays, you\'re f lululemonaddictoutletcanadaSale.angelasauceda ighting tooth and nail just looking to keep up together with your existence. Acquiring organized can help you win the fight. Appear to the ugg factors in just abo spam ut every of your spaces (desk, workplace, living room, bed' LIMIT 1;

Мы можем видеть, как время выполнения этого запроса меняется со временем

query_1_time

а также сколько строк он проверяет за последний месяц

query_max_rows

Это явно восходящая тенденция, и, очевидно, запрос не так хорошо масштабируется, поскольку появляется все больше и больше данных.

Я нахожу эти трендовые графики очень полезными, и они доступны в Query Analytics, поскольку мы постоянно анализируем и анализируем запросы. Мы можем видеть, что только за последний месяц количество строк, которые исследует этот запрос, увеличилось с ~ 130К до ~ 180К.

Итак, очевидный вопрос — как оптимизировать этот запрос?

Мы смотрим в план объяснения

+----+-------------+-------------+------+----------------------------------------------------------+-----------------+---------+-------+--------+-------------+
| id | select_type | table       | type | possible_keys                                            | key             | key_len | ref   | rows   | Extra       |
+----+-------------+-------------+------+----------------------------------------------------------+-----------------+---------+-------+--------+-------------+
|  1 | SIMPLE      | wp_comments | ref  | comment_post_ID,comment_approved_date_gmt,comment_parent | comment_post_ID | 8       | const | 188482 | Using where |
+----+-------------+-------------+------+----------------------------------------------------------+-----------------+---------+-------+--------+-------------+

и ПОКАЗАТЬ СОЗДАТЬ СТОЛ

 CREATE TABLE `wp_comments` (
  `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `comment_author` tinytext NOT NULL,
  `comment_author_email` varchar(100) NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) NOT NULL DEFAULT '',
  `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text NOT NULL,
  `comment_karma` int(11) NOT NULL DEFAULT '0',
  `comment_approved` varchar(20) NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) NOT NULL DEFAULT '',
  `comment_type` varchar(20) NOT NULL DEFAULT '',
  `comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`comment_ID`),
  KEY `comment_post_ID` (`comment_post_ID`),
  KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  KEY `comment_date_gmt` (`comment_date_gmt`),
  KEY `comment_parent` (`comment_parent`)
)

Очевидно, что WordPress не разработал эту схему для обработки 180000 комментариев к одному сообщению.

Есть несколько способов это исправить, я возьму самый простой способ и поменяю ключ 

 KEY comment_post_ID (comment_post_ID)

в 

 KEY comment_post_ID (comment_post_ID,comment_content(300))

и это меняет план выполнения на

+----+-------------+-------------+------+----------------------------------------------------------------------------------------------------------------+-------------------+---------+-------------+------+-------------+
| id | select_type | table       | type | possible_keys                                                                                                  | key               | key_len | ref         | rows | Extra       |
+----+-------------+-------------+------+----------------------------------------------------------------------------------------------------------------+-------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | wp_comments | ref  | comment_post_ID,comment_approved_date_gmt,comment_parent | comment_post | 910     | const,const |    1 | Using where |
+----+-------------+-------------+------+----------------------------------------------------------------------------------------------------------------+-------------------+---------+-------------+------+-------------+

От 186000 рядов до 910 рядов — это значительное улучшение!

Как это влияет на время выполнения? Давайте сделаем запрос на некоторое время и снова посмотрим на наш график трендов:

query_1_time_after

Падение с ~ 600 мс до ~ 34 мс

и для строк исследовано:

query_1_after

2-й запрос также не трудно найти, и он снова на wp_commentsстоле

query_2

Запрос проверяет до 16K строк, отправляя только 123 в лучшем случае.

Текст запроса (это из другого экземпляра WordPress, поэтому структура таблицы отличается) 

SELECT comment_post_ID FROM wp_comments WHERE LCASE(comment_author_email) = 'spam@gmail.com' AND comment_subscribe='Y' AND comment_approved = '1' GROUP BY comment_post_ID

и ОБЪЯСНИТЬ для этого конкретного

+----+-------------+-------------+------+---------------------------+---------------------------+---------+-------+------+----------------------------------------------+
| id | select_type | table       | type | possible_keys             | key                       | key_len | ref   | rows | Extra                                        |
+----+-------------+-------------+------+---------------------------+---------------------------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | wp_comments | ref  | comment_approved_date_gmt | comment_approved_date_gmt | 62      | const | 6411 | Using where; Using temporary; Using filesort |
+----+-------------+-------------+------+---------------------------+---------------------------+---------+-------+------+----------------------------------------------+

Эта структура таблицы

CREATE TABLE `wp_comments` (
  `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `comment_author` tinytext NOT NULL,
  `comment_author_email` varchar(100) NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) NOT NULL DEFAULT '',
  `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text NOT NULL,
  `comment_karma` int(11) NOT NULL DEFAULT '0',
  `comment_approved` varchar(20) NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) NOT NULL DEFAULT '',
  `comment_type` varchar(20) NOT NULL DEFAULT '',
  `comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `comment_reply_ID` int(11) NOT NULL DEFAULT '0',
  `comment_subscribe` enum('Y','N') NOT NULL DEFAULT 'N',
  `openid` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`comment_ID`),
  KEY `comment_post_ID` (`comment_post_ID`),
  KEY `comment_date_gmt` (`comment_date_gmt`),
  KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  KEY `comment_parent` (`comment_parent`)
)

Есть еще несколько способов сделать запрос более оптимальным, но давайте сделаем небольшое упражнение: пожалуйста, предложите свое решение в комментариях, и для того, который мне больше всего нравится, к концу февраля я передам свой персональный код скидки в Percona Live. MySQL Конференция и Экспо 2014

Итак, в заключение:

  • Query Analytics от Percona Cloud Tools дает немедленную информацию о том, какой запрос требует внимания
  • Благодаря постоянно работающим отчетам мы видим тенденции и результаты наших оптимизаций

Также обратите внимание, что Query Analytics не требует установки прокси-сервера MySQL, некоторого промежуточного программного обеспечения сторонних производителей или любых других анализаторов tcp-трафика. Он полностью работает с медленным журналом, генерируемым MySQL, Percona Server или MariaDB (Percona Server предоставляет гораздо больше информации в медленном журнале, чем ванильный MySQL).
Так что попробуйте Percona Cloud Tools бесплатно, пока он еще в бета-версии. Вы будете в рабочем состоянии минут!

На самом деле, вы хотите, чтобы я посмотрел на ваши вопросы и дал мой совет? Возможно. 12 февраля 2014 года я проведу вебинар под названием « Анализ производительности запросов MySQL с помощью инструментов Percona Cloud Tools» ; Пожалуйста, зарегистрируйтесь и посмотрите условия.

32
Share: