Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 state

*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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

After I spending all day on it, it turns-out to be innodb_flush_log_at_trx_commit=1 setting innodb_flush_log_at_trx_commit=0 fixed the problem.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.