Insert Buffer/change buffer double write buffer, double Adaptive Hash Index (AHI) InnoDB crash recovery InnoDB important parameters InnoDB monitoring

3 possible causes of master-slave inconsistency
1. Binlog format is not row
2, session-level shutdown Binlog
3, manually modify the data in the slave
Set sql_log_bin=0
Session level off Binlog, which can lead to master-slave inconsistency

The kill instance has not stopped for a long time, check the error log and come out

Kill-9

See Show Engine InnoDB status undo is too big

View three tables Innodb_trx, Innodb_locks, innodb_lock_waits

mysql5.7
Innodb_status_output=1
Innodb_status_output_locks=1
The equivalent of opening a mysql5.5 innodb_lock_monitor.

Dead lock
Innodb_print_all_deadlocks=1
The statement in the entire transaction that cannot print two SQL that involves a deadlock cannot be printed, only the SQL

Lesson 20: InnoDB Engine

1, avoid row overflow, the average length of each line is preferably not higher than 8KB, for page size is 16KB
2, 5.6 and later, it is best to use the standalone undo table space
3, shared table space initialization is slightly larger, such as 1GB
Use stand-alone table space settings
Innodb_file_per_tables=1
4, without compressed line format, compression brings limited performance, using Dynamic line format
5, less use SELECT * Text, blob
6, InnoDB table best Use self-increment type column master key
7, Innodb_flush_log_at_trx_commit
0, redo log buffer is not written to disk when transaction commits
1, redo log buffer is written to disk when transaction commits
2, only redo log buffer is written to the operating system cache when the transaction commits
It is generally recommended to set to 1 and set sync_binlog=1 to ensure data reliability
8, Innodb_log_file_size ib_logfile* is generally set to 512MB–4GB

Purge Threads

Delete records that do not exist in the secondary index
Delete a record that has been tagged delete-marked
Delete Undo log that is no longer needed
Starting from 5.6, separate the purge thread.
--innodb_purge_threads = 1 5.6 can have only one, 5.7 can set multiple
--innodb_max_purge_lag = 0
--innodb_purge_batch_size =
Unless the list of Unpurge is too big, there is no need to adjust, increase innodb_purge_batch_size
Show engine InnoDB Status History list Length (Unpurge's list)

Select min (userid) from t very slow lock wait, wait for delete session to commit TRANSACTION, release exclusive lock
Select MAX (userid) from T soon
Background event: After deleting large amounts of data
Background: After deleting large amounts of old data
Delete from T where pkid<3000 million order by Pkid
Subsequent gradual cleanup
Max does not affect
Take small steps to run

Scan the History list to confirm the transaction version number for each record, and whether it needs to be used in other transactions

The purge process of InnoDB
Update_undo generated logs will be placed in the history list when these older versions are not accessible
Need to perform cleanup operations
In addition, the tag delete operation of the page also needs to be physically cleaned out
Background purge threads are responsible for these tasks
Specific process
Confirm Visibility (Create Readview, similar datum points)
Verify that records need to be purge (verify which old transactions are purge)
Executive Purge
Clean up the History list and release Undo segment

Insert Buffer/change Buffer
function is
Improve I/O efficiency by turning IUD operations on non-unique secondary indexes from random I/O to sequential I/O

Working mechanism
First determine if the inserted nonclustered index page is in the buffer pool, and if so, insert it directly
If not, put in an insert buffer object first
When you read a secondary index page to a buffer pool, merge the records in the insert buffer into the secondary index page

2 related options:
--innodb_change_buffer_max_size defaults to 25%
--innodb_change_buffering default to All (Insert\delete\purge\change\all\none)

Show engine InnoDB status see below
INSERT BUFFER and ADAPTIVE HASH INDEX
-------------------------------------
Ibuf:size 1, free list len 1289, seg size 1291, 316623 merges
Merged operations:
Insert 249806, delete mark 1123127, delete 85482

--seg Size: 1291*16kb of the current insertion buffer

--Insert buffer effect = merges/(insert + delete Mark + DELETE) = 316623/(249806+1123127+85482) = 21.71%

--SIZE 1 = page in use

--free list len 1289 = Idle page

--SEG size = size + (free list len + 1) = 1 + (1289 + 1) = 1291

Double write buffer, dual writing
InnoDB There is a partial write problem
Purpose/function: Ensure the reliability of data writing
Prevent data page corruption and repair
Because InnoDB has a partial write problem
Crash occurs when a 16K page writes only part of the data
The redo record is a logical operation, not a physical block, and cannot be recovered by redo log crash recovery
The operating system block is the 4kb,innodb page is 16KB, then need 4 times io to write a full page

How to troubleshoot partial write issues
Double Write, Doublewrite
2 x 1M space, 2M (both disk files and memory space)
The page is written to Doublewrite in the first order when it is written
And then flush back to disk

Double Write
Performance loss
Double write writes are sequential and have a small performance penalty
Can be closed on slave
BTRFS,ZFS file system supports atomic write without opening double write
SSD and other storage devices that support atomic writes do not have to open double write
Innodb_doublewrite=0
Status
Innodb_dblwr_pages_written
Innodb_dblwr_writes
Innodb_dblwr_pages_written:innodb_dblwr_writes=64:1 (Refresh 64 dirty pages at a time), when the system write pressure is not very high, should be less than 64:1

Adaptive Hash Index (AHI)
Objective: To improve the retrieval efficiency of buffer pool
Buffer pool itself is actually a B + tree
O (b+tree height) vs O (1)
Build ahi on hotspot buffer pool, non-persistent
Only equivalent queries are supported, such as:
Idx_a_b (A, B)
WHERE a=xxx
WHERE A=xxx and B=xxx
-------------------------------------
INSERT BUFFER and ADAPTIVE HASH INDEX
-------------------------------------
Hash table Size 42499381, node heap has 68063 buffer (s)
1640.60 Hash searches/s, 3709.46 non-hash searches/s

1640/(1640+3709) = 30.6%

InnoDB's crash recovery
Usual recovery practices: first redo, after undo

After the Redo,redo is finished, the server begins to provide services externally, and the subsequent process is placed on the background thread to continue working
When an instance resumes from a crash, the active transaction needs to be extracted from undo, the transaction for the active state is rolled back directly, and the transaction for the prepare state is committed if the corresponding Binlog for the transaction is already logged, otherwise the transaction is rolled back
Change buffer Merge
Purge
Xa recover

Accelerate Crash recovery speed
Upgrade to version after 5.5
Provides IO device performance
Adjust the lower innodb_max_dirty_pages_pct,50 as appropriate
Set up Innodb_flush_log_at_trx_commit=1, allow each transaction to be submitted as soon as possible, avoid other transactions waiting, generate a lot of undo, increase purge workload

InnoDB Important parameters

Innodb_stats_on_metadata=0
Executes show table status, whether the statistics are updated when show index from XX

InnoDB Monitoring
Innodb_lock_table
Innodb_lock_monitor
Innodb_table_monitor
Innodb_tablespace_monitor

SYS schema
p_s, i_s, sys
3 System views

F

Insert Buffer/change buffer double write buffer, double Adaptive Hash Index (AHI) InnoDB crash recovery InnoDB important parameters InnoDB monitoring