Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to understand the truncation in MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Undo truncation in MySQL is still a nice feature. This reminds me of seeing a big ibdata a long time ago, but there is nothing I can do with it. The only way to shrink it is to rebuild or reconstruct the data.

Oracle has been used for a long time, and there are always some ideas about how a seemingly common technology can't be done in MySQL. Of course, this idea has been going on for some time.

MySQL 5.6in the undo to do a stripping, you can specify a separate undo table space, but to shrink the phase is still powerless, this is also a transitional feature of it, to MySQL 5.7, this function can be said to be on the right path, we can truncate, passive into active, this way is very good.

If you want to look forward to this feature, I think it can be continuously improved, that is, you can modify it online, switch undo, and so on.

To achieve this phase of functionality, it will take some work, that is, to complete these basic configurations at initialization time, otherwise you will receive some vague messages like this.

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 so we're going to initialize a whole new library for a simple test.

The content of my.cnf is as follows, you can specify it according to your needs.

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

The focus is on the last few parameters.

Initialize the data dictionary first

Mysqld-- initialize-- user=mysql-- basedir=/usr/local/mysql-- datadir=/home/mysql then configure so that the MySQL service starts

When service mysql start opens the file directory, you will be surprised to see the following undo files because of the parameters

The innodb_undo_tablespaces is 4, so there will be 4 files. The default number of innodb_undo_logs is 128, or at least 35, and the official website also has a detailed explanation. The innodb_undo_directory directory is obtained by default according to the data directory, so I simply removed it. The four Undo files are all 10m, which is the initial size.

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 We initialize the data and then insert some data.

Mysql > create table test_undo (id int,name varchar (30))

Mysql > insert into test_undo values (1)

Mysql > insert into test_undo values (2)

Insert can be executed repeatedly, and even if the data grows exponentially, the transaction will gradually become larger. When the data volume is about 2 million, the distribution of undo is as follows:

-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 when the undo004 data is around 16 million, the size of the undo file is as follows:

-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 at this time we start to test the truncated part, which is closely related to one parameter, which is innodb_purge_rseg_truncate_frequency. We can set it smaller appropriately for testing, so that we can see the effect immediately. For example, I set it to 20.

Mysql > set global innodb_purge_rseg_truncate_frequency=20; then I continue to open a large transaction, insert tens of millions of data, undo files will soar, of course, because the largest transaction occupies a undo file, that file will continue to grow, although exceeding the set threshold.

-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 and then we use a little trick to delete a few pieces of data to trigger the tipping point of truncation.

Mysql > delete from test_undo limit 10; look again, the undo file will shrink, of course, you can see clearly, not all, because it also has something to do with the size of the transaction.

-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

The above is the editor for you to share how to understand the truncation in MySQL, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.