不知道发生了什么。我运行 indexer --all --rotate 当它完成时，mysql 挂起并且不接受新连接。根据我的观察，一旦索引器完成，所有update,insert,delete查询都会进入query end
*mysql 表没有损坏
*我正在使用 Percona mysql 5.6.12-56
*Innodb 类型的表
*尝试从源代码和 rpm 安装 sphinx，也尝试了 Sphinx 2.1.1 和 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).
进程列表挂起时：
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 进程列表-2013-08-25-11-52.log | wc -l 352
sphinx配置文件
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/
}
每次运行索引器时，我都会在 /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 '('
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%$
看起来其他人也有这个问题，但我不明白他是如何解决这个问题的 http://sphinxsearch.com/forum/view.html?id=11072
答案1
索引器正在锁定查询缓存，这导致所有其他查询挂起。无论如何，您确实不想将查询缓存用于索引器，因此请将 SQL 查询更改为
SELECTSQL_NO_CACHE* FROM post
避免使用、锁定和污染查询缓存。