Mysql Buffering and caching settings detailed _mysql

The MySQL adjustable settings can be applied to the entire mysqld process, or to a single customer opportunity session.

Server-side settings

Each table can be represented as a file on disk that must be opened before read. To expedite the process of reading data from a file, mysqld caches these open files, with the maximum number specified by the Table_cache in/etc/mysqld.conf. Listing 4 shows how to display activities related to opening a table.

Listing 4. Show activities that open a table

Mysql> show STATUS like ' open%tables ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Open_tables  | 5000 |
| Opened_tables | 195  |
+---------------+-------+
2 rows in Set (0.00 sec)

Listing 4 shows that 5,000 tables are currently open and 195 tables need to be opened because there are no more file descriptors available in the cache (because the statistics are already cleared before, there may be 5,000 open tables in which there are only 195 opened records). If opened_tables quickly increases as the show STATUS command is rerun, the cache hit rate is not sufficient. If the open_tables is much smaller than the Table_cache setting, it means that the value is too large (although there is room to grow it is always not a bad thing). For example, you can adjust the cache of a table by using Table_cache =5000.

Like a table cache, there is also a cache for threads. MYSQLD generates threads as needed when receiving connections. On a busy server with a fast-changing connection, caching a thread for later use can speed up the initial connection.

Listing 5 shows how to determine whether enough threads are cached.

Listing 5. Show Thread usage statistics

Mysql> show STATUS like ' threads% ';
+-------------------+--------+
| Variable_name   | Value |
+-------------------+--------+
| threads_cached |
| threads_connected | |
| threads_created  | 838610 |
| Threads_running  | 3   |
+-------------------+--------+
4 rows in Set (0.00 sec)
 

The important value here is threads_created, which is incremented each time the mysqld needs to create a new thread. If this number increases rapidly as the show STATUS command is executed continuously, you should try to increase the thread cache. For example, you can use Thread_cache = 40来 in my.cnf to accomplish this.

The key buffer holds the index block of the MyISAM table. Ideally, requests for these blocks should come from memory, not from disk. Listing 6 shows how to determine how many blocks are read from disk and how many blocks are read from memory.

Listing 6. Determine keyword efficiency

Mysql> Show status like '%key_read% ';
+-------------------+-----------+
| Variable_name   | Value   |
+-------------------+-----------+
| key_read_requests | 163554268 |
| Key_reads     | 98247 |
+-------------------+-----------+
2 rows in Set (0.00 sec)

Key_reads represents the number of requests to hit the disk, Key_read_requests is the total. The number of read requests to hit a disk divided by the total number of read requests is an out of proportion--in this case, about 0.6 of every 1,000 requests have no hit memory. If you have more than 1 hit disks per 1,000 requests, you should consider increasing the keyword buffer. For example, Key_buffer =384m sets the buffer to 384MB.

Temporary tables can be used in more advanced queries, where the data must be saved to a temporary table before it is processed further (for example, groupby words); Ideally, temporary tables are created in memory. However, if the temporary table becomes too large, it needs to be written to disk. Listing 7 shows the statistics related to the creation of a temporary table.

Listing 7. Determining the use of temporary tables

Mysql> show STATUS like ' created_tmp% ';
+-------------------------+-------+
| Variable_name      | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 30660 |
| Created_tmp_files    | 2   |
| Created_tmp_tables   | 32912
| +-------------------------+-------+
3 rows in Set (0.00 sec)
 

Each use of a temporary table increases created_tmp_tables, and the disk-based table increases created_tmp_disk_tables. There are no strict rules for this ratio, because it depends on the query involved. Long observation Created_tmp_disk_tables Displays the ratio of disk tables created, and you can determine the efficiency of the settings. Both Tmp_table_size and max_heap_table_size can control the maximum size of a temporary table, so make sure that both values are set in MY.CNF.

Settings for each session

The following settings are for each session. Be careful when setting these numbers because they represent a lot of memory when multiplied by the number of possible connections! You can modify these numbers in a session by code, or modify these settings for all sessions in MY.CNF.

When MySQL must be sorted, a sort buffer is allocated to hold the data rows when the data is read from disk. If the data to be sorted is too large, the data must be saved to a temporary file on disk and sorted again. If the sort_merge_passes state variable is large, this indicates the activity of the disk. Listing 8 shows some status counter information related to the sort.

Listing 8. Show sort Statistics

Mysql> show STATUS like "sort%";
+-------------------+---------+
| Variable_name   | Value  |
+-------------------+---------+
| sort_merge_passes | 1    |
| Sort_range    | 79192  |
| Sort_rows     | 2066532 |
| Sort_scan     | 44006 |
+-------------------+---------+
4 rows in Set (0.00 sec) 

If the sort_merge_passes is large, it means you need to pay attention to sort_buffer_size. For example, sort_buffer_size = 4M Sets the sort buffer to 4MB.

MySQL also allocates some memory to read the table. Ideally, the index provides enough information to read only the rows that are needed, but sometimes the query (poor design or the nature of the data) requires reading a large amount of data from the table. To understand this behavior, you need to know how many SELECT statements have been run and the number of times you need to read the next row of data in the table (not directly through the index). The commands to implement this feature are shown in Listing 9.

Listing 9. Determine table scan rate

Mysql> show STATUS like "Com_select";
+---------------+--------+
| variable_name | Value |
+---------------+--------+
| Com_select  | 318243
| +---------------+--------+
1 row in Set (0.00 sec)
mysql> show STATUS like "Handler_read_rnd_next";
+-----------------------+-----------+
| Variable_name     | Value   |
+-----------------------+-----------+
| Handler_read_rnd_next | 165959471 |
+-----------------------+-----------+
1 row in Set (0.00 sec)

Handler_read_rnd_next/com_select the ratio of table scans--In this case, 521:1. If the value is more than 4000, you should view read_buffer_size, such as Read_buffer_size = 4M. If this number exceeds 8M, you should talk to the developers about tuning these queries!

To view the database cache configuration

Mysql> show VARIABLES like '%query_cache% ';
+ —————————— + ——— +
| variable_name | Value |
+ —————————— + ——— +
| have_query_cache | YES | – Whether the query cache is available
| query_cache_limit | 1048576 |– can cache the maximum value of a specific query result
| query_cache_min_res_unit | 4096 |
| query_cache_ Size | 599040 | – Size of query cache
| query_cache_type | On | – Block or support query caching
| query_cache_wlock_invalidate | Off |
+ —————————— + ——— +

Configuration method:

Find the following in the MySQL configuration file My.ini or my.cnf:

# Query cache is used to cache SELECT results and later Returnthem

# without actual executing the same Query once Agai N. Having thequery

# cache enabled significant speed improvements, Ifyour

# have a lot of identical queries and rarely changing tables.

the # "Qcache_lowmem_prunes" status variable to check if the CurrentValue # are high

enough for your load.

# note:in Case your tables change very often or if your queriesare

# textually different every time, the query cache May result Ina

# slowdown instead of a performance improvement.

Query_cache_size=0

The above information is the default configuration, the comment means that the MySQL query cache is used to cache the select query results, and the next time you receive the same query request, no longer perform the actual query processing and directly return the results, there is such a query cache can improve the speed of query, so that query performance is optimized, The prerequisite is that you have a large number of the same or similar queries, and rarely change the table data, otherwise it is not necessary to use this feature. You can check whether the current value satisfies your current system load by qcache_lowmem_prunes the value of the variable. Note: If you are querying a table that is updated more frequently and rarely has the same query, it is best not to use the query cache.

Specific configuration method:

1. will be query_cache_size set to a specific size, depending on the actual size of the query, but preferably set to 1024 multiples, reference value 32M.

2. Add one line:query_cache_type=1

The Query_cache_type parameter is used to control the type of cache, note that this value cannot be set arbitrarily, must be set to a number, optional item, and the description is as follows:

If set to 0, then you can say that your cache is not used at all, equivalent to disabled. But in this case the size system set by Query_cache_size is to be allocated for it, and this question needs to be tested?

If set to 1, all results will be cached unless your SELECT statement disables the query cache with Sql_no_cache.

If set to 2, caching is only specified in the SELECT statement by Sql_cache the query that requires caching.

OK, some of the files after configuration are as follows:

query_cache_size=128m

query_cache_type=1

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

Mysql> Show variables 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 value of query_cache_size and Query_cache_type is 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 under: 134217728 = 128*1024*1024.

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

In short, see the above display indicates the correct setting, but in the actual query is able to cache the query, but also manual testing, we can show Statuslike '%qcache% ', the statement to test, and now we open the query caching function, before executing the query, Let's take a look at the value of the relevant parameter:

 mysql> show status like '%qcache% '; + ————————-+ ——— –+ | variable_name |

Value | + ————————-+ ——— –+ |

Qcache_free_blocks |1 | | Qcache_free_memory |

134208800| |

Qcache_hits |