Поиск в пустом RT-индексе грузит сервер и ведет к его падению

General Discussions - Russian
1

Доброго времени суток!
Есть Manticore версии:

13.6.7 1ab9ccc95@25080704 (columnar 8.0.1 fc30df6@25072219) (secondary 8.0.1 fc30df6@25072219)
(knn 8.0.1 fc30df6@25072219) (embeddings 1.0.0) git branch manticore-13.6.7...origin/manticore-13.6.7

Сервер:

Процессор
AMD EPYC 7443P (24x2.85 ГГц SMT)
Память
32 ГБ — 4 × 8 ГБ DDR4 ECC Reg
Диск
2 × 480 ГБ SSD SATA Enterprise
2 × 960 ГБ SSD SATA Enterprise

Конфигурация /etc/manticoresearch/manticore.conf:

# ansible_managment
common {
    plugin_dir = /usr/local/lib/manticore
}
searchd {
    listen = X.X.X.X:9312
    listen = X.X.X.X:9306:mysql
    listen = X.X.X.X:9308:http
    listen = X.X.X.X:9322-9325:replication
    log = /var/log/manticore/searchd.log
    query_log = /var/log/manticore/query.log
    pid_file = /var/run/manticore/searchd.pid
    data_dir = /raid/lib/manticore
    query_log_format = sphinxql
    query_log_min_msec = 500
    query_log_mode = 644
    node_address = 10.9.2.11
    max_batch_queries = 0
    max_packet_size = 128M
    thread_stack = 16M
    binlog_flush = 0
    binlog_max_log_size = 16M
    binlog_path =  # disable logging
    threads = 46
    optimize_cutoff = 46
    collation_server = utf8_ci
    network_timeout = 300s
    sphinxql_timeout = 30m
    client_timeout = 1h

    secondary_indexes = 1

    telemetry = 0
}

Есть RT-индексы:

mysql> show tables; 
+--------------------+------+ 
| Table              | Type | 
+--------------------+------+ 
| customer_bookmarks | rt   | 
| customer_products  | rt   | 
+--------------------+------+ 
2 rows in set (0,00 sec)

Схема индекса customer_products:

mysql> show create table customer_products\G
*************************** 1. row ***************************
       Table: customer_products
Create Table: CREATE TABLE customer_products (
id bigint,
customer_id integer,
product_id integer,
action_id_list multi,
rating_star integer,
review_id integer,
review_status integer,
last_action_performed_at timestamp,
category_id_list multi,
availability_status integer,
released_at timestamp,
active_shops_id_list multi,
active_cities_id_list multi,
author_id_list multi,
publisher_series_id integer,
literature_work_cycle_id integer,
synchronized_at timestamp
)
1 row in set (0,01 sec)

Статус:

+-------------------------------+------------------------------------------------------------------------------------------------------------+
| Variable_name                 | Value                                                                                                      |
+-------------------------------+------------------------------------------------------------------------------------------------------------+
| table_type                    | rt                                                                                                         |
| indexed_documents             | 0                                                                                                          |
| indexed_bytes                 | 0                                                                                                          |
| ram_bytes                     | 2720                                                                                                       |
| disk_bytes                    | 1688                                                                                                       |
| disk_mapped                   | 0                                                                                                          |
| disk_mapped_cached            | 0                                                                                                          |
| disk_mapped_doclists          | 0                                                                                                          |
| disk_mapped_cached_doclists   | 0                                                                                                          |
| disk_mapped_hitlists          | 0                                                                                                          |
| disk_mapped_cached_hitlists   | 0                                                                                                          |
| killed_documents              | 0                                                                                                          |
| killed_rate                   | 0.00%                                                                                                      |
| ram_chunk                     | 0                                                                                                          |
| ram_chunk_segments_count      | 0                                                                                                          |
| disk_chunks                   | 0                                                                                                          |
| mem_limit                     | 134217728                                                                                                  |
| mem_limit_rate                | 33.33%                                                                                                     |
| ram_bytes_retired             | 0                                                                                                          |
| optimizing                    | 0                                                                                                          |
| locked                        | 0                                                                                                          |
| tid                           | 0                                                                                                          |
| tid_saved                     | 0                                                                                                          |
| query_time_1min               | {"queries":563, "avg_sec":0.287, "min_sec":0.087, "max_sec":5.623, "pct95_sec":0.592, "pct99_sec":2.798}   |
| query_time_5min               | {"queries":1786, "avg_sec":0.328, "min_sec":0.082, "max_sec":33.026, "pct95_sec":0.611, "pct99_sec":3.149} |
| query_time_15min              | {"queries":1786, "avg_sec":0.328, "min_sec":0.082, "max_sec":33.026, "pct95_sec":0.611, "pct99_sec":3.149} |
| query_time_total              | {"queries":1786, "avg_sec":0.328, "min_sec":0.082, "max_sec":33.026, "pct95_sec":0.687, "pct99_sec":3.920} |
| found_rows_1min               | {"queries":563, "avg":0, "min":0, "max":0, "pct95":0, "pct99":0}                                           |
| found_rows_5min               | {"queries":1786, "avg":0, "min":0, "max":0, "pct95":0, "pct99":0}                                          |
| found_rows_15min              | {"queries":1786, "avg":0, "min":0, "max":0, "pct95":0, "pct99":0}                                          |
| found_rows_total              | {"queries":1786, "avg":0, "min":0, "max":0, "pct95":0, "pct99":0}                                          |
| command_search                | 1786                                                                                                       |
| command_excerpt               | 0                                                                                                          |
| command_update                | 0                                                                                                          |
| command_keywords              | 0                                                                                                          |
| command_status                | 19                                                                                                         |
| command_delete                | 0                                                                                                          |
| command_insert                | 0                                                                                                          |
| command_replace               | 0                                                                                                          |
| command_commit                | 0                                                                                                          |
| command_suggest               | 0                                                                                                          |
| command_callpq                | 0                                                                                                          |
| command_getfield              | 0                                                                                                          |
| insert_replace_stats_ms_avg   | N/A N/A N/A                                                                                                |
| insert_replace_stats_ms_min   | N/A N/A N/A                                                                                                |
| insert_replace_stats_ms_max   | N/A N/A N/A                                                                                                |
| insert_replace_stats_ms_pct95 | N/A N/A N/A                                                                                                |
| insert_replace_stats_ms_pct99 | N/A N/A N/A                                                                                                |
| search_stats_ms_avg           | 0.287 0.328 0.328                                                                                          |
| search_stats_ms_min           | 0.087 0.082 0.082                                                                                          |
| search_stats_ms_max           | 5.623 33.026 33.026                                                                                        |
| search_stats_ms_pct95         | 0.592 0.611 0.611                                                                                          |
| search_stats_ms_pct99         | 2.798 3.149 3.149                                                                                          |
| update_stats_ms_avg           | N/A N/A N/A                                                                                                |
| update_stats_ms_min           | N/A N/A N/A                                                                                                |
| update_stats_ms_max           | N/A N/A N/A                                                                                                |
| update_stats_ms_pct95         | N/A N/A N/A                                                                                                |
| update_stats_ms_pct99         | N/A N/A N/A                                                                                                |
+-------------------------------+------------------------------------------------------------------------------------------------------------+
58 rows in set (0,00 sec)

Этот индекс пустой и не содержит данных.

В момент были запросы:

2025-11-05 07:43:15.389	
/* Wed Nov 05 04:38:27.024 2025 conn 18353 (*:61488) real 39.934 wall 39.934 found 0 */ SELECT product_id,    customer_id,    review_id,    rating_star FROM customer_products WHERE product_id IN (2442484,2566263,2584602,2680182,2690512,2696640,2716108,2723561,2746222,2751727,2763368,2802275,2817274,2842938,2859694,2880509,2885707,2899806,2907757,2909389,2919102,2919619,2934527,2934547,2935437,2948091,2955914,2971145,2977733,3004147,3015785,3023949,3028359,3029055,3029575,3033842,3034696,3039357,3039862,3043379,3048599,3055238,3061284,3070720,3075271,3076453,3078155,3079067,3079099,3080108,3080232,3085270,3085942,3086012,3090675,3090679,3091899,3091900,3091904,3092297,3092332,3093599,3094727,3095386,3101463,3102290,3103312,3111363,3112650,3113927,3114083,3115380,3115993,3116942,3119012,3119900,3120393,3121636,3121680,3123030,3123862,3125304,3125544,3125778,3125779,3126806,3127329,3128722,3129350,3130142,3130433,3132736) AND customer_id!=7451565 AND rating_star>=4 AND review_id>0 GROUP BY product_id WITHIN GROUP ORDER BY rating_star DESC, last_action_performed_at DESC LIMIT 92 OPTION max_matches=92, comment='fetch products high rating reviews';
	2025-11-05 07:43:15.389	
/* Wed Nov 05 04:37:11.665 2025 conn 18354 (*:52471) real 39.934 wall 39.934 found 0 */ SELECT product_id,    customer_id,    review_id,    rating_star FROM customer_products WHERE product_id IN (2508045,2530400,2542369,2566733,2593415,2598901,2623657,2632069,2694332,2724676,2746036,2758361,2763816,2764753,2768043,2768821,2778604,2794982,2822933,2823714,2840889,2841970,2846002,2849401,2849402,2864691,2878138,2879253,2899313,2900306,2909089,2912378,2918346,2918844,2919043,2920391,2921457,2922824,2932011,2934814,2949547,2959771,2963911,2967230,2967771,2972931,2976816,2977073,2977574,2979888,2985544,2994058,2995075,3000187,3010517,3015975,3016131,3022532,3025396,3034446,3035679,3037074,3046503,3046837,3046851,3050490,3050810,3053605,3056255,3056263,3068527,3070540,3072861,3073985,3078557,3079909,3081127,3081990,3084861,3085892,3091103,3094442,3099223,3099279,3099484,3101567,3105481,3105825,3106956,3109807,3114539,3114722,3115176,3116304,3120031,3120620,3121664,3122253,3123876,3125354,3126794) AND customer_id!=22285519 AND rating_star>=4 AND review_id>0 GROUP BY product_id WITHIN GROUP ORDER BY rating_star DESC, last_action_performed_at DESC LIMIT 101 OPTION max_matches=101, comment='fetch products high rating reviews';
	2025-11-05 07:43:15.389	
/* Wed Nov 05 04:36:37.251 2025 conn 18343 (*:23866) real 33.534 wall 33.534 found 0 */ SELECT product_id,    customer_id,    review_id,    rating_star FROM customer_products WHERE product_id IN (2771417,2855356,2897939,2920705,2928307,2978841,2981758,2982180,3001443,3022852,3028011,3039261,3043250,3051206,3052861,3053018,3054265,3056343,3058599,3065650,3066440,3066752,3066912,3069312,3073989,3075807,3076485,3078163,3078696,3078700,3079161,3079376,3079915,3080012,3081293,3081612,3081615,3082995,3083566,3085877,3085882,3085922,3087017,3088463,3088480,3089358,3090658,3090948,3091249,3091906,3091952,3091960,3092513,3093414,3093640,3094343,3094517,3094833,3095286,3096061,3097298,3097730,3098024,3098183,3098185,3099429,3100326,3101665,3102186,3102567,3102705,3102710,3102957,3103369,3104631,3105239,3105267,3105275,3105525,3106413,3106415,3106469,3106565,3109416,3109796,3111270,3111271,3111433,3111438,3111440,3111441,3111442,3111496,3111559,3111561,3111938,3111939,3115441,3116078,3116327,3116333,3116358,3117048,3117234,3117772,3118740,3119901,3120037,3121562,3122196,3122301,3123569,3124779,3125110,3125680,3126332,3126442,3127751,3127763,3128166,3129038,3129158,3129642,3129649,3130600,3131474,3131726,3132357,3132387,3132719,3132774,3133186) AND customer_id!=21276185 AND rating_star>=4 AND review_id>0 GROUP BY product_id WITHIN GROUP ORDER BY rating_star DESC, last_action_performed_at DESC LIMIT 132 OPTION max_matches=132, comment='fetch products high rating reviews';
	2025-11-05 07:43:15.389	
/* Wed Nov 05 04:36:41.275 2025 conn 18352 (*:37737) real 39.450 wall 39.450 found 0 */ SELECT product_id,    customer_id,    review_id,    rating_star FROM customer_products WHERE product_id IN (2723882,2739675,2761461,2827367,2896570,2899997,2907252,2925101,2936636,2992616,2999780,3000442,3001244,3006911,3010975,3012356,3015899,3019173,3021886,3024651,3026130,3027574,3034239,3038777,3039158,3042098,3042818,3045840,3048378,3048548,3048839,3050471,3050512,3051796,3051814,3051824,3052827,3058263,3058530,3063087,3064832,3065242,3065999,3069140,3070238,3070509,3070633,3070756,3074643,3074812,3076425,3079161,3079376,3079868,3080102,3080152,3082523,3087301,3089419,3089656,3094264,3097710,3102664,3102705,3105488,3106272,3107073,3107275,3107405,3109183,3109184,3110495,3110917,3111276,3112699,3115388,3120274,3120990,3121211,3121638,3123383,3123941,3124971,3125406,3125425,3126211,3127343,3128197,3128586,3128588,3128595,3129038,3129177,3129649,3129828,3130118,3130534,3130829,3131167,3131420,3131499,3132095,3132387,3132773,3132774,3133394,3134405,3134417) AND customer_id!=7093306 AND rating_star>=4 AND review_id>0 GROUP BY product_id WITHIN GROUP ORDER BY rating_star DESC, last_action_performed_at DESC LIMIT 108 OPTION max_matches=108, comment='fetch products high rating reviews';
	2025-11-05 07:43:15.389	
/* Wed Nov 05 04:36:40.976 2025 conn 18351 (*:27850) real 42.916 wall 42.916 found 0 */ SELECT product_id,    customer_id,    review_id,    rating_star FROM customer_products WHERE product_id IN (2508243,2611909,2675254,2690545,2737049,2750936,2759758,2769963,2809123,2822007,2831980,2833523,2849709,2906279,2918762,2942036,2947291,2953257,2963986,2965332,2981507,3028275,3042223,3051191,3068955,3078252,3083041,3089334,3092387,3094217,3094307,3096699,3098869,3101653,3101719,3102115,3102220,3102492,3102664,3103541,3104902,3106274,3106948,3106996,3107315,3108217,3109891,3109903,3111307,3112817,3113320,3113321,3113322,3113618,3115171,3115858,3117145,3117875,3118402,3120134,3120800,3120975,3121251,3121638,3121691,3121731,3121734,3122099,3122181,3122317,3122419,3122571,3122702,3122876,3123158,3123614,3123615,3123708,3123941,3124090,3124781,3125235,3125303,3125353,3125426,3125715,3126190,3126802,3127354,3127375,3127390,3127805,3128594,3128940,3129266,3129828,3129971,3130243,3130378,3130829,3131419,3131420,3132382,3132384,3133360,3133489) AND customer_id!=20348074 AND rating_star>=4 AND review_id>0 GROUP BY product_id WITHIN GROUP ORDER BY rating_star DESC, last_action_performed_at DESC LIMIT 106 OPTION max_matches=106, comment='fetch products high rating reviews';
	2025-11-05 07:43:15.389	
/* Wed Nov 05 04:36:24.865 2025 conn 18345 (*:44320) real 27.347 wall 27.347 found 0 */ SELECT product_id,    customer_id,    review_id,    rating_star FROM customer_products WHERE product_id IN (2626382,2701253,2875388,2920027,2935847,2935866,2942295,2946124,2964778,2975130,2976257,2977084,2978965,2987430,2990644,2998191,2999751,3003597,3003600,3003601,3011212,3011796,3017913,3019103,3024676,3025143,3025798,3031748,3035265,3035939,3036604,3037564,3037954,3039635,3040344,3041686,3041984,3042224,3042357,3043084,3044070,3048133,3048134,3048137,3048526,3049781,3050730,3052723,3053319,3055583,3057349,3063835,3065454,3067150,3067356,3068525,3068621,3070330,3072755,3073489,3073604,3076064,3077896,3078121,3079009,3080013,3080021,3081379,3082634,3082635,3082979,3082999,3086841,3087981,3088801,3090880,3092399,3092948,3093211,3093339,3094514,3095286,3098090,3099222,3100869,3102210,3102214,3102217,3102218,3104266,3105268,3107894,3108824,3111481,3112742,3112817,3112820,3113950,3113956,3114053,3114675,3115248,3116355,3116386,3116388,3116392,3117264,3117873,3117896,3120154,3120155,3120159,3120370,3120379,3121636,3122316,3123708,3124781,3125076,3125403,3126362,3126400,3126778,3129752,3130044,3131079,3132715,3134102) AND customer_id!=7565892 AND rating_star>=4 AND review_id>0 GROUP BY product_id WITHIN GROUP ORDER BY rating_star DESC, last_action_performed_at DESC LIMIT 128 OPTION max_matches=128, comment='fetch products high rating reviews';
	2025-11-05 07:43:15.389	
/* Wed Nov 05 04:36:06.643 2025 conn 18346 (*:26182) real 19.793 wall 19.793 found 0 */ SELECT product_id,    customer_id,    review_id,    rating_star FROM customer_products WHERE product_id IN (2675746,2766100,2777992,2777994,2806799,2922407,2922845,2942484,2967892,2977312,2993900,3006716,3010312,3022464,3027672,3031746,3038496,3046803,3048134,3049623,3055520,3057885,3058917,3058923,3061547,3063753,3065529,3068621,3068781,3070070,3070330,3070783,3073651,3075706,3076110,3077806,3077977,3078038,3079373,3081197,3081280,3081456,3081457,3082218,3082325,3082336,3083271,3083274,3085998,3086714,3089649,3089832,3091648,3091760,3091898,3092117,3092399,3093416,3094649,3094727,3095442,3096365,3097202,3098411,3098454,3101496,3101753,3102492,3102551,3105064,3105511,3106408,3107335,3107627,3107824,3107894,3108638,3108849,3109243,3109244,3109512,3110510,3111233,3111363,3112650,3112933,3113231,3113618,3113923,3114771,3115005,3115006,3115967,3116335,3116350,3117258,3117773,3117867,3118752,3119113,3119931,3119933,3120107,3120295,3121251,3121315,3122104,3123606,3124221,3124802,3125095,3125107,3125302,3125304,3125403,3125543,3125544,3126649,3126847,3129266,3129349,3129350,3129752,3129999,3130142,3130361,3133840) AND customer_id!=23375408 AND rating_star>=4 AND review_id>0 GROUP BY product_id WITHIN GROUP ORDER BY rating_star DESC, last_action_performed_at DESC LIMIT 127 OPTION max_matches=127, comment='fetch products high rating reviews';
	2025-11-05 07:43:15.389	
/* Wed Nov 05 04:35:59.463 2025 conn 18349 (*:6613) real 19.793 wall 19.793 found 0 */ SELECT product_id,    customer_id,    review_id,    rating_star FROM customer_products WHERE product_id IN (2549046,2632013,2642148,2745445,2762815,2784398,2801934,2808629,2815602,2815619,2823246,2823978,2837012,2856967,2870883,2875776,2881321,2892845,2898425,2902480,2903687,2906699,2907337,2911160,2911162,2911166,2918707,2921101,2922109,2922459,2922463,2923018,2942152,2942192,2946386,2954373,2963845,2964283,2964798,2965724,2968006,2968817,2973842,2977816,2979227,2981561,2982905,2982909,2983202,2984214,2986949,2987534,2989577,2989578,3001596,3008433,3013317,3013381,3018143,3022271,3024684,3025544,3026447,3028164,3028231,3029411,3029426,3033440,3034454,3034486,3035341,3036550,3040273,3041276,3042501,3047566,3048466,3050494,3053631,3055509,3058077,3063193,3063577,3065153,3065570,3067564,3067565,3068524,3070030,3070752,3073084,3077347,3078231,3079454,3079499,3081978,3083088,3084671,3085889,3088113,3089625,3092296,3092306,3092537,3092541,3092639,3092810,3093828,3093834,3093836,3098397,3098880,3098882,3101597,3106896,3106951,3108587,3114071,3114271,3118279,3126302,3130474) AND customer_id!=20000391 AND rating_star>=4 AND review_id>0 GROUP BY product_id WITHIN GROUP ORDER BY rating_star DESC, last_action_performed_at DESC LIMIT 122 OPTION max_matches=122, comment='fetch products high rating reviews';

Это часть запросов из лога.
Вот что по метрикам:

image
image3320×1757 469 KB

image
image3320×1340 327 KB

image
image3320×1750 247 KB

И метрики по индексам:

image
image3320×1390 279 KB

image
image3320×965 111 KB

image
image3320×1343 260 KB

И от Zabbix:
image
image3320×1208 304 KB

image
image1920×867 77.6 KB

image
image1920×867 76 KB

Это грузит CPU, диск и ведет к oom-kill и перезапуску сервиса Manticore.

В чем кроется проблема и как ее решить? Учитывая, что сервер еще не в prod и не нагружен в полной мере.

1 Like
2

Еще немного логов:

2025-11-06 14:31:47.876	
[Thu Nov  6 11:31:47.693 2025] [3148307] accepting connections
	2025-11-06 14:31:47.876	
[Thu Nov  6 11:31:47.630 2025] [3148317] prereading 2 tables
	2025-11-06 14:31:45.371	
[Thu Nov  6 11:31:45.205 2025] [3148307] listening on X.X.X.X:9308 for sphinx and http(s)
	2025-11-06 14:31:45.371	
[Thu Nov  6 11:31:45.205 2025] [3148307] listening on X.X.X.X:9306 for mysql
	2025-11-06 14:31:45.371	
[Thu Nov  6 11:31:45.205 2025] [3148307] listening on X.X.X.X:9312 for sphinx and http(s)
	2025-11-06 14:31:45.371	
[Thu Nov  6 11:31:45.205 2025] [3148307] starting daemon version '13.6.7 1ab9ccc95@25080704 (columnar 8.0.1 fc30df6@25072219) (secondary 8.0.1 fc30df6@25072219) (knn 8.0.1 fc30df6@25072219) (embeddings 1.0.0)' ...
	2025-11-06 14:31:45.370	
[Thu Nov  6 11:31:45.203 2025] [3148307] Using local time zone 'UTC'
	2025-11-06 14:31:45.370	
[Thu Nov  6 11:31:45.198 2025] [3148306] watchdog: main process 3148307 forked ok
	2025-11-06 14:31:44.622	
[Thu Nov  6 11:31:44.580 2025] [3124969] watchdog: main process 3124970 killed cleanly with SIGKILL, shutting down
	2025-11-06 14:31:23.288	
[Thu Nov  6 11:31:23.144 2025] [3124986] rt: table customer_bookmarks: diskchunk 95495(108), segments 25  saved in 29.711965 (29.712907) sec, RAM saved/new 341740884/248730632 ratio 0.333333 (soft limit 715827166, conf limit 2147483648)
	2025-11-06 14:30:58.243	
[Thu Nov  6 11:30:58.120 2025] [3125014] rt: table customer_bookmarks: merged chunks /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95490 and /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95491 to /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95496 in 1m (progressive mode). Remaining chunk count: 107
	2025-11-06 14:30:41.957	
[Thu Nov  6 11:30:41.818 2025] [3124983] rt: table customer_bookmarks: diskchunk 95494(108), segments 25  saved in 30.996277 (31.000273) sec, RAM saved/new 339841251/103817544 ratio 0.333333 (soft limit 715827166, conf limit 2147483648)
	2025-11-06 14:30:10.644	
[Thu Nov  6 11:30:10.607 2025] [3125010] rt: table customer_bookmarks: diskchunk 95492(107), segments 30  saved in 31.965528 (31.969414) sec, RAM saved/new 341669766/305862019 ratio 0.333333 (soft limit 715827166, conf limit 2147483648)
	2025-11-06 14:29:55.611	
[Thu Nov  6 11:29:55.386 2025] [3124988] rt: table customer_bookmarks: merged chunks /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95467 and /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95488 to /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95493 in 1m (progressive mode). Remaining chunk count: 106
	2025-11-06 14:29:38.824	
[Thu Nov  6 11:29:38.616 2025] [3124994] rt: table customer_bookmarks: diskchunk 95491(107), segments 24  saved in 29.760118 (29.760824) sec, RAM saved/new 332840314/341673074 ratio 0.333333 (soft limit 715827166, conf limit 2147483648)
	2025-11-06 14:29:06.753	
[Thu Nov  6 11:29:06.596 2025] [3125009] rt: table customer_bookmarks: diskchunk 95490(106), segments 27  saved in 28.896382 (28.900213) sec, RAM saved/new 327058651/264876696 ratio 0.333333 (soft limit 715827166, conf limit 2147483648)
	2025-11-06 14:28:27.657	
[Thu Nov  6 11:28:27.633 2025] [3124988] rt: table customer_bookmarks: merged chunks /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95484 and /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95486 to /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95489 in 2m (progressive mode). Remaining chunk count: 105
	2025-11-06 14:28:06.107	
[Thu Nov  6 11:28:06.002 2025] [3124978] rt: table customer_bookmarks: diskchunk 95488(106), segments 28  saved in 92.331902 (92.334137) sec, RAM saved/new 313615653/88672021 ratio 0.333333 (soft limit 715827166, conf limit 2147483648)
	2025-11-06 14:26:21.942	
[Thu Nov  6 11:26:21.761 2025] [3125015] rt: table customer_bookmarks: merged chunks /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95481 and /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95483 to /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95487 in 22m (progressive mode). Remaining chunk count: 105
	2025-11-06 14:25:58.636	
[Thu Nov  6 11:25:58.594 2025] [3124994] rt: table customer_bookmarks: diskchunk 95486(106), segments 24  saved in 1293.805949 (1293.813244) sec, RAM saved/new 318491497/143896656 ratio 0.333333 (soft limit 715827166, conf limit 2147483648)
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:25:53.862 2025] [3124981] [BUDDY] [2025-11-06 11:25:10 *3125036.3]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:25:53.859 2025] [3124981] [BUDDY] [2025-11-06 11:25:10 *3125038.5]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:22:42 *3125034.1]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:22:42 *3125035.2]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:22:42 *3125037.4]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:22:42 *3125040.7]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:22:29 *3125035.2]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:22:29 *3125042.9]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:22:18 *3125034.1]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:22:18 *3125042.9]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:22:18 *3125037.4]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:22:18 *3125043.10]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:22:00 *3125043.10]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:25:55.460	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:22:00 *3125042.9]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:24:25.093	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:22:00 *3125034.1]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:24:25.066	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:21:41 *3125043.10]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:24:25.012	
[Thu Nov  6 11:24:24.826 2025] [3124989] [BUDDY] [2025-11-06 11:21:41 *3125042.9]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:24:25.002	
[Thu Nov  6 11:24:24.807 2025] [3124989] [BUDDY] [2025-11-06 11:21:41 *3125035.2]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:12:21.052	
[Thu Nov  6 11:12:20.503 2025] [3125014] [BUDDY] [2025-11-06 11:11:58 *3125041.8]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:12:20.964	
[Thu Nov  6 11:12:20.503 2025] [3125014] [BUDDY] [2025-11-06 11:11:58 *3125035.2]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:12:20.948	
[Thu Nov  6 11:12:20.503 2025] [3125014] [BUDDY] [2025-11-06 11:11:58 *3125043.10]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:12:20.935	
[Thu Nov  6 11:12:20.500 2025] [3125014] [BUDDY] [2025-11-06 11:11:58 *3125037.4]	WARNING	TableRow::lock(): timeout, force unlock
	2025-11-06 14:04:15.027	
[Thu Nov  6 11:04:14.916 2025] [3125010] rt: table customer_bookmarks: diskchunk 95484(105), segments 32  saved in 27.567190 (27.571982) sec, RAM saved/new 313788805/182521662 ratio 0.333333 (soft limit 715827166, conf limit 2147483648)
	2025-11-06 14:03:58.486	
[Thu Nov  6 11:03:58.464 2025] [3125008] rt: table customer_bookmarks: merged chunks /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95479 and /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95480 to /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95485 in 1m (progressive mode). Remaining chunk count: 104
	2025-11-06 14:03:45.209	
[Thu Nov  6 11:03:45.176 2025] [3124978] rt: table customer_bookmarks: diskchunk 95483(105), segments 32  saved in 32.196539 (32.197085) sec, RAM saved/new 351136860/313788805 ratio 0.333333 (soft limit 715827166, conf limit 2147483648)
	2025-11-06 14:03:12.148	
[Thu Nov  6 11:03:12.101 2025] [3125016] rt: table customer_bookmarks: diskchunk 95481(104), segments 24  saved in 29.828269 (29.833145) sec, RAM saved/new 331864262/351136860 ratio 0.333333 (soft limit 715827166, conf limit 2147483648)
	2025-11-06 14:02:46.344	
[Thu Nov  6 11:02:46.104 2025] [3125011] rt: table customer_bookmarks: merged chunks /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95475 and /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95477 to /raid/lib/manticore/customer_bookmarks/customer_bookmarks.95482 in 1m (progressive mode). Remaining chunk count: 103
	2025-11-06 14:02:29.807	
[Thu Nov  6 11:02:29.678 2025] [3124999] rt: table customer_bookmarks: diskchunk 95480(104), segments 24  saved in 30.113370 (30.115337) sec, RAM saved/new 329486010/206278878 ratio 0.333333 (soft limit 715827166, conf limit 2147483648)
	2025-11-06 14:01:56.734	
[Thu Nov  6 11:01:56.566 2025] [3124990] rt: table customer_bookmarks: diskchunk 95479(103), segments 25  saved in 31.606398 (31.607240) sec, RAM saved/new 343699116/261506410 ratio 0.333333 (soft limit 715827166, conf limit 2147483648)

В это время грузит CPU, диск и память - OOM.

3

Интересный кейс. Попробую повторить локально. Все запросы типа такого или есть другие?

SELECT product_id,    customer_id,    review_id,    rating_star FROM customer_products WHERE product_id IN (2442484,2566263,2584602,2680182,2690512,2696640,2716108,2723561,2746222,2751727,2763368,2802275,2817274,2842938,2859694,2880509,2885707,2899806,2907757,2909389,2919102,2919619,2934527,2934547,2935437,2948091,2955914,2971145,2977733,3004147,3015785,3023949,3028359,3029055,3029575,3033842,3034696,3039357,3039862,3043379,3048599,3055238,3061284,3070720,3075271,3076453,3078155,3079067,3079099,3080108,3080232,3085270,3085942,3086012,3090675,3090679,3091899,3091900,3091904,3092297,3092332,3093599,3094727,3095386,3101463,3102290,3103312,3111363,3112650,3113927,3114083,3115380,3115993,3116942,3119012,3119900,3120393,3121636,3121680,3123030,3123862,3125304,3125544,3125778,3125779,3126806,3127329,3128722,3129350,3130142,3130433,3132736) AND customer_id!=7451565 AND rating_star>=4 AND review_id>0 GROUP BY product_id WITHIN GROUP ORDER BY rating_star DESC, last_action_performed_at DESC LIMIT 92 OPTION max_matches=92, comment='fetch products high rating reviews';
4

Не могу повторить вот так:

 ~  cat 3058.conf
searchd {
    listen = 127.0.0.1:9312
    listen = 127.0.0.1:9306:mysql
    listen = 127.0.0.1:9308:http
    log = /opt/homebrew/var/log/manticore/searchd.log
    query_log = /opt/homebrew/var/log/manticore/query.log
    pid_file = /opt/homebrew/var/run/manticore/searchd.pid
    data_dir = /opt/homebrew/var/manticore

    query_log_format = sphinxql
    query_log_min_msec = 500
    query_log_mode = 644
    max_batch_queries = 0
    max_packet_size = 128M
    thread_stack = 16M
    binlog_flush = 0
    binlog_max_log_size = 16M
    binlog_path =  # disable logging
    threads = 46
    optimize_cutoff = 46
    collation_server = utf8_ci
    network_timeout = 300s
    sphinxql_timeout = 30m
    client_timeout = 1h

    secondary_indexes = 1

    telemetry = 0
}

mysql -P9306 -h0 -e "drop table if exists customer_products; CREATE TABLE customer_products ( id bigint, customer_id integer, product_id integer, action_id_list multi, rating_star integer, review_id integer, review_status integer, last_action_performed_at timestamp, category_id_list multi, availability_status integer, released_at timestamp, active_shops_id_list multi, active_cities_id_list multi, author_id_list multi, publisher_series_id integer, literature_work_cycle_id integer, synchronized_at timestamp ) "


manticore-load \
--threads=5 \
--total=100000 \
--load="SELECT product_id,    customer_id,    review_id,    rating_star FROM customer_products WHERE product_id IN (2442484,2566263,2584602,2680182,2690512,2696640,2716108,2723561,2746222,2751727,2763368,2802275,2817274,2842938,2859694,2880509,2885707,2899806,2907757,2909389,2919102,2919619,2934527,2934547,2935437,2948091,2955914,2971145,2977733,3004147,3015785,3023949,3028359,3029055,3029575,3033842,3034696,3039357,3039862,3043379,3048599,3055238,3061284,3070720,3075271,3076453,3078155,3079067,3079099,3080108,3080232,3085270,3085942,3086012,3090675,3090679,3091899,3091900,3091904,3092297,3092332,3093599,3094727,3095386,3101463,3102290,3103312,3111363,3112650,3113927,3114083,3115380,3115993,3116942,3119012,3119900,3120393,3121636,3121680,3123030,3123862,3125304,3125544,3125778,3125779,3126806,3127329,3128722,3129350,3130142,3130433,3132736) AND customer_id!=7451565 AND rating_star>=4 AND review_id>0 GROUP BY product_id WITHIN GROUP ORDER BY rating_star DESC, last_action_performed_at DESC LIMIT 92 OPTION max_matches=92, comment='fetch products high rating reviews'"
======================================================================================
Process 1: Running with threads=5
Process 1: Using cached data from: /tmp/manticore_load_45436109c3e46f94c42029349f2728c3
-------------------------------------------------------------------------------------------------------------
Time      Elapsed   Progress    QPS      DPS      CPU     Workers    Chunks    Merging   Disk        Inserted
-------------------------------------------------------------------------------------------------------------
14:35:34  1ms       -           -        -        N/A     -          0                   0B          0
14:35:35  1.0s      17%         16940    -        N/A     2          0                   0B          0
14:35:36  2.0s      34%         17085    -        N/A     3          0                   0B          0
14:35:37  3.0s      50%         15768    -        N/A     2          0                   0B          0
14:35:38  4.0s      67%         16763    -        N/A     2          0                   0B          0
14:35:39  5.0s      85%         18106    -        N/A     2          0                   0B          0
--------------------------------------------------------------------------------------
Process 1 final statistics:
Load command:     SELECT product_id,    customer_id,    review_id,    rating_star FROM customer_products WHERE product_id IN (2442484,2566263,2584602,2680182,2690512,2696640,2716108,2723561,2746222,2751727,2763368,2802275,2817274,2842938,2859694,2880509,2885707,2899806,2907757,2909389,2919102,2919619,2934527,2934547,2935437,2948091,2955914,2971145,2977733,3004147,3015785,3023949,3028359,3029055,3029575,3033842,3034696,3039357,3039862,3043379,3048599,3055238,3061284,3070720,3075271,3076453,3078155,3079067,3079099,3080108,3080232,3085270,3085942,3086012,3090675,3090679,3091899,3091900,3091904,3092297,3092332,3093599,3094727,3095386,3101463,3102290,3103312,3111363,3112650,3113927,3114083,3115380,3115993,3116942,3119012,3119900,3120393,3121636,3121680,3123030,3123862,3125304,3125544,3125778,3125779,3126806,3127329,3128722,3129350,3130142,3130433,3132736) AND customer_id!=7451565 AND rating_star>=4 AND review_id>0 GROUP BY product_id WITHIN GROUP ORDER BY rating_star DESC, last_action_performed_at DESC LIMIT 92 OPTION max_matches=92, comment='fetch products high rating reviews'
Total time:       5.9s
Total queries:    100000
Threads:          5
Batch size:       1
QPS avg:          16932
QPS 95p:          18106
QPS 99p:          18106
Latency avg:      0.3 ms
Latency 50p:      0.5 ms
Latency 95p:      0.5 ms
Latency 99p:      0.5 ms
--------------------------------------------------------------------------------------

Можете у себя попробовать аналогично?

5

Попробуем проверить.

Вчера выкатили правку, где отключили эти запросы в индекс customer_products.
По метрикам проблемы теперь нет.
Было так:

image
image3307×1280 260 KB

image
image3340×920 229 KB

И по индексам:

image
image3311×1335 288 KB

После 19:43 зашла правка, запросы прекратились.
По нагрузке запросов метрика Queries in 1m.

Потому не понятно, как на пустом индексе запросы вызывают нагрузку?

6

Результат теста:

manticore-load --threads=5 --total=100000 --load="SELECT product_id,    customer_id,    review_id,    rating_star FROM customer_products WHERE product_id IN (2442484,2566263,2584602,2680182,2690512,2696640,2716108,2723561,2746222,2751727,2763368,2802275,2817274,2842938,2859694,2880509,2885707,2899806,2907757,2909389,2919102,2919619,2934527,2934547,2935437,2948091,2955914,2971145,2977733,3004147,3015785,3023949,3028359,3029055,3029575,3033842,3034696,3039357,3039862,3043379,3048599,3055238,3061284,3070720,3075271,3076453,3078155,3079067,3079099,3080108,3080232,3085270,3085942,3086012,3090675,3090679,3091899,3091900,3091904,3092297,3092332,3093599,3094727,3095386,3101463,3102290,3103312,3111363,3112650,3113927,3114083,3115380,3115993,3116942,3119012,3119900,3120393,3121636,3121680,3123030,3123862,3125304,3125544,3125778,3125779,3126806,3127329,3128722,3129350,3130142,3130433,3132736) AND customer_id!=7451565 AND rating_star>=4 AND review_id>0 GROUP BY product_id WITHIN GROUP ORDER BY rating_star DESC, last_action_performed_at DESC LIMIT 92 OPTION max_matches=92, comment='fetch products high rating reviews'"
======================================================================================
Process 1: Running with threads=5
Process 1: Generating new data cache /tmp/manticore_load_45436109c3e46f94c42029349f2728c3 ... 100%
-------------------------------------------------------------------------------------------------------------
Time      Elapsed   Progress    QPS      DPS      CPU     Workers    Chunks    Merging   Disk        Inserted
-------------------------------------------------------------------------------------------------------------
12:26:38  2ms       -           -        -        N/A     -          0                   0B          0       
12:26:39  1.0s      9%          9347     -        N/A     6          0                   0B          0       
12:26:40  2.0s      17%         7324     -        18%     9          0                   0B          0       
12:26:41  3.0s      26%         9504     -        18%     7          0                   0B          0       
12:26:42  4.0s      38%         11462    -        17%     5          0                   0B          0       
12:26:43  5.0s      46%         8494     -        16%     5          0                   0B          0       
12:26:44  6.1s      53%         6686     -        13%     5          0                   0B          0       
12:26:45  7.1s      59%         6238     -        12%     5          0                   0B          0       
12:26:46  8.1s      66%         7355     -        12%     8          0                   0B          0       
12:26:47  9.1s      74%         7081     -        12%     6          0                   0B          0       
12:26:49  10.1s     80%         6351     -        14%     5          0                   0B          0       
12:26:50  11.3s     86%         6282     -        13%     7          0                   0B          0       
12:26:51  12.3s     93%         7285     -        15%     7          0                   0B          0       
--------------------------------------------------------------------------------------
Process 1 final statistics:
Load command:     SELECT product_id,    customer_id,    review_id,    rating_star FROM customer_products WHERE product_id IN (2442484,2566263,2584602,2680182,2690512,2696640,2716108,2723561,2746222,2751727,2763368,2802275,2817274,2842938,2859694,2880509,2885707,2899806,2907757,2909389,2919102,2919619,2934527,2934547,2935437,2948091,2955914,2971145,2977733,3004147,3015785,3023949,3028359,3029055,3029575,3033842,3034696,3039357,3039862,3043379,3048599,3055238,3061284,3070720,3075271,3076453,3078155,3079067,3079099,3080108,3080232,3085270,3085942,3086012,3090675,3090679,3091899,3091900,3091904,3092297,3092332,3093599,3094727,3095386,3101463,3102290,3103312,3111363,3112650,3113927,3114083,3115380,3115993,3116942,3119012,3119900,3120393,3121636,3121680,3123030,3123862,3125304,3125544,3125778,3125779,3126806,3127329,3128722,3129350,3130142,3130433,3132736) AND customer_id!=7451565 AND rating_star>=4 AND review_id>0 GROUP BY product_id WITHIN GROUP ORDER BY rating_star DESC, last_action_performed_at DESC LIMIT 92 OPTION max_matches=92, comment='fetch products high rating reviews'
Total time:       12.8s
Total queries:    100000
Threads:          5
Batch size:       1
QPS avg:          7784
QPS 95p:          11462
QPS 99p:          11462
Latency avg:      0.6 ms
Latency 50p:      0.5 ms
Latency 95p:      1.5 ms
Latency 99p:      1.5 ms
--------------------------------------------------------------------------------------

Но этот тест быстрый с повторением 1 запроса.
По метрикам нет каких-либо всплесков.
У меня еще подготовлены все запросы, что были в момент, но уникальные с разными выборками, опциями.
Попробую еще своим скриптом прогнать более продолжительный тест.

7

Больше не актуально, перешли на другую схему.