Support » Plugin: RSS for Yandex Turbo » Проблема с производительностью SQL

  • Resolved 7logic

    (@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 5 months ago by 7logic.
    • This topic was modified 5 months ago by 7logic.
    • This topic was modified 5 months ago by 7logic.
    • This topic was modified 5 months ago by 7logic.
    • This topic was modified 5 months ago by 7logic.
Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Author Flector

    (@flector)

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

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

    7logic

    (@7logic)

    Добрый день!

    Дело в том, что 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 5 months ago by Yui.
    • This reply was modified 5 months ago by Yui. Reason: drop signature part
    Plugin Author Flector

    (@flector)

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

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

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

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

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

    Технически ограничение по дате имеет смысл в некоторых ситуациях, т.к. кардинально меняет план запроса, что сказывается положительно на времени выполнения 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, сделаю апдейт в этой ветке.

    @7logic
    Коллега, столкнулись на одном большем сайте с точно такой же проблемой: https://prnt.sc/u8zfzq
    Судя по профилю, запросы к бд занимают сейчас > 30 секунд. Есть возможность подсказать, как переписать данный запрос к бд без наложения ограничения по полю date, но минуя использование WP_Query? Буду очень признателен.

    • This reply was modified 4 months, 3 weeks ago by dhawkmoon.
Viewing 5 replies - 1 through 5 (of 5 total)
  • You must be logged in to reply to this topic.