Hi there.
I’m in the middle of migrating my project from sphinxsearch to manticore and have noticed slower query execution for fast queries (about x3 slower).
I dug into it a bit and found out that init state in profiling table has much higher duration in manticore 0.006 vs 0.0002 and switches count is also higher 260 vs 2. I can assume that switches count correlates with table chunks count.
It’s worth mentioning that there are different index types in manticore (rt) and sphinx (plain) and manticore index files take twice more disk space then sphinx’s because of extra store column and different ngram_chars and charset_table settings.
So the question is what happens in this init stage and whether I can reduce this stage duration or not?
Here is sample query:
SELECT *, WEIGHT() as w FROM post
WHERE MATCH('(@(tags,text,user_name) "sallyfox"/1|"sally fox"/2)') AND created>1705915641
GROUP BY user_id
ORDER BY likes DESC
LIMIT 3;
Manticore profiling:
+--------------+----------+----------+---------+
| Status | Duration | Switches | Percent |
+--------------+----------+----------+---------+
| unknown | 0.000218 | 6 | 2.28 |
| local_search | 0.000407 | 2 | 4.26 |
| sql_parse | 0.000064 | 1 | 0.67 |
| dict_setup | 0.000062 | 129 | 0.65 |
| parse | 0.001591 | 129 | 16.65 |
| transforms | 0.000269 | 129 | 2.82 |
| init | 0.006451 | 260 | 67.51 |
| init_segment | 0.000123 | 23 | 1.29 |
| get_docs | 0.000007 | 26 | 0.07 |
| filter | 0.000002 | 3 | 0.02 |
| rank | 0.000010 | 49 | 0.10 |
| finalize | 0.000006 | 1 | 0.06 |
| clone_attrs | 0.000054 | 1 | 0.57 |
| aggregate | 0.000253 | 1 | 2.65 |
| net_write | 0.000038 | 2 | 0.40 |
| total | 0.009555 | 762 | 0 |
+--------------+----------+----------+---------+
Sphinx profiling:
+--------------+----------+----------+---------+
| Status | Duration | Switches | Percent |
+--------------+----------+----------+---------+
| unknown | 0.000068 | 4 | 11.09 |
| local_search | 0.000269 | 1 | 43.88 |
| sql_parse | 0.000044 | 1 | 7.18 |
| dict_setup | 0.000000 | 1 | 0.00 |
| parse | 0.000045 | 1 | 7.34 |
| transforms | 0.000001 | 1 | 0.16 |
| init | 0.000184 | 2 | 30.02 |
| aggregate | 0.000002 | 1 | 0.33 |
| total | 0.000613 | 12 | 0 |
+--------------+----------+----------+---------+
Manticore index:
CREATE TABLE IF NOT EXISTS post (
id bigint,
host_id multi,
post_id int,
user_id int,
likes int,
type int,
created timestamp,
user_name text,
user_slug text,
`text` text,
tags text,
tags_es text,
data string stored
)
index_exact_words='1'
charset_table='non_cjk'
min_word_len='2'
morphology='stem_enru'
ngram_len='1'
ngram_chars='cjk';
Manticore variables:
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| autocommit | 1 |
| auto_optimize | 1 |
| optimize_cutoff | 128 |
| collation_connection | libc_ci |
| query_log_format | sphinxql |
| session_read_only | 0 |
| log_level | info |
| max_allowed_packet | 134217728 |
| character_set_client | utf8 |
| character_set_connection | utf8 |
| grouping_in_utc | 0 |
| last_insert_id | |
| pseudo_sharding | 1 |
| secondary_indexes | 1 |
| accurate_aggregation | 0 |
| distinct_precision_threshold | 3500 |
| threads_ex_effective | |
| thread_stack | 1048576 |
| threads_ex | |
+------------------------------+-----------+