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)

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Jerry的SAP技术分享

SAP S4CRM 1811 服务订单API介绍

Jerry在今年2月28日,SAP Customer Management for S/4HANA 1.0正式问世这个具有纪念意义的日子,同时发布了中英文版的博...

1763
来自专栏smy

抛弃vue-resource拥抱axios

vue-resource用法 import Vue from 'vue' import VueResource from 'vue-resource' Vue....

5795
来自专栏Jerry的SAP技术分享

一些SAP Partners能够通过二次开发实现打通C4HANA和S4HANA

有好几位朋友在公众号后台给我留言询问SAP C/4HANA和S/4HANA集成的方案。

1290
来自专栏晨星先生的自留地

一次不完全成功的渗透

2455
来自专栏机器人网

牛人教你DIY!用树莓派 Zero做飞控图传

虽然可以买到商品成品,且会比我们做出来的东西更美观,性能指标更强,但是在折腾中学习,按照自己的需求进行优化改动,享受创造的喜悦,不正是创客精神嘛。 本项目使用手...

6288
来自专栏杨建荣的学习笔记

多套Oracle 10g整合迁移到11g的方案

在数据迁移中,除了跨平台,全量,增量数据迁移之外,还有一类会把已有的难度升级,那就是整合式迁移,比如原来有两个数据,迁移后是一个,类似这样的需求,如果再加...

3943
来自专栏bboysoul

linux修改DNS地址

不知道大家有没有这样的感觉,就是上网的时候域名解析很慢,是什么原因呢?当然是可恶的运营商,垃圾的运营商了,现在我教大家修改自己的DNS解析地址来加快上网的速度

5733
来自专栏Java后端技术栈

从今天起让我们忘记Java中的get/set方法吧!

今天给大家推荐一款神器!让我们可以以一种更优雅的姿势编写我们的get/set方法。

782
来自专栏杨建荣的学习笔记

Oracle 18c初体验

如果有一段时间不接触Oracle或者是不大了解Oracle,其实很容易会产生误解。以为12c到18c之间差距的版本应该还有13c,14c,...17c等...

2690
来自专栏大神带我来搬砖

做大数据再不用提心吊胆了!

前几天,家住北京市朝阳区的程序员诸葛建国非常郁闷,他突然接到客户电话,说公司网站没法正常工作了,登上服务器一看,他大吃了一惊——服务器的硬盘满了!! 当时诸葛...

3126

扫码关注云+社区

领取腾讯云代金券