Bill: MYSQL5.6 Cache Performance Optimization My.ini file configuration scheme

Using MySQL version:5.6

 [ client  ]  ......default-character-set  = Gbkdefault-storage-engine  =myisammax_connections  =1000max_connect_errors  =500back_log  =200 Interactive_timeout  =7200query_cache_size  = 128m 
 Query_cache_type=1......table_open_cache  = 4000 ......myisam_max_sort_file_size  =myisam_sort_buffer_size  =128mkey_buffer_size  = 1024mread_buffer_size  =512m  

Official documents:

Http://dev.mysql.com/doc/refman/5.6/en/mysqld-option-tables.html

Http://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html

1, Back_log

The Back_log value indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops answering a new request. That is, if the MySQL connection data reaches max_connections , the new request will be present in the stack, waiting for a connection to release the resource, the number of that stack is back_log, if the number of waiting connections exceeds back_log, The connection resource will not be granted. Will be reported:unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | Login | The NULL pending process timed out.

The Back_log value cannot exceed the size of the listening queue for TCP/IP connections. If it is not valid, view the size of the listening queue for the current system's TCP/IP connection command: Cat/proc/sys/net/ipv4/tcp_max_syn_backlog The current system is 1024. For Linux systems, the recommended setting is an integer less than 512.

View MySQL current system default Back_log value, command:

 like ' Back_log '; View Current Quantity

2, Interactive_timeout

Interactive_timeout is the number of seconds that MySQL waits before waiting for an active connection to close the connection.

Wait_timeout is the number of seconds that MySQL waits before waiting for an inactive connection to close the connection.

3, Query_cache_size, Query_cache_type

MySQL query cache is used to cache select query results, and the next time you receive the same query request, no longer perform the actual query processing and return the results directly, there is such a query cache can improve the speed of queries, query performance is optimized, the precondition is that you have a large number of identical or similar queries, The data in the table is rarely changed , otherwise it is not necessary to use this feature. The value of the Qcache_lowmem_prunes variable can be used to check whether the current value satisfies the load of your current system. Note: If the table you are querying is updated more frequently and there are very few identical queries, it is best not to use the query cache.

Specific configuration method:

1. Set Query_cache_size to a specific size, depending on the actual size of the query, but preferably set to a multiple of 1024, the reference value is 32M.

2. Add one line:query_cache_type=1

If set to 0, then you can say that your cache is useless at all, which is equivalent to disabling it. But in this case, does the size of the query_cache_size system have to be assigned to it, and the question remains to be tested?

If set to 1, all results will be cached unless your SELECT statement uses Sql_no_cache to disable query caching.

If set to 2, only the queries that require caching are specified by Sql_cache in the SELECT statement.

Save the file, restart the MySQL service, and verify that it is actually turned on by the following query:

 like '%query_cache%';

+ —————————— + ——— –+

| variable_name | Value |

+ —————————— + ——— –+

| Have_query_cache | YES |

| Query_cache_limit | 1048576 |

| Query_cache_min_res_unit | 4096 |

| Query_cache_size | 134217728 |

| Query_cache_type | On |

| Query_cache_wlock_invalidate | OFF |

+ —————————— + ——— –+

6 rows in Set (0.00 sec)

Mainly see whether the values of query_cache_size and Query_cache_type are consistent with our set:

Here the value of the query_cache_size is 134217728, we set the 128M, the actual is the same, but the unit is different, you can convert the next: 134217728 = 128*1024*1024.

The Query_cache_type is set to 1, which is shown as on, as mentioned earlier.

In short, see the above display indicates that the settings are correct, but in the actual query whether the query can be cached, but also requires manual testing, we can through the show status like '%qcache% '; Statement to test, now that we have opened the query cache function, before executing the query, we first look at the values of the relevant parameters:

 like '%qcache%';

+ ————————-+ ——— –+

| variable_name | Value |

+ ————————-+ ——— –+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 134208800 |

| Qcache_hits | 0 |

| Qcache_inserts | 0 |

| Qcache_lowmem_prunes | 0 |

| qcache_not_cached | 2 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 1 |

+ ————————-+ ——— –+

8 rows in Set (0.00 sec)

Here, by the way, explain the effect of these parameters:

Qcache_free_blocks: Indicates how many remaining blocks are currently in the query cache, and if the value is larger, it means that there is too much memory fragmentation in the query cache and may be collated at some time.

Qcache_free_memory: The memory size of the query cache, through this parameter can be very clear to know whether the current system query memory is sufficient, is more, or is not enough, the DBA can make adjustments according to the actual situation.

Qcache_hits: Indicates how many times the cache has been hit. We can mainly use this value to verify the effect of our query cache. The larger the number, the better the caching effect.

Qcache_inserts: Indicates how many misses were missed and then inserted, meaning that the new SQL request was not found in the cache, had to perform query processing, and then insert the results into the query cache after executing the query processing. Such a situation, the more times, the query cache is applied to the less, the effect is not ideal. Of course, the query cache is empty after the system has just started, which is normal.

Qcache_lowmem_prunes: This parameter records how many queries have been removed from the query cache because of insufficient memory. With this value, the user can adjust the cache size appropriately.

Qcache_not_cached: Represents the number of queries that are not cached because of the Query_cache_type settings.

Qcache_queries_in_cache: The number of queries cached in the current cache.

Qcache_total_blocks: The number of blocks currently cached.

4, Table_open_cache

The Table_cache parameter sets the number of table caches. Each connection comes in with at least one table cache open. Therefore, the size of the Table_open_cache should be related to the Max_connections setting . For example, for 200 parallel-running connections, the table should have at least 200xN of Cache , where N is the maximum number of tables in a join of a query that the application can execute . In addition, some additional file descriptors need to be reserved for temporary tables and files.

Caching mechanism

When Mysql accesses a table, if the table is already open in the cache, it can access the cache directly, and if it is not yet cached, but there is room in the MySQL table buffer, then the table is opened and placed in the table buffer, and if the table cache is full, the currently unused table is released according to certain rules. or temporarily expand the table cache to hold, the advantage of using table caching is that you can access the contents of the table more quickly.

Parameter tuning

In general, you can determine whether you need to increase the value of Table_cache (where Open_tables is the number of currently open tables) by looking at the status values Open_tables and Opened_tables of the database running peak time, opened_tables is the number of tables that have been opened). That is, if open_tables approach Table_open_cache, and opened_tables this value is gradually increasing, then consider increasing the size of this value. There is table_locks_waited higher than the time, also need to increase the table_open_cache.

 like ' Open%_tables ';

If the value of Open_tables is close to the value of Table_open_cache and the opened_tables is growing, it is possible that MySQL is releasing the cached table to accommodate the new table, which may require a larger Table_open_cache value. For most cases,

Values that are more appropriate:

Open_tables/opened_tables >= 0.85
Open_tables/table_open_cache <= 0.95

If the grasp of this parameter is not very accurate, VPS management encyclopedia gives a very conservative set of suggestions: Put the MySQL database in the production environment for a period of time, and then adjust the value of the parameter is larger than the number of opened_tables, It is also guaranteed to be slightly larger than opened_tables under extreme conditions of higher loads.

In the case of MySQL default installation, the value of Table_open_cache in the machine below 2G memory defaults to 256 to 512, if the machine has 4G memory, the default value is 2048, but this means that the larger the machine memory, this value should be greater, because table_ Open_cache increased, so that MySQL response to SQL faster, will inevitably generate more deadlock (dead Lock), which makes the database a whole set of operations slowed down, severely affect performance. So in peacetime maintenance or in accordance with the actual situation of the library to make judgments, to find the most suitable for your maintenance of the library's Table_open_cache value.

Empty cache

Perform

Flush tables;

The command will empty all currently cached tables.

5, Myisam_max_sort_file_size

# The size of the maximum temporary file allowed when MySQL rebuilds the index (when REPAIR, ALTER TABLE or LOAD DATA INFILE).
# If the file size is larger than this value, the index will be created by the key buffer (slower)

6, Myisam_sort_buffer_size

# MyISAM the buffer required to reorder when the table is changed

7, Key_buffer_size

1. The size of a single key_buffer can not exceed 4G, if set more than 4G, it is possible to encounter the following 3 bugs:

http://bugs.mysql.com/bug.php?id=29446

http://bugs.mysql.com/bug.php?id=29419

http://bugs.mysql.com/bug.php?id=5731

2. It is recommended that the Key_buffer be set to 1/4 of physical memory (for MyISAM engine), or even 30%~40% of physical memory, if the key_buffer_size setting is too large, the system will change pages frequently and reduce system performance. Because MySQL caches data using the operating system's cache, we have to leave enough memory for the system, and in many cases the data is much larger than the index.

3. If the machine performance is superior, can set up multiple key_buffer, let different key_buffer to cache the specialized index separately

Above just for "novice", we can also further optimize the key_buffer_size, using "show status" to see ", Key_read_requests Key_reads Key_write_requests as well as" to Key_writes adjust to the size of the application that is more suitable for you,Key_reads/Key_read_requests的大小正常情况下得小于0.01

8, Read_buffer_size

# MySQL read into 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. As with Sort_buffer_size, the allocated memory for this parameter is also exclusive to each connection.

Bill: MYSQL5.6 Cache Performance Optimization My.ini file configuration scheme