Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1898

pt-archiver keeps transaction open while waiting for replica to catch up

Details

    • Bug
    • Status: Done
    • Medium
    • Resolution: Fixed
    • 3.2.1
    • 3.3.0
    • None
    • None
    • 1

    Description

      Setup master with a large table

      -- give master some advantage on performance
      SET GLOBAL innodb_flush_log_at_trx_commit=2;
      SET GLOBAL sync_binlog=1000000;
      SET GLOBAL innodb_buffer_pool_size=2*1024*1024*1024;
      USE test;
       
      DROP TABLE IF EXISTS `joinit`;
       
      CREATE TABLE `joinit` (
       `i` int(11) NOT NULL AUTO_INCREMENT,
       `s` varchar(64) DEFAULT NULL,
       `t` time NOT NULL,
       `g` int(11) NOT NULL,
       PRIMARY KEY (`i`),
       KEY g_idx (g)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      
      INSERT INTO joinit VALUES (NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )));
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; -- +256 rows
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; -- +512 rows
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; -- +1024 rows
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; 
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; 
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; 
      INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; -
      ...run few more so it has a few million rows
      

       

       

      On replica

      SET GLOBAL innodb_flush_log_at_trx_commit=1;
      SET GLOBAL sync_binlog=1;
      SET GLOBAL innodb_buffer_pool_size=128*1024*1024;

       

       

      Then run

       

      PTDEBUG=1 ../pt-archiver --purge --where "g < 45" --commit-each --limit=500 --statistics --source "h=localhost,S=/tmp/mysql_sandbox19731.sock,D=test,t=joinit,u=msandbox,p=msandbox" --check-slave-lag="h=localhost,S=/tmp/mysql_sandbox19732.sock,u=msandbox,p=msandbox" --max-lag=10 > /tmp/pt-archiver.debug 2>&1

       

       

      As soon as replica is delayed you will see a transaction open holding 1 row lock and 1 undo log entry:

      ---TRANSACTION 4287278, ACTIVE 2 sec
      2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
      MySQL thread id 45, OS thread handle 140647079315200, query id 15158693 localhost msandbox
      Trx read view will not see trx with id >= 4287278, sees < 4287278

       

      And as soon as you run some workload...

      sysbench --mysql-host=127.0.0.1 --mysql-port=19731 --mysql-user=root --mysql-password=msandbox --mysql-db=test /usr/share/sysbench/oltp_read_write.lua --tables=100 --threads=6 --table_size=100 --time=1000000 --report-interval=1 --db-ps-mode=disable --range-size=9205 --skip-trx=false --point-selects=0 --simple-ranges=0 --sum-ranges=0 --order-ranges=0 --distinct-ranges=0 --index-updates=3 --non-index-updates=0 run

       

      ...history list will sky rocket:

      ~$ while true; do { ./use -e "SHOW ENGINE INNODB STATUS\G"|grep History; sleep 1; } done
      History list length 174403
      History list length 186116
      History list length 191834
      History list length 193754
      History list length 194419
      History list length 195433
      History list length 199125
      History list length 203649
      History list length 213459
      History list length 222541

      This is quite bad, as large history list will heavily impact performance.

       

      See attached PTDEBUG output for above run: pt-archiver.debug

      Suggested fix is: do not hold transaction open while waiting. 

       

      Attachments

        Activity

          People

            carlos.salguero Carlos Salguero (Inactive)
            marcos.albe Marcos Albe
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Smart Checklist