不知道发生了什么事。 我运行索引器–all –rotate当它完成mysql挂起，不接受新的连接。 从索引器完成后我的观察，所有update,insert,delete查询进入query end
* MySQL表没有损坏
*我正在使用Percona MySQL 5.6.12-56
* Innodbtypes的表
*试图从源和rpm安装狮身人面像，也尝试过狮身人面像2.1.1和狮身人面像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 processlist-2013-08-25-11-5.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/ }
每次我运行索引器我在/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
索引器正在locking查询caching，导致所有其他查询挂起。 你真的不希望为索引器使用查询caching，所以更改SQL查询
SELECT SQL_NO_CACHE * FROM post
以避免使用，locking和污染查询caching。