Not sure what's going on. I run indexer --all --rotate
When it finishes mysql hangs and not accepting new connections.
from my observation as soon as indexer finishes, all update,insert,delete queries goes into query end
*mysql tables are not corrupt
*i'm using Percona mysql 5.6.12-56
*table in Innodb type
*tried to install sphinx from source and rpm, also tried Sphinx 2.1.1 and Sphinx 2.0.8
indexer --all --rotate
Sphinx 2.1.1-beta (rel21-r3701)
Copyright (c) 2001-2013, Andrew Aksyonoff
Copyright (c) 2008-2013, Sphinx Technologies Inc (http://sphinxsearch.com)
using config file '/etc/sphinx/sphinx.conf'...
indexing index 'online'...
collected 27114 docs, 99.0 MB
sorted 258.8 Mhits, 100.0% done
total 27114 docs, 98993190 bytes
total 119.609 sec, 827633 bytes/sec, 226.68 docs/sec
total 21 reads, 4.497 sec, 53362.9 kb/call avg, 214.1 msec/call avg
total 2510 writes, 3.210 sec, 968.1 kb/call avg, 1.2 msec/call avg
rotating indices: successfully sent SIGHUP to searchd (pid=12773).
processlist when it hangs:
Id User Host db Command Time State Info Rows_sent Rows_examined
31891 forum_DB localhost forum_DB Query 346 query end INSERT INTO ibf_sessions (`id`,`member_name`,`member_id$
31905 forum_DB localhost forum_DB Query 346 query end DELETE FROM ibf_sessions WHERE (id='yandex=95108240250_$
31964 forum_DB localhost forum_DB Query 345 query end INSERT INTO ibf_sessions (`id`,`member_name`,`member_id$
32062 forum_DB localhost forum_DB Query 343 query end INSERT INTO ibf_topic_views (`views_tid`) VALUES(599181$
32077 forum_DB localhost forum_DB Query 343 query end INSERT INTO ibf_topic_views (`views_tid`) VALUES(599181$
32353 forum_DB localhost forum_DB Query 338 query end INSERT INTO ibf_core_like_cache (`like_cache_id`,`like_$
32443 forum_DB localhost forum_DB Query 336 query end INSERT INTO ibf_core_like_cache (`like_cache_id`,`like_$
32450 forum_DB localhost forum_DB Query 336 query end INSERT INTO ibf_sessions (`id`,`member_name`,`member_id$
32518 forum_DB localhost forum_DB Query 335 query end INSERT INTO ibf_sessions (`id`,`member_name`,`member_id$
32617 forum_DB localhost forum_DB Query 333 query end INSERT INTO ibf_core_like_cache (`like_cache_id`,`like_$
32642 forum_DB localhost forum_DB Query 332 query end INSERT INTO ibf_sessions (`id`,`member_name`,`member_i
...
37207 online localhost online Query 247 Waiting for query cache lock SELECT id, short_story, title, date, alt_name, category$
37216 forum_DB localhost forum_DB Query 247 query end INSERT INTO ibf_sessions (`id`,`member_name`,`member_id$
37228 online localhost online Query 247 Waiting for query cache lock SELECT id, short_story, title, date, alt_name, category$
37232 online localhost online Query 247 System lock SELECT id, autor, date, short_story, SUBSTRING(full_story, 1, 15) as fu$
37239 online localhost online Query 247 FULLTEXT initialization SELECT id, short_story, title, date, alt_name, category, flag F$
37243 music localhost music Query 247 Waiting for query cache lock TRUNCATE TABLE dle_login_log 0 0
37250 online localhost online Query 246 Sending data SELECT COUNT(*) as count FROM dle_post WHERE approve=1 AND allow_main=1$
37253 files localhost files Query 246 Waiting for query cache lock TRUNCATE TABLE dle_views 0 0
37264 music localhost music Query 246 Waiting for table metadata lock TRUNCATE TABLE dle_login_log 0 0
37271 files localhost files Query 245 Waiting for table metadata lock SELECT COUNT(*) as count, news_id FROM dle_views GROUP $
37279 online localhost online Query 245 Sending data SELECT COUNT(*) as count FROM dle_post WHERE approve=1 AND allow_main=1$
37288 files localhost files Query 244 Waiting for table metadata lock SELECT COUNT(*) as count, news_id FROM dle_views GROUP $
37289 online localhost online Query 244 FULLTEXT initialization SELECT id, short_story, title, date, alt_name, category, flag F$
37291 files localhost files Query 244 Waiting for table metadata lock SELECT COUNT(*) as count, news_id FROM dle_views GROUP $
37292 online localhost online Query 244 Waiting for query cache lock TRUNCATE TABLE dle_login_log 0 0
37296 online localhost online Query 244 Sending data SELECT COUNT(*) as count FROM dle_post WHERE approve=1 AND
...
cat processlist-2013-08-25-11-52.log | wc -l 352
sphinx.conf
source online_posts
{
type = mysql
sql_host =
sql_user =
sql_pass =
sql_db = online_test
sql_port = 3306 # optional, default is 3306
sql_query = \
SELECT * FROM post
#sql_attr_uint = group_id
sql_attr_timestamp = date
sql_query_pre = SET NAMES utf8
sql_query_pre = SET CHARACTER SET utf8
sql_query_pre = SET SESSION query_cache_type=OFF
sql_query_info = SELECT * FROM post WHERE id=$id
}
index online
{
source = online_posts
path = /var/lib/sphinx/online
docinfo = extern
charset_type = utf-8
morphology = stem_enru
min_word_len = 2
min_prefix_len = 0
min_infix_len = 2
charset_table = 0..9, A..Z->a..z, _, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F
enable_star = 1
}
indexer
{
mem_limit = 512M
}
searchd
{
listen = 9312
listen = 9306:mysql41
log = /var/log/sphinx/searchd.log
query_log = /var/log/sphinx/query.log
read_timeout = 5
max_children = 30
pid_file = /var/run/sphinx/searchd.pid
max_matches = 1000
seamless_rotate = 1
preopen_indexes = 1
unlink_old = 1
workers = threads # for RT to work
binlog_path = /var/lib/sphinx/
}
every time i run indexer i get the following in /var/log/mysql.log
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%$
syntax error, unexpected '-', expecting FTS_TEXT or FTS_TERM or FTS_NUMB or '('
syntax error, unexpected '-', expecting FTS_TEXT or FTS_TERM or FTS_NUMB or '('
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%$
Looks like others have this problem as well, but I don't understand how he solved that http://sphinxsearch.com/forum/view.html?id=11072
