Parallel BG search does not always work

[Adrian Partl]

Spider engine version 3.0.

The parallel BG search does not always work, depending on the query one asks.
On a table with one primary key column (id) and one data column (data), the following
queries are correctly run in parallel if BG search is on:

id ranges from 3000000000 to 8500000000
data ranges from 0 to 1000

SELECT * from foo where id < 3000000010
SELECT * from foo where data > 1000.1

however the following query is run in sequential order, where each node is asked for
results sequentially:

SELECT * from foo where data > 999.9
SELECT * from foo where id > 3000000010
SELECT * from foo where id < 5000000010

With the data column the behavior seems to be dependent on the maximum value in the
table. I have no idea though, why the second and third query on the primary key column do
not run in parallel. Somehow this seems correlated with the amount of data returned (does
the optimiser play a role in this?)

Let me know if you need more information.

[Kentoku SHIBA (kentokushiba)]

Thank you for reporting the problem. Please try to use the following.
http://spiderformysql.com/downloads/spider-3.0/mysql-5.5.14-spider-3.0-vp-0.18-hs-1.2-q4m-0.95-linux-x86_64-glibc25g.tgz
http://spiderformysql.com/downloads/spider-3.0/mysql-5.5.14-spider-3.0-vp-0.18-hs-1.2-q4m-0.95g.tgz

[Adrian Partl (ndriax)]

I tried again with the sources you provided. The problem still remains whenh using the same configuration. Is there something I need to reconfigure? Or can I send you any other information that might help you?

[Kentoku SHIBA (kentokushiba)]

Thank you for checking! If you set "bgs_mode" >= 1, no need other configuration. Could you please give me the table structure and sample data for reproducing?

[Adrian Partl (ndriax)]

OK, "bgs_mode" is set to 1, so that was not the problem. I have dumped some data from one of our tables into
a CSV file, together with the CREATE TABLE statements for the nodes and the spider head node. I hope 100000
rows is enough? You can have more if you need...

Download the data here:
https://cloud.aip.de/public.php?service=files&token=e180bbc175e89979ae7dc6a662f67fc30b363bae&file=/spider_bug_report.tar.gz

[Kentoku SHIBA (kentokushiba)]

Hi Adrian!
Thank you for your cooperation. Please try to use the following.
http://spiderformysql.com/downloads/spider-3.0/mysql-5.5.14-spider-3.0-vp-0.18-hs-1.2-q4m-0.95-linux-x86_64-glibc25i.tgz
http://spiderformysql.com/downloads/spider-3.0/mysql-5.5.14-spider-3.0-vp-0.18-hs-1.2-q4m-0.95i.tgz

[Adrian Partl (ndriax)]

Hi!

Thank you for your update! The bug is now fixed in the version you provided. It now works!

Thanks again,

Adrian

[Adrian Partl (ndriax)]

Should a query like

delete from FOO.BAR where x < 87;

also be executed in parallel? If yes, this would not yet work with the version you provided.

[Adrian Partl (ndriax)]

Hi,

I think this is still related to the issue I reported here. If not, feel free to open this as a new bug.

With the version you provided, a parallel BG query using ORDER BY and LIMIT will cause the server to crash
(I know the query is rather stupid with large tables since the head node needs to do the sorting right? But
still it should not take the server down):

select * from FOO order by BAR limit 10;

will crash. This will not crash however:

select * from FOO order by BAR;
select * from FOO limit 10;

Stacktrace from the log file is:

/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x33)[0x7558e3]
/usr/local/mysql/bin/mysqld(handle_segfault+0x42b)[0x50153b]
/lib64/libpthread.so.0[0x309760f500]
/usr/local/mysql/lib/plugin/ha_spider.so(_ZN9ha_spider8positionEPKh+0x111)[0x2b2667584601]
/usr/local/mysql/bin/mysqld(_ZN12ha_partition8positionEPKh+0x27)[0x8b0d57]
/usr/local/mysql/bin/mysqld(_Z8filesortP3THDP5TABLEP13st_sort_fieldjP10SQL_SELECTybPy+0x12ae)[0x64b8de]
/usr/local/mysql/bin/mysqld[0x596af5]
/usr/local/mysql/bin/mysqld(_ZN4JOIN4execEv+0xb25)[0x5a07e5]
/usr/local/mysql/bin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x162)[0x59c862]
/usr/local/mysql/bin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x17c)[0x5a1efc]
/usr/local/mysql/bin/mysqld[0x5657b4]
/usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x2c65)[0x56b5e5]
/usr/local/mysql/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x16d)[0x56cd7d]
/usr/local/mysql/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1257)[0x56e717]
/usr/local/mysql/bin/mysqld(_Z24do_handle_one_connectionP3THD+0x176)[0x5f7686]
/usr/local/mysql/bin/mysqld(handle_one_connection+0x4a)[0x5f76ea]
/usr/local/mysql/bin/mysqld(pfs_spawn_thread+0x54)[0x7ae034]
/lib64/libpthread.so.0[0x3097607851]
/lib64/libc.so.6(clone+0x6d)[0x3096ae890d]

I'm happy to provide any other information that might help...

Thanks!