InnoDB Optimization for MySQL Optimization

It is easy to interrupt the study plan, and it is not easy to stick to it. Recently, there was a meeting in the company. To adjust the business direction, it is suggested to learn NodeJS. Before NodeJS, I will be at 1 o'clock, but I have not studied it in depth. Node syntax and client Js is basically a sample, this half year rarely develop things with the client. Originally, the foundation of JS was OK, and I was unfamiliar with this knowledge. It seems that knowledge is used in and out of use, not commonly used, and will be forgotten soon. Therefore, I reviewed the related knowledge of JS again. Learn the knowledge of Node server and socket. MySQL's plan ran aground like this. On Sunday, he ate, drank and slept, and was lazy in the morning. He stayed up until the afternoon. No more nonsense, continue the optimization series of MySQL, and look at the optimization items of InnoDB this time.

The primary index of InnoDB is a clustered index, which shares a table space with the data. For InnoDB, data is index, and index is data. The biggest difference between InnoDB caching mechanism and MyISAM is that InnoDB caches not only indexes, but also data.

1. InnoDB Cache Pool

InnoDB cache pool (InnoDB buffer pool) is the key to improve the performance of InnoDB. It can cache data, indexes and even other management data (metadata, row-level locks). You can use show variables like 'innodb% pool%'; To view the 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 InnoDB cache pool (InnoDBBufferPool), and the default value is 128M. The size of InnoDB cache pool has a great impact on the overall performance of InnoDB. If the current MySQL server is specially used as 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 cache pool is divided into one area. Appropriate increase of this parameter value can improve the concurrency performance of InnoDB.

innodb_additional_mem_pool_size

Specify the cache size used by InnoDB to store data dictionaries and other internal data. The default value is 2M. The larger the number of tables in InnoDB, the size of this parameter should be increased appropriately.

2. Internal structure of InnoDB cache pool

InnoDB maintains a cache pool in memory for caching data and indexes. The cache pool can be regarded as a very long linked list (list), which is divided into two sub-linked lists, one of which stores old page data, old page data is a data page that has not been accessed for a long time, one of which stores new page, and new page is a data page that has been accessed recently. old page accounts for 37% of the total linked list size by default and 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 old page and new page is called midpoint.

When the user accesses the data, InnoDB first searches the data in the InnoDB cache. If there is no data in the cache pool, InnoDB inserts the data in the hard disk into the InnoDB cache pool. If the cache pool is full, LRU algorithm is used to find out the expired old data

3. The InnoDB cache pool warms up.

After the MySQL server is started for a period of time, InnoDB will put the frequently accessed data (business data and management data) into the InnoDB cache, that is, the InnoDB cache pool stores the frequently accessed data (referred to as hot data for short). When the size of the InnoDB cache pool is several 10G or hundreds of G, what if you restart MySQL and load the hot data from the previous InnoDB cache pool into the InnoDB cache pool?

If the InnoDB cache pool preheated by InnoDB itself alone will be a long time period, which is a serious production accident for a busy system and cannot be tolerated. Fortunately, when MySQL version 5.6 supports service shutdown, hot data can be saved to the hard disk. When MySQL restarts, the hot data in the hard disk is first loaded into the cache of InnoDB, which can shorten the warm-up time and improve the efficiency when the business is busy and 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

If the MySQL service is stopped, the hot data in the InnoDB cache will be saved to the hard disk.

innodb_buffer_pool_load_at_starup

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

innodb_buffer_pool_dump_now

It is turned off by default. If this parameter is turned on, the hot data in the InnoDB cache pool will be saved to the local hard disk manually when the MySQL service is stopped.

innodb_buffer_pool_load_now

It is turned off by default. If this parameter is turned on, the data of the local hard disk will be manually loaded into the InnoDB cache pool when starting the MySQL service.

innodb_buffer_pool_filename

If the InnoDB warm-up function is turned on and the MySQL service is stopped, MySQL saves the hot data in the InnoDB cache pool to the database root directory, and the default file name is the value of this parameter.

After the InnoDB cache is turned on, you can use the following command to view the status information of the current InnoDB cache pool warm-up:

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