MYSQL buffer and cache settings

Supports more than 100 Adjustable settings, but fortunately, a few can meet most of the needs. To find the correct values of these settings, you can run the show status command to view the STATUS variables and determine whether the operation of mysqld meets our expectations. The memory allocated to the buffer and cache cannot exceed the existing memory in the system. Therefore, tuning usually requires some compromise.

MySQL Adjustable settings can be applied to the entire mysqld process, or to a single client session.

Server Settings

Each table can be represented as a file on the disk. It must be opened first and then read. To speed up the process of reading data from files, mysqld caches these open files. The maximum number of these files is specified by table_cache in/etc/mysqld. conf. Listing 4 shows how to display activities related to opening a table.

Listing 4. Show the activity of opening a table
Mysql> show status like 'open % tables ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Open_tables | 5000 |
| Opened_table | 195 |
+ --------------- + ------- +
2 rows in set (0.00 sec)

Listing 4 shows that 5,000 tables are currently opened, and 195 tables need to be opened, because no available file descriptor is available in the cache (because the statistics have been cleared before, therefore, there may be 5,000 open records in 195 open tables ). If Opened_tables rapidly increases with the re-running of the show status Command, the cache hit rate is insufficient. If Open_tables is much smaller than table_cache, it indicates that the value is too large (but it is not a bad thing to increase the space ). For example, you can use table_cache = 5000 to adjust the table cache.

Similar to the table cache, a thread also has a cache. Mysqld will generate a thread as needed when receiving connections. On a busy server with fast connection changes, caching threads for later use can speed up the initial connection.

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

Listing 5. display 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. This value increases every time mysqld needs to create a new thread. If this number increases rapidly when the show status command is executed continuously, you should try to increase the thread cache. For example, thread_cache = 40 can be used in my. cnf to achieve this goal.

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

Listing 6. Determining 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 indicates the number of requests that hit the disk, and Key_read_requests indicates the total number. The number of read requests that hit the disk divided by the total number of read requests is the ratio of not hit-in this example, each 1,000 requests, about 0.6 do not hit the memory. If the number of disks hit by each 1,000 requests exceeds 1, you should consider increasing the keyword buffer. For example, if key_buffer = 384 M, the buffer is set to 384 MB.

Temporary tables can be used in more advanced queries. data must be saved to temporary tables before further processing (such as group by statements). Ideally, create a temporary table in the memory. However, if the temporary table is too large, it needs to be written to the disk. Listing 7 shows the statistics related to creating a temporary table.

Listing 7. determine 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 | 1, 32912 |
+ ------------------------- + ------- +
3 rows in set (0.00 sec)

Created_tmp_tables is increased each time a temporary table is used, and Created_tmp_disk_tables is increased for a disk-based table. There are no strict rules for this ratio, because it depends on the involved query. After a long period of observation, Created_tmp_disk_tables will display the ratio of the created disk table. You can determine the efficiency of the settings. Both tmp_table_size and max_heap_table_size can control the maximum size of the temporary table. Therefore, make sure that both values are set in my. cnf.

Settings for each session

The following settings apply to each session. Exercise caution when setting these numbers because these options indicate a large amount of memory when multiplied by the number of possible connections! You can use the code to modify the numbers in a session, or modify these settings for all sessions in my. cnf.

When MySQL needs to sort data, it will allocate a sort buffer when reading data from the disk to store the data rows. If the data to be sorted is too large, the data must be saved to the temporary files on the disk and sorted again. If the sort_merge_passes status variable is large, it indicates disk activity. Listing 8 lists some status counters related to sorting.

Listing 8. Displaying sorting 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 sort_merge_passes is large, you need to pay attention to sort_buffer_size. For example, sort_buffer_size = 4 M sets the sorting buffer to 4 MB.

MySQL also allocates some memory to read the table. Ideally, the Index provides enough information to read only the required rows, but sometimes the query (poor design or data nature) needs to read a large amount of data in the table. To understand this behavior, you need to know how many SELECT statements are run, and the number of times the data in the next row in the table needs to be read (instead of directly accessed through the index ). The command to implement this function is shown in listing 9.

Listing 9. Determining the table scan rate
Mysql> show status like "com_select ";
+ --------------- + -------- +
| Variable_name | Value |
+ --------------- + -------- +
| Com_selected | 318243 |
+ --------------- + -------- +
1 row in set (0.00 sec)

Mysql> show status like "handler_read_rnd_next ";
+ ----------------------- + ----------- +
| Variable_name | Value |
+ ----------------------- + ----------- +
| Handler_read_rnd_next | 1, 165959471 |
+ ----------------------- + ----------- +
1 row in set (0.00 sec)

Handler_read_rnd_next/Com_select returns the table scan rate-in this example, It is 521: 1. If the value exceeds 4000, check read_buffer_size, for example, read_buffer_size = 4 M. If this number exceeds 8 Mb, you should discuss with the developer about tuning these queries!