{"id":5728,"date":"2014-04-06T10:30:02","date_gmt":"2014-04-06T10:30:02","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2014\/04\/06\/sphinx-indexer-causes-mysql-to-hang-collection-of-common-programming-errors\/"},"modified":"2014-04-06T10:30:02","modified_gmt":"2014-04-06T10:30:02","slug":"sphinx-indexer-causes-mysql-to-hang-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2014\/04\/06\/sphinx-indexer-causes-mysql-to-hang-collection-of-common-programming-errors\/","title":{"rendered":"Sphinx: Indexer causes MySQL to hang-Collection of common programming errors"},"content":{"rendered":"<p>Not sure what&#8217;s going on. I run indexer &#8211;all &#8211;rotate When it finishes mysql hangs and not accepting new connections. from my observation as soon as indexer finishes, all <code>update,insert,delete<\/code> queries goes into <code>query end<\/code> state<\/p>\n<p>*mysql tables are not corrupt<\/p>\n<p>*i&#8217;m using Percona mysql 5.6.12-56<\/p>\n<p>*table in Innodb type<\/p>\n<p>*tried to install sphinx from source and rpm, also tried Sphinx 2.1.1 and Sphinx 2.0.8<\/p>\n<pre><code>indexer --all --rotate\nSphinx 2.1.1-beta (rel21-r3701)\nCopyright (c) 2001-2013, Andrew Aksyonoff\nCopyright (c) 2008-2013, Sphinx Technologies Inc (http:\/\/sphinxsearch.com)\n\nusing config file '\/etc\/sphinx\/sphinx.conf'...\nindexing index 'online'...\ncollected 27114 docs, 99.0 MB\nsorted 258.8 Mhits, 100.0% done\ntotal 27114 docs, 98993190 bytes\ntotal 119.609 sec, 827633 bytes\/sec, 226.68 docs\/sec\ntotal 21 reads, 4.497 sec, 53362.9 kb\/call avg, 214.1 msec\/call avg\ntotal 2510 writes, 3.210 sec, 968.1 kb\/call avg, 1.2 msec\/call avg\nrotating indices: successfully sent SIGHUP to searchd (pid=12773).\n<\/code><\/pre>\n<p>processlist when it hangs:<\/p>\n<pre><code>    Id  User    Host    db  Command Time    State   Info    Rows_sent   Rows_examined\n    31891   forum_DB        localhost   forum_DB        Query   346     query end   INSERT INTO ibf_sessions (`id`,`member_name`,`member_id$\n    31905   forum_DB        localhost   forum_DB        Query   346     query end   DELETE FROM ibf_sessions WHERE (id='yandex=95108240250_$\n    31964   forum_DB        localhost   forum_DB        Query   345     query end   INSERT INTO ibf_sessions (`id`,`member_name`,`member_id$\n    32062   forum_DB        localhost   forum_DB        Query   343     query end   INSERT INTO ibf_topic_views (`views_tid`) VALUES(599181$\n    32077   forum_DB        localhost   forum_DB        Query   343     query end   INSERT INTO ibf_topic_views (`views_tid`) VALUES(599181$\n    32353   forum_DB        localhost   forum_DB        Query   338     query end   INSERT INTO ibf_core_like_cache (`like_cache_id`,`like_$\n    32443   forum_DB        localhost   forum_DB        Query   336     query end   INSERT INTO ibf_core_like_cache (`like_cache_id`,`like_$\n    32450   forum_DB        localhost   forum_DB        Query   336     query end   INSERT INTO ibf_sessions (`id`,`member_name`,`member_id$\n    32518   forum_DB        localhost   forum_DB        Query   335     query end   INSERT INTO ibf_sessions (`id`,`member_name`,`member_id$\n    32617   forum_DB        localhost   forum_DB        Query   333     query end   INSERT INTO ibf_core_like_cache (`like_cache_id`,`like_$\n    32642   forum_DB        localhost   forum_DB        Query   332     query end   INSERT INTO ibf_sessions (`id`,`member_name`,`member_i\n...\n37207   online  localhost   online  Query   247     Waiting for query cache lock    SELECT id, short_story, title, date, alt_name, category$\n37216   forum_DB        localhost   forum_DB        Query   247     query end   INSERT INTO ibf_sessions (`id`,`member_name`,`member_id$\n37228   online  localhost   online  Query   247     Waiting for query cache lock    SELECT id, short_story, title, date, alt_name, category$\n37232   online  localhost   online  Query   247     System lock     SELECT id, autor, date, short_story, SUBSTRING(full_story, 1, 15) as fu$\n37239   online  localhost   online  Query   247     FULLTEXT initialization SELECT id, short_story, title, date, alt_name, category, flag F$\n37243   music   localhost   music   Query   247     Waiting for query cache lock    TRUNCATE TABLE dle_login_log    0   0\n37250   online  localhost   online  Query   246     Sending data    SELECT COUNT(*) as count FROM dle_post WHERE approve=1 AND allow_main=1$\n37253   files   localhost   files   Query   246     Waiting for query cache lock    TRUNCATE TABLE dle_views        0   0\n37264   music   localhost   music   Query   246     Waiting for table metadata lock TRUNCATE TABLE dle_login_log    0   0\n37271   files   localhost   files   Query   245     Waiting for table metadata lock SELECT COUNT(*) as count, news_id FROM dle_views GROUP $\n37279   online  localhost   online  Query   245     Sending data    SELECT COUNT(*) as count FROM dle_post WHERE approve=1 AND allow_main=1$\n37288   files   localhost   files   Query   244     Waiting for table metadata lock SELECT COUNT(*) as count, news_id FROM dle_views GROUP $\n37289   online  localhost   online  Query   244     FULLTEXT initialization SELECT id, short_story, title, date, alt_name, category, flag F$\n37291   files   localhost   files   Query   244     Waiting for table metadata lock SELECT COUNT(*) as count, news_id FROM dle_views GROUP $\n37292   online  localhost   online  Query   244     Waiting for query cache lock    TRUNCATE TABLE dle_login_log    0   0\n37296   online  localhost   online  Query   244     Sending data    SELECT COUNT(*) as count FROM dle_post WHERE approve=1 AND\n...\n<\/code><\/pre>\n<p>cat processlist-2013-08-25-11-52.log | wc -l 352<\/p>\n<p>sphinx.conf<\/p>\n<pre><code>source online_posts\n{\n        type                    = mysql\n\n        sql_host                = \n        sql_user                = \n        sql_pass                = \n        sql_db                  = online_test\n        sql_port                = 3306  # optional, default is 3306\n\n        sql_query               = \\\n                SELECT * FROM post\n\n        #sql_attr_uint          = group_id\n        sql_attr_timestamp  = date\n\n        sql_query_pre = SET NAMES utf8\n        sql_query_pre = SET CHARACTER SET utf8\n        sql_query_pre = SET SESSION query_cache_type=OFF\n\n        sql_query_info          = SELECT * FROM post WHERE id=$id\n}\n\nindex online\n{\n        source                  = online_posts\n        path                    = \/var\/lib\/sphinx\/online\n        docinfo                 = extern\n        charset_type            = utf-8\n        morphology              = stem_enru\n\n        min_word_len            = 2\n        min_prefix_len          = 0\n        min_infix_len           = 2\n\n        charset_table = 0..9, A..Z-&gt;a..z, _, a..z, U+410..U+42F-&gt;U+430..U+44F, U+430..U+44F\n\n        enable_star = 1\n}\n\nindexer\n{\n    mem_limit       = 512M\n}\n\n\nsearchd\n{\n    listen          = 9312\n    listen          = 9306:mysql41\n    log         = \/var\/log\/sphinx\/searchd.log\n    query_log       = \/var\/log\/sphinx\/query.log\n    read_timeout        = 5\n    max_children        = 30\n    pid_file        = \/var\/run\/sphinx\/searchd.pid\n    max_matches     = 1000\n    seamless_rotate     = 1\n    preopen_indexes     = 1\n    unlink_old      = 1\n    workers         = threads # for RT to work\n    binlog_path     = \/var\/lib\/sphinx\/\n}\n<\/code><\/pre>\n<p>every time i run indexer i get the following in \/var\/log\/mysql.log<\/p>\n<pre><code>%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%$\nsyntax error, unexpected '-', expecting FTS_TEXT or FTS_TERM or FTS_NUMB or '('\nsyntax error, unexpected '-', expecting FTS_TEXT or FTS_TERM or FTS_NUMB or '('\n%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%$\n<\/code><\/pre>\n<p>Looks like others have this problem as well, but I don&#8217;t understand how he solved that http:\/\/sphinxsearch.com\/forum\/view.html?id=11072<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Not sure what&#8217;s going on. I run indexer &#8211;all &#8211;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 state *mysql tables are not corrupt *i&#8217;m using Percona mysql 5.6.12-56 *table in Innodb type *tried to install sphinx from [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-5728","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/5728","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=5728"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/5728\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=5728"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=5728"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=5728"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}