乐趣区

关于事务:技术分享-大数据量更新回滚效率提升方法

作者:周启超
爱可生北分团队 DBA,次要负责项目前期建设及前期疑难问题反对。做事认真,对事负责。
本文起源:原创投稿
* 爱可生开源社区出品,原创内容未经受权不得随便应用,转载请分割小编并注明起源。


咱们常常会遇到操作一张大表,发现操作工夫过长或影响在线业务了,想要回退大表操作的场景。在咱们进行大表操作之后,期待回滚是一个很漫长的过程,只管你可能对晓得一些缩短工夫的办法,处于对生产环境数据完整性的敬畏,也会抉择不做染指。最终抉择不作为的起因大多源于对操作影响的不确定性。实际出真知,上面针对两种次要晋升事务回滚速度的形式进行验证,一种是晋升操作可用内存空间,一种是通过停实例,禁用 redo 回滚形式进行进行验证。

仔细阅读过官网手册的同学,肯定留意到了对于晋升大事务回滚效率,官网提供了两种办法:一是减少 innodb_buffer_pool_size 参数大小,二是正当利用 innodb_force_recovery=3 参数,跳过事务回滚过程。第一种形式比拟温和,innodb_buffer_pool_size 参数是能够动静调整的,可行性也较高。第二种形式相较之下较暴力,但成果较好。

上面咱们看下第一种形式的成果如何:

mysql>set global innodb_buffer_pool_size = 1073741824;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> use sbtest;
Database changed
mysql> update sbtest1 set k=k+1;                                                                                                                                   
Query OK, 16023947 rows affected (7 min 23.23 sec)
Rows matched: 16023947  Changed: 16023947  Warnings: 0
mysql>
mysql> set global innodb_buffer_pool_size = 5368709120;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%uffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 5368709120     |
+-------------------------------------+----------------+
10 rows in set (0.02 sec)
mysql> rollback;
Query OK, 0 rows affected (6 min 39.41 sec)

最后更新操作用时 7 min 23.23 sec 回滚操作用时 6 min 39.41 sec 相较于更新操作回滚操作耗时缩短了将近一分钟,成果仿佛并不显著。当然回滚工夫和更新操作工夫进行比照不太谨严,上面对不同大小 innodb_buffer_pool_size 条件状况下更新和回滚操作工夫进行一个汇总。

咱们能够看到 innodb_buffer_pool_size 设置大于数据量大小时,大表操作工夫才会有较显著的降落。

试验的时候会发现 /opt/mysql/data/3400/ibdata1 零碎表空间操作较多,这是在进行 double write 操作。

# pt-ioprofile --profile-pid=7777 --aggregate=sum --cell=count --group-by=filename --save-samples=5.txt --run-time=500
Sun Oct 11 12:55:35 UTC 2020
Tracing process ID 7777
     total       read   pwrite64      write      fsync       open      lseek filename
     12287          0       6941          0       5346          0          0 /opt/mysql/data/3400/ibdata1
      5010          0          0          0       5010          0          0 /opt/mysql/data/3400/sbtest/sbtest1.ibd
      4837          0          0       4837          0          0          0 /opt/mysql/tmp/3400/MLfgQmPl
      2291          0        372          0       1361          0        558 /opt/mysql/data/3400/undo003
      1066          0        421          0        645          0          0 /opt/mysql/log/redolog/3400/ib_logfile0
         4          1          0          1          0          1          1 /opt/mysql/data/3400/mysql/slow_log.CSV
         1          0          0          1          0          0          0 /opt/mysql/data/3400/mysql-slow.log
# pt-ioprofile --profile-pid=7777 --aggregate=sum --cell=count --group-by=filename --save-samples=6.txt --run-time=500
Sun Oct 11 13:07:44 UTC 2020
Tracing process ID 7777
     total       read    pread64   pwrite64      write      fsync      lseek  ftruncate filename
     11206          0          0       6453          0       4753          0          0 /opt/mysql/data/3400/ibdata1
      4515          0         30          0          0       4485          0          0 /opt/mysql/data/3400/sbtest/sbtest1.ibd
      1266          0          1          0          0       1265          0          0 /opt/mysql/data/3400/undo003
       914          0          0        361          0        553          0          0 /opt/mysql/log/redolog/3400/ib_logfile0
         3          1          0          0          1          0          1          0 /opt/mysql/data/3400/mysql/slow_log.CSV
         3          0          0          0          1          0          1          1 /opt/mysql/tmp/3400/MLfgQmPl
         1          0          0          0          1          0          0          0 /opt/mysql/data/3400/mysql-slow.log

禁用 double write 并不能给性能带来性能晋升。如下为统计信息:

通过验证,在做大数据量操作长期调大 innodb_buffer_pool_size 对大事务更新和回滚是有肯定成果的。

第二种办法操作流程如下:

kill -9 MySQL 过程;备份 MySQL 数据及日志目录;为 mysql server 设置 innodb_force_recovery=3 参数;而后启动 MySQL 过程;失常敞开 MySQL Server 过程;去掉 innodb_force_recovery=3 参数启动 MySQL 过程。实现复原过程。(innodb_force_recovery 这个参数个别用于 ” 重大故障排除场景 ”,生产环境慎用,若用于生产环境需首先明确 innodb_force_recovery 设置对现有环境数据可能的影响状况)。

在实现最初启动操作之后,谬误日志中会记录一条“[Note] InnoDB: Rollback of non-prepared transactions completed”信息。此形式无需期待事务回滚操作,实现上述操作步骤的工夫即为环境复原的工夫。

总结

两种形式各有本人的长处,第一种形式对线上业务零碎影响较小,不会中断在线业务。第二种形式成果更显著,会短暂影响业务间断,回滚所有没有提交的事务。

退出移动版