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

This article mainly explains "MySQL innodb transaction implementation". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian to study and learn "MySQL innodb transaction implementation" together.

【1】Redo Log

In Innodb storage engine, transaction log is implemented through redo and Innodb storage engine log buffer. When a transaction is started, the lsn(log sequence number) of the transaction is recorded; when the transaction is executed, the log of InnoDB storage engine is recorded.

When a transaction commits, the storage engine log buffer must be written to disk (controlled by innodb_flush_log_at_trx_commit), that is, the log needs to be written before writing data. This approach is called "pre-write logging,"

innodb ensures transaction integrity in this way. This means that the data pages stored on disk and the pages on the memory buffer pool are not synchronized, and the redo log is written first, and then the data file is written, so it is an asynchronous way. Observe the gap by showing engine innodb status\G

--Create a table z, and then create a stored procedure load_test that imports data into the table. Observe the current redo log status by using the command show engine innodb status

create table z(a int, primary key(a))engine=innodb;DELIMITER $$DROP PROCEDURE IF EXISTS `test`.` load_test`$$CREATE PROCEDURE load_test(COUNT INT)BEGIN DECLARE i INT UNSIGNED DEFAULT 0; START TRANSACTION; WHILE i

< COUNT DO REPLACE INTO z SELECT i; -- 用replace是因为需要重复调用，避免主键重复insert报错。 SET i=i+1; END WHILE; COMMIT;END;$$ ...... --- LOG --- Log sequence number 20499052099 当前的LSN Log flushed up to 20499052099 表示刷新到redo log的LSN Pages flushed up to 20499052099 表示刷新到磁盘的lsn Last checkpoint at 20499052099 0 pending log writes, 0 pending chkp writes 373 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 1270857 Buffer pool size 8191 Free buffers 7562 Database pages 612 Old database pages 205 Modified db pages 0 Pending reads 0 ...... mysql>

call test.load_test(100000);

Query OK, 0 rows affected, 1 warning (6.28 sec)

mysql> show engine innodb status\G

......

---

LOG

---

Log sequence number 20504734913

Log flushed up to 20504734913

Pages flushed up to 20504734913

Last checkpoint at 20504734913

0 pending log writes, 0 pending chkp writes

398 log i/o's done, 0.38 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

......

Log sequence number and Log flushed up and Pages flushed u are still the same.

Import more data points and reopen another window to see innodb status.

mysql> call test.load_test(1000000);

mysql> show engine innodb status\G

......

---

LOG

---

Log sequence number 20527044411

Log flushed up to 20525763843

Pages flushed up to 20517902997

Last checkpoint at 20504829471

0 pending log writes, 0 pending chkp writes

432 log i/o's done, 1.26 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Log sequence number > Log flushed up > Pages flushed up > Last checkpoint, so you can also see from here, write redo logs first, then write data files.

On the productiion that is actually written frequently, these three values will be different.

【2】Undo

The undo record is the opposite of redo, insert becomes delete, update becomes update, redo is placed in redo file. Undo is stored in an internal special segment in a shared table space (ibdata1 or ibdata2).

py_innodb_page_info.py

Download address: wget http://david-mysql-tools.googlecode.com/svn-history/r15/trunk/py_innodb_page_type/py_innodb_page_info.py

[root@mysql data56]# python /root/py_innodb_page_info.py /data56/ibdata1

Traceback (most recent call last):

File "/root/py_innodb_page_info.py", line 3, in

import mylib

ImportError: No module named mylib

[root@mysql data56]#

You also need to download the mylib.py and include.py files and place them in a directory with py_innodb_page_info.py

wget http://david-mysql-tools.googlecode.com/svn-history/r15/trunk/py_innodb_page_type/mylib.py

wget http://david-mysql-tools.googlecode.com/svn-history/r15/trunk/py_innodb_page_type/include.py

PS: Sometimes the network will break down, and you have to execute wget several times to download it.

[root@mysql ~]# python /root/py_innodb_page_info.py /data56/ibdata1

Total number of page: 8960:

Insert Buffer Free List: 1035

Insert Buffer Bitmap: 1

System Page: 134

Transaction system Page: 1

Freshly Allocated Page: 4010

Undo Log Page: 1556

File Segment inode: 5

B-tree Node: 2217

File Space Header: 1

[root@mysql ~]#

[root@mysql ~]#

undo is not a physical recovery, it is a logical recovery because it is achieved by executing the opposite dml statement. And it doesn't recycle new pages added because of inserts and updates.

Undo page recycling is done through the master thread.

Verify how long it takes for binlog produced in row mode to pass to slave library!

derived

time /usr/local/mysql56/bin/mysqldump --socket=/data56/mysql.sock -uroot -p123456 -P3307 --extended-insert=false --all-databases> /root/adb.sql

[root@mysql ~]# time /usr/local/mysql56/bin/mysqldump --socket=/data56/mysql.sock -uroot -p123456 -P3307 --extended-insert=false --all-databases> /root/adb.sql

Warning: Using a password on the command line interface can be insecure.

real 5m10.757s

user 2m42.137s

sys 0m11.346s

import

time /usr/local/mysql56/bin/mysql --socket=/data56/mysql.sock -uroot -p123456 -P3307

< /root/adb.sql 在从库上面检查： mysql>

show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.88.49.119

Master_User: repl

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql56-bin.000008

Read_Master_Log_Pos: 513272653

Relay_Log_File: mysql56-relay-bin.000019

Relay_Log_Pos: 103964146

Relay_Master_Log_File: mysql56-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 418536570

Relay_Log_Space: 513273109

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 857

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 11901

Master_UUID: a6a1d870-80b5-11e2-84d2-00155d016a07

Master_Info_File: /data56/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: System lock

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

ps: Seconds_Behind_Master: 857, there is a delay, you can see in row mode, import6G monotonic insert sql, there is a delay.

Thank you for reading, the above is the "MySQL innodb transaction implementation" content, after the study of this article, I believe that everyone has a deeper understanding of the MySQL innodb transaction implementation, the specific use of the situation also needs to be verified. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

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.