细节
-
错误
-
状态: Done">完成
-
媒介
-
解决方法: 固定
-
3.2.1之上
-
没有一个
-
没有一个
媒介-
1
-
平台冲刺33
描述
设置主与一个大表
SET GLOBAL innodb_flush_log_at_trx_commit=2;SET GLOBAL sync_binlog=1000000;SET GLOBAL innodb_buffer_pool_size=2*1024*1024*1024;使用测试;删除表如果存在' joinit ';创建表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
在副本
SET GLOBAL innodb_flush_log_at_trx_commit=1;SET GLOBAL sync_binlog=1;SET GLOBAL innodb_buffer_pool_size=128*1024*1024;
然后运行
PTDEBUG = 1 . ./pt-archiver——清除——where"g < 45"——commit-each——limit=500——statistics——source“h = localhost, S = / tmp / mysql_sandbox19731.sock, D =测试,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-archive .debug 2>&1
一旦副本被延迟，你将看到一个事务打开，持有1行锁和1个撤销日志条目:
——TRANSACTION 4287278, ACTIVE 2 sec 2 lock struct(s)，堆大小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
一旦您运行了一些工作负载……
Sysbench——mysql-host=127.0.0.1——mysql-port=19731——mysql-user=root——mysql-password=msandbox——mysql-db=test /usr/share/sysbench/oltp_read_writeLua——tables=100——threads=6——table_size=100——time=1000000——report-interval=1——db-ps-mode=disable——range-size=9205——skip-trx=假——point-selects=0——simple-ranges=0——sum-ranges=0——order-ranges=0——distinct-ranges=0——index-updates=3——non-index-updates=0
.．.历史名单将飞天火箭:
~ $而真正的；做{./使用-e查询引擎innodb状态\ g| grep历史;睡眠1;} done历史表长度174403历史表长度186116历史表长度191834历史表长度193754历史表长度194419历史表长度195433历史表长度199125历史表长度203649历史表长度213459历史表长度222541
这是非常糟糕的，因为大量的历史列表将严重影响性能。
参见所附的PTDEBUG输出:pt-archiver.debug
建议的修复方法是:在等待时不保持事务打开。