MySQL database engines

Source: Internet
Author: User
Main Parameters: 1) key_buffer_size-this is very important for the MyISAM table and is used to set the general KeyCache size in MySQL.

Main Parameters: 1) key_buffer_size-this is very important for the MyISAM table and is used to set the general Key Cache size in MySQL.

Main parameters:

1), key_buffer_size-This is very important for the MyISAM table and is used to set the size of the general Key Cache in MySQL. Generally, if MySQL runs on a 32-bit platform, it is recommended that the value not exceed 2 GB. If it is a 64-bit platform, you do not need to consider this limit, but it is recommended that the value not exceed 4 GB.

A reasonable value depends on the index size, data volume, and load. Remember, the MyISAM Table uses the operating system cache to cache data. Therefore, you need to leave some memory for them, in many cases, the data is much larger than the index. However, it is always necessary to check whether all key_buffer files are used -- The. MYI file only has 1 GB, while the key_buffer is set to 4 GB. This is a waste. If you seldom use the MyISAM table, keep the key_buffer_size smaller than 16-32 MB to meet the requirements for temporary table indexes on the disk.

2), key_buffer_block_size-Cache Block Size in the index Cache.

It is used to set the size of each Cache Block. In fact, it also limits the size of the File Block when the Index Block in the ". MYI" File is read.

Secondary parameters:

1) table_cache --Opening a table may have a high overhead. For example, MyISAM marks the MYI file header that the table is in use. You certainly do not want this operation to be too frequent. Therefore, you usually need to increase the number of caches so that the opened tables can be cached to the maximum extent. It requires the resources and memory of the operating system, which is of course not a problem for the current hardware configuration. If you have more than 200 tables, it may be appropriate to set it to 1024 (each thread needs to open the Table). If the number of connections is large, it will increase its value. I have seen a 100,000 error.

2) thread_cache --The overhead of thread creation and destruction may be large, because the connection/disconnection of each thread is required. I usually set at least 16. If the application has a large number of skip concurrent connections and the value of Threads_Created is large, I will increase the value. It does not need to create a new thread in common operations.

3) query_cache --This is useful if your application has a large number of reads without application-level caching. Don't set it too large, because it also requires a lot of overhead to maintain it, which causes MySQL to slow down. Usually set to 32-512 Mb. After setting, it is best to track for a period of time to check whether the operation is good. If the cache hit rate is too low under a certain load, enable it.

4) sort_buffer_size --If you only have some simple queries, you don't need to increase the value even though you have 64 GB of memory. Poor performance may be reduced.

Key Cache-related performance status parameter variables:

1). key_cache_pision_limit:The entire cache is divided into multiple regions as a percentage. The default value is 100, that is, only Warm Area. In fact, the LRU Algorithm Used in MySQL's Key Cache is not like the traditional algorithm, but only the Access frequency and the last access time, it is implemented through a unique linked list, but is divided into two parts.

2). key_cache_age_threshold:Controls when a table is downgraded in each region. The smaller the value, the easier it is to downgrade to the lower-level area. Table scanning is divided into Sequential Scan and Radom Scan. read_buffer_size indicates the cache used when sequential scan is set, read_rnd_buffer_size indicates the cache used when radom scan is set.

3). Key_blocks_not_flushed:The number of data blocks that have been changed in the key cache but have not been cleared to the hard disk.

4). Key_blocks_unused:Number of unused blocks in the key cache. You can use this value to determine how many key caches are used. For more information, see section 5.3.3. Key_buffer_size in "server system variables.

5). Key_blocks_used:Number of Blocks Used in the key cache. This value is a high-level mark, indicating the maximum number of blocks used at the same time.

6), Key_read_requests:The number of data block requests from the cache read key.

7), Key_reads:The number of data blocks read from the hard disk. If Key_reads is large, the value of Key_buffer_size may be too small. You can use Key_reads/Key_read_requests to calculate the cache loss rate.

8), Key_write_requests:Number of requests that write the data block of the Key to the cache.

9), Key_writes:The number of physical writes to the data block that writes the key to the hard disk.

10), Last_query_cost:The total cost of the final compiled query calculated by the query optimizer. Compare the costs of different query schemes for the same query. The default value 0 indicates that no compilation query is available. The default value is 0. Last_query_cost has a session range.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.