MySQL buffer and cache settings

Supports more than 100 adjustable settings; Fortunately, mastering a few can satisfy most of the needs. Finding the correct values for these settings allows you to view the status variables by using the show status command to determine whether the mysqld is working as expected. The memory allocated to buffers and caches cannot exceed the existing memory in the system, so tuning usually requires some compromise.

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 | 27 |
| threads_connected | 15 |
| 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 further processing, such as the GROUP by, in which case the temporary table is 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)

The

Handler_read_rnd_next/com_select the table scan ratio--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!