MySQL Optimization-InnoDB Optimization-mysql Optimization-innodb

MySQL Optimization-InnoDB Optimization-mysql Optimization-innodb

Learning Plans are easily interrupted, and persistence is not easy. We have recently held a meeting in the company. To adjust the business direction, we suggest learning NodeJS. I would have done a little before NodeJS, but I have not studied it in depth. The syntax of Node is basically the same as that of client Js. In the past six months, few clients have been developed. I am still familiar with the JS basics. It seems that all the knowledge is used for withdrawal and is not commonly used. It will not be long before it will be forgotten. So I reviewed the relevant JS knowledge again. I learned about Node servers and sockets. MySQL's plan was stranded like this. On Sunday, I had to eat, drink, and sleep. In the morning, I was so lazy that I had to wait until the afternoon. If you don't talk much about it, continue the MySQL optimization series. This time, let's take a look at the InnoDB optimization items.

The primary index of InnoDB is a clustered index, and the index and data are common tablespaces. For InnoDB, data is an index and an index is data. The biggest difference between the InnoDB cache mechanism and MyISAM is that InnoDB not only caches indexes, but also caches data.

I. InnoDB cache pool

The InnoDB buffer pool is the key to improving InnoDB performance. It can cache data, cache indexes, and even other management data (metadata and 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 InnoDBBufferPool size. The default value is MB. the size of the InnoDB cache 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, indicating that the InnoDB cache pool is divided into a region. Adding this parameter value appropriately can improve the concurrency performance of InnoDB.

Innodb_additional_mem_pool_size

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

Ii. Internal Structure of the InnoDB cache pool

InnoDB maintains a cache pool in the memory for caching data and indexing. The cache pool can be considered as a long list ). the linked list is divided into two sub-linked lists. One sub-linked list stores the old page data. The old page data is a data page that has not been accessed for a long time. A sub-linked list stores the new page, new page is the recently accessed data page. By default, the old page occupies 37% of the size of the entire linked list. You can view it using 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 first searches for data in the InnoDB cache. If no data exists in the cache pool, InnoDB inserts the data in the hard disk into the InnoDB cache pool. If the cache pool is full, the LRU algorithm is used to identify the expired old data.

Iii. InnoDB cache pool push.

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 (hot data ). When the size of the InnoDB cache pool is dozens or hundreds of GB, If you restart MySQL, what if you load the hot data from the InnoDB cache pool to the InnoDB cache pool?

If InnoDB cache pool is pushed by InnoDB itself, it will be a short period of time, which is a serious production accident for busy systems, not tolerable. Fortunately, when MySQL5.6 supports service shutdown, you can save Hot data to the hard disk. when MySQL restarts, it first loads the hot data in the hard disk to the InnoDB cache, this can shorten the push time and improve the efficiency when the business is busy and high concurrency.

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

By default, the parameter is disabled. If the parameter is enabled, the hot data in the InnoDB cache will be saved to the hard disk.

Innodb_buffer_pool_load_at_starup

This parameter is disabled by default. If this parameter is enabled, MySQL loads the hot data from the local hard disk to the InnoDB cache pool when the MySQL service is started.

Innodb_buffer_pool_dump_now

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

Innodb_buffer_pool_load_now

This parameter is disabled by default. If this parameter is enabled, data on the local hard disk is manually loaded into the InnoDB cache pool when MySQL is started,

Innodb_buffer_pool_filename

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

After InnoDB cache is enabled, run the following command to view the status information of the current InnoDB cache pool push:

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 and I will not explain it.

Iv. InnoDB Real-time Monitoring

mysql> show engine innodb status\G