Most recently, I noticed that while indexing a table with Sphinx, the table that it indexes is locked not only for writing, but also for reading. You have to wait for the page to load up to 10 seconds until the indexing is completed. The table is not small (~ 150k records) and is rarely updated so it costs MyISAM. As far as I know, SELECT does not create table-level locking in MyISAM, but only INSERT, UPDATE do it.

I did a test. The same query from the sql_query variable with which Sphinx is addressed, I executed in MySql with the cache turned off (SQL_NO_CACHE) in order to get real numbers. I repeated it several times, the execution time is ~ 0.0020 sec, which I would say very quickly when retrieving all ~ 150k records. Indexing occurs on the crown every 10 minutes. And every 10 minutes you can not get anything from the table until Sphinx completes. Pages in which there are no SELECTs for this table are loaded without any problems during indexing.

What is the problem? Here is my config, ordinary no different from others.

source table { type = mysql sql_host = localhost sql_user = sql_pass = sql_db = table sql_port = 3306 sql_query_pre = SET NAMES utf8 sql_query_pre = SET CHARACTER SET utf8 sql_query = SELECT id, cast, director FROM table ORDER BY id DESC sql_field_string = cast sql_field_string = director sql_ranged_throttle = 0 } index table { source = table path = /home/sphinx/data/table docinfo = extern morphology = stem_enru min_word_len = 1 charset_table = 0..9, A..Z->a..z, _, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F, U+401->U+0435, U+451->U+0435 min_infix_len = 2 index_exact_words = 1 } indexer { mem_limit = 256M } searchd { listen = 3312 log = /home/sphinx/log/searchd.log read_timeout = 5 max_children = 30 pid_file = /home/sphinx/log/searchd.pid }
  • SHOW FULL PROCESSLIST make during indexing and see what requests are executed and how they affect others. - Firepro
  • @Firepro executes a query from the sql_query variable which in sphinx.conf. Its status is Sending data . All requests that below have the status Waiting for table level lock . After a few seconds, the request from sphinx receives the status Writing to net . Bottom requests are still locked with the status Waiting for table level lock . Any idea how to treat? - Max_Payne

1 answer 1

The answer is here and a good explanation of the situation here .

You can set low-priority-updates = 1 in the [mysqld] section of the MySQL my.cnf to increase the priority of updates.

Another option is to use the RT index.

Another option is to use InnoDB.