MySQL performance indicator calculation and Optimization

MySQL performance indicator calculation and Optimization
1 QPS computing (queries per second) for databases dominated by the MyISAM Engine

Mysql> show GLOBAL status like 'questions ';
+ --------------- + ------------ +
| Variable_name | Value |
+ --------------- + ------------ +
| Questions | 2009191409 |
+ --------------- + ------------ +
1 row in set (0.00 sec)

Mysql> show global status like 'uptime ';
+ --------------- + -------- +
| Variable_name | Value |
+ --------------- + -------- +
| Uptime | 388402 |
+ --------------- + -------- +
1 row in set (0.00 sec)

QPS = questions/uptime = 5172, the average QPS since mysql was started, if you want to calculate the QPS in a certain period of time, you can get the interval t2-t1 during peak hours, then calculate the Q value of t2 and t1 moment, QPS = (q2-q1)/(t2-t1)

For DB dominated by the InnnoDB Engine

Mysql> show global status like 'com _ Update ';
+ --------------- + ---------- +
| Variable_name | Value |
+ --------------- + ---------- +
| Com_update | 87094306 |
+ --------------- + ---------- +
1 row in set (0.00 sec)

Mysql> show global status like 'com _ select ';
+ --------------- + ------------ +
| Variable_name | Value |
+ --------------- + ------------ +
| Com_selected | 1108143397 |
+ --------------- + ------------ +
1 row in set (0.00 sec)

Mysql> show global status like 'com _ delete ';
+ --------------- + -------- +
| Variable_name | Value |
+ --------------- + -------- +
| Com_delete | 379058 |
+ --------------- + -------- +
1 row in set (0.00 sec)

Mysql> show global status like 'uptime ';

+ --------------- + -------- +
| Variable_name | Value |
+ --------------- + -------- +
| Uptime | 388816 |
+ --------------- + -------- +
1 row in set (0.00 sec)

QPS = (com_update + com_insert + com_delete + com_select)/uptime = 3076. The QPS query method for a certain period of time is the same as above.

2 TPS calculation (transactions per second)

Mysql> show global status like 'com _ commit ';

+ --------------- + --------- +
| Variable_name | Value |
+ --------------- + --------- +
| Com_commit | 7424815 |
+ --------------- + --------- +
1 row in set (0.00 sec)


Mysql> show global status like 'com _ rollback ';
+ --------------- + --------- +
| Variable_name | Value |
+ --------------- + --------- +
| Com_rollback | 1073179 |
+ --------------- + --------- +
1 row in set (0.00 sec)

Mysql> show global status like 'uptime ';
+ --------------- + -------- +
| Variable_name | Value |
+ --------------- + -------- +
| Uptime | 389467 |
+ --------------- + -------- +
1 row in set (0.00 sec)

TPS = (com_commit + com_rollback)/uptime = 22

3. Number of threads and hit rate

Mysql> show global status like 'threads _ % ';
+ ----------------- + ------- +
| Variable_name | Value |
+ ----------------- + ------- +
| Threads_cached | 480 | // represents the number of Idle threads in the thread cache at this moment.
| Threads_connected | 153 | // indicates the number of established connections. Because a connection requires a thread, it can also be viewed as the number of currently used threads.
| Threads_created | 20344 | // indicates the number of threads created since the last service was started.
| Threads_running | 2 | // number of currently activated (non-sleep) threads
+ ----------------- + ------- +
4 rows in set (0.00 sec)

Mysql> show global status like 'connections ';
+ --------------- + ----------- +
| Variable_name | Value |
+ --------------- + ----------- +
| Connections | 381487397 |
+ --------------- + ----------- +
1 row in set (0.00 sec)

Thread cache hit rate = 1-threads_created/Connections = 99.994%

Number of thread caches we set

Mysql> show variables like '% thread_cache_size % ';
+ ----------------- + ------- +
| Variable_name | Value |
+ ----------------- + ------- +
| Thread_cache_size | 500 |
+ ----------------- + ------- +
1 row in set (0.00 sec)

According to Threads_connected, You can estimate the size of thread_cache_size. Generally, 250 is a good upper limit. If the memory is large enough, you can set it to the same value as thread_cache_size and threaads_connected;

Or observe the value of threads_created. If the value is large or keeps increasing, you can increase the value of thread_cache_size as appropriate. In sleep state, each thread occupies about KB of memory, therefore, when the memory is sufficient, setting too small will not save too much memory, unless the value has exceeded several thousand.

4 Table Cache

Mysql> show global status like 'open _ tables % ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Open_tables | 2228 |
+ --------------- + ------- +
1 row in set (0.00 sec)

We set to enable the table cache and table definition cache.

Mysql> show variables like 'table _ open_cache ';
+ ------------------ + ------- +
| Variable_name | Value |
+ ------------------ + ------- +
| Table_open_cache | 16384 |
+ ------------------ + ------- +
1 row in set (0.00 sec)

Mysql> show variables like 'table _ defi % ';
+ ------------------------ + ------- +
| Variable_name | Value |
+ ------------------------ + ------- +
| Table_definition_caching | 2000 |
+ ------------------------ + ------- +
1 row in set (0.00 sec)

For MyISAM:

Each time mysql opens a table, it reads some data into the table_open_cache cache. when mysql cannot find the corresponding information in this cache, it will directly read the data on the disk, therefore, this value must be large enough to avoid the need to re-open and re-parse the table definition. Generally, it is set to 10 times of max_connections, but it is best to keep it within 10000.

You can also set the value based on the open_tables status. If the value of open_tables changes greatly every second, you may need to increase the value of table_open_cache.

Table_definition_cache is usually set to the number of tables on the server, unless there are tens of thousands of tables.

For InnoDB:

Different from MyISAM, the open table and open file of InnoDB are not directly linked. That is, when the frm table is opened, the corresponding ibd file may be closed;

Therefore, InnoDB only uses table_definiton_cache and does not use table_open_cache;

The frm file is stored in table_definition_cache, while idb is determined by innodb_open_files (provided innodb_file_per_table is enabled). It is best to set innodb_open_files to a large enough size so that the server can keep all the files. the ibd file is opened at the same time.

5. Maximum number of connections

Mysql> show global status like 'max _ used_connections ';
+ ---------------------- + ------- +
| Variable_name | Value |
+ ---------------------- + ------- +
| Max_used_connections | 1785 |
+ ---------------------- + ------- +
1 row in set (0.00 sec)

The max_connections size we set

Mysql> show variables like 'max _ connections % ';
+ ----------------- + ------- +
| Variable_name | Value |
+ ----------------- + ------- +
| Max_connections | 4000 |
+ ----------------- + ------- +
1 row in set (0.00 sec)

Generally, the size of max_connections should be set to a value greater than that of Max_used_connections. The status value of Max_used_connections indicates whether a server connection has a spike in a certain period of time. If the value is greater than the value of max_connections, this indicates that the client is rejected at least once and can be set to meet the following conditions: Max_used_connections/max_connections = 0.8

6 Innodb cache hit rate

Mysql> show global status like 'innodb _ buffer_pool_read % ';
+ --------------------------------------- + -------------- +
| Variable_name | Value |
+ --------------------------------------- + -------------- +
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 268720 | // Number of pre-read pages
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 480291074970 | // number of times read from the buffer pool
| Innodb_buffer_pool_reads | 29912739 | // The number of pages read from the physical disk.
+ --------------------------------------- + -------------- +
5 rows in set (0.00 sec)

Buffer Pool hit rate = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads) = 99.994%

If the value is less than 99.9%, we recommend that you increase the value of innodb_buffer_pool_size. This value is generally set to 75%-85% of the total memory size, you can also calculate the cache required by the operating system + the memory required for each mysql connection (such as the sort buffer and temporary table) + MyISAM key cache. The remaining memory is allocated to innodb_buffer_pool_size, but it is not recommended to set too large, this may cause frequent memory swap, long push and shutdown times, and other problems.

7. MyISAM Key Buffer hit rate and Buffer usage

Mysql> show global status like 'key _ % ';
+ ------------------------ + ----------- +
| Variable_name | Value |
+ ------------------------ + ----------- +
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 106662 |
| Key_blocks_used | 107171
| Key_read_requests | 883825678 |
| Key_reads | 133294 |
| Key_write_requests | 217310758 |
| Key_writes | 2061054 |
+ ------------------------ + ----------- +
7 rows in set (0.00 sec)

Mysql> show variables like '% key_cache_block_size % ';
+ ---------------------- + ------- +
| Variable_name | Value |
+ ---------------------- + ------- +
| Key_cache_blocking _size | 1024 |
+ ---------------------- + ------- +
1 row in set (0.00 sec)


Mysql> show variables like '% key_buffer_size % ';
+ ----------------- + ----------- +
| Variable_name | Value |
+ ----------------- + ----------- +
| Key_buffer_size | 134217728 |
+ ----------------- + ----------- +
1 row in set (0.00 sec)

Buffer usage = 1-(Key_blocks_unused * key_cache_block_size/key_buffer_size) = 18.6%

Read hit rate = 1-key_reads/Key_read_requests = 99.98%

Write hit rate = 1-Key_writes/Key_write_requests = 99.05%

We can see that the buffer usage is not high. If you haven't used all the key buffers for a long time, you can reduce the buffer.

The key cache hit rate may not be significant, because it is related to applications. Some applications work well with a hit rate of 95%, and some applications require a hit rate of 99.99%. Therefore, from experience, the number of cache hits per second is more important. If an independent disk can perform 100 random reads per second, five cache hits per second may not cause I/O to be busy, however, an error may occur if there are 80 entries per second.

Cache hits per second = Key_reads/uptime = 0.33

8. Temporary table usage

Mysql> show global status like 'created _ tmp % ';
+ ------------------------- + ---------- +
| Variable_name | Value |
+ ------------------------- + ---------- +
| Created_tmp_disk_tables | 19226325 |
| Created_tmp_files | 117 |
| Created_tmp_tables | 1, 56265812 |
+ ------------------------- + ---------- +
3 rows in set (0.00 sec)

Mysql> show variables like '% tmp_table_size % ';
+ ---------------- + ---------- +
| Variable_name | Value |
+ ---------------- + ---------- +
| Tmp_table_size | 67108864 |
+ ---------------- + ---------- +
1 row in set (0.00 sec)

We can see that a total of 56265812 temporary tables have been created, of which 19226325 involve disk I/O, which accounts for about 0.34. This proves that the data volume involved in the join statement is too large in Database Application sorting, to optimize the SQL statement or increase the value of tmp_table_size, I set it to 64 MB. The ratio should be within 0.2.

9 binlog cache Usage

Mysql> show status like 'binlog _ cache % ';
+ ----------------------- + ---------- +
| Variable_name | Value |
+ ----------------------- + ---------- +
| Binlog_cache_disk_use | 15 |
| Binlog_cache_usage | 95978256 |
+ ----------------------- + ---------- +
2 rows in set (0.00 sec)

Mysql> show variables like 'binlog _ cache_size ';
+ ------------------- + --------- +
| Variable_name | Value |
+ ------------------- + --------- +
| Bin log_cache_size | 1048576 |
+ ------------------- + --------- +
1 row in set (0.00 sec)

Binlog_cache_disk_use indicates the number of times temporary files are used for cached binary logs due to insufficient memory designed by binlog_cache_size.

Binlog_cache_use indicates the number of times binlog_cache_size is cached.

When the value of Binlog_cache_disk_use is large, we can increase the value of binlog_cache_size appropriately.

10 Innodb log buffer size settings

Mysql> show variables like '% innodb_log_buffer_size % ';
+ ------------------------ + --------- +
| Variable_name | Value |
+ ------------------------ + --------- +
| Innodb_log_buffer_size | 8388608 |
+ ------------------------ + --------- +
1 row in set (0.00 sec)
Mysql> show status like 'innodb _ log_waits ';
+ ------------------ + ------- +
| Variable_name | Value |
+ ------------------ + ------- +
| Innodb_log_waits | 0 |
+ ------------------ + ------- +
1 row in set (0.00 sec)

Innodb_log_buffer_size I set 8 M, which should be large enough; Innodb_log_waits indicates the number of waits due to insufficient log buffer. If this value is not 0, you can increase the value of innodb_log_buffer_size as appropriate.

11 Table scan condition judgment

Mysql> show global status like 'handler _ read % ';
+ ----------------------- + -------------- +
| Variable_name | Value |
+ ----------------------- + -------------- +
| Handler_read_first | 19180695 |
| Handler_read_key | 30303690598
| Handler_read_last | 290721 |
| Handler_read_next | 51169834260 |
| Handler_read_prev | 1267528402 |
| Handler_read_rnd| 219230406 |
| Handler_read_rnd_next | 1, 344713226172 |
+ ----------------------- + -------------- +
7 rows in set (0.00 sec)

Handler_read_first: Number of times the index is used for scanning. This value is not clear about big novels. the system performance is good or bad.

Handler_read_key: number of queries by key. A greater value indicates better system performance.

Handler_read_next: The number of times indexes are sorted.

Handler_read_prev: This option indicates the number of times the data is retrieved from the data file in reverse ORDER of the index during index scanning, which is generally order by... DESC

Handler_read_rnd: a larger value indicates that there are a large number of operations in the system that do not use indexes for sorting, or index is not used for join operations.

Handler_read_rnd_next: The number of times data files are scanned. A larger value indicates a large number of full table scans, or a reasonable index creation. The indexes that have been created are not well utilized.

12 Innodb_buffer_pool_wait_free

Mysql> show global status like 'innodb _ buffer_pool_wait_free ';
+ ------------------------------ + ------- +
| Variable_name | Value |
+ ------------------------------ + ------- +
| Innodb_buffer_pool_wait_free | 0 |
+ ------------------------------ + ------- +
1 row in set (0.00 sec)

If the value is not 0, the buffer pool has no free space. The possible cause is that innodb_buffer_pool_size is too large to reduce the value.

13 join Operation Information

Mysql> show global status like 'select _ full_join ';
+ ------------------ + ------- +
| Variable_name | Value |
+ ------------------ + ------- +
| Select_full_join | 10403 |
+ ------------------ + ------- +
1 row in set (0.00 sec)

This value indicates the number of times no indexes are used in the join operation. A large value indicates that the join statement is very difficult to write.

Mysql> show global status like 'select _ range ';
+ --------------- + ---------- +
| Variable_name | Value |
+ --------------- + ---------- +
| Select_range | 22450380 |
+ --------------- + ---------- +
1 row in set (0.00 sec)

This value indicates the number of join statements using ranges in the first table. A large value indicates that join is not written properly. Generally, you can check the ratio of select_full_join to select_range to determine the performance of join statements in the system.

Mysql> show global status like 'select _ range_check ';
+ -------------------- + ------- +
| Variable_name | Value |
+ -------------------- + ------- +
| Select_range_check | 0 |
+ -------------------- + ------- +
1 row in set (0.00 sec)

If the value is not 0, check whether the index is reasonable.

14 slow Query

Mysql> show global status like 'slow _ queries ';
+ --------------- + -------- +
| Variable_name | Value |
+ --------------- + -------- +
| Slow_queries | 114111 |
+ --------------- + -------- +
1 row in set (0.00 sec)

This value indicates the number of slow queries since mysql was started, that is, the number of times the execution time exceeds long_query_time. You can determine the number of slow queries per unit time based on the Slow_queries/uptime ratio, then judge the system performance.

15 Table lock Information

Mysql> show global status like 'table _ lock % ';
+ ----------------------- + ------------ +
| Variable_name | Value |
+ ----------------------- + ------------ +
| Table_locks_immediate | 1644917567 |
| Table_locks_waited | 53 |
+ ----------------------- + ------------ +
2 rows in set (0.00 sec)

The ratio of the two values: Table_locks_waited/Table_locks_immediate tends to be 0. If the value is large, it indicates that the system lock blocking is serious.