+2 votes
by
Hello, I'm transferring my site search to sphinxsearch, now it's running on mysql fulltext.

I downloaded and installed the latest version of sphinxsearch from the official site.

I get terrible time results (sphinxsearch is slower). Here I created a test environment for search (works on the same dataset):
https://izap24.ru/sphinx/test.php

To get results, you can search for car brands, such as "bmw e46".

Did I configure the sphinx wrong?
by
65 ms Sphinx vs 2.45 s Mysql. Where is slower?
by
Ah, this is on warm data. On cold data, it takes the same amount of time.
by
Alexei Ukolov Yes, a second request will speed up many times over.
by
Everything is on the same server. Bases mySql and indexes Sphinx on different disks, both SSD. If you need any more data I will provide.

I use these settings:

source src1
{
type = mysql

sql_host = localhost
sql_user =
sql_pass =
sql_db =
sql_port = 3306 # optional, default is 3306

# Установим кодировку для работы с БД
sql_query_pre = SET NAMES utf8
sql_query_pre = SET CHARACTER SET utf8

sql_query = \
SELECT prod_search_index.id, prod_search_index.shop, prod_search_index.category, prod_search_index.prise, prod_search_index.brand,prod_search_index.name_ru,prod_search_index.active,prod_search_index.moto, \
IF(`prod_search_index`.`shop`!=1,1 , 0) AS `belbd`\
FROM prod_search_index \

sql_attr_uint = shop
sql_attr_uint = category
sql_attr_uint = prise
sql_attr_uint = brand
sql_attr_uint = active
sql_attr_uint = moto
sql_attr_uint = belbd
sql_attr_multi = uint folder from ranged-query; \
SELECT `prod`, `folder` \
FROM `in_folders` \
WHERE `id`>=$start AND `id`<$end; \
SELECT MIN(`id`), MAX(`id`) FROM `in_folders`

#sql_ranged_throttle = 200
}

index izap_prod_search_index
{
source = src1
path = /var/www/siriusit/data/sphinxdata/indexes/izap_prod_search_index
mlock = 0
morphology = stem_ru
min_word_len = 1
}

index izap_prod_search_index_rt
{
type = rt
rt_mem_limit = 128M
path = /var/www/siriusit/data/sphinxdata/indexes/izap_prod_search_index_rt
rt_field = name_ru
rt_attr_uint = shop
rt_attr_uint = category
rt_attr_uint = prise
rt_attr_uint = brand
rt_attr_uint = active
rt_attr_uint = moto
rt_attr_uint = belbd
}

index izap_prod_search
{
type = distributed
local = izap_prod_search_index
local = izap_prod_search_index_rt
}

indexer
{
mem_limit = 128M
}

searchd
{
listen = 9312
listen = 9306:mysql41
log = /var/www/siriusit/data/sphinxdata/logs/searchd.log
query_log = /var/www/siriusit/data/sphinxdata/logs/query.log
read_timeout = 5
max_children = 30
pid_file = /var/www/siriusit/data/sphinxdata/searchd.pid
seamless_rotate = 1
preopen_indexes = 1
unlink_old = 1
workers = threads # for RT to work
binlog_path = /var/www/siriusit/data/sphinxdata/binlogs
}

3 Answers

0 votes
by
In general, I put the same query on the cron and measured the time in ms for mysql and sphinx.
The results were unstable and ambiguous:
2020-07-13 00:00:07: 242|5743
2020-07-13 01:00:09: 1795|5827
2020-07-13 02:00:04: 201|2086
2020-07-13 03:00:04: 119|3124
2020-07-13 04:00:06: 2807|1685
2020-07-13 05:06:27: 1153|384722
2020-07-13 06:00:05: 1114|2317
2020-07-13 07:00:07: 2719|3121
2020-07-13 08:00:05: 1699|2627
2020-07-13 09:00:17: 1575|12711
2020-07-13 10:00:03: 115|1954
2020-07-13 11:00:06: 1188|3142
2020-07-13 12:00:03: 639|1922
2020-07-13 13:00:04: 859|2369
2020-07-13 14:00:05: 1206|2813
2020-07-13 15:00:04: 273|2263
2020-07-13 16:00:04: 124|1901
2020-07-13 17:00:12: 1305|9818
2020-07-13 18:00:05: 924|3223
2020-07-13 19:00:15: 256|12140
2020-07-13 20:00:11: 1674|2470
2020-07-13 21:00:02: 500|1184
2020-07-13 22:00:03: 63|1707
2020-07-13 23:00:04: 711|2129
Then rolled out the new engine on the site and oh well:

2020-07-16 00:00:04: 33|2362
2020-07-16 01:00:03: 77|2029
2020-07-16 02:00:04: 64|2280
2020-07-16 03:00:03: 21|2091
2020-07-16 04:00:03: 22|1555
2020-07-16 05:05:52: 71|350448
2020-07-16 06:00:06: 85|4421
2020-07-16 07:00:03: 72|2492
2020-07-16 08:00:07: 72|5772
2020-07-16 09:00:04: 69|2436
2020-07-16 10:00:03: 32|1510
2020-07-16 11:00:03: 23|2007
2020-07-16 12:00:04: 54|1902
2020-07-16 13:00:03: 71|2213
2020-07-16 14:00:03: 31|2423
2020-07-16 15:00:03: 72|1581
2020-07-16 16:00:04: 24|2300
2020-07-16 17:00:07: 41|6524
2020-07-16 18:00:03: 38|1935
2020-07-16 19:00:03: 60|2060
2020-07-16 20:00:03: 32|1891
2020-07-16 21:00:03: 22|2277
2020-07-16 22:00:04: 22|2355
2020-07-16 23:00:03: 33|2003

Apparently the engine itself must be warmed up to work well)
0 votes
by
Wrong set up or wrong search
sphinx is always an order of two faster than a muscle in a search
by
The default settings are mostly. Now I removed the definition of rt indices and paralleled my test environment, which in mysql and sphinx simultaneously searched (after all, the server is under load).

As a result, I get almost consistently much better performance from sphinx
+1 vote
by
Did I configure the sphinx wrong?
How should we know? Did you show us the settings? Or did you describe the server? Maybe you have them on different servers. Maybe you process the received data differently? Maybe your query itself is a bit messed up?
by
Clarified my question. In the case of Mysql, the data are selected. In the case of Sphinx I only get the id.
...