How to use mysqladmin to get the current TPS and QPS of a MySQL instance

mysqladmin is an official mysql client program that performs management and operation. It can be used to operate the MySQL database service. In MySQL 5.5 and earlier versions, the most common method is to use it to shut down the mysql instance:

mysqladmin -uxxx -pxxx -Pxxx -hxxx shutdown

With the upgrade of MySQL version, the shutdown command can be used directly to shut down the MySQL service in MySQL5.7. In MySQL 8.0, you can use the restart command to restart the MySQL service. The mysqladmin tool is used less and less in daily operation and maintenance scenarios.

Today, let's see how to use the mysqladmin tool to obtain the current TPS and QPS of a MySQL instance. To solve this problem, we must first know the reference indicators of TPS and QPS. In MySQL, we can use the show global status command to view the current indicators of MySQL, as follows:

[email protected]:(none) 13:37:50>>show global status;
+------------------------------------------+----------------+
| Variable_name | Value |
+------------------------------------------+----------------+
| Aborted_clients | 85032 |
| Aborted_connects | 176 |
| Binlog_cache_disk_use | 3293 |
| Binlog_cache_use | 14631673326 |
| Binlog_stmt_cache_disk_use | 13 |
| Binlog_stmt_cache_use | 9948429 |
| Bytes_received | 19921975442457 |
| Bytes_sent | 13553543054684 |
| Com_admin_commands | 913630 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |

There are several important indicators:

1. Questions:

The number of statements executed by the MySQL server. It only includes statements sent by the client to the server, and does not include statements executed in stored procedures. This parameter does not count Com_ping, Com_statistics, Com_stmt_prepare, Com_stmt_close, and Com_stat_reset commands, but includes show commands and dml commands. Does not contain statements for master-slave replication.

2. Queries:

The number of statements executed by the server. Unlike the Questions parameter, it includes statements executed in stored procedures and built-in master-slave replication statements. It does not count Com_ping and Com_statistics commands.

3. Com_xxx:

Represents the number of times xxx statement has been executed. Each type has a status variable, examples include Com_select, Com_insert, Com_delete, Com_update, Com_commit, Com_rollback, etc.

Generally, there are several ways to calculate TPS and QPS:

Solution 1: If all SQL statements executed on the MySQL server are considered, QPS is calculated based on Queries, and TPS is calculated based on the algebraic sum of Com_commit and Com_rollback.

Solution 2: If we only consider all SQL statements executed by the business side through the command line, we can calculate QPS based on Question and TPS based on the algebraic sum of Com_commit and Com_rollback.

Solution 3: Calculate QPS and TPS based on Com_xxx status

The following are the commands to view TPS and QPS using mysqladmin:

[root ~]#./mysqladmin -r -i 2 -uroot -pxxxxxx -P4306 --socket=/data/mysql_4306/tmp/mysql.sock ext| awk '/Queries/{printf("QPS:%d\n",$4)}'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
QPS:55668415
QPS:1
QPS:34
QPS:32
QPS:108
QPS:6
QPS:7

[root ~]# ./mysqladmin -r -i 2 -uroot -pxxxxxx -P4306 --socket=/data/mysql_4306/tmp/mysql.sock ext| awk '/Com_commit/{printf("TPS:%d\n",$4)}'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
TPS:27449691
TPS:56
TPS:1
TPS:2
TPS:28

Here we explain the parameters -r and -i. We use the mysqladmin --help command to filter these two parameters:

• -r, --relative

Show difference between current and previous values ​​when used with -i. Currently only works with extended-status.

To translate, it is the difference between the current and previous values. It needs to be used with -i. Let's look at the -i parameter:

• -i, --sleep=# Execute commands repeatedly with a sleep between.

That is, the time interval

In addition, there is an ext option behind it. See the explanation of this option:

extended-status:

Gives an extended status message from the server #Translation: Gives more status information of the MySQL server

This makes the above command pretty self-explanatory. Use the -r parameter to calculate the difference between different status variables, and then the -i parameter controls the length of time. Setting it to 1 means the difference per second, and ext means outputting more MySQL status information. TPS and QPS can be easily calculated. Different calculation methods are slightly different. More accurate TPS and QPS values ​​can be calculated according to different scenarios.

When calculating TPS and QPS, there are several criteria that can be replaced as appropriate:

1. com_commit will not record implicitly committed transactions, so if implicit commit is enabled, the tps calculation using com_commit will be inaccurate.

2. If there are many MyISAM tables in the database, it is more appropriate to use questions for calculation.

3. If there are many InnoDB tables in the database, it is more appropriate to use the com_* data source for calculation.

4. Questions records all select and dml queries since mysqld was started, including the number of show command queries. This is somewhat inaccurate. For example, many databases have monitoring systems running, which perform a show query on the database every 5 seconds to obtain the current database status. These queries are recorded in the QPS and TPS statistics, causing a certain amount of "data pollution".

5. When some implicit commits are enabled, TPS can be viewed using Com_insert + Com_update + Com_delete.

Finally, here is a method to determine whether mysql is alive:

[root ~]# ./mysqladmin -r -i 2 -uroot -pdba@JJMatch -P4306 --socket=/data/mysql_4306/tmp/mysql.sock ping
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqld is alive
mysqld is alive
mysqld is alive

Use the mysqladmin ping command to check the survival status of mysql.

With the upgrade of MySQL, the frequency of using the mysqladmin tool will become less and less, and almost all daily needs are met through the mysql tool. The mysqladmin tool of mysql8.0 version is slightly adjusted based on mysql5.7. If you are interested, you can study it yourself.

The above is the details of how to use mysqladmin to obtain the current TPS and QPS of a MySQL instance. For more information about mysqladmin statistics of MySQL T/QPS, please pay attention to other related articles on 123WORDPRESS.COM!