Performans issue

[yigits]

Hello,
I used sphinx search since 2014. A long time I didn’t update to 3 version and I used 2.2.11 version.
After that I use manticore, I think manticore has more performance than sphinx, for standart queries.

There is an issue with the performance. I have some PLAIN tables over 3M row. Sometimes I need to use OPTION max_mathes=3000000 to include all data to the query.

When I tried this on

• sphinx search (2.2.11) responses 3-4 seconds.
• sphinx search (3.6.1) not responding. (conf changed to sort_mem=512M)
• manticore search (6.2.12) responses 7-8 seconds.
  (All tests were performed with the same data and in the same environment. (cpu/ram/os))

When I tried only
Select id FROM table OPTION max_mathes=3000000;

The manticore performance nearly same with sphinx search 2.2.11.

When I added new columns or use *,
Manticore performance lags behind sphinx search 2.2.11

There is no error log
I am not sure where is the problem.

Is there any performance optimization for this situation?

[Sergey]

Hi. What’s the table schema, the query which is slower in Manticore than in Sphinx and a sample document?

[yigits]

Table Schema

sql_field_string = type
sql_field_string = hash_id
sql_field_string = name
sql_field_string = name_en
sql_field_string = user_string_id
sql_field_string = user_hash_id
sql_field_string = short_type
sql_field_string = short_method
sql_field_string = process
sql_field_string = category
sql_field_string = cities
sql_field_string = details
sql_field_string = country_name
sql_field_string = region_name
sql_field_string = level_one_category
sql_field_string = level_two_category
sql_field_string = level_three_category
sql_field_string = detail_related
sql_attr_uint = id
sql_attr_uint = type_id
sql_attr_uint = method_id
sql_attr_uint = country_id
sql_attr_uint = status
sql_attr_uint = city_id
sql_attr_uint = district_id
sql_attr_uint = price
sql_attr_uint = level_one_category_id
sql_attr_uint = level_two_category_id
sql_attr_uint = level_three_category_id
sql_attr_uint = detail_type_id
sql_attr_uint = detail_doc_id
sql_attr_uint = detail_duration
sql_attr_uint = detail_multipliyer
sql_attr_uint = admin_id
sql_attr_uint = source_id
sql_attr_uint = category_status
sql_attr_uint = is_suggested
sql_attr_uint = is_online
sql_attr_timestamp = action_time
sql_attr_timestamp = added_time
sql_attr_timestamp = updated_time
sql_attr_timestamp = last_action_time

Manticore (6.2.12)) Server Dedicated (12 thread/64 Gb Ram/512 Gb NVme)
Sphinx (2.2.11) Server Cloud (8 thread/16 Gb Ram/240 Gb NVme)

Query 1

select * FROM archive where action_time <= 1697489999 order by action_time desc LIMIT 1000, 20 OPTION max_matches=3000000;

Manticore:
20 rows in set (7.586 sec)

Sphinx
20 rows in set (5.076 sec)

Query 2

select * FROM archive where action_time <= 1697489999 order by action_time desc;

Manticore
20 rows in set (0.012 sec)

Sphinx:
20 rows in set (0.298 sec)

The problem occurs when I added “OPTION max_matches=3000000;”

Is there any performance optimization for this situation?

[Sergey]

The problem occurs when I added “OPTION max_matches=3000000;”
What’s the performance if you don’t increase max_matches that much?

[yigits]

There is no problem when I don’t add “OPTION max_matches=3000000;”

@Sergey

What’s the performance if you don’t increase max_matches that much?

@yigits

Query 2

select * FROM archive where action_time <= 1697489999 order by action_time desc;

Manticore
20 rows in set (0.012 sec)

Sphinx:
20 rows in set (0.298 sec)

[Sergey]

Is there any non-sql_field_string full-text fields in the table?

[yigits]

sql_field_string = type (e.g: single or multi)
sql_field_string = hash_id (e.g: URprJC2nEX4b3ztvVswQmW)
sql_field_string = name (255 character text)
sql_field_string = name_en (255 character text)
sql_field_string = user_string_id (e.g.: URprJC2nEX4b3ztvVswQmW)
sql_field_string = user_hash_id (e.g.: URprJC2nEX4b3ztvVswQmW)
sql_field_string = short_type (e.g.: T1 or T2 …)
sql_field_string = short_method (e.g.: M1 or M2 …)
sql_field_string = process (e.g.: completed or active)
sql_field_string = category (255 character)
sql_field_string = cities (e.g.: London, Istanbul…)
sql_field_string = details (very long full-text)
sql_field_string = country_name (e.g.: United Kingdom, Turkey, Germany)
sql_field_string = region_name (e.g.: Asia, Europa)
sql_field_string = level_one_category (e.g: Category 1)
sql_field_string = level_two_category (e.g: Category 2)
sql_field_string = level_three_category (e.g: Category 3)
sql_field_string = detail_related (long full-text)

Some values used for query, some values used for storage to get and show the data.

[Sergey]

What’s the sql_query?

[yigits]

@Sergey

What’s the sql_query?

The sql queries.

@yigits

select * FROM archive where action_time <= 1697489999 order by action_time desc;

@yigits

select * FROM archive where action_time <= 1697489999 order by action_time desc LIMIT 1000, 20 OPTION max_matches=3000000;

[Sergey]

This doesn’t look like an sql_query from the config. OK, let’s do it another way: pls show show create table archive and desc archive in Manticore.