Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
3.2.1
-
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.