Summary of several important performance index calculation and optimization methods for MySQL

Summary of several important performance index calculation and optimization methods for MySQL

1 QPS calculation (number of queries per second)

For DB based on 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 of MySQL since it was started. If you want to calculate the QPS within a certain period of time, you can get the interval time t2-t1 during the peak period, and then calculate the q value at t2 and t1 respectively, QPS=(q2-q1)/(t2-t1)

For DBs based on 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_select | 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 within 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 Thread connections and hit rate

mysql> show global status like 'threads_%';
+-------------------+------+
| Variable_name | Value |
+-------------------+------+
| Threads_cached | 480 | //Represents how many idle threads are in the thread cache at this moment| Threads_connected | 153 | //Represents the number of currently established connections. Since one connection requires one thread, it can also be seen as the number of threads currently in use| Threads_created | 20344 | //Represents the number of threads created since the last service startup| Threads_running | 2 | //Represents the number of currently active (non-sleeping) 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%

We set the number of thread cachesmysql> show variables like '%thread_cache_size%';
+-------------------+------+
| Variable_name | Value |
+-------------------+------+
| thread_cache_size | 500 |
+-------------------+------+
1 row in set (0.00 sec)

Based on Threads_connected, you can estimate how large the thread_cache_size value should be set. Generally speaking, 250 is a good upper limit. If the memory is large enough, you can also set the thread_cache_size value to be the same as the thread_connected value.

Or by observing the threads_created value, if the value is large or keeps growing, you can increase the thread_cache_size value appropriately; in the dormant state, each thread occupies about 256KB of memory, so when there is enough memory, setting it too small will not save much memory unless the value exceeds 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 up the cache for open tables and the cache for table definitions

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_cache | 2000 |
+------------------------+-------+
1 row in set (0.00 sec)

For MyISAM:

Every 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 read it directly from the disk. Therefore, this value should be set large enough to avoid the need to reopen and re-parse the table definition. It is generally set to 10 times of max_connections, but it is best to keep it within 10000.

Another basis is to set it according to the value of the status open_tables. If you find that 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 simply set to the number of tables present in the server, unless there are tens of thousands of tables.

For InnoDB:

Unlike MyISAM, there is no direct connection between open table and open file in InnoDB, that is, when the frm table is opened, its corresponding ibd file may be closed;

Therefore, InnoDB will only use table_definiton_cache and will not use table_open_cache;

The frm file is stored in table_definition_cache, while idb is determined by innodb_open_files (assuming innodb_file_per_table is enabled). It is best to set innodb_open_files large enough so that the server can 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 | 1785 |
+----------------------+-------+
1 row in set (0.00 sec)

We set the max_connections size

mysql> show variables like 'max_connections%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 4000 |
+-----------------+-------+
1 row in set (0.00 sec)

Usually the size of max_connections should be set larger than the max_used_connections state value. The max_used_connections state value reflects whether the server connection has a spike in a certain period of time. If this value is larger than the max_connections value, it means that the client has been rejected at least once. It can be simply 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 reads from the buffer pool | Innodb_buffer_pool_reads | 29912739 | //Indicates 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 this value is less than 99.9%, it is recommended to increase the value of innodb_buffer_pool_size. This value is generally set to 75%-85% of the total memory size, or calculate the cache required by the operating system + the memory required for each MySQL connection (such as sort buffer and temporary tables) + MyISAM key cache, and give the remaining memory to innodb_buffer_pool_size. However, it should not be set too large, which will cause frequent memory swapping, long warm-up and shutdown time, 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_block_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%

It can be seen that the usage rate of the buffer is not high. If all the key buffers have not been used up after a long time, you can consider reducing the buffer size.

The key cache hit rate may not mean much because it is application-dependent. Some applications work well with a 95% hit rate, while others require 99.99%. So from experience, the number of cache misses per second is more important. Assuming that a standalone disk can do 100 random reads per second, then 5 cache misses per second may not cause I/O busyness, but 80 per second may cause problems.

Cache misses 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 | 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)

It can be seen that a total of 56,265,812 temporary tables were created, of which 19,226,325 involved disk IO, accounting for about 0.34. This proves that the amount of data involved in sorting and join statements in database applications is too large, and it is necessary to optimize SQL or increase the value of tmp_table_size. I set it to 64M. The ratio should be controlled within 0.2.

9 Binlog cache usage

mysql> show status like 'Binlog_cache%'; 
+----------------------+----------+
| Variable_name | Value |
+----------------------+----------+
| Binlog_cache_disk_use | 15 |
| Binlog_cache_use | 95978256 |
+----------------------+----------+
2 rows in set (0.00 sec)

mysql> show variables like 'binlog_cache_size';
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| binlog_cache_size | 1048576 |
+-------------------+---------+
1 row in set (0.00 sec)

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

Binlog_cache_use indicates the number of times binlog_cache_size is used for caching

When the corresponding Binlog_cache_disk_use value is relatively large, we can consider appropriately increasing the corresponding value of binlog_cache_size

10 Innodb log buffer size setting

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)

I set innodb_log_buffer_size to 8M, 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 appropriately.

11. Table Scan Status 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 | 344713226172 |
+----------------------+--------------+
7 rows in set (0.00 sec)

Handler_read_first: The number of times an index scan is used. The value of this value cannot tell whether the system performance is good or bad.

Handler_read_key: The number of queries through the key. The larger the value, the better the system performance.

Handler_read_next: The number of times to sort using the index
Handler_read_prev: This option indicates the number of times to fetch data from the data file in reverse order of the index when performing an index scan, usually ORDER BY ... DESC

Handler_read_rnd: The larger the value, the more likely it is that there are a large number of operations in the system that do not use indexes for sorting, or that indexes are not used during joins.

Handler_read_rnd_next: The number of times the data file is scanned. The larger the value, the more likely it is that there are a lot of full table scans, or that the index is created inappropriately, and the established index is not used well.

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, it means that there is no free space in the buffer pool. The possible reason is that the innodb_buffer_pool_size is set too large. You can reduce this value appropriately.

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 the index was not used in the join operation. A large value indicates that the join statement is written with problems.

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 joins using ranges in the first table. A large value indicates that the join is written properly. You can usually 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, you need to check whether the index of the table is reasonable, which means re-evaluating the index of each row in table n in table n+1 to see if the cost is minimal. The number of joins made means that table n+1 does not have a useful index for this join.

mysql> show GLOBAL status like 'select_scan';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Select_scan | 116037811 |
+---------------+-----------+
1 row in set (0.00 sec)

select_scan indicates the number of connections to scan the first table. This is fine if every row in the first table is involved in the join. If you don't want to return all rows but don't use an index to find the rows you need, a large count is bad.

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. The number of slow queries per unit time can be determined based on the ratio of Slow_queries/uptime, and thus the performance of the system can be determined.

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 is seriously blocked.

The above summary of several important MySQL performance indicator calculations and optimization methods is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:
  • mysql calculate time difference function
  • Multiple ways to calculate age by birthday in MySQL
  • Method to calculate time difference in php and MySql
  • MySQL string length calculation implementation code (gb2312+utf8)
  • The meaning and calculation method of QPS and TPS of MySQL database
  • A brief discussion on the calculation method of key_len in mysql explain
  • How to calculate the value of ken_len in MySQL query plan
  • Example analysis of interval calculation of mysql date and time
  • Detailed explanation of MySQL date addition and subtraction functions
  • mysql trigger creation and usage examples
  • Detailed explanation of the basic usage of MySQL triggers [create, view, delete, etc.]
  • Detailed explanation of MySQL cumulative calculation implementation method

<<:  Vue imitates Ctrip's carousel effect (sliding carousel, highly adaptive below)

>>:  Implementing Markdown rendering in Vue single-page application

Recommend

How to migrate the data directory in mysql8.0.20

The default storage directory of mysql is /var/li...

Detailed explanation of the adaptive adaptation problem of Vue mobile terminal

1. Create a project with vue ui 2. Select basic c...

js realizes 3D sound effects through audioContext

This article shares the specific code of js to ac...

Gojs implements ant line animation effect

Table of contents 1. Gojs Implementation 1. Drawi...

Three Vue slots to solve parent-child component communication

Table of contents Preface Environment Preparation...

Detailed explanation of Deepin using docker to install mysql database

Query the MySQL source first docker search mysql ...

JavaScript canvas implements graphics and text with shadows

Use canvas to create graphics and text with shado...

Turn web pages into black and white (compatible with Google, Firefox, IE and other browsers)

Write to the css file Copy code The code is as fol...

Example of how to increase swap in CentOS7 system

Preface Swap is a special file (or partition) loc...

React antd realizes dynamic increase and decrease of form

I encountered a pitfall when writing dynamic form...

Detailed explanation of how to manually deploy a remote MySQL database in Linux

1. Install mysql Run the following command to upd...

Implementation example of react project from new creation to deployment

Start a new project This article mainly records t...

uniapp realizes the recording upload function

Table of contents uni-app Introduction HTML part ...

HTML markup language - table tag

Click here to return to the 123WORDPRESS.COM HTML ...

Gearman + MySQL to achieve persistence operation example

This article uses the gearman+mysql method to imp...