“This is the seventh day of my participation in the August More Text Challenge. For details, see: August More Text Challenge.”
1. Introduction
Equivalent to the Linux file system, only more powerful
2. Function understanding
- Read and write data
- Data security and consistency
- To improve performance
- Hot backup
- Automatic fault recovery
- High availability support, etc
3. Storage engine introduction
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+-- ----------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+-- ----------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+-- ----------+
9 rows in set (0.01 sec)
Copy the codeThe default storage engine is InnoDB
Next generation storage engine: RocksDB, MyRocks, TokuDB Cause: high compression, high data insertion performance, other features as InnoDB.
4. Core features of InnoDB
- Transaction
- MVCC (Multi-version Cocurrency Control)
- A foreign key
- Auto Crash Safey Recovery (ACSR) Automatic fail-safe Recovery
- Row-level Lock
- Hot Backup
- Copy (multi-threaded, GTID, MTS)
5. View the storage engine
5.1 Viewing Storage Engine Settings
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+-- ----------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+-- ----------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+-- ----------+
9 rows in set (0.00 sec)
Copy the codemysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
1 row in set (0.02 sec)
Copy the code5.2 Viewing the Table Storage Engine Status
mysql> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ -----------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ -----------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'student id'.`sname` varchar(64) NOT NULL COMMENT 'name'.`age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'age'.`sex` char(4) NOT NULL COMMENT 'gender'.`intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time of Admission',
PRIMARY KEY (`id`),
KEY `idx_name` (`sname`))ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 |
+---------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ -----------------------------------------------------------------------------+
1 row in set (0.00 sec)
Copy the code6. Modify the storage engine
6.1 Modifying a Storage Engine
mysql> alter table student engine=innodb;
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
Copy the code6.2 Defragmenting
mysql> alter table student engine=innodb;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
Copy the codeNote: Only InnoDB works
Can be used for regular defragmentation.
7. InnoDB storage engine physical storage structure
7.1 The most intuitive storage method (/data/mysql/data)
- Ibdata1: system data dictionary information (statistics),undo tablespace data (version 5.6, shared tablespace reserved, only used to store: data dictionary information,undo, temporary table; In version 5.7, temporary tables were made independent; In version 8.0,undo has also been isolated.
- Ib_logfile0, ib_logFILE1: redo log files, transaction log files
- Ibtmp1: temporary tablespace disk location where temporary tables are stored
- FRM: Stores table column information
- Ibd: the data rows and indexes of the table
7.2 Tablespace (Tablespace)
7.2.1 Tablespace Data Is Faulty
- Ibdata1: statistics of the entire library + undo
- Ibd: data row and index
5.5 Shared Tablespace = 5.6 Independent Tablespace
7.2.2 Sharing a Tablespace (ibData1 to N)
Version 5.5 default schema, 5.6 conversion to a separate table space requires all data to be stored in the same table space, management is confusing
In version 5.6, the shared tablespace is reserved for storing: data dictionary information, undo tables, and temporary tables. In version 5.7, temporary tables are independent from version 8.0, and undo tables are independent from version 8.0
Refer to the official document for specific changes:
Dev.mysql.com/doc/refman/… Dev.mysql.com/doc/refman/… Dev.mysql.com/doc/refman/…
Shared tablespace Settings (set to parameter file before initializing data during MySQL setup)
View shared tablespace Settings:
mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend |
+-------------------------+
1 row in set (0.00 sec)
Copy the codemysql> show variables like '%extend%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 64 |
+-----------------------------+-------+
1 row in set (0.00 sec)
Copy the codeThe meanings of the configuration file are as follows:
- The name of the shared tablespace is ibdata1.
- The allocated space size is 12M;
- If you don’t have enough space, autoextend.
- Each capacity expansion is 64 MB
We can specify the shared tablespace size when we initialize the configuration file
mysqld –initialize-insecure xxxxxxxxx innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend innodb_autoextend_increment=64
Specify two configuration files ibData1 and ibDatA2, each with a size of 512 MB. When ibDatA1 is used up, use ibDatA2. When both configuration files are used up, expand the capacity automatically.
7.2.3 Independent tablespace
As of 5.6, the default tablespaces are no longer shared tablespaces, and are replaced by independent tablespaces
The main store is user data.
Storage features: A table is an IBD file, storing data rows and index information
Conclusion: One data table = IBD + FRM + IBDatA1
Redo Log: ibdata1; Undo Log: ibdatA2; Ibtmp1. Temporary data is generated during join and union operations and is automatically cleared when used up
mysql> select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+| | + 1-------------------------+
1 row in set (0.00 sec)
Copy the code1 indicates that the independent tablespace is enabled.
Table space mode switching affects only subsequent tables, not previous tables.
7.2.4 Independent tablespace migration
It is not possible to directly migrate the.rfm and.ibd files because the table metadata information is still in the ibData1 file. Without this information, the table cannot be viewed. So, we need the exact same metadata, so how do we get the exact same metadata, we just take the original building sentence.
First, we have a student table on library 3306, and I want to migrate it to multi-instance library 3307.
Let’s copy the file:
# cp /data/mysql/data/account/student.* /data/3307/data/account/
# cd /data/3307/data/account/
# chown mysql.mysql student.*
Copy the codeLet’s go to library 3307 and look at this table:
Database changed
mysql> show tables;
+-------------------+
| Tables_in_account |
+-------------------+
| student |
+-------------------+
1 row in set (0.00 sec)
Copy the codeDiscovery can be viewed. So let’s do it again with desc.
mysql> desc student;
ERROR 1146 (42S02): Table 'account.student' doesn't exist
Copy the codeFRM file, but the data dictionary is different from the.frm file. The.frm file is only the structure of the table, but the desc command uses the system configuration file.
While we can see the table name, we can’t see the column information, nor can we see the data information.
What’s missing is the statistics in idDatA1.
Where does metadata come from? Metadata cannot be added, deleted, modified, or queried directly. Metadata can only be generated and queried using special commands.
So how do we generate it? We just have to create it. We find the first building sentence.
Let’s just delete these two files
# mv /data/3307/data/account/student.* /tmp/
Copy the codeLet’s go and look at constructing sentences
mysql> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ -----------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ -----------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'student id'.`sname` varchar(64) NOT NULL COMMENT 'name'.`age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'age'.`sex` char(4) NOT NULL COMMENT 'gender'.`intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time of Admission',
PRIMARY KEY (`id`),
KEY `idx_name` (`sname`))ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 |
+---------+-------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ -----------------------------------------------------------------------------+
1 row in set (0.00 sec)
Copy the codeLet’s go to library 3307 and create:
mysql> CREATE TABLE `student` (
-> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'student id'- >`sname` varchar(64) NOT NULL COMMENT 'name'- >`age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'age'- >`sex` char(4) NOT NULL COMMENT 'gender'- >`intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time of Admission',
-> PRIMARY KEY (`id`), - >KEY `idx_name` (`sname`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
ERROR 1146 (42S02): Table 'account.student' doesn't exist
Copy the codeI don’t think so. Let’s delete the database:
mysql> drop database account; Query OK, 0 rows affected (0.01sec)Copy the codemysql> create database account;
Query OK, 1 row affected (0.00 sec)
mysql> use account;
Database changed
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'student id'.`sname` varchar(64) NOT NULL COMMENT 'name'.`age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'age'.`sex` char(4) NOT NULL COMMENT 'gender'.`intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time of Admission',
PRIMARY KEY (`id`),
KEY `idx_name` (`sname`))ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
ERROR 1146 (42S02): Table 'account.student' doesn't exist
Copy the codeYou can’t create it again.
Let’s try changing the name:
CREATE TABLE `student1` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'student id'.`sname` varchar(64) NOT NULL COMMENT 'name'.`age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'age'.`sex` char(4) NOT NULL COMMENT 'gender'.`intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time of Admission',
PRIMARY KEY (`id`),
KEY `idx_name` (`sname`))ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.09 sec)
Copy the codeThe result is a successful creation.
We will copy the.ibd file back, and the.rfm file will not be needed because it already exists.
Previous database table files:
-rw-r----- 1 mysql mysql 60 jan 23 22:36 db. Opt-rw-r ----- 1 mysql mysql 8714 Jan 27 21:46 student. Frm-rw-r ----- 1 mysql Mysql 114688 January 27 21:46 student.ibdCopy the codeCreate database table file:
-rw-r----- 1 mysql mysql 65 Jan 28 13:45 db. opt-rw-r ----- 1 mysql mysql 8714 Jan 28 13:46 student1. FRM -rw-r----- 1 mysql Mysql 114688 January 28 13:46 student1.ibdCopy the codeLet’s copy the.ibd file
# cp /data/mysql/data/account/student.ibd /data/3307/data/account/
# chown mysql.mysql /data/3307/data/account/student.ibd
Copy the codeIbd: student1.ibd: rm: rm: rm: rm: rm: rm: rm: rm: rm: rm: rm: rm: rm: rm: rm
mysql> alter table student1 discard tablespace;
Query OK, 0 rows affected (0.09 sec)
Copy the codeThen, we look at the file and find that the student1.ibd file is missing.
-rw-r----- 1 mysql mysql 65 Jan 28 13:45 db. opt-rw-r ----- 1 mysql mysql 8714 Jan 28 13:46 student1. FRM -rw-r----- 1 mysql Mysql 114688 January 28 13:50 student.ibdCopy the codeLet’s copy the student.ibd file to it
# mv student.ibd student1.ibd
Copy the codeSo can we look at the data now? Let’s try it out
mysql> select * from student1;
ERROR 1814 (HY000): Tablespace has been discarded for table 'student1'
Copy the codeWe need to add tablespace and import the.ibd file
mysql> alter table student1 import tablespace;
Query OK, 0 rows affected, 1 warning (0.07 sec)
Copy the codeLet’s check again:
mysql> select * from student1;
+----+--------+-----+-----+---------------------+
| id | sname | age | sex | intime |
+----+--------+-----+-----+---------------------+| 1 | guan yu | | | 20 m 2021-01-24 20:21:50 | | | | | | 21 m liu2 bei4 2021-01-24 20:23:23 | | 3 | zhaoyun | | | m 22 2021-01-24 20:24:38 | | | 4. Huang zhong | | | 23 m 2021-01-24 20:24:38 | +----+--------+-----+-----+---------------------+
4 rows in set (0.00 sec)
Copy the codeconclusion
- Create an empty table with the same structure as the original table
- Delete ibD file from null table:
alter table table_name discard tablespace; - Copy the ibD of the original table and modify the permissions.
- Import ibD from ibD:
alter table table_name import tablespaces;
Function: Used for data backup and replication.
8. InnoDB core features
8.1 transactions
8.1.1 ACID characteristics of transactions
-
It’s Atomic.
- All statements as a unit are successfully executed or cancelled, and no intermediate state can occur.
-
It is Consistent.
- If the database is in a consistent state at the start of a transaction, it will remain in a consistent state for the duration of the transaction.
-
Isolated (Isolated)
- Transactions do not affect each other.
-
Durable (Durable)
- Upon successful completion of the transaction, all changes made are accurately recorded in the database. Changes made are not lost.
8.1.2 Transaction Lifecycle (standard transaction control statements)
-
How to start a transaction
begin;
-
Standard transaction statements
- DML
- insert
- update
- delete
- DML
-
End of transaction
- Commit:
commit; - Roll back:
rollback;(Note that only uncommitted items can be rolled back.)
- Commit:
8.1.3 AutoCommit
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+| | + 1--------------+
1 row in set (0.00 sec)
Copy the codeAs soon as the DML statement is executed, MySQL will automatically begin and commit.
Modifying parameters online:
set autocommit=0;A session level change only affects the current session and takes effect immediately
mysql> set autocommit=0; Query OK, 0 rows affected (0.01sec) mysql>select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
Copy the codeLet’s check it out in another session:
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+| | + 1--------------+
1 row in set (0.00 sec)
Copy the codeLet’s disconnect the session and reconnect to check:
mysql> exit
Bye
[root@VM-0-3-centos account]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 146
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement. mysql>select @@autocommit;
+--------------+
| @@autocommit |
+--------------+| | + 1--------------+
1 row in set (0.00 sec)
Copy the codeset global autocommit=0;The global level modification takes effect after the interface is disconnected and reconnected. Affects all newly opened Windows
mysql> set global autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
Copy the codeLet’s go to another window that’s still open and check it out:
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+| | + 1--------------+
1 row in set (0.00 sec)
Copy the codeLet’s disconnect the window and reconnect, and check again:
mysql> exit
Bye
[root@VM-0-3-centos ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 143
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement. mysql>select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
Copy the code- The modified configuration file takes permanent effect. Need to restart
vim /etc/my.cnf
autocommit=0
Copy the code8.1.4 Implicit Submission
Statements that trigger an implicit commit:
Begin a b (COMMIT) Implicitly submits the begin/set commandCopy the codeOther non-transactional statements that cause a commit:
- DDL statements: ALTER, CREATE, and DROP
- DCL statements: Grant, REVOKE, set password
- Lock statements: Lock tables, unlock tables
Examples of statements that result in an implicit commit:
- truncate table
- load data infile
- select for update
8.2 How to guarantee ACID for transactions
8.2.1 Some conceptual nouns
-
Redo log:
- Redo logs, ib_logfile0~ 1,50 MB size, used for polling
-
Redo log buffer:
- Redo memory area
-
Ibd:
- Stores data rows and indexes
-
Buffer pool:
- The data buffer pool is used to buffer data and indexes
-
LSN:
- Log sequence number.
- Stored in the disk data page, redo file, buffer pool, and redo buffer.
- Each time the MySQL database starts, it compares the LSN of the disk data page and the LSN of the redolog. The database can start properly only when the LSN of the disk data page and the LSN of the redolog are consistent
-
WAL:
- The write ahead log. Write logs first for persistence
- Logs are written to disk before data
-
Dirty pages:
- A dirty page is a page that has been modified in memory before being written to disk.
-
CKPT:
- To check whether a dirty page is written to disk is to checkpoint
-
TXID:
- Transaction number, InnoDB will generate a transaction number for each transaction, along with the entire transaction
8.2.2 Transaction logs — redo
Function:
- Main function: guarantee “D”, AC also has a certain role
What is recorded?
- Records changes to the in-memory data page.
- Provide fast persistence (WAL)
- Implement roll forward operations in the CSR process (disk data page and redo log LSN)
Redo log location
The redo log files are ib_logfile0 and ib_logfile1
redo buffer
Buffer: change information + the LSN of the page at the time of the change
Redo write strategy
Commit; Flushing the redo buffer from the current transaction will also flush the redo buffer from the current transaction to disk
MySQL: At startup, the log file and data file LSN must be consistent. If the log file and data file LSN are inconsistent, the CSR is triggered.
MySQL CSR — Roll forward
MySQL: At startup, ensure that the log file and data file LSN are consistent. If the log file is inconsistent, the CSR is triggered
Case 1:
We do a transaction: begin; update; commit.
- In begin, TXID=tx_01 is assigned immediately.
- During the update, the data page (dp_01,LSN=101) to be modified is loaded into the data buffer
- The DBWR thread will update the DP_01 data page and update LSN=102
- LOGBWR Log writing thread that stores dp_01 data page changes +LSN+TXID into the RedoBuffer
- During the commit,LGWR writes the redobuffer to the redolog log file. Based on the WAL rule, the commit command is executed only after the log is completely written to disk.
- If the system breaks down, the dirty pages cannot be written to the disk, and all memory data is lost
- When MySQL restarts again, redolog must be the same as the LSN of the disk data page. However, in this case, dp_01, TXID=tx_01 disk LSN=101, dp_01, TXID=tx_01, and LSN=102 in redolog
MySQL cannot start properly at this time. MySQL triggers CSR, equalizes LSN in memory, triggers CKPT, and updates memory data pages to disk to ensure that disk data pages are consistent with redolog LSN. MySQL starts normally.
This process is called a “roll forward operation” based on REDO.
8.2.3 undo — Rolls back logs
role
In the characteristics of ACID, the characteristics of A are mainly guaranteed, and it also has A certain effect on CI
What was recorded?
- Records the state of the data before modification;
- Rollback restores memory data modifications to the original ones
- Implement rollback of uncommitted data in CSR
- Implement consistency snapshot, with locking mechanism, ensure that MVCC, read and write operations will not block each other
8.2.4 lock
Isolation between transactions is implemented.
Row-level Lock in InnoDB
gap
next-lock
8.2.5 Isolation Level
- RU: read uncommitted, dirty read
- RC: Read has been committed. Magic reads may occur and dirty reads can be prevented
- RR: repeatability, the function is to prevent “magic read” phenomenon, using the undo snapshot technology + GAP (GAP lock) + NextLock (down key lock)
- SR: Serializable, prevents deadlocks, but has poor concurrent transaction performance
MVCC ——> undo snapshot
- RU will have dirty reads
- RC will appear
- RR solves non-repeatable reads through MVCC basis, but there may be phantom reads, which can be avoided by GAP + NextLock.
MySQL > view current isolation level
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set.1 warning (0.00 sec)
Copy the codeIn RR mode, GAP and next-lock are used to avoid phantom reads, which must be supported by indexes.
8.2.6 InnoDB core parameters
- Default Settings for storage engines:
default_storage_engine=innodb
Copy the code- Table space schema:
innodb_file_per_table=1
Copy the code- Number and size of files in the shared tablespace
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
Copy the code- One of the “double one” tables
innodb_flush_log_at_trx_commit=1
Copy the code- Log Refreshing Method
innodb_flush_method=(O_DERECT, fsync)
Copy the codeThe default is fsync, O_DERECT is recommended
Fsync mode:
O_DIRECT mode:
O_DSYNC
Highest safety mode
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DERECT
Copy the codeHighest performance mode
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync
Copy the code- Redo log size
innodb_log_buffer_size=16777216
innodb_log_file_size=50331648
innodb_log_file_in_group=3
Copy the code- Dirty brush strategy
innodb_max_dirty_pages_pct=75
Copy the codeWhat other mechanisms trigger write to disk?
- CSR
- When redo space is full