Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

MySQL--innodb log management

2025-01-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

innodb log management mechanism:

1. innodb storage engine supports transaction ACID characteristics. This theory is basically a relational database-related data recovery prototype design, including log, rollback, redo, concurrency control, buffer pool and other management aspects. The content is very comprehensive.

The buffer pool of innodb is mainly used to store the accessed data pages. It is a continuous memory. Through certain algorithms, this memory can be effectively managed. It is the system module with the largest memory in the database system.

Access to data in innodb storage engine is per page (It can also be called a block, the default is 16KB) from the database file to the buffer pool, and then use the same size of memory space in memory to make a mapping; in the future to improve the efficiency of data access, the database system has allocated a lot of such space in advance, used to exchange data with files; The size of buffer pool can be configured in the configuration file. The parameter innodb_buffer_pool_size determines the size. The default size is 128MB. Before MySQL 5.7.4, this value could not be modified once MySQL was started. If you want to modify it, you can only exit MySQL process, and then modify the corresponding configuration file to set the new buffer pool size. Restart will take effect.

Note: After MySQL 5.7.5, you can dynamically adjust innodb_buffer_pool_size when MySQL process is running. It should be emphasized that if the size of buffer pool exceeds 1GB, you should adjust innodb_buffer_pool_instances=N and divide it into several instances to prompt MySQL's concurrency ability to process requests, because buffer pool manages pages through linked lists. At the same time, in order to protect pages, you need to lock linked lists when accessing them. In the case of multithreading, Concurrently reading and writing cached pages in the buffer pool requires lock contention and waiting. So modify to multiple instances, each instance manages its own memory and linked list, which can improve efficiency.

3. Buffer pool implementation principle:

Buffer pool can have multiple instances, which can be set by parameter innodb_buffer_pool_instance in the configuration file. The default value is 1. The buffer pool with multiple instances is mainly implemented to improve concurrency during data page access. The space size of each instance is the same, that is to say, the system will divide the size of the entire configured buffer pool equally according to the number of instances, and then each instance will perform initialization operations separately;

--Note: During operation and maintenance, the status parameter innodb_buffer_pool_bytes_data is always smaller than innodb_buffer_pool_size, because the control header information takes up part of the space. The actual allocation method is that the buffer pool pages are allocated from the back to the front of the entire instance pool, one page at a time, and the control structure is allocated from the front to the back, one buffer_block_t structure at a time until they meet, so that an instance is initialized.

First, redo log file management:

redo log is used to do database crash recovery, which is one of the important functions of database security. In database operations, it holds a record of changes to the data in the innodb table, so it is also called a log file. innodb storage engine generally includes 2 log files by default. After creating a new database, two files named ib_logfile0 and ib_logfile1 will be created. If these two files do not exist when starting the database, innodb will recreate log files according to configuration parameters or default values;

1.1 LSN full name: log sequence number:

In innodb's internal log management, a very important concept is LSN, the full name is log sequence number, which is used to accurately record log location information, and it is continuously growing. In innodb, the size is 8 bytes, and its growth is calculated based on the number of logs written by an MTR, and the LSN grows as many logs are written. (LSN is a completely logical concept, increasing by 1 for each physical transaction committed.)

1.2 In innodb, log files are managed through log groups, which is a logical definition and contains several log files. The log files in a group are equal in size, and the size is set by parameters. Now innodb only holds one log group. (Before MySQL 5.5, the maximum log group was 4G; after MySQL 5.6.3, it can be set to 512G)

1.3 The redo log is written in bytes, although it looks like multiple log files, but when understood, it can be imagined as a file. (Each log file in the log group has its own format, and the interior is also cut according to pages of equal size, each page size is 512 bytes)

Note: Efficiency is highest if each write is a multiple of disk block size, and logging converts scattered random writes to the database by logical transactions into sequential writes of 512 byte integer multiples of data, which greatly improves database efficiency.

1.4 Format of redo log file:

Each log file has a file header (in ordinary pages, there will be 12 bytes used to store page header information, which is mainly used to manage the data storage mode of the page itself;--note that only 2KB is the log header, followed by one continuous, used to store MTR generated log pages)

1.5 MTRinnodb Physical Affairs:

It is a very important mechanism in innodb storage engine to ensure the integrity and persistence of physical page write operations. It is called MTR because its meaning is equivalent to a mini-transaction, which is represented by MTR. Here it is called "physical transaction", which is called relative to logical transaction.

Since a physical transaction is called a transaction, it also has the beginning and commit of the transaction. The beginning of the physical transaction is actually the initialization of the physical transaction structure mtr_struct. The commit of the physical transaction is mainly to write all the logs generated by this physical transaction to the log buffer of the innodb log system, and then wait for the srv_master_thread thread to regularly flush the log data from the log buffer of the log system into the log file;

---Note: The storage of log buffer is only a temporary intermediate state. The size of log buffer can be set by parameter innodb_log_buffer_size. Generally, it is relatively small and cannot store many logs.

--Log is the modification record of all buffer pool pages involved recorded by MTR of physical transaction when logical transaction performs DML operation on database;

1.6 Log Key reasons for improved performance:

①: Because the log is used to record the modification records of the page in the buffer pool, so the page writing is converted into writing to the log, then the page does not need to be swiped every time, and the page only needs to be written in memory, and the performance will be very good;

②: Usually, a page is 16KB, if not written, the unit written each time is still 16KB, even if the data is modified very little, this will lead to invalid IO is very serious.

1.7 Problems with redo log size settings:

①: If the setting is very large, although the performance may be very good, but if the database has abnormal downtime, there may be a lot of logs that have not been flushed, that is, the difference between the log flushed up to and the last checkpointat is too much, and recovery takes a long time. (The redo log recovery is sequential, and is sorted according to the size of the page number;)

②: The setting of log capacity size should preferably match the total size of buffer pool. If the log capacity is too small and the buffer pool is too large, this will cause frequent checkpoints in the buffer pool, and the large buffer pool cannot be used well. If the log capacity is too large and the buffer pool is too small, the buffer page will often be eliminated, increasing the IO frequency. At the same time, if the database is down unexpectedly, the buffer pool is too small, and the recovery will be slower.

1.8 redo logging format:

innodb logs are physical logs with logical meaning, so the format of log records is not completely physical information, but has a certain logical meaning. The basic format is as follows: type, space, offset (The page number in the file specified by the previous space, in units of page size), data (Indicates the data corresponding to this log record, which is uncertain and varies according to different type values)---type There are many types, and the more commonly used ones are: ①: mlog_ibyte, mlog_2bytes, mlog_4bytes, mlog_8bytes: These four types indicate that you want to write a log record in a certain position.(two, four, eight) bytes of content;②: mlog_write_string: This type of log is actually similar to mlog_ibyte, except that mlog_ibyte is to write a fixed-length data, while mlog_write_string is to write a variable-length data.③: mlog_undo_insert: This type of log is generated when a record is set as the minimum record in the page, because it is only a mark, and the stored content is relatively simple;④: mlog_init_file_page: This type of log is relatively simple, only the basic header information in front, no data part; ④: mlog_comp_page_create: This type only requires a type of village and the location of the page to be created; ④: mlog_multi_rec_end: This type of record is very special, it only acts as a tag, and its contents only take up one byte of type value. 7: mlog_comp_rec_clust_delete_mark: This type of log indicates that a record in the aggregated index needs to be marked with a delete mark; 8: mlog_comp_rec_update_in_place: This type of log records updated record information, including information about all updated columns. : mlog_comp_page_reorganize: This type of log indicates that the specified page is to be reorganized, and its recorded content is also very simple. It only needs to store which page to reorganize.

1.9 Time of day: There are 5 times:

①: Log buffer space is used up, this will have generated log buffer in the log to disk, this is the most common way;②: master thread in the background every second brush, the log in the current buffer to disk;③: Every time a DML operation is executed, it will actively check whether the log space is sufficient. If the amount of space used has exceeded a preset experience value, it will actively swipe the log to ensure that when it is actually executed later, it will not passively swipe the disk in the process, but here it will only write files (written into the OS buffer). Disk 4: When making checkpoints, it is necessary to ensure that the log with the smallest LSN value in all pages to be flushed has been flushed to disk. Otherwise, if the database is down at this time, the log does not exist, but the data page has been modified, resulting in inconsistent data, which violates the principle of writing logs; 5: When submitting logical transactions, different behaviors will occur due to different values of the parameter innodb_flush_log_at_trx_commit. If 0 is set, the log buffer will not be flushed at all when the transaction is submitted, which is the most dangerous setting; if 2 is set, the log will be written to the file when the transaction is submitted, but the disk will not be flushed. As long as the operating system is not suspended, even if the database is suspended, the data will not be lost. Generally, it is set to 2;

1.10 redo log brush mechanism:

When committing a transaction (logic), you can control the redo log writing mechanism through the parameter innodb_flush_log_at_trx_commit. Different parameter values will generate different behaviors. The main parameter values are as follows:

①: innodb_flush_log_at_trx_commit=0 When the transaction is committed, MySQL will not process the contents of the log buffer, nor will it process the disk flushing operation of the log file. MySQL's background master thread will flush the files in the buffer to the log file every 1s;(The host is normal, and after the database is down: generally only the transactions of the last 1s will be lost) ②: innodb_flush_log_at_trx_commit=1 When the transaction is committed, the log in the log buffer will be written to the file and flushed to the disk at the same time, so as to ensure that the database transaction will not be lost at all. This setting affects database performance;(host normal, database downtime: data will not be lost) ③: innodb_flush_log_at_trx_commit=2 When a transaction is committed, the log cache log will be written to a file, but will not be flushed to disk. The MySQL background master thread flushes the log files cached by the system to disk every 1s;(the host is normal, and the data will not be lost after the database is down)

---Note: If the host where the database is located is down: Parameter 0 will lose the transactions of the last 1s; Parameter 1 will not lose any data; Parameter 2 will lose the transactions of the last 1s;

Second, database undo segment management:

The total number of rollback segments supported in innodb is: 128X1024=131072. At the beginning of each transaction, an rseg will be allocated, that is, from the array with length of 128, an adjacent rseg will be found according to the most recent use;

During the execution of a transaction, two types of rollback logs are generated, one is the undo record of insert, and the other is the undo record of update.(Because innodb divides undo into two categories, one is new, that is, insert, and the other is modified, that is, update. The classification is based on whether to do purge operation after transaction submission, because insert does not need purge. As long as the transaction is submitted, this rollback record can be lost. For update and delete operations, if the transaction is submitted, it also needs to serve MVCC. Then you need to put these logs in the history list, waiting to do the multi-version query of the purge already MVCC, so it is divided into two categories)

2.1 Database undo logging format:

There are four types of undo:

①: trx_undo_insert_rec: Record the inserted undo log type. When the inserted record is used for rollback, it only needs to use its primary key to implement rollback operation, so in the undo log, only the table ID and primary key information are recorded;②: trx_undo_upd_exist_rec: update an undo log type with existing records;③: trx_undo_upd_del_rec: update an undo log type with deletion mark;④: trx_undo_del_mark_rec: undo log type with deletion mark when deleting records;--Note: Different from redo log storage, undo log storage does not collapse pages;---Note: Use the parameter innodb_force_recovery to decide whether to do rollback operation. If it is set to 3 or above, it will not be rolled back when innodb is started, which may lead to logical inconsistency of database;

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.

Views: 203

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report