About AdminPingCommand_MySQL of MySQL

About AdminPingCommand bitsCN.com of MySQL

Preface:

The Admin Ping Command was thoroughly tested during the recent online QPS soaring diagnosis process. In addition, I have recently read some related knowledge in some foreign articles, so I will write a blog post to make a summary.

1. about Admin Ping Command?

The descriptions and descriptions of Admin Ping Command are not found in the official documentation. Therefore, Google has made many pages and summarized the explanations as follows:

What is the Admin Ping Command (hereinafter referred to as the ping Command )?

The ping command actually provides an interface that consumes commands slightly. this interface is used to determine whether a persistent connection is alive or whether a remote MySQL is alive.

When the remote MySQL Server receives this special form of command, no more syntax parsing and execution plan optimization will be performed, and the results will be directly returned.

What is the use of Admin Ping Command?

This command is very useful when you use the link pool method to connect to MySQL. Because the connection pool does not know whether the link is still alive when it returns an idle link handle to the client (that is, it does not time out or is killed by the Server ). The connection pool must use a command to determine and trigger reconnection. In this case, Ping Command is undoubtedly the least costly. If you use java code to implement the MySQL connection pool, it is best to use the Admin Ping Command to determine if the connection is alive.

How to call Admin Ping Command?

Each MySQL client/connector implements Ping Command in different ways. Java usage see: http://mysqlblog.fivefarmers.com/2010/08/25/connector-j-ping/

The use of Perl is simply $ dbh-> ping (); see: http://www.turbolift.com/mysql/DBD_3.21.X.php3#ping

2. performance advantage test of Admin Ping Command

After reading the above introduction, you must have many questions. For example, how fast is ping than select 1? How to monitor on the Server? The following is a one-to-one analysis by actual tests.

On the MySQL Server, how does one observe the Admin Ping Command?

In fact, MySQL has a dedicated counter to count the ping command. You can use show global status like 'com _ admin_commands 'to view the total number of ping commands received after the Server is started.

What are the performance advantages of Admin Ping Command?

The following uses a simple perl test script to observe the performance gap between the commonly used select 1 and ping commands. The script executes ping and select 1 for 100,000 times, respectively, and compares the total execution time difference between the two by using time. The script is as follows:

use strict;use warnings;use utf8;use DBI;use IO::Socket;use FileHandle;my $CONFIG_SERVER_IP  ='192.168.26.167';my $CONFIG_SERVER_DB='test';my $CONFIG_SERVER_PORT='3310';my $CONFIG_SERVER_USER='root';my $CONFIG_SERVER_PASS='password';my $dbh = DBI->connect("DBI:mysql:$CONFIG_SERVER_DB;host=$CONFIG_SERVER_IP;port=$CONFIG_SERVER_PORT", $CONFIG_SERVER_USER, $CONFIG_SERVER_PASS,{RaiseError => 1}) || die "Could not connect to database: $DBI::errstr";

for(my $i=0;$i<100000;$i++){    #my $result=$dbh->do('/* ping */select 1');    my $result=$dbh->ping;}

Execution result:

select 1:real    0m5.968suser    0m1.235ssys    0m1.175sreal    0m6.003suser    0m1.195ssys    0m1.236sreal    0m6.155suser    0m1.225ssys    0m1.237s

Admin Ping:real    0m3.426suser    0m0.604ssys    0m1.115sreal    0m3.483suser    0m0.681ssys    0m1.081sreal    0m3.467suser    0m0.614ssys    0m1.136s

After the average value is obtained,

SELECT 1 100,000 times Execution time: 6.03 seconds

Admin PING 100,000 Execution time: 3.45 seconds

It can be seen that the performance of the latter is 74% higher. for the implementation of the connection pool with extremely high response time requirements, the resource saving is considerable.

References:

Http://mysqlblog.fivefarmers.com/2010/08/25/connector-j-ping/

BitsCN.com