MySQL performance index calculation and optimization method

MySQL performance index calculation and optimization method
1 QPS calculation (number of queries per second)

For MyISAM engine-based DB

Mysql> show global status like ' questions ';
+---------------+------------+
| variable_name | Value |
+---------------+------------+
| Questions | 2805815665 |
+---------------+------------+
1 row in Set (0.00 sec)

mysql> show global status like ' uptime ';
+---------------+----------+
| variable_name | Value |
+---------------+----------+
| Uptime | 17115114 |
+---------------+----------+
1 row in Set (0.00 sec)
Mysql> select 2805815665/17115114;
+---------------------+
| 2805815665/17115114 |
+---------------------+
| 163.9379 |
+---------------------+
1 row in Set (0.00 sec)
Qps=questions/uptime=163,mysql average QPS since startup, if you want to calculate the QPS for a time period, Interval time t2-t1 can be obtained during peak periods, then the Q values of T2 and T1 moments are calculated respectively, qps= (Q2-Q1)/(T2-T1)

For INNNODB engine-based DB
Mysql> show global status like ' Com_update ';
+---------------+----------+
| variable_name | Value |
+---------------+----------+
| Com_update | 78299864 |
+---------------+----------+
1 row in Set (0.00 sec)
Mysql> show global status like ' Com_insert ';
+---------------+----------+
| variable_name | Value |
+---------------+----------+
| Com_insert | 80467605 |
+---------------+----------+
1 row in Set (0.01 sec)
Mysql> show global status like ' Com_select ';
+---------------+------------+
| variable_name | Value |
+---------------+------------+
| Com_select | 3366989053 |
+---------------+------------+
1 row in Set (0.00 sec)

Mysql> show global status like ' Com_delete ';
+---------------+----------+
| variable_name | Value |
+---------------+----------+
| Com_delete | 36038170 |
+---------------+----------+
1 row in Set (0.00 sec)

Mysql> show global status like ' uptime ';
+---------------+----------+
| variable_name | Value |
+---------------+----------+
| Uptime | 17115204 |
+---------------+----------+
1 row in Set (0.00 sec)
Mysql> Select (78299864+80467605+36038170+3366989053)/78299864;
+--------------------------------------------------+
| (78299864+80467605+36038170+3366989053)/78299864 |
+--------------------------------------------------+
| 45.4892 |
+--------------------------------------------------+
1 row in Set (0.00 sec)
Qps= (Com_update+com_insert+com_delete+com_select)/uptime=45, a time period of the QPS Query method ibid.

2 TPS Calculation (number of transactions per second)
Mysql> show global status like ' Com_commit ';
+---------------+-----------+
| variable_name | Value |
+---------------+-----------+
| Com_commit | 510648259 |
+---------------+-----------+
1 row in Set (0.00 sec)

Mysql> show global status like ' Com_rollback ';
+---------------+----------+
| variable_name | Value |
+---------------+----------+
| Com_rollback | 95227537 |
+---------------+----------+
1 row in Set (0.00 sec)

Mysql> show global status like ' uptime ';
+---------------+----------+
| variable_name | Value |
+---------------+----------+
| Uptime | 17115356 |
+---------------+----------+
1 row in Set (0.00 sec)
Mysql> Select (510648259+95227537)/17115356;
+-------------------------------+
| (510648259+95227537)/17115356 |
+-------------------------------+
| 35.3995 |
+-------------------------------+
1 row in Set (0.00 sec)
Tps= (Com_commit+com_rollback)/uptime=35
3 Number of thread connections and hit ratio
Mysql> show global status like ' threads_% ';
+-------------------+-------+
| variable_name | Value |
+-------------------+-------+
| threads_cached | 74 |//represents how many idle threads are currently in the thread cache at this moment
| threads_connected | 106 |//represents the number of connections that are currently established because a connection requires one thread, so it can also be considered as the number of threads currently being used
| threads_created | 16760 |//represents the number of threads that have been created since the most recent service started
| threads_running | 6 |//represents the number of currently active (non-sleep) threads
+-------------------+-------+
4 rows in Set (0.00 sec)
Mysql> show global status like ' connections ';
+---------------+----------+
| variable_name | Value |
+---------------+----------+
| Connections | 18352277 |
+---------------+----------+
1 row in Set (0.00 sec)
Mysql> Select 1-16760/18352277;
+------------------+
| 1-16760/18352277 |
+------------------+
| 0.9991 |
+------------------+
1 row in Set (0.00 sec)

Thread Cache Hit Ratio =1-threads_created/connections = 99.991%

Set the number of thread caches
Mysql> Show variables like '%thread_cache_size% ';
+-------------------+-------+
| variable_name | Value |
+-------------------+-------+
| Thread_cache_size | 128 |
+-------------------+-------+
1 row in Set (0.00 sec)
Depending on the threads_connected can be estimated thread_cache_size value should be set how big, generally speaking 250 is a good upper limit value, if the memory is large enough, can also be set to Thread_cache_size value and Threads_ Connected values are the same;

or by observing the threads_created value, if the value is large or growing, you can increase the value of thread_cache_size appropriately, and in the dormant state, each thread consumes approximately 256KB of memory, so when the memory is sufficient, the setting is too small to save too much memory. Unless the value has exceeded thousands of.

4 Table Cache
Mysql> show global status like ' open_tables% ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Open_tables | 1822 |
+---------------+-------+
1 row in Set (0.00 sec)

Set cache and table definition cache for open tables
Mysql> Show variables like ' Table_open_cache ';
+------------------+-------+
| variable_name | Value |
+------------------+-------+
| Table_open_cache | 3000 |
+------------------+-------+
1 row in Set (0.00 sec)
Mysql> Show variables like ' table_defi% ';
+------------------------+-------+
| variable_name | Value |
+------------------------+-------+
| Table_definition_cache | 1400 |
+------------------------+-------+
1 row in Set (0.01 sec)

For MyISAM:

Every time MySQL opens a table, it reads some data into the Table_open_cache cache, and when MySQL does not find the corresponding information in this cache, it goes directly to the disk, so the value is set large enough to avoid the need to reopen and re-parse the table definition. It is generally set to 10 times times max_connections, but it is best to stay within 10000.
There is also a basis for setting according to the value of the state open_tables, if you find that the value of open_tables varies greatly per second, you may need to increase the value of Table_open_cache.
Table_definition_cache is usually simply set to the number of tables that exist in the server, unless there are tens of thousands of tables.

For InnoDB:
Unlike MyISAM, the open table of InnoDB is not directly related to open file, that is, its corresponding IBD file may be turned off when the frm table is opened;

So InnoDB will only use the Table_definiton_cache, will not use Table_open_cache;
The frm file is saved in Table_definition_cache, and IDB is determined by Innodb_open_files (provided the innodb_file_per_table is turned on), preferably Innodb_open_ Files is set large enough to allow the server to keep all. ibd files open at the same time.

5 Maximum number of connections

Mysql> show global status like ' Max_used_connections ';
+----------------------+-------+
| variable_name | Value |
+----------------------+-------+
| max_used_connections | 1004 |
+----------------------+-------+
1 row in Set (0.00 sec)
Set Max_connections size

Mysql> Show variables like ' max_connections% ';
+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| max_connections | 2000 |
+-----------------+-------+
1 row in Set (0.00 sec)
Usually the size of the max_connections should be set to greater than the Max_used_connections state value, and the Max_used_connections state value reflects whether the server connection has spikes in a certain period of time if the value is greater than max_ The connections value, which represents a client that has been rejected at least once, can be simply set to meet the following criteria: max_used_connections/max_connections=0.8

6 Innodb Cache Hit Ratio
mysql> show global status like ' innodb_buffer_pool_read% ';
+---------------------------------------+---------------+
| variable_name | Value |
+---------------------------------------+---------------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 730802 |//Pre-read pages
| innodb_buffer_pool_read_ahead_evicted | 0 |
| innodb_buffer_pool_read_requests | 3801141425753 |//The number of reads from the Buffer pool
| Innodb_buffer_pool_reads | 2755728 |//indicates the number of pages read from the physical disk
+---------------------------------------+---------------+
5 rows in Set (0.00 sec)
Mysql> Select 3801141425753/(3801141425753+730802+2755728);
+----------------------------------------------+
| 3801141425753/(3801141425753+730802+2755728) |
+----------------------------------------------+
| 1.0000 |
+----------------------------------------------+
1 row 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) =100%

If the value is less than 99.9%, it is recommended that you increase the value of innodb_buffer_pool_size, which is typically set to 75%-85% of the total memory size, or to the memory required by the operating system to cache +mysql per connection (for example, sort buffers and temporary tables) + MyISAM key Cache, the rest of the memory to Innodb_buffer_pool_size, but also should not be set too large, will cause frequent exchange of memory, preheating and shutdown for a long time and so on.

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 | 6692 |
| key_blocks_used | 6698 |
| key_read_requests | 51726731594 |
| Key_reads | 5423302 |
| key_write_requests | 11733536354 |
| Key_writes | 77164219 |
+------------------------+-------------+
7 Rows in Set (0.00 sec)
Mysql> Show variables like '%key_cache_block_size% ';
+----------------------+-------+
| variable_name | Value |
+----------------------+-------+
| Key_cache_block_size | 1024 |
+----------------------+-------+
1 row in Set (0.00 sec)

Mysql> Show variables like '%key_buffer_size% ';
+-----------------+---------+
| variable_name | Value |
+-----------------+---------+
| Key_buffer_size | 8388608 |
+-----------------+---------+
1 row in Set (0.00 sec)
Buffer Utilization =1-(key_blocks_unused*key_cache_block_size/key_buffer_size) =18.31%

Read hit rate =1-key_reads/key_read_requests=99.99%

Write hit rate =1-key_writes/key_write_requests =99.34%

You can see that the buffer usage is not high, if you haven't used all the key buffers for a long time, consider turning the buffer down a little bit.

The key cache hit ratio may not be significant because it is related to the application, some applications work well at 95% hit rate, some require 99.99%, so the number of cache misses per second is more important from experience, assuming that a single disk can do 100 random reads per second, Then there are 5 buffer misses per second that may not cause I/O to be busy, but there may be a problem with 80 per second.

Cache misses per second =key_reads/uptime=0.33

8 Use of temporary tables

Mysql> show global status like ' created_tmp% ';
+-------------------------+----------+
| variable_name | Value |
+-------------------------+----------+
| Created_tmp_disk_tables | 19226325 |
| Created_tmp_files | 117 |
| Created_tmp_tables | 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)
To see a total of 56265812 temporary tables created, 19226325 of which involve disk IO, The approximate proportion accounted for 0.34, to prove that the database application in the order, the join statement involves too much data, need to optimize SQL or increase the value of tmp_table_size, I set the 64M. This ratio should be controlled within 0.2.

9 Binlog Cache Usage
Mysql> Show status like ' binlog_cache% ';
+-----------------------+----------+
| variable_name | Value |
+-----------------------+----------+
| Binlog_cache_disk_use | 645946 |
| Binlog_cache_use | 61175970 |
+-----------------------+----------+
2 rows in Set (0.01 sec)
Mysql> Show variables like ' binlog_cache_size ';
+-------------------+-------+
| variable_name | Value |
+-------------------+-------+
| Binlog_cache_size | 32768 |
+-------------------+-------+
1 row in Set (0.00 sec)
Binlog_cache_disk_use says the number of temporary files used by the cache binary log is due to the lack of memory we binlog_cache_size designed

Binlog_cache_use indicates the number of times the binlog_cache_size cache is used

When the corresponding Binlog_cache_disk_use value is relatively large, we can consider the appropriate height binlog_cache_size corresponding value

Size setting for INNODB log buffer size

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 is set to 8M, it should be large enough, innodb_log_waits indicates the number of waits due to insufficient log buffer, if the value is not 0, you can increase it appropriately innodb_log_buffer_ The value of size.

11 Table Scan Condition judgment

Mysql> show global status like ' handler_read% ';
+-----------------------+---------------+
| variable_name | Value |
+-----------------------+---------------+
| Handler_read_first | 121257954 |
| Handler_read_key | 79172593389 |
| Handler_read_last | 1192685 |
| Handler_read_next | 1397568482926 |
| Handler_read_prev | 48594574644 |
| Handler_read_rnd | 2584877785 |
| Handler_read_rnd_next | 1083694866736 |
+-----------------------+---------------+
7 Rows in Set (0.00 sec)
Handler_read_first: The number of times the index scan is used, the size of the value is not sure whether the system performance is good or bad
Handler_read_key: The number of queries through key, the greater the value of the system to prove the better performance
Handler_read_next: Number of times the index is used for sorting
Handler_read_prev: This option indicates the number of times the data is taken from the data file in reverse order by index scan, which is usually the ... DESC
Handler_read_rnd: The larger the value proves that there is a large number of operations in the system that are not sorted using the index, or that the join is not used to index
Handler_read_rnd_next: The number of times a data file has been scanned, the larger the value proves to have a large number of full-table scans, or the creation of a reasonably indexed index that does not make good use of an already established index

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)
A value not of 0 indicates that the buffer pool does not have free space, possibly because the innodb_buffer_pool_size setting is too large to properly reduce the value.

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 the index was not used in the join operation, and a large value indicates a problem with the join statement

Mysql> show global status like ' Select_range ';
+---------------+----------+
| variable_name | Value |
+---------------+----------+
| Select_range | 22450380 |
+---------------+----------+
1 row in Set (0.00 sec)
This value indicates that the first table uses ranges's join number, which is large enough to indicate that join is not a problem, and it is often possible to see the ratios of Select_full_join and Select_range to determine the performance of join statements in a 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, it is necessary to check whether the index of the table is reasonable, indicating whether the index of each row in table n is re-evaluated in table n+1 for the minimum cost of the join, meaning that table n+1 does not have a useful index for the join.

Mysql> show GLOBAL status like ' Select_scan ';
+---------------+-----------+
| variable_name | Value |
+---------------+-----------+
| Select_scan | 116037811 |
+---------------+-----------+
1 row in Set (0.00 sec)
Select_scan represents the number of connections scanned for the first table, and if each row in the first table participates in the join, the result is not a problem; If you don't want to return all rows but don't use the index to find the rows you want, the count is awful.

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 started, that is, the number of times the execution time exceeds long_query_time, the number of slow queries per unit time can be judged according to the ratio of slow_queries/uptime, and then the performance of the system is judged.

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 these two values: Table_locks_waited/table_locks_immediate tends to 0, if the value is larger, it means that the system lock block is more serious

MySQL performance index calculation and optimization method