Home > Database > Mysql Tutorial > body text

Some operations on MySQL optimization

迷茫
Release: 2017-03-26 13:13:23
Original
1228 people have browsed it

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"
Copy after login

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"
Copy after login

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
Copy after login

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

Buffer pool hit rate 1000 / 1000
Copy after login

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"
Copy after login

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"
Copy after login

, 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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!