MySQL Optimization: InnoDB Optimization

Study plans are easily interrupted and difficult to stick to. There was a meeting in the company recently to adjust the business direction, and it was recommended to learn NodeJS. I knew a little bit about NodeJS before, but I didn't study it in depth. The syntax of Node is basically the same as that of client-side Js. In the past six months, there has been very little development of client-side stuff. Although I have a good knowledge of JS, I am now unfamiliar with this area. It seems that knowledge is used or lost. If it is not used frequently, it will be forgotten soon. So I reviewed the relevant knowledge of JS again. Learned about Node's server and socket. The MySQL plan was shelved. I ate, drank and slept on Sunday. I was so lazy in the morning that I finally made it to the afternoon. Without further ado, let’s continue with the MySQL optimization series. This time, let’s look at the optimization items of InnoDB.

The primary index of InnoDB is a clustered index, and the index and data share the same tablespace. For InnoDB, data is index and index is data. The biggest difference between InnoDB's cache mechanism and MyISAM is that InnoDB not only caches indexes, but also caches data.

1. InnoDB Cache Pool

The InnoDB buffer pool is the key to improving InnoDB performance. It can cache data, indexes, and even other management data (metadata, row-level locks). You can use show variables like 'innodb%pool%'; to view related parameter options.

mysql> show variables like 'innodb%pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_additional_mem_pool_size | 8388608 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+

innodb_buffer_pool_size

innodb_buffer_pool_size is used to set the size of the InnoDB buffer pool (InnoDBBufferPool). The default value is 128M. The size of the InnoDB buffer pool has a great impact on the overall performance of InnoDB. If the current MySQL server is dedicated to MySQL service, you can increase the size of this parameter as much as possible.

innodb_buffer_pool_instance

The default value of innodb_buffer_pool_instance is 1, which means that the InnoDB buffer pool is divided into one area. Properly increasing this parameter value can improve the concurrency performance of InnoDB.

innodb_additional_mem_pool_size

Specifies the cache size used by InnoDB to store the data dictionary and other internal data. The default value is 2M. The more InnoDB tables there are, the more this parameter should be increased.

2. Internal structure of InnoDB cache pool

InnoDB maintains a buffer pool in memory for caching data and indexes. The cache pool can be considered as a very long linked list. The linked list is divided into two sub-lists. One sub-list stores old page data, which is data pages that have not been accessed for a long time. The other sub-list stores new page data, which is the most recently accessed data page. By default, old pages account for 37% of the entire linked list size, which can be viewed through the innodb_old_blocks_pct parameter.

mysql> show variables like 'innodb_old_blocks%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
+------------------------+-------+

The intersection of the old page and the new page is called the midpoint.

When a user accesses data, InnoDB will first search for the data in the InnoDB cache. If there is no data in the cache pool, InnoDB will insert the data on the hard disk into the InnoDB cache pool. If the cache pool is full, the LRU algorithm is used to clear out expired old data.

3. Preheat the InnoDB cache pool.

After the MySQL server has been started for a period of time, InnoDB will put frequently accessed data (business data, management data) into the InnoDB cache. That is, the InnoDB cache pool stores data that needs to be accessed frequently (referred to as hot data). When the size of the InnoDB cache pool is tens or hundreds of GB, if you restart MySQL, how do you load the hot data in the previous InnoDB cache pool into the InnoDB cache pool?

If the InnoDB cache pool is preheated solely by InnoDB itself, it will take a long time. For a system with busy business, a long period of downtime is a serious production accident and cannot be tolerated. Fortunately, MySQL version 5.6 supports saving hot data to the hard disk when shutting down the service. When MySQL is restarted, the hot data on the hard disk is first loaded into the InnoDB cache. This can shorten the warm-up time and improve efficiency when the business is busy and highly concurrent.

mysql> show variables like '%innodb%pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_additional_mem_pool_size | 8388608 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+

innodb_buffer_pool_dump_at_shutdown

The default is off. If the parameter is turned on and the MySQL service is stopped, the hot data in the InnoDB cache will be saved to the hard disk.

innodb_buffer_pool_load_at_startup

The default is off. If this parameter is turned on, when the MySQL service is started, MySQL loads the hot data on the local hard disk into the InnoDB cache pool.

innodb_buffer_pool_dump_now

This parameter is disabled by default. If this parameter is enabled, when the MySQL service is stopped, the hot data in the InnoDB cache pool is manually saved to the local hard disk.

innodb_buffer_pool_load_now

The default setting is off. If this parameter is turned on, when the MySQL service is started, the data on the local hard disk is manually loaded into the InnoDB cache pool.

innodb_buffer_pool_filename

If the InnoDB preheating function is enabled, when the MySQL service is stopped, MySQL saves the hot data in the InnoDB cache pool to the database root directory. The default file name is the value of this parameter.

After enabling InnoDB cache, you can use the following command to view the status information of the current InnoDB cache pool preheating:

show status like 'innodb_buffer%';
+---------------------------------------+-------------+
| Variable_name | Value |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 218 |
| Innodb_buffer_pool_bytes_data | 3571712 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 1 |
| Innodb_buffer_pool_pages_free | 7973 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 8191 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 1497 |
| Innodb_buffer_pool_reads | 219 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 1 |
+---------------------------------------+-------------+

The English here is relatively simple, so I won’t explain it.

4. InnoDB real-time monitoring

mysql> show engine innodb status\G