Mysql memory parameter optimization

1. Key buffer

A. The parameter key_buffer_size only affects the table of the myisam engine;

B. It determines the speed of index processing, especially the speed of index reading;

C. You can check whether this parameter is set appropriately to evaluate it. Two status values ​​show status like '%key_read%';

D. Key_read_requests represents the total number of requests, key_reads represents the number of read disks;

E. key_reads / key_read_requests should be as low as possible, at least 1:100. 1:1000 is better;

F. Another way to estimate key_buffer_size is to add up the space occupied by the indexes of each table in your database.

2. Query cache

A. The query cache mainly stores SELECT statements and query results in the buffer;

B. The reasonable setting of the parameter query_cache_size can be used to check the status of the db: show status like '%qcache%' ​​​​Indicates the currently cached items Number

Qcache inserts 20649006

Qcache hits 79060095 It seems that the repeated query rate is quite high

Qcache lowmem PRunes 617913 There have been so many cases where the cache is too low

Qcache not cached 189896

Q cache free memory 18573912 currently remaining Cache space

Qcache free blocks 5328 This number seems a bit large and has many fragments

Qcache total blocks 30953

F. The results show that the query cache value needs to be set larger;

G. Qcache_free_blocks, if the value is very large , it indicates that there are many fragments in the buffer

H. The value of Qcache_lowmem_prunes is very large, which indicates that there is often insufficient buffering. At the same time, the value of Qcache_hits is very large, which indicates that the query buffer is used very frequently. At this time, the buffer size of Qcache_hits needs to be increased. If the value is small, it means that your query repetition rate is very low. In this case, using query buffering will affect efficiency, so you can consider not using query buffering. In addition, adding SQL_NO_CACHE to the SELECT statement can clearly indicate that the query buffer is not used.

3. Table cache

A. table_cache specifies the size of the table cache;

B. Whenever MySQL accesses a table, if there is space in the table buffer, the table is opened and placed in it. This allows faster access to table contents;

C. By checking the status values ​​Open_tables and Opened_tables at peak times, you can decide whether you need to increase the value of table_cache. If you find that open_tables is equal to table_cache, and open_tables is constantly growing, then you need to increase the value of table_cache;

D. Note that table_cache cannot be set to a large value blindly. If set too high, it may cause insufficient file descriptors, resulting in unstable performance or connection failure.

4. Innodb buffer

A. innodb_buffer_pool_size - This is very important for Innodb tables. Innodb tables are more sensitive to buffering than MyISAM tables. MyISAM can run fine under the default key_buffer_size setting, but Innodb runs like a snail under the default innodb_buffer_pool_size setting.

B. Since Innodb caches both data and indexes, there is no need to leave too much MySQL database memory to the operating system. Therefore, if you only need to use Innodb, you can set it up to 70-80% of the available memory.

C. If your data volume is not large and will not increase dramatically, then there is no need to set innodb_buffer_pool_size too large.

D. innodb_log_file_size is important in case of high write load especially large data sets. The larger the value, the better the performance, but be aware that recovery time may increase. I usually set it to 64-512MB, depending on the server size. The default setting of innodb_log_buffer_size provides acceptable server performance under moderate write load and short transactions.

E. Are you worried that Innodb_flush_logs_at_trx_commit is 1000 times slower than MyISAM? It seems that maybe you forgot to modify this parameter. The default value is 1, which means that every committed update transaction (or every statement outside of a transaction) is flushed to disk, which is quite resource-intensive, especially without a battery-backed cache. Many applications, especially those converted from MyISAM, will be fine by setting its value to 2, which means that the log will not be flushed to disk, but only to the operating system cache. The log is still flushed to disk every second, so the cost of 1-2 updates per second is usually not lost. If it is set to 0, it will be much faster, but it will also be relatively unsafe. Some transactions will be lost when the MySQL server crashes. A setting of 2 causes the portion of the transaction that is flushed to the operating system cache to be lost.

The above is the content of Mysql memory parameter optimization. For more related articles, please pay attention to the PHP Chinese website (m.sbmmt.com)!