|
When queries to spider table generate multi-queries to the datanodes, the setting of 'wait_timeout' on the datanode becomes important to exceed the time the longest running part will take, since the datanode need to wait between calls.
Here is an example when running with too low wait_timeout. Increasing it makes the issue go away.
I suggest that the spider engine sends a 'SET SESSION wait_timeout = <high value>' when initializing the multi-query.
mysql cli:
user@spider_head [db]> select min(id), max(id), avg(id), bit_xor(id), min(id2), max(id2), avg(id2), bit_xor(id2), count(*) FROM t3;
|
ERROR 12701 (HY000): Remote MySQL server has gone away
|
spider_head mysql.log:
20180619 19:09:45 [RECV SPIDER SQL] from [user][localhost] to 57: sql: select min(id), max(id), avg(id), bit_xor(id), min(id2), max(id2), avg(id2), bit_xor(id2), count(*) FROM t3
|
20180619 19:09:45 [SEND SPIDER SQL] from 57 to [spider-datanode1.com] 4017254: sql: commit
|
20180619 19:09:45 [RECV SPIDER SQL] from [user][localhost] to 57: sql: select min(id), max(id), avg(id), bit_xor(id), min(id2), max(id2), avg(id2), bit_xor(id2), count(*) FROM t3
|
20180619 19:09:45 [SEND SPIDER SQL] from 57 to [spider-datanode2.com] 5497655: sql: commit
|
20180619 19:09:45 [RECV SPIDER SQL] from [user][localhost] to 57: sql: select min(id), max(id), avg(id), bit_xor(id), min(id2), max(id2), avg(id2), bit_xor(id2), count(*) FROM t3
|
20180619 19:09:45 [SEND SPIDER SQL] from 57 to [spider-datanode3.com] 6159089: sql: commit
|
20180619 19:09:45 [RECV SPIDER SQL] from [user][localhost] to 57: sql: select min(id), max(id), avg(id), bit_xor(id), min(id2), max(id2), avg(id2), bit_xor(id2), count(*) FROM t3
|
20180619 19:09:45 [SEND SPIDER SQL] from 57 to [spider-datanode4.com] 23813888: sql: commit
|
spider_head query.log:
57 Query select min(id), max(id), avg(id), bit_xor(id), min(id2), max(id2), avg(id2), bit_xor(id2), count(*) FROM t3
|
57 Query mysql spider-datanode1.example.com set session transaction isolation level read committed;set session autocommit = 1;start transaction
|
57 Query mysql spider-datanode1.example.com select `id`,`id2` from `db`.`t3` order by `id`,`id2` limit 1
|
57 Query mysql spider-datanode2.example.com set session transaction isolation level read committed;set session autocommit = 1;start transaction
|
57 Query mysql spider-datanode2.example.com select `id`,`id2` from `db`.`t3` order by `id`,`id2` limit 1
|
57 Query mysql spider-datanode3.example.com set session transaction isolation level read committed;set session autocommit = 1;start transaction
|
57 Query mysql spider-datanode3.example.com select `id`,`id2` from `db`.`t3` order by `id`,`id2` limit 1
|
57 Query mysql spider-datanode4.example.com set session transaction isolation level read committed;set session autocommit = 1;start transaction
|
57 Query mysql spider-datanode4.example.com select `id`,`id2` from `db`.`t3` order by `id`,`id2` limit 1
|
57 Query mysql spider-datanode1.example.com select `id`,`id2` from `db`.`t3` order by `id` desc,`id2` desc limit 1
|
57 Query mysql spider-datanode2.example.com select `id`,`id2` from `db`.`t3` order by `id` desc,`id2` desc limit 1
|
57 Query mysql spider-datanode3.example.com select `id`,`id2` from `db`.`t3` order by `id` desc,`id2` desc limit 1
|
57 Query mysql spider-datanode4.example.com select `id`,`id2` from `db`.`t3` order by `id` desc,`id2` desc limit 1
|
57 Query mysql spider-datanode1.example.com select `id`,`id2` from `db`.`t3` order by `id2` limit 1
|
57 Query mysql spider-datanode2.example.com select `id`,`id2` from `db`.`t3` order by `id2` limit 1
|
57 Query mysql spider-datanode3.example.com select `id`,`id2` from `db`.`t3` order by `id2` limit 1
|
57 Query mysql spider-datanode4.example.com select `id`,`id2` from `db`.`t3` order by `id2` limit 1
|
57 Query mysql spider-datanode1.example.com select `id`,`id2` from `db`.`t3` order by `id2` desc limit 1
|
57 Query mysql spider-datanode2.example.com select `id`,`id2` from `db`.`t3` order by `id2` desc limit 1
|
57 Query mysql spider-datanode3.example.com select `id`,`id2` from `db`.`t3` order by `id2` desc limit 1
|
57 Query mysql spider-datanode4.example.com select `id`,`id2` from `db`.`t3` order by `id2` desc limit 1
|
57 Query mysql spider-datanode1.example.com select `id`,`id2` from `db`.`t3` order by `id`,`id2`
|
57 Query mysql spider-datanode2.example.com select `id`,`id2` from `db`.`t3` order by `id`,`id2`
|
57 Query mysql spider-datanode3.example.com select `id`,`id2` from `db`.`t3` order by `id`,`id2`
|
57 Query mysql spider-datanode4.example.com select `id`,`id2` from `db`.`t3` order by `id`,`id2`
|
57 Query mysql spider-datanode1.example.com commit
|
57 Query mysql spider-datanode2.example.com commit
|
57 Query mysql spider-datanode3.example.com commit
|
57 Query mysql spider-datanode4.example.com commit
|
57 Quit
|
datanode1 mysqld.log:
2018-06-19 19:06:10 4017254 [Warning] Aborted connection 4017254 to db: 'db' user: 'spider_user' host: 'spider-head.example.com' (Got timeout reading communication packets)
|
datanode1 query.log:
4016452 Connect spider_user@spider-head.example.com as anonymous on
|
4016452 Query set session transaction isolation level read committed;set session autocommit = 1;start transaction
|
4016452 Query SET NAMES latin1
|
4016452 Init DB db
|
4016452 Query select `id`,`id2` from `db`.`t3` order by `id`,`id2` limit 1
|
4016452 Query select `id`,`id2` from `db`.`t3` order by `id` desc,`id2` desc limit 1
|
4016452 Query select `id`,`id2` from `db`.`t3` order by `id2` limit 1
|
4016452 Query select `id`,`id2` from `db`.`t3` order by `id2` desc limit 1
|
4016452 Query select `id`,`id2` from `db`.`t3` order by `id`,`id2`
|
4016452 Query commit
|
4016452 Quit
|
|