Доброго времени суток!
Есть 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)
Статус:
mysql> show table customer_products status;
+-------------------------------+------------------------------------------------------------------------------------------------------------+
| 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":850, "avg_sec":0.335, "min_sec":0.081, "max_sec":21.815, "pct95_sec":0.699, "pct99_sec":4.551} |
| query_time_5min | {"queries":1458, "avg_sec":0.287, "min_sec":0.075, "max_sec":21.815, "pct95_sec":0.379, "pct99_sec":3.636} |
| query_time_15min | {"queries":1458, "avg_sec":0.287, "min_sec":0.075, "max_sec":21.815, "pct95_sec":0.379, "pct99_sec":3.636} |
| query_time_total | {"queries":1458, "avg_sec":0.287, "min_sec":0.075, "max_sec":21.815, "pct95_sec":0.309, "pct99_sec":4.521} |
| found_rows_1min | {"queries":850, "avg":0, "min":0, "max":0, "pct95":0, "pct99":0} |
| found_rows_5min | {"queries":1458, "avg":0, "min":0, "max":0, "pct95":0, "pct99":0} |
| found_rows_15min | {"queries":1458, "avg":0, "min":0, "max":0, "pct95":0, "pct99":0} |
| found_rows_total | {"queries":1458, "avg":0, "min":0, "max":0, "pct95":0, "pct99":0} |
| command_search | 1458 |
| command_excerpt | 0 |
| command_update | 0 |
| command_keywords | 0 |
| command_status | 12 |
| 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.335 0.287 0.287 |
| search_stats_ms_min | 0.081 0.075 0.075 |
| search_stats_ms_max | 21.815 21.815 21.815 |
| search_stats_ms_pct95 | 0.699 0.379 0.379 |
| search_stats_ms_pct99 | 4.551 3.636 3.636 |
| 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)
Этот индекс пустой и не содержит данных.
Схема индекса customer_bookmarks:
mysql> show create table customer_bookmarks\G
*************************** 1. row ***************************
Table: customer_bookmarks
Create Table: CREATE TABLE customer_bookmarks (
id bigint,
customer_id integer,
product_id integer,
action_id_list multi,
has_high_rating_review_from_other_customers bool,
added_at timestamp,
category_id_list multi,
availability_status integer,
released_at timestamp,
active_shops_id_list multi,
active_cities_id_list multi,
marketing_status_list multi,
rating_star integer,
author_id_list multi,
publisher_series_id integer,
binding_id integer,
literature_work_cycle_id integer,
price_retail float,
synchronized_at timestamp
) rt_mem_limit='2147483648'
1 row in set (0,00 sec)
Это большой индекс.
Статус:
mysql> show table customer_bookmarks status;
+-------------------------------+--------------------------------------------------------------------------+
| Variable_name | Value |
+-------------------------------+--------------------------------------------------------------------------+
| table_type | rt |
| indexed_documents | 73604480 |
| indexed_bytes | 0 |
| ram_bytes | 28478752487 |
| disk_bytes | 236362452158 |
| disk_mapped | 177070898348 |
| disk_mapped_cached | 27661987840 |
| disk_mapped_doclists | 0 |
| disk_mapped_cached_doclists | 0 |
| disk_mapped_hitlists | 0 |
| disk_mapped_cached_hitlists | 0 |
| killed_documents | 35734889 |
| killed_rate | 32.68% |
| ram_chunk | 815700815 |
| ram_chunk_segments_count | 36 |
| disk_chunks | 247 |
| mem_limit | 2147483648 |
| mem_limit_rate | 50.00% |
| ram_bytes_retired | 67978685 |
| optimizing | 0 |
| locked | 0 |
| tid | 0 |
| tid_saved | 0 |
| query_time_1min | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| query_time_5min | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| query_time_15min | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| query_time_total | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_1min | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_5min | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_15min | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_total | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| command_search | 0 |
| command_excerpt | 0 |
| command_update | 0 |
| command_keywords | 0 |
| command_status | 15 |
| command_delete | 70 |
| command_insert | 0 |
| command_replace | 155 |
| command_commit | 0 |
| command_suggest | 0 |
| command_callpq | 0 |
| command_getfield | 0 |
| insert_replace_stats_ms_avg | 136.031 117.961 117.961 |
| insert_replace_stats_ms_min | 0.336 0.175 0.175 |
| insert_replace_stats_ms_max | 852.835 1553.103 1553.103 |
| insert_replace_stats_ms_pct95 | 852.835 456.535 456.535 |
| insert_replace_stats_ms_pct99 | 852.835 1097.778 1097.778 |
| search_stats_ms_avg | N/A N/A N/A |
| search_stats_ms_min | N/A N/A N/A |
| search_stats_ms_max | N/A N/A N/A |
| search_stats_ms_pct95 | N/A N/A N/A |
| search_stats_ms_pct99 | N/A N/A N/A |
| 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 (2,86 sec)
Метрики:
Изначально по нему не было ограничений по optimize_cutoff и rt_mem_limit (по умолчанию 128MB).
Это привело к большому числу chunks - 900 и более.
Затем задал в конфигурации:
optimize_cutoff = 46
И на уровне схемы для индекса customer_bookmarks:
ALTER TABLE customer_bookmarks rt_mem_limit='1G';
После этого была запущена оптимизация и количество chunks пошло на спад:
Затем увеличил rt_mem_limit до 2G:
На графике это после 16:07.
Индекс довольно большой:
- 73604480 документов
- 236GB по статусу
show table customer_bookmarks status;и 275GB на диске фактически
ОЗУ 32Gb на сервере, но этого явно не хватает. А учитывая, что сервер еще не в prod - нет трафика, то когда будет трафик, то нагрузка вырастет и уже не потянет.
Отсюда вопросы:
- Какие есть рекомендации для оптимизации конфигурации индекса?
- Какие есть рекомендации для оптимизации конфигурации сервера - сколько памяти нужно выделить для корректной работы, более 128-256GB или более?
- Судя по Disc cached ratio для этого индекса весьма низкий, что также является проблемой.