REDO parsing in innodb

The INNODB Storage engine pre-writes logs to ensure transaction integrity. This means that the data pages stored on the disk and the pages in the memory buffer pool are not synchronized.

The INNODB Storage engine pre-writes logs to ensure transaction integrity. This means that the data pages stored on the disk and the pages in the memory buffer pool are not synchronized.

In the innodb Storage engine, transaction logs are implemented by redo log files and innodb log buffer. When a transaction starts, an LSN (Log sequence number) of the transaction is recorded. when the transaction is executed, the transaction Log is inserted into the buffer pool of the innodb Storage engine. when the transaction is committed, you must write the log buffer of the innodb crude talent engine to the disk (default implementation: innodb_flush_log_at_trx_commit = 1). That is, before writing data, you must first write logs, this method is used to pre-write logs (write-ahead logging, WAL ).

The INNODB Storage engine pre-writes logs to ensure transaction integrity. This means that the data pages stored on the disk and the pages in the memory buffer pool are not synchronized. to modify the pages in the coarse buffer, write the redo log file first ,, then write the data to the disk. Therefore, it is an Asynchronous Method. You can use show engine innodb status to observe the gap between the current disk and the log;

Mysql> create table z (a int, primary key (a) engine = innodb;
Query OK, 0 rows affected (0.01 sec)

Mysql>
Mysql> create procedure load_test (count int) begin declare I int unsigned default 0;
-> Start transaction;
-> While I -> Insert into z select I;
-> Set I = I + 1;
-> End while;
-> Commit;
-> End;
-> $
Query OK, 0 rows affected (0.05 sec)

Mysql> delimiter;
Mysql> show engine innodb status \ G;
* *************************** 1. row ***************************
Type: InnoDB
Name:
Status:
============================================
121130 15:41:01 INNODB MONITOR OUTPUT
============================================
Per second averages calculated from the last 12 seconds
-----------------
BACKGROUND THREAD
-----------------
Srv_master_thread loops: 224 seconds second, 224 sleeps, 18 10_second, 66 background, 66 flush
Srv_master_thread log flush and writes: 228
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 114, signal count 113
Mutex spin waits 6, rounds 180, OS waits 2
RW-shared spins 99, rounds 2970, OS waits 99
RW-excl spins 0, rounds 390, OS waits 13
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 390.00 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
14:17:47 121130
* ** (1) TRANSACTION:
TRANSACTION 11F40, ACTIVE 71 sec starting index read
Mysql tables in use 1, locked 1
Lock wait 3 lock struct (s), heap size 320, 2 row lock (s)
MySQL thread id 80, OS thread handle 0x33d62740, and query id 4152 localhost root statistics
Select * from t3 where a = 2 for update
* ** (1) waiting for this lock to be granted:
Record locks space id 0 page no 3132 n bits 80 index 'primary' of table 'test'. 't3 'trx id 11F40 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 physical record: n_fields 3; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 201700011f35; asc 5 ;;
2: len 7; hex e00000013a011d; asc :;;

* ** (2) TRANSACTION:
TRANSACTION 11F41, ACTIVE 42 sec starting index read
Mysql tables in use 1, locked 1
3 lock struct (s), heap size 320, 2 row lock (s)
MySQL thread id 79, operating thread handle 0x33d62600, query id 4153 localhost root statistics
Select * from t3 where a = 1 for update
* ** (2) holds the lock (S ):
Record locks space id 0 page no 3132 n bits 80 index 'primary' of table 'test'. 't3 'trx id 11F41 lock_mode X locks rec but not gap
Record lock, heap no 3 physical record: n_fields 3; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 201700011f35; asc 5 ;;
2: len 7; hex e00000013a011d; asc :;;

* ** (2) waiting for this lock to be granted:
Record locks space id 0 page no 3132 n bits 80 index 'primary' of table 'test'. 't3 'trx id 11F41 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 physical record: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 201700011f35; asc 5 ;;
2: len 7; hex e00000013a0110; asc :;;

* ** We roll back transaction (2)
------------
TRANSACTIONS
------------
Trx id counter 11F45
Purge done for trx's n: o <11F35 undo n: o <0
History list length 1433
List of transactions for each session:
--- TRANSACTION 11F41, not started
MySQL thread id 79, OS thread handle 0x33d62600, and query id 4162 localhost root
Show engine innodb status
--- TRANSACTION 11F36, not started
MySQL thread id 77, OS thread handle 0x33d61e80, query id 4118 localhost root
--- TRANSACTION 11F2B, not started
MySQL thread id 75, OS thread handle 0x33d61d40, query id 4077 192.168.0.69 root
--- TRANSACTION 0, not started
MySQL thread id 74, OS thread handle 0x33d61fc0, and query id 4070 192.168.0.69 root
--- TRANSACTION 0, not started
MySQL thread id 23, OS thread handle 0x33d62380, query id 114 192.168.0.69 root
--- TRANSACTION 11F40, ACTIVE 5065 sec
3 lock struct (s), heap size 320, 2 row lock (s)
MySQL thread id 80, OS thread handle 0x33d62740, query id 4152 localhost root
--------
File I/O
--------
I/O thread 0 state: waiting for I/o request (insert buffer thread)
I/O thread 1 state: waiting for I/o request (log thread)
I/O thread 2 state: waiting for I/o request (read thread)
I/O thread 3 state: waiting for I/o request (read thread)
I/O thread 4 state: waiting for I/o request (read thread)
I/O thread 5 state: waiting for I/o request (read thread)
I/O thread 6 state: waiting for I/o request (write thread)
I/O thread 7 state: waiting for I/o request (write thread)
I/O thread 8 state: waiting for I/o request (write thread)
I/O thread 9 state: waiting for I/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0], aio writes: 0 [0, 0, 0, 0],
Ibuf aio reads: 0, log I/o's: 0, sync I/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
691 OS file reads, 1923 OS file writes, 1010 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
Merged operations:
Insert 0, delete mark 0, delete 0
Discarded operations:
Insert 0, delete mark 0, delete 0
Hash table size 553253, node heap has 6 buffer (s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 594246629
Logflushed up to 594246629
Last checkpoint at 594246629
0 pending log writes, 0 pending chkp writes
649 log I/o's done, 0.00 log I/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 135987200; in additional pool allocated 0
Dictionary memory allocated 281610
Buffer pool size 8192
Free buffers 4305
Database pages 3881
Old database pages 1418
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 633, created 3248, written 5875
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3881, unzip_LRU len: 0
I/O sum [0]: cur [0], unzip sum [0]: cur [0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 869673728, state: waiting for server activity
Number of rows inserted 105750, updated 0, deleted 0, read 59717
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
======================================

1 row in set (0.00 sec)

ERROR:
No query specified

Mysql>

Mysql> call load_test (10000 );
Query OK, 0 rows affected (0.50 sec)