Some operations on MySQL optimization

1. Cache pool

MySQL is designed through the cache pool, which means that part of the data is stored in the memory. When we look for a piece of data , if it is found in the cache pool, it can be directly
obtained from the cache. If it is not there, then load it from the disk. Therefore, it is recommended that the database server adopts a 64-bit operating system, so that it can use a large of memory. The following parameters are used to set the memory pool
:

SHOW VARIABLES LIKE "innodb_buffer_pool_instances"

Check the number of cache pools, which can be set through glob or directly in the configuration file .

show VARIABLES like "innodb_buffer_pool_size"

Check the size of each cache pool, The query comes out in bytes.

It should be noted that this setting should take into account the capabilities of the own server.

In order to verify the cache hit rate, you can pass

SHOW ENGINE INNODB STATUS

There is a line of output that is very important, you can see Hit rate:

Buffer pool hit rate 1000 / 1000

represents the cache hit rate, which generally cannot be less than 95%

2. LRU list

Cache in data is stored in the LRU method, which means that the latest data queried is placed at the front, which means it is hot data, but there are some variants of this LRU algorithm, specifically each

query The data that comes out is not inserted into the front position, but into the middle. This position becomes the midpoint. The default position is the 3/5 position. The reason for this is to prevent some table scanning operations from

All the data is refreshed. There are several parameters that can be set.

SHOW VARIABLES LIKE "innodb_old_blocks_pct"

represents the position of the midpoint. The default is:

, which is probably 3/5

There is also another parameter:

SHOW VARIABLES LIKE "innodb_old_blocks_time"

, which is how long it takes for the data to take effect when it is inserted into the midpoint.

If you encounter a situation where hotspot data is relatively concentrated in a real environment, you can prevent hotspot data from being flushed out by setting LRU parameters

The above is the detailed content of Some operations on MySQL optimization. For more information, please follow other related articles on the PHP Chinese website!