SphinxSE查询导致MySQL崩溃
我使用 MySQL 5.5.9 和 MySQL 5.5.16 以及 mysqlse rev 2973
这是 sphinx 查询：
SELECT
id
FROM sphinx_search t
WHERE `query` = 'mode=extended2;
maxmatches=1000000;
query=@site_id,"0acd771ae618e89097564494af8bb838";
filter=search_engine_id,5;
limit=100; offset=0;
index=keywords_idx;
sort=extended:keyword_ord ASC;';
这是 MySQL 堆栈跟踪：
Thread pointer: 0x7f57600
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x496900b8 thread_stack 0x40000
/usr/libexec/mysqld(my_print_stacktrace+0x33)[0x7aa013]
/usr/libexec/mysqld(handle_segfault+0x381)[0x5002e1]
/lib64/libpthread.so.0[0x3384c0eb10]
/lib64/libc.so.6(strstr+0x1b)[0x338447a32b]
/usr/lib64/mysql/plugin/ha_sphinx.so[0x2aaab2e006cc]
/usr/lib64/mysql/plugin/ha_sphinx.so(_ZN9ha_sphinx4openEPKcij+0x172)[0x2aaab2e00bc2]
/usr/libexec/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x3f)[0x678cff]
/usr/libexec/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0x539)[0x5e
8349]
/usr/libexec/mysqld(_Z10open_tableP3THDP10TABLE_LISTP11st_mem_rootP18Open_table_context+0x
802)[0x53ea12]
/usr/libexec/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x2d0)[0x5
40060]
/usr/libexec/mysqld(_Z18mysqld_show_createP3THDP10TABLE_LIST+0x146)[0x5bb916]
/usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x27a6)[0x573d76]
/usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x10a)[0x5775ba]
/usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x14fc)[0x578b1c]
/usr/libexec/mysqld(_Z10do_commandP3THD+0xc2)[0x578e72]
/usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0xf2)[0x60ac82]
/usr/libexec/mysqld(handle_one_connection+0x53)[0x60ad73]
/lib64/libpthread.so.0[0x3384c0673d]
/lib64/libc.so.6(clone+0x6d)[0x33844d44bd]
I used MySQL 5.5.9 and MySQL 5.5.16 and mysqlse rev 2973
Here is the sphinx query:
SELECT
id
FROM sphinx_search t
WHERE `query` = 'mode=extended2;
maxmatches=1000000;
query=@site_id,"0acd771ae618e89097564494af8bb838";
filter=search_engine_id,5;
limit=100; offset=0;
index=keywords_idx;
sort=extended:keyword_ord ASC;';
here is the MySQL stacktrace:
Thread pointer: 0x7f57600
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x496900b8 thread_stack 0x40000
/usr/libexec/mysqld(my_print_stacktrace+0x33)[0x7aa013]
/usr/libexec/mysqld(handle_segfault+0x381)[0x5002e1]
/lib64/libpthread.so.0[0x3384c0eb10]
/lib64/libc.so.6(strstr+0x1b)[0x338447a32b]
/usr/lib64/mysql/plugin/ha_sphinx.so[0x2aaab2e006cc]
/usr/lib64/mysql/plugin/ha_sphinx.so(_ZN9ha_sphinx4openEPKcij+0x172)[0x2aaab2e00bc2]
/usr/libexec/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x3f)[0x678cff]
/usr/libexec/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0x539)[0x5e
8349]
/usr/libexec/mysqld(_Z10open_tableP3THDP10TABLE_LISTP11st_mem_rootP18Open_table_context+0x
802)[0x53ea12]
/usr/libexec/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x2d0)[0x5
40060]
/usr/libexec/mysqld(_Z18mysqld_show_createP3THDP10TABLE_LIST+0x146)[0x5bb916]
/usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x27a6)[0x573d76]
/usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x10a)[0x5775ba]
/usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x14fc)[0x578b1c]
/usr/libexec/mysqld(_Z10do_commandP3THD+0xc2)[0x578e72]
/usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0xf2)[0x60ac82]
/usr/libexec/mysqld(handle_one_connection+0x53)[0x60ad73]
/lib64/libpthread.so.0[0x3384c0673d]
/lib64/libc.so.6(clone+0x6d)[0x33844d44bd]
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我没有足够的代表直接发表评论，我不想提供此答案，但我确实认为这可能会导致您出现问题或导致此问题，您有 1,000,000 最大匹配数是否有特殊原因？
请参阅：http://sphinxsearch.com/docs/1.10/conf-max-matches .html
您要求 sphinx 为您获取某些内容，然后过滤最多 100 万个结果并对它们进行排名/排序，然后仅处理这 100 万个结果中的前 100 个...
I don't have enough rep to comment directly I dont want to provide this as an answer but I do think it could be causing you issues or caused this issue, is there a particular reason you have 1,000,000 max matches?
see: http://sphinxsearch.com/docs/1.10/conf-max-matches.html
You're asking sphinx to get something for you, then filter up to 1 million of them and rank/sort them then deal only the first 100 of those 1 million results...