Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16530

Spider datanodes needs adjusted wait_timeout for long running queries on spider head node

Details

    Description

      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
      

      Attachments

        Issue Links

          Activity

            Add the following parameters.

            • spider_remote_wait_timeout
              Set remote wait_timeout at connecting for improvement performance of
              connection if you know.
              -1,0 : No set.
              1 or more : Seconds.
              The default value is -1
            • spider_wait_timeout
              The wait time to remote servers.
              -1,0 : No set.
              1 or more : Seconds.
              The default value is 604800

            3a683fc

            Kentoku Kentoku Shiba (Inactive) added a comment - Add the following parameters. spider_remote_wait_timeout Set remote wait_timeout at connecting for improvement performance of connection if you know. -1,0 : No set. 1 or more : Seconds. The default value is -1 spider_wait_timeout The wait time to remote servers. -1,0 : No set. 1 or more : Seconds. The default value is 604800 3a683fc

            Would you please review this changes?

            Kentoku Kentoku Shiba (Inactive) added a comment - Would you please review this changes?

            Okay to push

            sachin.setiya.007 Sachin Setiya (Inactive) added a comment - Okay to push

            merged into 10.4 tree

            Kentoku Kentoku Shiba (Inactive) added a comment - merged into 10.4 tree

            People

              Kentoku Kentoku Shiba (Inactive)
              mattiasjonsson Mattias Jonsson
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.