Mysql dba system learning (21) mysql storage engine InnoDB

Mysql storage engine InnoDB

1. Subject structure:

Default 7 Background threads, 4 io threads (insert buffer, log, read, write), 1 master thread (highest priority), 1 lock monitoring thread, one error monitoring thread. You can view it through show engine innodb status. The new version has increased the default read thread and write thread to four, respectively, and can be viewed through show variables like 'innodb _ io_thread %.
Storage engine composition:

Buffer pool, redo log buffer, and additional memory pool ). the specific configuration can be set by show variables like 'innodb _ buffer_pool_size 'and show variables like
'Innodb _ log_buffer_size 'and show variables like 'innodb _ additional_mem_pool_size.

Buffer Pool:

The largest block memory used to store various data caches, including index pages, data pages, undo pages, insert buffering, adaptive hash indexes, innodb Storage lock information, and data dictionary information. The working method always reads database files to the buffer pool by PAGE (16 k per page), and then retains the cached data in the buffer pool based on the least recently used (lru) algorithm. If the database file needs to be modified, the pages in the cache pool are always modified first (dirty pages are changed after modification), and then the dirty pages in the buffer pool are refreshed to the file at a certain frequency. Run show engine innodb status.
Log Buffer:

Put the redo log information into this buffer zone first, and then refresh it to the redo log file at a certain frequency. 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/2134522W3-0.jpg "title =" Z ~ Yefvn1_mgt_21_6379a_@w6c.jpg "alt =" 103407256.jpg"/>

Innodb_buffer_pool_size is generally set to 80% of the machine's physical memory.

Innodb_additional_mem_pool_size is generally the information of the cached data dictionary and data structure, and should not be too large.

2. Two writes:

It provides the reliability of InnoDB data. If the write failure occurs, you can restore the log by redoing the log. However, the redoing log records the physical operations on the page. If the page itself is damaged, redo it. Therefore, a copy of the page is required before the application redo log. When the write failure occurs, the page is restored through the copy of the page and then redone. This is doublewire.
Data Recovery = page copy + redo log


650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/2134525120-1.jpg "alt =" 1fe184a3-3358-3c21-a248-844e1c713e50.jpg "/>


3. adaptive hash Index

The InnoDB Storage engine proposes an adaptive hash index. The storage engine monitors the query of indexes on the table. If it is observed that the creation of a hash index will increase the speed, a hash index will be created, so it is called adaptive. Adaptive hash indexes can only be used to search for equivalent queries, for example, select * from table where index_col = '***'. In addition, adaptive hash is controlled by the InnoDB Storage engine, we can only disable or enable innodb_adaptive_hash_index, Which is enabled by default.

4,Redo log files

If the instance and media fail, redo log files can be used. For example, if the database power is down, the InnoDB Storage engine uses the redo log to restore the data to the time before power loss to ensure data integrity. The innodb_log_file_size parameter specifies the size of the redo log file. innodb_log_file_in_group specifies the number of redo log files in the log file group. The default value is 2. innodb_mirrored_log_groups specifies the number of log image file groups, the default value is 1, which indicates that there is only one log file group and there is no image. innodb_log_group_home_dir specifies the path of the log file group, which is under the database path by default.
Differences between binary logs and redo logs: first, binary logs record all log records related to Mysql, including logs from other storage engines such as InnoDB, MyISAM, and Heap. While the InnoDB Storage engine redo log only stores transaction logs about it. Second, no matter whether the format of the binary log file record is set to STATEMENT, ROW, or MIXED, it records the specific operations of a transaction. While the InnoDB Storage engine retries the log file to record the physical situation of changes to each page. In addition, the binary log file is recorded before the transaction is committed, and the redo log entries are constantly written into the redo log file during the transaction.

5. tablespace files

The default tablespace file of InnoDB is ibdata1. You can use show variables like 'innodb _ file_per_table 'to check whether each table generates a separate. idb tablespace file. However, a single tablespace file only stores data, indexes, insert buffering, and other information of the table. The remaining information is stored in the default tablespace. Each file can automatically grow

Mysql> show variables like "% file_path % ";
+ ----------------------- + ------------------------ +
| Variable_name | Value |
+ ----------------------- + ------------------------ +
| Innodb_data_file_path | ibdata1: 10 M: autoextend |
+ ----------------------- + ------------------------ +
1 row in set (0.00 sec)

Innodb always prompts me 060809 16:49:11 [ERROR] InnoDB: syntax error in innodb_data_file_path

Innodb_data_file_path = ibdata1: 50 M; ibdata2: 50 M: autoextend
The full Suffix of a specified data file includes the file name, its size, and several optional attributes:
File_name: file_size [: autoextend [: max: max_file_size]
The autoextend attribute and the subsequent attribute can only be used for the last data file in the innodb_data_file_path row.
If you specify the autoextend option for the final data file. If the data file consumes free space in the tablespace, InnoDB expands the data file. The Extended Range is 8 MB each time.
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/213452D35-2.jpg "alt =" b25d9694-0c64-3767-943d-e0fc714d741f.jpg "/>

6,Mysql innodb locks
The implementation of the InnoDB Storage engine lock is very similar to that of Oracle. It provides consistent non-locking read, row-Level Lock support, and row-Level Lock without related overhead, and can get concurrency and consistency at the same time.
The InnoDB Storage engine implements the following two standard row-level locks:
S Lock: allows a transaction to read a row of data;
X Lock: allows a transaction to delete or update a row of data.
When a transaction has obtained the row r shared lock, another transaction can immediately obtain the row r shared lock because reading data that does not change the row r is called lock compatibility. However, if a transaction wants to obtain the exclusive lock of row r, it must wait for the transaction to release the shared lock on Row r-this situation is called the lock incompatibility.

650) this. length = 650; "class =" magplus "title =" click to view original size picture "src =" http://www.bkjia.com/uploads/allimg/131229/21345240G-3.jpg "alt =" b4cc4aea-5c1a-3962-8e61-3b594ce2a7d3.jpg "height =" 83 "width =" 700 "/>

Before InnoDB Plugin, you can only use commands such as show full processlist and show engine inoodb status to view the current database request, and then judge the locks in the current transaction. In the new InnoDB Plugin, INNODB_TRX, INNODB_LOCKS, and InnoDB_LOCK_WAITS are added under the INFORMATION_SCHEMA architecture. With these three tables, you can easily monitor the current transaction and analyze possible lock problems.

This article is from the "alive" blog. For more information, contact the author!