Optimization of MySQL database 3 "Optimization 3" Cache settings

1 , table Cache

related parameters: Table_open_cache

Specifies the size of the table cache. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and put into it, which allows for faster access to the table contents. By checking the status value of the peak time, if you find that open_tables equals Table_cache, and opened_tables is growing, you need to increase the value of Table_open_cache. Note that this parameter can not be blindly set to a large, if the setting is too large, will cause insufficient file descriptors, resulting in performance instability or database connection failure. Recommended for 512

Table_cache = 512 Specifies the size of the table cache, and if the opened_tables is too large, the table_cache should be larger. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and put into it, which allows for faster access to the table contents. By checking the status values of peak time open_tables and Opened_tables, you can determine whether you need to increase the value of Table_cache. If you find that open_tables equals Table_cache, and opened_tables is growing, you need to increase the value of Table_cache (the above status values can use Show status like ' Open%tables ' obtained). Note that you cannot blindly set the Table_cache to a very large value. If set too high, it may cause insufficient file descriptors, resulting in performance instability or connection failures.

2 , query caching

related parameters: query_cache_size/query_cache_type

QC (Note: query cache abbreviation) is primarily used to cache result sets in MySQL, which is a result set executed by an SQL statement, so only for SELECT statements. Querying on the MySQL server enables high-speed query caching. Having the database engine quietly handled in the background is one of the most effective ways to improve performance. When the same query is executed multiple times, it is fairly fast if the result is extracted from the cache.

But with the development, this parameter also reveals some problems. The memory of the machine is getting bigger and larger, and people are accustomed to allocating the previously useful parameters more and more. This increase in parameters also raises a number of questions. Let's start by analyzing how Query_cache_size works: When a select query works in DB, the DB caches the statement, and when the same SQL comes back to the DB, the DB returns the result from the cache to the client if the table does not change. There is a shut-down point, that is, when DB is working with Query_cache, it requires that the table involved in the statement not be changed during this time period. So what happens to the data in Query_cache if the table is changed? The first thing to do is to invalidate the Query_cache and the table-related statements, and then write the update. So if the query_cache is very large, the query structure of the table is more, the query statement invalidation is slow, an update or insert will be very slow, so see is update or insert how slow. Therefore, in the database write volume or update volume is also relatively large system, this parameter is not suitable for allocation too large. And in the high concurrency, write a large-scale system, built to disable the function.
The use of QC requires multiple parameter mates, the most critical of which is the query_cache_size and Query_cache_type, which sets the memory size of the cache recordset, and the latter sets the use of QC in what scenario. In the past experience, the medium-sized website, query_cache_size set 256MB enough. Of course, you can also make adjustments by calculating the QC's hit ratio.

qcache_hits/(qcache_hits + qcache_inserts) * 100%

Query_cache_type has three options: 0 (OFF, no QC), 1 (on, using QC by default), 2 (DEMAND, no QCby default). Why add "Default"? MySQL also supports the dynamic use of cached SQL syntax, as follows:

# Force use of cache SELECT Sql_cache ID from table

# force do not use cache SELECT Sql_no_cache ID from table

In some processing tasks, we can actually prevent the query cache from working.

Query cache does not work
$r = mysql_query ("Select Usernamefrom user WHERE signup_date >= curdate ()");
Query Cache works!
$today =date ("y-m-d");
$r = mysql_query ("Selectusername from user WHERE signup_date >= ' $today '");

3 , index cache

related parameters:key_buffer_size

This is a parameter that has the greatest impact on MyISAM table performance, and is used to set the size of the memory area used to cache index files in the MyISAM storage engine. If there is enough memory, this cache area size can be set to the sum of the index size of all the MyISAM tables, that is, all the * in the data directory. Myi the sum of the file sizes.

Note that because the MyISAM engine caches only the index blocks in memory, the table database blocks are not cached. Therefore, it is important to query the SQL statements so that the filters are in the index as much as possible in order to use the index cache to improve query efficiency.

Calculate the probability of an index cache Miss Key_reads/key_read_requests * 100%

If the key_reads is too large, then the key_buffer_size should be bigger. Increase the index (for all reads and multiple writes) that can be better processed, and if the sequential scan requests to the table are very frequent, and you think that frequent scans are going too slowly, you can improve their performance by increasing the value of the variable and the size of the memory buffer. The parameter can be set to 384M or 512M for a server that has around 4GB. By checking the status values key_read_requests and Key_reads, you can see if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above status values can be obtained using the show status like ' key_read% '). Note: This parameter value setting is too large to cause the overall efficiency of the server to degrade.

4 , insert Cache

related parameters:bulk_insert_buffer_size

Used by the MyISAM engine to cache the temporary cache write data when bulk insert data is used. When we use the following data to write to the statement, we will use this memory region to cache the bulk structure of data to help bulk write data files, the default 8M, it is recommended not to exceed 32M

Insert ... select ...
Insert ....), (...), (...),,...
Load Data infile .../* Non-empty table */

5. Log Cache

related parameters:binlog_cache_size

Used in an environment where binary logging (Binlog) logging is turned on, a memory cache that is designed to cache binlog data in a short period of time, as used by MySQL to improve binlog recording efficiency.

If there are no large transactions in the database, writing is not particularly frequent, 2MB~4MB is a suitable choice. However, if the database large transactions, write more frequently, can be appropriately increased. When used, it is also possible to analyze whether the set of binlog_cache_size is sufficient by Binlog_cache_use and binlog_cache_disk_use, and whether there is a large number of Binlog_ The cache was cached with temporary files due to insufficient memory size.

6 , other cache parameter settings

1 ), Key_buffer changing the index buffer length

in general, this variable controls the length of the buffer used when processing the index table (read/write operations). The MySQL user manual indicates that the variable can be continuously increased to ensure optimal performance of the index table, and it is recommended to use the size of system memory 25% as the value of the variable. This is one of the most important configuration variables for MySQL, and if you are interested in optimizing and improving your system performance, you can start by changing the value of the key_buffer_size variable.  

　　2 ), Long_query_time set a time limit for slow-length queries

MySQL has a "slow query log" that automatically records all queries that have not yet ended in a specific time frame. This log is useful for tracking inefficient or misbehaving queries and for finding optimized objects. The Long_query_time variable controls this maximum time limit, in seconds.  

3), Sort_buffer_size Key Optimization Parameters

sort_buffer_size = 4M the size of the buffer that can be used when the query is sorted. Note: The allocated memory for this parameter is per-connection exclusive, and if there are 100 connections, then the actual allocated total sort buffer size is 100x4 = 400MB. Therefore, the recommended setting for a server that has around 4GB is 4-8m.  

4 ), read_buffer_size the size of the buffer that can be used by the Read query operation

read_buffer_size= 4M. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection. MySQL read-in buffer size. A request to sequentially scan a table allocates a read-in buffer, and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer. If the sequential scan requests for a table are frequent, and you think that frequent scans are going too slowly, you can improve their performance by increasing the value of the variable and the size of the memory buffer.  

5 ), join_buffer_size the size of the buffer that can be used by the Federated query Operation

join_buffer_size= 8M, as with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.  

6 ), thread_cache_size

thread_cache_size= 64 Server thread Cache This value indicates that the number of threads stored in the cache can be re-used, and if the threads_created is too large, increase the value of thread_cache_size. If there is room in the cache when the connection is disconnected, then the client's thread will be placed in the cache, and if the thread is requested again, then the request will be read from the cache, and if the cache is empty or a new request, then the thread will be recreated, if there are many new threads, Increasing this value can improve system performance. You can see the effect of this variable by comparing the variables of the Connections and threads_created states.

7 ), tmp_table_size

if the created_tmp_disk_tables is too large, increase the value of tmp_table_size and replace disk-based with a temporary memory-based table. The default size of Tmp_table_size is 32M. If a temporary table exceeds that size, MySQL produces an error in the form of the tabletbl_name is, and if you do many advanced GROUP by queries, you can increase the tmp_table_size value.  

8 ), read_rnd_buffer_size

MySQL's random read (query operation) buffer size. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When you sort a query, MySQL scans the buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriately as possible to avoid excessive memory overhead.

Optimization of MySQL database 3 "Optimization 3" Cache settings