Detailed explanation of examples of InnoDB database optimization

InnoDB is designed for maximum performance when Mysql handles huge amounts of data. Its CPU efficiency is probably unmatched by any other disk-based relational database engine. Innodb is very popular among websites or applications with large amounts of data. Then it doesn’t need to be optimized. The answer is obvious: of course not! ! !

Study plans are easily interrupted, and persistence is not easy. Recently, there was a meeting in the company to adjust the business direction, so I suggested learning 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 client-side JS. In the past six months, few client-side things have been developed. Although my basic knowledge of JS was pretty good, I was unfamiliar with this knowledge. It seems that knowledge is used or lost. If it is not used frequently, it will be forgotten in a short time. So I reviewed the relevant knowledge of JS again. Learned the server and socket knowledge of Node. The MySQL plan was put on hold. On Sunday, I ate, drank, and slept. I was terribly lazy in the morning and stayed up until the afternoon. Without further ado, let’s continue the MySQL optimization series, this time looking at the optimization items of InnoDB. InnoDB’s main

is a clustered index, and the index and data share a common table space. For InnoDB, the data is the index, and the index is the data. The biggest difference between the InnoDBcaching mechanism and MyISAM is that InnoDB not only caches indexes, but also caches data.

InnoDB cache pool (InnoDB buffer pool) is the key to improving the performance of InnoDB. It can cache both data and indexes. Even other management data (metadata, row-level locks), etc. 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 is used to set the size of the InnoDB cache pool (InnoDBBufferPool). The default value is 128M.InnoDB cache The size of the pool has a greater impact on the overall performance of InnoDB. If the current MySQL server is exclusively used as a MySQL service, you can increase the size of this parameter as much as possible.

The default value of innodb_buffer_pool_instance is 1, which means that the InnoDB cache pool is divided into a region. Appropriately increasing the parameter value can improve the concurrency performance of InnoDB.

Specify the cache size used by InnoDB to store the data dictionary and other internal data. The default value is 2M. The more tables there are in InnoDB, the more Increase the size of this parameter appropriately.

InnoDB maintains a cache pool in memory for caching data and indexes. The cache pool can be thought of as a very long linked list (

). The linked list is divided into two sub-linked lists, one sub-linked list stores old page data, and the old page data is a data page that has not been accessed for a long time. A sub-linked list stores new page, and new page is the most recently accessed data page. The old page accounts for 37% of the entire linked list size by default, 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 |
+------------------------+-------+

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

After the MySQL server is 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 frequently accessed data ( Referred to as hot data). When the size of the InnoDB cache pool is tens of gigabytes or hundreds of gigabytes, if you restart MySQL, what if you load the hot data in the previous InnoDB cache pool into the InnoDB cache pool?

If you rely solely on InnoDB to preheat the InnoDB cache pool, it will take a long time. For systems with busy business, long-term hangup is a serious production accident and cannot be done. tolerate. Fortunately, MySQL version 5.6 supports saving hot data to the hard disk when shutting down the service. MySQL restarts by first loading the hot data in the hard disk into the InnoDB cache, which can shorten the warm-up time and improve the concurrency of busy business. time efficiency.

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   |
+-------------------------------------+----------------+

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 will be saved to the hard drive.

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

默认关闭，如果开启该参数，停止MySQL服务时，以手动方式将InnoDB缓存池中的热数据保存到本地硬盘。

innodb_buffer_pool_load_now

默认关闭，如果开启该参数，启动MySQL服务时，以手动方式将本地硬盘的数据加载到InnoDB缓存池中，

innodb_buffer_pool_filename

如果开启InnoDB预热功能，停止MySQL服务是，MySQL将InnoDB缓存池中的热数据保存到数据库根目录下，默认文件名是这个参数的值。

开启InnoDB缓存后，可以使用如下命令查看当前InnoDB缓存池预热的状态信息：

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      |
+---------------------------------------+-------------+