6.3.6: Запрос с JOIN грузит CPU в 100%

General Discussions - Russian
1

Доброго времени суток.

Есть:

Server version: 6.3.6 593045790@24080214 (columnar 2.3.0 88a01c3@24052206) (secondary 2.3.0 88a01c3@24052206) (knn 2.3.0 88a01c3@24052206) git branch manticore-6.3.6...origin/manticore-6.3.6

1 сервер (8 CPU, 12 GB RAM) и индексы RT.

Индексы:

products 
mysql> desc products;
+--------------------------------------------------+-----------+----------------+
| Field                                            | Type      | Properties     |
+--------------------------------------------------+-----------+----------------+
| id                                               | bigint    |                |
| name                                             | text      | stored         |
| slug                                             | text      | stored         |
| description                                      | text      | stored         |
| selling_text                                     | text      | stored         |
| reasons_to_buy                                   | text      | stored         |
| additional_images                                | text      | stored         |
| school_umk_title                                 | text      | indexed stored |
| seo_description                                  | text      | stored         |
| relations_set                                    | text      | indexed stored |
| author_full_name_list                            | text      | indexed stored |
| publisher_series_name                            | text      | indexed stored |
| publisher_name                                   | text      | indexed stored |
| publisher_brand_name                             | text      | indexed stored |
| manufacturer_name                                | text      | indexed stored |
| manufacturer_brand_name                          | text      | indexed stored |
| image                                            | string    |                |
| price_retail                                     | float     |                |
| price_discounted_for_guest                       | float     |                |
| price_discount_percent_for_guest                 | float     |                |
| price_discounted_for_authorized                  | float     |                |
| price_discount_percent_for_authorized            | float     |                |
| price_discounted_for_subscriber                  | float     |                |
| price_discount_percent_for_subscriber            | float     |                |
| marketing_status_list                            | mva       |                |
| availability_status                              | uint      |                |
| availability_quantity                            | uint      |                |
| vendor_id                                        | uint      |                |
| nds                                              | uint      |                |
| reasons_to_buy_list                              | json      |                |
| literature_work_publishing_year                  | uint      |                |
| preorder_available_at                            | timestamp |                |
| released_at                                      | timestamp |                |
| created_at                                       | timestamp |                |
| isbns                                            | string    |                |
| printing_page_count                              | uint      |                |
| printing_page_format                             | string    |                |
| printing_copy_count                              | uint      |                |
| weight                                           | uint      |                |
| height                                           | uint      |                |
| width                                            | uint      |                |
| length                                           | uint      |                |
| excerpts                                         | json      |                |
| rating_average                                   | float     |                |
| rating_weight                                    | uint      |                |
| rating_star                                      | uint      |                |
| rating_count                                     | uint      |                |
| review_count                                     | uint      |                |
| purchase_stats_week_count                        | uint      |                |
| purchase_stats_month_count                       | uint      |                |
| purchase_stats_year_count                        | uint      |                |
| purchase_stats_day_avg_count                     | uint      |                |
| sales_stat_year_turnover_amount_without_vat      | float     |                |
| sales_stat_year_turnover_quantity                | uint      |                |
| sales_stat_year_markup                           | float     |                |
| sales_stat_half_year_turnover_amount_without_vat | float     |                |
| sales_stat_half_year_turnover_quantity           | uint      |                |
| sales_stat_half_year_markup                      | float     |                |
| sales_stat_quarter_turnover_amount_without_vat   | float     |                |
| sales_stat_quarter_turnover_quantity             | uint      |                |
| sales_stat_quarter_markup                        | float     |                |
| sales_stat_month_turnover_amount_without_vat     | float     |                |
| sales_stat_month_turnover_quantity               | uint      |                |
| sales_stat_month_markup                          | float     |                |
| school_grade_id_list                             | mva       |                |
| school_subject_id_list                           | mva       |                |
| school_material_type_id_list                     | mva       |                |
| school_exam_id_list                              | mva       |                |
| school_education_system_id_list                  | mva       |                |
| school_umk_id_list                               | mva       |                |
| school_exam_year_id_list                         | mva       |                |
| school_purpose_id_list                           | mva       |                |
| seo_title                                        | string    |                |
| main_category_id_list                            | mva       |                |
| category_id_list                                 | mva       |                |
| tbk_id_list                                      | mva       |                |
| ekn_id_list                                      | mva       |                |
| author_id_list                                   | mva       |                |
| translator_id_list                               | mva       |                |
| illustrator_id_list                              | mva       |                |
| publisher_series_id                              | uint      |                |
| publisher_id                                     | uint      |                |
| publisher_brand_id                               | uint      |                |
| manufacturer_id                                  | uint      |                |
| manufacturer_brand_id                            | uint      |                |
| literature_work_cycle_id                         | uint      |                |
| literature_work_cycle_volume_id                  | uint      |                |
| age_restriction                                  | bigint    |                |
| binding_id                                       | uint      |                |
| tag_id_list                                      | mva       |                |
| product_set_id_list                              | mva       |                |
| product_type_id                                  | uint      |                |
| article_number_id                                | uint      |                |
| product_type_id_list                             | mva       |                |
| article_number_id_list                           | mva       |                |
| stationery_brush_shape_id_list                   | mva       |                |
| stationery_brush_material_id_list                | mva       |                |
| stationery_brush_number_id_list                  | mva       |                |
| stationery_painting_technique_id_list            | mva       |                |
| stationery_lead_hardness_id_list                 | mva       |                |
| stationery_format_id_list                        | mva       |                |
| stationery_line_type_id_list                     | mva       |                |
| stationery_ink_color_id_list                     | mva       |                |
| stationery_lead_diameter_id_list                 | mva       |                |
| stationery_case_shape_id_list                    | mva       |                |
| stationery_mechanism_type_id_list                | mva       |                |
| stationery_diameter_id_list                      | mva       |                |
| stationery_feature_id_list                       | mva       |                |
| stationery_colors_quantity_id_list               | mva       |                |
| stationery_gender_id_list                        | mva       |                |
| stationery_length_id_list                        | mva       |                |
| stationery_staple_number_id_list                 | mva       |                |
| stationery_stapler_number_id_list                | mva       |                |
| stationery_material_id_list                      | mva       |                |
| stationery_punched_sheets_quantity_id_list       | mva       |                |
| stationery_pen_thickness_id_list                 | mva       |                |
| stationery_mounting_type_id_list                 | mva       |                |
| stationery_pen_tip_shape_id_list                 | mva       |                |
| stationery_ink_base_id_list                      | mva       |                |
| stationery_calculator_capacity_id_list           | mva       |                |
| stationery_calendar_year_id_list                 | mva       |                |
| stationery_calendar_type_id_list                 | mva       |                |
| stationery_calendar_subject_id_list              | mva       |                |
| stationery_clasp_type_id_list                    | mva       |                |
| stationery_compartments_quantity_id_list         | mva       |                |
| stationery_cover_binding_id_list                 | mva       |                |
| stationery_package_type_id_list                  | mva       |                |
| stationery_cover_surface_id_list                 | mva       |                |
| stationery_universal_id_list                     | mva       |                |
| stationery_sheets_quantity_id_list               | mva       |                |
| stationery_color_id_list                         | mva       |                |
| stationery_volume_id_list                        | mva       |                |
| comic_universal_id_list                          | mva       |                |
| comic_character_id_list                          | mva       |                |
| comic_genre_id_list                              | mva       |                |
| comic_series_id_list                             | mva       |                |
| comic_type_id_list                               | mva       |                |
| comic_line_id_list                               | mva       |                |
| comic_section_id_list                            | mva       |                |
| comic_subject_id_list                            | mva       |                |
| game_player_quantity_id_list                     | mva       |                |
| game_use_case_id_list                            | mva       |                |
| game_skill_id_list                               | mva       |                |
| game_audience_id_list                            | mva       |                |
| game_child_age_id_list                           | mva       |                |
| game_series_id_list                              | mva       |                |
| game_duration_id_list                            | mva       |                |
| constructor_detail_quantity_id_list              | mva       |                |
| constructor_nation_id_list                       | mva       |                |
| constructor_equipment_type_id_list               | mva       |                |
| souvenir_reason_id_list                          | mva       |                |
| souvenir_format_id_list                          | mva       |                |
| souvenir_set_quantity_id_list                    | mva       |                |
| toy_type_id_list                                 | mva       |                |
| toy_height_id_list                               | mva       |                |
| gift_hobby_id_list                               | mva       |                |
| gift_for_children_id_list                        | mva       |                |
| gift_new_year_id_list                            | mva       |                |
| gift_section_id_list                             | mva       |                |
| gift_books_on_interest_id_list                   | mva       |                |
| product_collection_id_list                       | mva       |                |
| shop_id_list                                     | mva       |                |
| city_id_list                                     | mva       |                |
| shop_brand_id_list                               | mva       |                |
| active_shops_id_list                             | mva       |                |
| active_cities_id_list                            | mva       |                |
| active_shop_brands_id_list                       | mva       |                |
| all_shops_id_list                                | mva       |                |
| all_cities_id_list                               | mva       |                |
| all_shop_brands_id_list                          | mva       |                |
| videos                                           | json      |                |
| attended_foreign_agents                          | json      |                |
| synchronized_at                                  | timestamp |                |
| updated_at                                       | timestamp |                |
| price_retail_min                                 | float     |                |
| price_purchase                                   | float     |                |
| common_language_id_list                          | mva       |                |
| common_audience_id_list                          | mva       |                |
| book_sub_genre_id_list                           | mva       |                |
| book_publication_type_id_list                    | mva       |                |
| book_literature_work_country_id_list             | mva       |                |
+--------------------------------------------------+-----------+----------------+
181 rows in set (0,00 sec)

Количество:

mysql> select count(*) from products;
+----------+
| count(*) |
+----------+
|  2048293 |
+----------+

и

customer_products_bookmarks 
mysql> desc customer_products_bookmarks;
+-------------+-----------+------------+
| Field       | Type      | Properties |
+-------------+-----------+------------+
| id          | bigint    |            |
| customer_id | uint      |            |
| product_id  | uint      |            |
| created_at  | timestamp |            |
+-------------+-----------+------------+
4 rows in set (0,00 sec)

Количество:

mysql> select count(*) from customer_products_bookmarks;
+----------+
| count(*) |
+----------+
| 64298313 |

Запрос:

SELECT id
FROM products
	inner join customer_products_bookmarks on products.id = customer_products_bookmarks.product_id
LIMIT 10
option max_matches = 10

Проверка с profiling:

mysql> SET profiling=1;                                                                                                                                                                                                                                         
Query OK, 0 rows affected (0,00 sec)
mysql> SELECT id FROM products inner join customer_products_bookmarks on products.id = customer_products_bookmarks.product_id ORDER BY id DESC LIMIT 10 OPTION max_matches = 10;                                                                                              
+---------+
| id      |
+---------+
| 9502061 |
| 9502061 |
| 9502061 |
| 9502061 |
| 9502061 |
| 9502061 |
| 9502061 |
| 9502061 |
| 9502061 |
| 9502061 |
+---------+
10 rows in set, 1 warning (18,81 sec)
--- 10 out of 15271829 results in 18s 814ms ---

mysql> SHOW PROFILE;
+---------------+-----------+----------+---------+
| Status        | Duration  | Switches | Percent |
+---------------+-----------+----------+---------+
| unknown       | 0.000163  | 6        | 0.00    |
| local_search  | 0.003331  | 2        | 0.00    |
| sql_parse     | 0.000610  | 1        | 0.00    |
| setup_iter    | 0.000014  | 16       | 0.00    |
| fullscan      | 73.487250 | 17       | 86.87   |
| dict_setup    | 0.000003  | 1        | 0.00    |
| parse         | 0.000003  | 1        | 0.00    |
| init          | 0.000174  | 4        | 0.00    |
| finalize      | 11.099995 | 10       | 13.12   |
| clone_attrs   | 0.000194  | 1        | 0.00    |
| aggregate     | 0.002001  | 3        | 0.00    |
| net_write     | 0.000068  | 2        | 0.00    |
| eval_post     | 0.000036  | 1        | 0.00    |
| eval_getfield | 0.000001  | 1        | 0.00    |
| total         | 84.593843 | 66       | 0       |
+---------------+-----------+----------+---------+
15 rows in set (0,00 sec)

При выполнении только 1 запроса CPU по всем ядрам в 100%. Запрос в итоге выполнился, но цена высока. Несколько запросов и сервис ляжет.
И на основе результата в SHOW PROFILE видно высокое использование fullscan в 86.87% от общего результата.

Что можно с этим сделать?
Какого влияние join_batch_size? На уровне OPTION join_batch_size он не работает:

ERROR 1064 (42000): unknown option 'join_batch_size'

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

2

Попробуйте на более новой версии

3

Там 4 релиза разных: 7, 9, 10 и 13. По commit видел несколько в 7 касаемо JOIN.
На каком именно, сразу 13? Для понимая в плане оптимизаций.

4

конечно лучше всегда брать последний релиз

В changelog даже написано Changelog | Manticore Search Manual , что в 13 релизе было несколько фиксов крешей в joined table и добавлена поддержка join в distributed таблице состоящей из локальных таблиц

5

У нас RT на 1 сервере. Хорошо, попробуем.
Напишу как проверим.

6

Тест на новой версии 13.2.3

1 тест

mysql> SET profiling=1; 
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT id
    -> FROM products
    -> inner join customer_products_bookmarks on products.id = customer_products_bookmarks.product_id
    -> LIMIT 10
    -> option max_matches = 10
    -> ;
+---------+
| id      |
+---------+
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
+---------+
10 rows in set, 1 warning (1 min 3,61 sec)
--- 10 out of 64281841 results in 1m 3.6s ---

mysql> SHOW PROFILE;
+---------------+------------+----------+---------+
| Status        | Duration   | Switches | Percent |
+---------------+------------+----------+---------+
| unknown       | 0.000122   | 6        | 0.00    |
| local_search  | 0.001519   | 2        | 0.00    |
| sql_parse     | 0.000110   | 1        | 0.00    |
| setup_iter    | 0.000011   | 16       | 0.00    |
| fullscan      | 74.448242  | 17       | 54.12   |
| dict_setup    | 0.000003   | 1        | 0.00    |
| parse         | 0.000017   | 1        | 0.00    |
| init          | 0.000103   | 4        | 0.00    |
| finalize      | 63.104650  | 10       | 45.88   |
| clone_attrs   | 0.000163   | 1        | 0.00    |
| aggregate     | 0.000700   | 3        | 0.00    |
| net_write     | 0.000067   | 2        | 0.00    |
| eval_post     | 0.000025   | 1        | 0.00    |
| eval_getfield | 0.000001   | 1        | 0.00    |
| total         | 137.555733 | 66       | 0       |
+---------------+------------+----------+---------+
15 rows in set (0,00 sec)

2 тест

mysql> SELECT id
    -> FROM products
    -> inner join customer_products_bookmarks on products.id = customer_products_bookmarks.product_id
    -> LIMIT 10
    -> option max_matches = 10
    -> ;
+---------+
| id      |
+---------+
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
+---------+
10 rows in set, 1 warning (1 min 2,56 sec)
--- 10 out of 64281841 results in 1m 2.6s ---

mysql> SHOW PROFILE;
+---------------+------------+----------+---------+
| Status        | Duration   | Switches | Percent |
+---------------+------------+----------+---------+
| unknown       | 0.000120   | 6        | 0.00    |
| local_search  | 0.001264   | 2        | 0.00    |
| sql_parse     | 0.000077   | 1        | 0.00    |
| setup_iter    | 0.000011   | 16       | 0.00    |
| fullscan      | 69.949789  | 17       | 52.83   |
| dict_setup    | 0.000002   | 1        | 0.00    |
| parse         | 0.000014   | 1        | 0.00    |
| init          | 0.000107   | 4        | 0.00    |
| finalize      | 62.459371  | 10       | 47.17   |
| clone_attrs   | 0.000122   | 1        | 0.00    |
| aggregate     | 0.000583   | 3        | 0.00    |
| net_write     | 0.000069   | 2        | 0.00    |
| eval_post     | 0.000022   | 1        | 0.00    |
| eval_getfield | 0.000001   | 1        | 0.00    |
| total         | 132.411552 | 66       | 0       |
+---------------+------------+----------+---------+
15 rows in set (0,00 sec)

3 тест (без profiling)

mysql> SET profiling=0;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT id
    -> FROM products
    -> inner join customer_products_bookmarks on products.id = customer_products_bookmarks.product_id
    -> LIMIT 10
    -> option max_matches = 10
    -> ;
+---------+
| id      |
+---------+
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
| 7976815 |
+---------+
10 rows in set, 1 warning (1 min 2,22 sec)
--- 10 out of 64281841 results in 1m 2.2s ---

4 тест (с profiling и join_batch_size)

join_batch_size=2000

mysql> SELECT id FROM products inner join customer_products_bookmarks on products.id = customer_products_bookmarks.product_id LIMIT 10
option max_matches = 10, join_batch_size=2000;
+---------+
| id      |
+---------+
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
+---------+
10 rows in set, 1 warning (1 min 14,38 sec)
--- 10 out of 64281841 results in 1m 14.4s ---

join_batch_size=5000

mysql> SELECT id FROM products inner join customer_products_bookmarks on products.id = customer_products_bookmarks.product_id LIMIT 10
option max_matches = 10, join_batch_size=5000;
+---------+
| id      |
+---------+
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
+---------+
10 rows in set, 1 warning (1 min 12,85 sec)
--- 10 out of 64281841 results in 1m 12.8s ---

mysql> SHOW PROFILE;
+---------------+------------+----------+---------+
| Status        | Duration   | Switches | Percent |
+---------------+------------+----------+---------+
| unknown       | 0.000152   | 6        | 0.00    |
| local_search  | 0.022251   | 2        | 0.01    |
| sql_parse     | 0.000135   | 1        | 0.00    |
| setup_iter    | 0.000018   | 16       | 0.00    |
| fullscan      | 80.677418  | 17       | 52.66   |
| dict_setup    | 0.000002   | 1        | 0.00    |
| parse         | 0.000015   | 1        | 0.00    |
| init          | 0.000062   | 4        | 0.00    |
| finalize      | 72.514350  | 14       | 47.33   |
| clone_attrs   | 0.000398   | 1        | 0.00    |
| aggregate     | 0.000758   | 3        | 0.00    |
| net_write     | 0.000058   | 2        | 0.00    |
| eval_post     | 0.000027   | 1        | 0.00    |
| eval_getfield | 0.000001   | 1        | 0.00    |
| total         | 153.215645 | 70       | 0       |
+---------------+------------+----------+---------+
15 rows in set (0,00 sec)

join_batch_size=10000

mysql> SELECT id FROM products inner join customer_products_bookmarks on products.id = customer_products_bookmarks.product_id LIMIT 10
option max_matches = 10, join_batch_size=10000;
+---------+
| id      |
+---------+
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
| 8116789 |
+---------+
10 rows in set, 1 warning (1 min 7,67 sec)
--- 10 out of 64281841 results in 1m 7.7s ---
mysql> SHOW PROFILE;
+---------------+------------+----------+---------+
| Status        | Duration   | Switches | Percent |
+---------------+------------+----------+---------+
| unknown       | 0.000141   | 6        | 0.00    |
| local_search  | 0.011035   | 2        | 0.01    |
| sql_parse     | 0.000275   | 1        | 0.00    |
| setup_iter    | 0.000018   | 16       | 0.00    |
| fullscan      | 69.670937  | 17       | 50.63   |
| dict_setup    | 0.000002   | 1        | 0.00    |
| parse         | 0.000018   | 1        | 0.00    |
| init          | 0.000152   | 4        | 0.00    |
| finalize      | 67.914262  | 10       | 49.36   |
| clone_attrs   | 0.000538   | 1        | 0.00    |
| aggregate     | 0.000841   | 3        | 0.00    |
| net_write     | 0.000071   | 2        | 0.00    |
| eval_post     | 0.000027   | 1        | 0.00    |
| eval_getfield | 0.000001   | 1        | 0.00    |
| total         | 137.598318 | 66       | 0       |
+---------------+------------+----------+---------+
15 rows in set (0,00 sec)

Результат

Все CPU как на версии 6.3.6 не грузит, но при этом выполняется многократно дольше: 18 sec (6.3.6) VS 1 min 7 sec (13) +.
Также на 13 версии заметно используются 1 ядро на 100% при этом запросе, а опция join_batch_size не влияет на результат.

7

если вы хотите ограничить нагрузку от отдного запроса, то можете использовать QUERY опцию max_threads_per_query=1 Server settings > Searchd | Manticore Search Manual - например всем JOIN запросам ставить эту опцию.

Если же вы хотите разобраться в отличиях в перфе между версией 6.3.6 и 13 - то создайте тикет на Github где выложите воспроизводимый пример, файлы индексов которые можно загрузить в демон версии 6.3.6 и версии 13 и примеры запросов и вывод show profiling этих запросов которые вы видите на версиях 6.3.6 и 13, чтобы при воспроизведении локально мы могли убедится, что получаем аналогичные цифры перфоманса, как вы приводили в предыдущем посте.