Проблема с производительностью SQL

Resolved

(@7logic)

Здравствуйте!

На проектах с относительно большой БД наблюдаются проблемы с производительностью следующего запроса:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'ytrssenabled_meta_value' )  LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1  AND ( 
  wp_term_relationships.term_taxonomy_id IN (1,4,16,178,591,1113,1465)
) AND ( 
  wp_postmeta.post_id IS NULL 
  OR 
  ( mt1.meta_key = 'ytrssenabled_meta_value' AND mt1.meta_value != 'yes' )
) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 40

План запроса:

+----+-------------+-----------------------+------------+--------+---------------------------------------------------------------------------------------+---------+---------+-----------------------------------------------+-------+----------+-----------------------------------------------------------+
| id | select_type | table                 | partitions | type   | possible_keys                                                                         | key     | key_len | ref                                           | rows  | filtered | Extra                                                     |
+----+-------------+-----------------------+------------+--------+---------------------------------------------------------------------------------------+---------+---------+-----------------------------------------------+-------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | wp_term_relationships | NULL       | index  | PRIMARY,term_taxonomy_id                                                              | PRIMARY | 16      | NULL                                          | 57546 |    34.27 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | wp_posts              | NULL       | eq_ref | PRIMARY,type_status_date,type_status_modified_gmt,status_type_password1_date_modified | PRIMARY | 8       | project.wp_term_relationships.object_id       |     1 |    17.22 | Using where                                               |
|  1 | SIMPLE      | wp_postmeta           | NULL       | ref    | post_id,meta_key                                                                      | post_id | 8       | project.wp_term_relationships.object_id       |   232 |   100.00 | Using where                                               |
|  1 | SIMPLE      | mt1                   | NULL       | ref    | post_id                                                                               | post_id | 8       | project.wp_term_relationships.object_id       |   232 |   100.00 | Using where                                               |
+----+-------------+-----------------------+------------+--------+---------------------------------------------------------------------------------------+---------+---------+-----------------------------------------------+-------+----------+-----------------------------------------------------------+
4 rows in set, 1 warning (0.13 sec)

В проекте следующий объем данных по таблицам WP:

wp_postmeta – 25 млн записей
wp_posts – 150 тыс записей
wp_term_relationships – 58 тыс записей

Профилирование запроса:

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=2;
+----------+-----+--------------------------------+------------+------------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+---------------------------+----------------------+-------------+
| QUERY_ID | SEQ | STATE                          | DURATION   | CPU_USER   | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION           | SOURCE_FILE          | SOURCE_LINE |
+----------+-----+--------------------------------+------------+------------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+---------------------------+----------------------+-------------+
|        2 |   2 | starting                       |   0.000019 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                      | NULL                 |        NULL |
|        2 |   3 | Waiting for query cache lock   |   0.000004 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | try_lock                  | sql_cache.cc         |         509 |
|        2 |   4 | starting                       |   0.000003 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | try_lock                  | sql_cache.cc         |         509 |
|        2 |   5 | checking query cache for query |   0.000120 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | send_result_to_client     | sql_cache.cc         |        1655 |
|        2 |   6 | checking permissions           |   0.000005 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access              | sql_authorization.cc |         809 |
|        2 |   7 | checking permissions           |   0.000004 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access              | sql_authorization.cc |         809 |
|        2 |   8 | checking permissions           |   0.000003 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access              | sql_authorization.cc |         809 |
|        2 |   9 | checking permissions           |   0.000004 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access              | sql_authorization.cc |         809 |
|        2 |  10 | Opening tables                 |   0.000028 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables               | sql_base.cc          |        5793 |
|        2 |  11 | init                           |   0.000052 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query              | sql_select.cc        |         128 |
|        2 |  12 | System lock                    |   0.000013 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables         | lock.cc              |         330 |
|        2 |  13 | Waiting for query cache lock   |   0.000003 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | try_lock                  | sql_cache.cc         |         509 |
|        2 |  14 | System lock                    |   0.000022 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | try_lock                  | sql_cache.cc         |         509 |
|        2 |  15 | optimizing                     |   0.000027 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize                  | sql_optimizer.cc     |         158 |
|        2 |  16 | statistics                     |   0.001341 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize                  | sql_optimizer.cc     |         374 |
|        2 |  17 | preparing                      |   0.000031 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize                  | sql_optimizer.cc     |         482 |
|        2 |  18 | Creating tmp table             |   0.000043 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | create_intermediate_table | sql_executor.cc      |         223 |
|        2 |  19 | Sorting result                 |   0.000007 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | make_tmp_tables_info      | sql_select.cc        |        3849 |
|        2 |  20 | executing                      |   0.000003 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                      | sql_executor.cc      |         126 |
|        2 |  21 | Sending data                   | 158.098420 | 173.576000 | 101.552000 |             26545 |                6927 |          560 |         66448 |             0 |                 0 |                 0 |                 2 |     0 | exec                      | sql_executor.cc      |         202 |
|        2 |  22 | Creating sort index            |   0.003703 |   0.012000 |   0.000000 |                 1 |                   1 |            0 |           736 |             0 |                 0 |                 0 |                 0 |     0 | sort_table                | sql_executor.cc      |        2621 |
|        2 |  23 | end                            |   0.000008 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query              | sql_select.cc        |         206 |
|        2 |  24 | query end                      |   0.000007 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command     | sql_parse.cc         |        4956 |
|        2 |  25 | removing tmp table             |   0.000010 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | free_tmp_table            | sql_tmp_table.cc     |        2413 |
|        2 |  26 | query end                      |   0.000005 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | free_tmp_table            | sql_tmp_table.cc     |        2442 |
|        2 |  27 | closing tables                 |   0.000014 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command     | sql_parse.cc         |        5009 |
|        2 |  28 | freeing items                  |   0.000029 |   0.000000 |   0.000000 |                 0 |                   1 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse               | sql_parse.cc         |        5622 |
|        2 |  29 | logging slow query             |   0.000050 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |            16 |             0 |                 0 |                 0 |                 0 |     0 | log_slow_do               | log.cc               |        1716 |
|        2 |  30 | cleaning up                    |   0.000034 |   0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command          | sql_parse.cc         |        1931 |
+----------+-----+--------------------------------+------------+------------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+---------------------------+----------------------+-------------+
29 rows in set, 1 warning (0.00 sec)

Знаю как минимум ещё один большой проект, где подобную проблему обошли форком плагина с последующей доработкой.

Аналогичная проблема может наблюдаться в плагине Яндекс.Дзен.

This topic was modified 4 years, 9 months ago by 7logic.
This topic was modified 4 years, 9 months ago by 7logic.
This topic was modified 4 years, 9 months ago by 7logic.
This topic was modified 4 years, 9 months ago by 7logic.
This topic was modified 4 years, 9 months ago by 7logic.

Viewing 5 replies - 1 through 5 (of 5 total)

Plugin Author Flector
(@flector)

4 years, 9 months ago

так позаимствуйте код у тех, кто форк делал.

дело в том, что никаких собственных sql-запросов в плагине нет.
используется стандартный цикл WP_Query движка. наверняка он не слишком оптимизированный, но оно и понятно – в движке за раз тысячи записей не выбираются. и как его оптимизировать мне абсолютно неизвестно.
Thread Starter 7logic
(@7logic)

4 years, 9 months ago
Добрый день!

Дело в том, что WP_Query – всего лишь инструмент в руках разработчика, и его важно правильно использовать. Мы поддерживаем доработку плагинов и не хотим плодить хаки в проектах для решения проблем. Я углубился в проблему, разобрался и хочу предложить вам решение. Буду признателен, если найдёте время для анализа предложенного решения и доработки плагина. Кстати, вы поддерживаете pull request через github?

Предлагаю добавить в настройки плагина фильтр по дате создания записей – “Выгружать записи не старше %s”. Например, можно добавить список с вариантами 1 год, 6 мес, 3 мес, 1 мес, 2 нед. При включении этого фильтра добавлять date_query в args[] для WPQuery. Подобная настройка была бы полезна и для плагина RSS for Yandex Zen.

Ниже я опишу технические детали, почему это решение работает.

Указанный в тикете запрос относится к следующему участку кода:
```
$args = array(
    'paged' => $paged,
    'ignore_sticky_posts' => 1,
    'post_type' => $yttype,
    'post_status' => 'publish',
    'posts_per_page' => $ytrazbnumber,
    'tax_query' => $tax_query,
    'meta_query' => array(
        'relation' => 'OR',
            array('key' => 'ytrssenabled_meta_value', 'compare' => 'NOT EXISTS',),
            array('key' => 'ytrssenabled_meta_value', 'value' => 'yes', 'compare' => '!=',),
    )
);
$args = apply_filters( 'yturbo_query_args', $args );
$query = new WP_Query( $args );
```
Аналогичная проблема есть в плагине RSS for Yandex Zen на следующем участке кода:
```
$args = array('ignore_sticky_posts' => 1, 'post_type' => $yztype, 'post_status' => 'publish', 'posts_per_page' => $yznumber,'tax_query' => $tax_query,
'meta_query' => array('relation' => 'OR', array('key' => 'yzrssenabled_meta_value', 'compare' => 'NOT EXISTS',),
array('key' => 'yzrssenabled_meta_value', 'value' => 'yes', 'compare' => '!=',),));

$args_alt = apply_filters( 'yzen_query_args', $args, 8 );
if (isset($args_alt) && is_array($args_alt)) $args = $args_alt;
$query = new WP_Query( $args );
```
Указанный выше порядок аргументов заставляет WP генерировать неоптимальный SQL-код с несколькими LEFT JOIN, что вызывает сборку временной таблицы, которая на больших БД может не влезать в память и начинает сбрасываться на диск. Проблема не новая, проявляется на запросах WPQuery, где включён meta_query с логикой OR, проблема описана в тикетах WP 6-летней давности:
https://core.trac.wordpress.org/ticket/24093
https://core.trac.wordpress.org/ticket/30044

Оптимизация в ядре так и не была проведена, решение кроется в декомпозиции запроса с применением подзапросов или наложении ограничений по индексу, чтобы изменить план запроса.

На мой взгляд, самым правильным решением является наложение фильтра по полю post_date, которое есть в индексе. В нашем примере ограничим выборку записей 1 месяцем, получается такой запрос:
```
SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'ytrssenabled_meta_value' )  LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1  AND ( 
  wp_term_relationships.term_taxonomy_id IN (1,4,16,178,591,1113,1465)
) AND ( 
  wp_postmeta.post_id IS NULL 
  OR 
  ( mt1.meta_key = 'ytrssenabled_meta_value' AND mt1.meta_value != 'yes' )
) AND wp_posts.post_date >= '2020-07-18' and wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 40
```
План выполнения запроса изменился кардинально:
```
+----+-------------+-----------------------+------------+-------+---------------------------------------------------------------------------------------+------------------+---------+---------------------------+------+----------+-----------------------------------------------------------+
| id | select_type | table                 | partitions | type  | possible_keys                                                                         | key              | key_len | ref                       | rows | filtered | Extra                                                     |
+----+-------------+-----------------------+------------+-------+---------------------------------------------------------------------------------------+------------------+---------+---------------------------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | wp_posts              | NULL       | range | PRIMARY,type_status_date,type_status_modified_gmt,status_type_password1_date_modified | type_status_date | 169     | NULL                      |  719 |   100.00 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | wp_term_relationships | NULL       | ref   | PRIMARY,term_taxonomy_id                                                              | PRIMARY          | 8       | u100007_ramns.wp_posts.ID |    2 |    34.26 | Using where; Using index                                  |
|  1 | SIMPLE      | wp_postmeta           | NULL       | ref   | post_id,meta_key                                                                      | post_id          | 8       | u100007_ramns.wp_posts.ID |  232 |   100.00 | Using where                                               |
|  1 | SIMPLE      | mt1                   | NULL       | ref   | post_id                                                                               | post_id          | 8       | u100007_ramns.wp_posts.ID |  232 |   100.00 | Using where                                               |
+----+-------------+-----------------------+------------+-------+---------------------------------------------------------------------------------------+------------------+---------+---------------------------+------+----------+-----------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
```
Такой запрос выполняется в десятки раз быстрее, а результат выполнения запроса тот же (в проекте добавляются десятки записей в день).

Резюмируя, хочу добавить, что ту же логику возможно реализовать через встроенные в код плагина фильтры yturbo_query_args и yzen_query_args (для плагина RSS for Yandex Zen), но добавление фильтра по дате в настройки плагина видится наиболее правильным решением. Подобный фильтр поможет избежать проблем производительности в проектах с относительно большим объёмом данных.

Спасибо за внимание.

С уважением,
Александр Зайцев
/* moderator note: пожалуйста, не используйте подписи на форумах. */
- This reply was modified 4 years, 9 months ago by Yui.
- This reply was modified 4 years, 9 months ago by Yui. Reason: drop signature part
Plugin Author Flector
(@flector)

4 years, 9 months ago

для плагина дзена это логично, там дзен все равно старые записи не возьмет. хотя и бессмысленно, так как в дзене в ленту должны попадать только последние записи, а лента из 20-50 записей долго все равно не формируется.

но для турбо это бессмысленно. людям надо, чтобы в ленты попадали все записи, ну какое тут ограничение по дате то?

есть предложения, как еще улучшить wp_query, не используя ограничение по дате?

в любом случае, есть фильтры на параметры цикла. есть возможность один раз скормить вебмастеру ленты, а потом их отключить (турбо-страницы отключены при этом не будут).
Thread Starter 7logic
(@7logic)

4 years, 8 months ago
Здравствуйте!

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

В нашем примере с wp_postmeta 25 млн записей при запросе всего 40 записей для фида Яндекс.Турбо время выполнения SQL занимало 160 секунд. После добавления фильтра по дате – порядка 10 секунд.

Добавление фильтра даты для запросов Яндекс.Дзен также изменит план выполнения запроса: БД не придётся собирать огромную таблицу, сохранять её на диск, чтобы потом из неё выбрать 20-50 записей. При наложении даты объем данных, с которыми БД будет работать, в разы станет меньше (дата есть в индексе). Согласен, что это больше похоже на хак, но это рабочее решение.

есть предложения, как еще улучшить wp_query, не используя ограничение по дате?

На данный момент проблема существует непосредственно в ядре WP в коде WP_Query при использовании meta_query с логикой OR при наличии большой таблицы wp_postmeta (тикет 24093, тикет 30044).

Альтернативным решением может быть отказ от условия OR для проверки ytrssenabled_meta_value:
array('key' => 'ytrssenabled_meta_value', 'compare' => 'NOT EXISTS',),
В этом случае WP не будет генерировать много JOIN в SQL, и запрос будет работать быстрее. Для того, чтобы это работало, можно предусмотреть добавление отсутствующих в wp_postmeta значений ytrssenabled_meta_value при активации плагина.

Ещё одним из вариантов может быть кастомный код для формирования SQL вместо WP_Query.

Самым лучшим вариантом я вижу написание патча для ядра WP в части оптимизации SQL при использовании проверки NOT EXISTS и проверки значения для одного ключа (релевантный тикет):
```
    'meta_query' => array(
        'relation' => 'OR',
            array('key' => 'ytrssenabled_meta_value', 'compare' => 'NOT EXISTS',),
            array('key' => 'ytrssenabled_meta_value', 'value' => 'yes', 'compare' => '!=',),
    )
```
В этом случае не придётся изменять код плагина, а WP_Query будет генерировать более оптимальный SQL. Наша команда попробует поучаствовать в этом процессе. Если сделаем патч, и он войдёт в новую сборку WP, сделаю апдейт в этой ветке.
dhawkmoon
(@dhawkmoon)

4 years, 8 months ago
@7logic
Коллега, столкнулись на одном большем сайте с точно такой же проблемой: https://prnt.sc/u8zfzq
Судя по профилю, запросы к бд занимают сейчас > 30 секунд. Есть возможность подсказать, как переписать данный запрос к бд без наложения ограничения по полю date, но минуя использование WP_Query? Буду очень признателен.
- This reply was modified 4 years, 8 months ago by dhawkmoon.

Viewing 5 replies - 1 through 5 (of 5 total)

The topic ‘Проблема с производительностью SQL’ is closed to new replies.

Tags