Mysql technology insider InnoDB Insert (Insert) operation, mysqlinnodb

Mysql technology insider InnoDB Insert (Insert) operation, mysqlinnodb

What is the internal reaction of an innodb table after the client sends an insert command? This article will reveal this story to everyone. Of course, I am not very easy to learn. If you find anything wrong, you can point it out and discuss it together.

I suddenly found it difficult to explain this process in words. Instead, I would like to use a picture to make it clearer. I have not figured out the problem: the write time of bin_log, the location of the commit operation corresponding to redo and innodb_buffer respectively.
The above content is yesterday, and today we have found a new one. Please ignore it consciously. I didn't draw binlog_buffer in the figure .. What I want to say is that after the transaction is started, whether the data write disk is before the commit operation or after the commit operation, you may think this problem is ridiculous, I think that commit is definitely before writing data to a disk, but my next experiment may change your mind. Now let's make a hypothesis: Our innodb_buffer is 1 GB, while an insert operation actually inserts 2 GB of data. Are you sure you want to write the disk after commit? The experiment is as follows:

The procedure is as follows:
1. Enable manual transaction commit

Mysql> show variables like 'autocommit ';
+ ----- + --- +
| Variable_name | Value |
+ ----- + --- +
| Autocommit | OFF |
+ ----- + --- +
1 row in set (0.00 sec)

2. Create the test. test innodb table

Mysql> create table test (id bigint, salary bigint );
Query OK, 0 rows affected (0.05 sec)

3. Check the initial table space size (forgive me for being left empty. I called a stored procedure at the beginning and did not enable manual submission, starting from 12 Mb)

Rw-rw -- 1 mysql 8590 July 7 21:07 test. frm
-Rw -- 1 mysql 12582912 July 7 21:19 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 12 M July 7 21:19 test. ibd
4. Use the stored procedure to insert data (the VM is too slow. data records are inserted and 50 seconds are used)
Mysql> call t (1000000 );
Query OK, 1 row affected (50.94 sec)

5. Check the table space size. (You can insert and view the table space. It's a shame)

Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 12 M July 7 21:19 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 44 M July 7 21:21 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 44 M July 7 21:21 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 44 M July 7 21:21 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 48 M July 7 21:21 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 48 M July 7 21:21 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 48 M July 7 21:21 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 48 M July 7 21:21 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 48 M July 7 21:21 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 52 M July 7 21:21 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 52 M July 7 21:21 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 52 M July 7 21:21 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 52 M July 7 21:21 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 54 M July 7 21:21 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 60 M July 7 21:36 test. ibd

Well, the tablespace is growing constantly .. I think the only explanation is that the data has been written to the disk before the commit operation.

I was curious to perform the rollback operation to check the table space. It took 24.65 seconds to roll back million pieces of data.
+ ----- + --- +
1 row in set (0.00 sec)

Mysql> rollback;
Query OK, 0 rows affected (24.65 sec)

Then, check the tablespace size.

-Rw -- 1 mysql 60 M July 7 21:36 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 60 M July 7 21:36 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 60 M July 7 21:36 test. ibd

However, the tablespace is not reduced.

I like to break through the sandpot and try again to see if the table space will increase,

Mysql> call t (1000000 );
Query OK, 1 row affected (38.14 sec)

-Rw -- 1 mysql 60 M July 7 21:36 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 60 M July 7 21:36 test. ibd
[Root @ ashe test] # ls-lh test. ibd
-Rw -- 1 mysql 60 M July 7 21:49 test. ibd
[Root @ ashe test] #

Madan, 60 M, I think I should read more about the innodb kernel. I should even look at its source code to find my doubts.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.