专栏首页杨建荣的学习笔记MySQL中的undo截断(r11笔记第89天)

MySQL中的undo截断(r11笔记第89天)

MySQL中的undo截断还是一个很不错的特性。这让我想起了很久以前看到一个诺大的ibdata,但是却拿它无能为力,想把它收缩唯一的办法就是重建或者重构数据。

Oracle用得久了,总会有一些想法,看起来很平常的技术怎么在MySQL中却无能为力。当然这个念头也有些日子了。

MySQL 5.6中把undo做了剥离,可以指定单独的undo表空间,但是要收缩阶段还是无能为力,这个也算是一个过渡的特性吧,到了MySQL 5.7中,这个功能就可以说是上了正道了,我们可以截断,化被动为主动,这种方式就很好。

而如果要展望这个特性,我觉得还可以持续改进,就是可以在线修改,切换undo等。

要实现这个阶段功能,其实还需要花点功夫,那就是在初始化的时候就完成这些基础配置,否则会收到下面这样有些模糊的提示信息。

2017-02-28 22:39:48 7fedca8127e0 InnoDB: Expected to open 1 undo tablespaces but was able 2017-02-28 22:39:48 7fedca8127e0 InnoDB: to find only 0 undo tablespaces. 2017-02-28 22:39:48 7fedca8127e0 InnoDB: Set the innodb_undo_tablespaces parameter to the 2017-02-28 22:39:48 7fedca8127e0 InnoDB: correct value and retry. Suggested value is 0所以我们打算初始化一个全新的库来做一个简单的测试。

my.cnf的内容如下,你可以自己根据需要指定也可以。

my.cnf [client] socket = /home/mysql/mysql.sock [mysql] socket = /home/mysql/mysql.sock default-character-set = utf8 [mysqld] user = mysql basedir = /usr/local/mysql datadir = /home/mysql port = 3306 socket = /home/mysql/mysql.sock pid-file = /home/mysql/mysql.pid max_allowed_packet = 32M ft_min_word_len = 4 event_scheduler = 1 explicit_defaults_for_timestamp=true tmpdir = /dev/shm character-set-server = utf8 #innodb_undo_directory=/data/undolog innodb_undo_tablespaces=4 innodb_undo_logs=128 innodb_max_undo_log_size=200M innodb_purge_rseg_truncate_frequency innodb_undo_log_truncate=1 重点就是最后的几个参数了。

先初始化一下数据字典,

mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql

然后配置使得MySQL服务启动

service mysql start

打开文件目录,就会赫然看到下面的几个undo文件,因为参数

innodb_undo_tablespaces为4,所以会有4个文件。innodb_undo_logs默认是128个,至少是35个,官网也有详细的解释。而innodb_undo_directory的目录则默认按照数据目录来取得,所以我索性去掉了。4个Undo文件都是10M,也就是初始大小。

drwxr-x--- 2 mysql mysql 8192 Feb 28 23:09 performance_schema drwxr-x--- 2 mysql mysql 8192 Feb 28 23:09 sys -rw-r----- 1 mysql mysql 10485760 Feb 28 23:09 undo001 -rw-r----- 1 mysql mysql 10485760 Feb 28 23:09 undo002 -rw-r----- 1 mysql mysql 10485760 Feb 28 23:09 undo003 -rw-r----- 1 mysql mysql 10485760 Feb 28 23:09 undo004

我们初始化数据,然后插入一些数据。

mysql> create table test_undo(id int,name varchar(30)); mysql> insert into test_undo values(1,'a'); mysql> insert into test_undo values(2,'b'); insert可以反复执行,数据就是指数级的增长,事务也会逐渐变大,大概在200万数据量的时候,undo的分布如下:

-rw-r----- 1 mysql mysql 13631488 Feb 28 23:16 undo001 -rw-r----- 1 mysql mysql 22020096 Feb 28 23:16 undo002 -rw-r----- 1 mysql mysql 10485760 Feb 28 23:16 undo003 -rw-r----- 1 mysql mysql 10485760 Feb 28 23:16 undo004

数据在1600万左右的时候,undo文件的大小如下:

-rw-r----- 1 mysql mysql 13631488 Feb 28 23:20 undo001 -rw-r----- 1 mysql mysql 22020096 Feb 28 23:20 undo002 -rw-r----- 1 mysql mysql 150994944 Feb 28 23:20 undo003 -rw-r----- 1 mysql mysql 75497472 Feb 28 23:20 undo004

这个时候我们开始测试一下截断的部分,和一个参数密切相关,那就是innodb_purge_rseg_truncate_frequency,我们可以为了测试,适当设置小一些,能够马上看到效果,比如我设置为20

mysql> set global innodb_purge_rseg_truncate_frequency=20;

然后我继续开启一个很大的事务,插入千万数据,undo的文件就会暴增,当然因为最大的事务占用了一个undo文件,那个文件还是会持续增大,尽管超过了设定的阈值。

-rw-r----- 1 mysql mysql 13631488 Feb 28 23:33 undo001 -rw-r----- 1 mysql mysql 22020096 Feb 28 23:33 undo002 -rw-r----- 1 mysql mysql 150994944 Feb 28 23:33 undo003 -rw-r----- 1 mysql mysql 293601280 Feb 28 23:33 undo004

然后我们使点小技巧,delete几条数据,触发截断的临界点。

mysql> delete from test_undo limit 10;

再次查看,undo的文件就会收缩,当然你可以清晰的看到,不是所有的,因为和事务大小也有关系。

-rw-r----- 1 mysql mysql 13631488 Feb 28 23:34 undo001 -rw-r----- 1 mysql mysql 22020096 Feb 28 23:34 undo002 -rw-r----- 1 mysql mysql 150994944 Feb 28 23:34 undo003 -rw-r----- 1 mysql mysql 10485760 Feb 28 23:34 undo004

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes),作者:杨建荣

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-02-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 最近的几个技术问题总结和答疑 (r8笔记第19天)

    笔记写了不少,有时候有的朋友问我几个关键字,我就会从脑海里进行搜索,凡是写过的,搜索一下总能找到,帮助了别人,提高了自己,何乐而不为。 但是笔记写了很多...

    jeanron100
  • CentOS7压力测试MGR

    最近在规划CentOS7版本中的MySQL测试情况,于是找了公司内部的虚拟机来做下模拟测试。

    jeanron100
  • MySQL 5.6, 5.7并行复制测试(r12笔记第9天)

    对于主从延迟,其实一直以来就是一个颇有争议的话题,在MySQL阵营中,如果容忍一定的延迟的场景,通过主从来达到读写分离是个很不错的方案,但是延迟率到底有多...

    jeanron100
  • Mac下MySQL忘记密码重置

    在上述指令运行后,新开一个终端,同时保持原来那个终端也开着,在新的终端输入指令如下:

    代码咖啡
  • mysql 语法集锦

    使用SHOW语句找出在服务器上当前存在什么数据库: mysql> SHOW DATABASES; 创建一个数据库MYSQLDATA mysql> CREATE...

    赵腰静
  • mysql 命令小集锦

    1:使用SHOW语句找出在服务器上当前存在什么数据库: mysql> SHOW DATABASES; 2:创建一个数据库MYSQLDATA mysql> CRE...

    赵腰静
  • 部署(2.同步数据库)

    1.使用Navicat for MySQL软件,新建MySQL连接,与云服务器的数据库取得远程链接

    玩蛇的胖纸
  • MySql常用命令总结

    1:使用SHOW语句找出在服务器上当前存在什么数据库: mysql> SHOW DATABASES; 2:2、创建一个数据库MYSQLDATA mysql> C...

    叫我可儿呀
  • reset master、reset slave与reset slave all

    reset master、reset slave与reset slave all 今天测一测这几个参数,首先说下测试环境:

    AsiaYe
  • 1.django restframework 项目部署到ubuntu18.04上(同步数据库)

    选择本地要传输到服务器的数据库,然后选择数据库连接,点击开始,确定,然后等待传输完成,关闭。

    玩蛇的胖纸

扫码关注云+社区

领取腾讯云代金券