PHPのお勉強!

PHP TOP

Statistics

MySQL Native Driver contains support for gathering statistics on the communication between the client and the server. The statistics gathered are of two main types:

  • Client statistics
  • Connection statistics

When using the mysqli extension, these statistics can be obtained through two API calls:

注意: Statistics are aggregated among all extensions that use the MySQL Native Driver. For example, if the mysqli extension and the PDO MySQL driver are both set-up to use MySQLnd, then function calls from mysqli and method calls from PDO will affect the statistics. There is no way to find out how much a certain API call of any extension that has been compiled against MySQL Native Driver has impacted a certain statistic.

Retrieving statistics

Client statistics can be retrieved by calling the mysqli_get_client_stats() function.

Connection statistics can be retrieved by calling the mysqli_get_connection_stats() function.

Both functions return an associative array, where the name of a statistic is the key for the corresponding statistical data.

MySQL Native Driver Statistics

Most statistics are associated to a connection, but some are associated to the process in which case this will be mentioned.

The following statistics are produced by the MySQL Native Driver:

Network Related Statistics
bytes_sent
Number of bytes sent from PHP to the MySQL server.
bytes_received
Number of bytes received from the MySQL server.
packets_sent
Number of packets sent by the MySQL Client Server protocol.
packets_received
Number of packets received from the MySQL Client Server protocol.
protocol_overhead_in
MySQL Client Server protocol overhead in bytes for incoming traffic. Currently only the Packet Header (4 bytes) is considered as overhead. protocol_overhead_in = packets_received * 4
protocol_overhead_out
MySQL Client Server protocol overhead in bytes for outgoing traffic. Currently only the Packet Header (4 bytes) is considered as overhead. protocol_overhead_out = packets_received * 4
bytes_received_ok_packet
Total size of bytes of MySQL Client Server protocol OK packets received. OK packets can contain a status message. The length of the status message can vary and thus the size of an OK packet is not fixed.

注意: The total size in bytes includes the size of the header packet (4 bytes, see protocol overhead).

packets_received_ok
Number of MySQL Client Server protocol OK packets received.
bytes_received_eof_packet
Total size in bytes of MySQL Client Server protocol EOF packets received. EOF can vary in size depending on the server version. Also, EOF can transport an error message.

注意: The total size in bytes includes the size of the header packet (4 bytes, see protocol overhead).

packets_received_eof
Number of MySQL Client Server protocol EOF packets. Like with other packet statistics the number of packets will be increased even if PHP does not receive the expected packet but, for example, an error message.
bytes_received_rset_header_packet
Total size in bytes of MySQL Client Server protocol result set header packets. The size of the packets varies depending on the payload (LOAD LOCAL INFILE, INSERT, UPDATE, SELECT, error message).

注意: The total size in bytes includes the size of the header packet (4 bytes, see protocol overhead).

packets_received_rset_header
Number of MySQL Client Server protocol result set header packets.
bytes_received_rset_field_meta_packet
Total size in bytes of MySQL Client Server protocol result set metadata (field information) packets. Of course the size varies with the fields in the result set. The packet may also transport an error or an EOF packet in case of COM_LIST_FIELDS.

注意: The total size in bytes includes the size of the header packet (4 bytes, see protocol overhead).

packets_received_rset_field_meta
Number of MySQL Client Server protocol result set metadata (field information) packets.
bytes_received_rset_row_packet
Total size in bytes of MySQL Client Server protocol result set row data packets. The packet may also transport an error or an EOF packet. One can compute the number of error and EOF packets by subtracting rows_fetched_from_server_normal and rows_fetched_from_server_ps from bytes_received_rset_row_packet.

注意: The total size in bytes includes the size of the header packet (4 bytes, see protocol overhead).

packets_received_rset_row
Number of MySQL Client Server protocol result set row data packets.
bytes_received_prepare_response_packet
Total size in bytes of MySQL Client Server protocol OK for Prepared Statement Initialization packets (prepared statement init packets). The packet may also transport an error. The packet size depends on the MySQL version.

注意: The total size in bytes includes the size of the header packet (4 bytes, see protocol overhead).

packets_received_prepare_response
Number of MySQL Client Server protocol OK for Prepared Statement Initialization packets (prepared statement init packets).
bytes_received_change_user_packet
Total size in bytes of MySQL Client Server protocol COM_CHANGE_USER packets. The packet may also transport an error or EOF.

注意: The total size in bytes includes the size of the header packet (4 bytes, see protocol overhead).

packets_received_change_user
Number of MySQL Client Server protocol COM_CHANGE_USER packets.
packets_sent_command
Number of MySQL Client Server protocol commands sent from PHP to MySQL. There is no way to know which specific commands and how many of them have been sent.
bytes_received_real_data_normal
Number of bytes of payload fetched by the PHP client from mysqlnd using the text protocol. This is the size of the actual data contained in result sets that do not originate from prepared statements and which have been fetched by the PHP client. Note that although a full result set may have been pulled from MySQL by mysqlnd, this statistic only counts actual data pulled from mysqlnd by the PHP client.

An example of a code sequence that will increase the value is as follows:

$mysqli = new mysqli();
$res = $mysqli->query("SELECT 'abc'");
$res->fetch_assoc();
$res->close();
Every fetch operation will increase the value.

However, the statistic will not be increased if the result set is only buffered on the client, but not fetched, such as in the following example:

$mysqli = new mysqli();
$res = $mysqli->query("SELECT 'abc'");
$res->close();

bytes_received_real_data_ps
Number of bytes of the payload fetched by the PHP client from mysqlnd using the prepared statement protocol. This is the size of the actual data contained in result sets that originate from prepared statements and which have been fetched by the PHP client. The value will not be increased if the result set is not subsequently read by the PHP client. Note that although a full result set may have been pulled from MySQL by mysqlnd, this statistic only counts actual data pulled from mysqlnd by the PHP client. See also bytes_received_real_data_normal.
Result Set Related Statistics
result_set_queries
Number of queries that have generated a result set. Examples of queries that generate a result set: SELECT, SHOW. The statistic will not be incremented if there is an error reading the result set header packet from the line.

注意: This statistic can be used as an indirect measure for the number of queries PHP has sent to MySQL. This could help identifying a client that causes a high database load.