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:
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.
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.
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:
bytes_sent
bytes_received
packets_sent
packets_received
protocol_overhead_in
protocol_overhead_in = packets_received * 4
protocol_overhead_out
protocol_overhead_out = packets_received * 4
bytes_received_ok_packet
注意: The total size in bytes includes the size of the header packet (4 bytes, see protocol overhead).
packets_received_ok
bytes_received_eof_packet
注意: The total size in bytes includes the size of the header packet (4 bytes, see protocol overhead).
packets_received_eof
bytes_received_rset_header_packet
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
bytes_received_rset_field_meta_packet
注意: The total size in bytes includes the size of the header packet (4 bytes, see protocol overhead).
packets_received_rset_field_meta
bytes_received_rset_row_packet
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
bytes_received_prepare_response_packet
注意: The total size in bytes includes the size of the header packet (4 bytes, see protocol overhead).
packets_received_prepare_response
bytes_received_change_user_packet
注意: The total size in bytes includes the size of the header packet (4 bytes, see protocol overhead).
packets_received_change_user
packets_sent_command
bytes_received_real_data_normal
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();
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
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_queries
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.
non_result_set_queries
INSERT
, UPDATE
, LOAD DATA
.
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.
no_index_used
--log-queries-not-using-indexes
).
注意: Those queries can be reported via an exception by calling
mysqli_report(MYSQLI_REPORT_INDEX);
. It is possible to have them be reported via a warning instead by callingmysqli_report(MYSQLI_REPORT_INDEX ^ MYSQLI_REPORT_STRICT);
.
bad_index_used
--log-slow-queries
).
注意: Those queries can be reported via an exception by calling
mysqli_report(MYSQLI_REPORT_INDEX);
. It is possible to have them be reported via a warning instead by callingmysqli_report(MYSQLI_REPORT_INDEX ^ MYSQLI_REPORT_STRICT);
.
slow_queries
long_query_time
seconds to execute and required at least
min_examined_row_limit
rows to be examined.
Not reported through mysqli_report().
buffered_sets
Examples of API calls that will buffer result sets on the client: mysqli_query(), mysqli_store_result(), mysqli_stmt_get_result()
unbuffered_sets
Examples of API calls that will not buffer result sets on the client: mysqli_use_result()
ps_buffered_sets
Examples of API calls that will buffer result sets on the client: mysqli_stmt_store_result()
ps_unbuffered_sets
flushed_normal_sets
注意: Flushing happens only with unbuffered result sets. Unbuffered result sets must be fetched completely before a new query can be run on the connection otherwise MySQL will throw an error. If the application does not fetch all rows from an unbuffered result set, mysqlnd does implicitly fetch the result set to clear the line. See also
rows_skipped_normal
,rows_skipped_ps
.Some possible causes for an implicit flush:
- Faulty client application
- Client stopped reading after it found what it was looking for but has made MySQL calculate more records than needed
- Client application has stopped unexpectedly
flushed_ps_sets
注意: Flushing happens only with unbuffered result sets. Unbuffered result sets must be fetched completely before a new query can be run on the connection otherwise MySQL will throw an error. If the application does not fetch all rows from an unbuffered result set, mysqlnd does implicitly fetch the result set to clear the line. See also
rows_skipped_normal
,rows_skipped_ps
.Some possible causes for an implicit flush:
- Faulty client application
- Client stopped reading after it found what it was looking for but has made MySQL calculate more records than needed
- Client application has stopped unexpectedly
ps_prepared_never_executed
ps_prepared_once_executed
rows_fetched_from_server_normal
rows_fetched_from_server_ps
packets_received_rset_row
.
rows_buffered_from_client_normal
Examples of queries that will buffer results:
rows_buffered_from_server_ps
rows_buffered_from_client_normal
but for prepared statements.
rows_fetched_from_client_normal_buffered
rows_fetched_from_client_ps_buffered
rows_fetched_from_client_normal_unbuffered
rows_fetched_from_client_ps_unbuffered
rows_fetched_from_client_ps_cursor
rows_skipped_normal
rows_skipped_ps
copy_on_write_saved
copy_on_write_performed
explicit_free_result
implicit_free_result
proto_text_fetched_null
MYSQL_TYPE_NULL
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_null
MYSQL_TYPE_NULL
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_bit
MYSQL_TYPE_BIT
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_bit
MYSQL_TYPE_BIT
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_tinyint
MYSQL_TYPE_TINY
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_tinyint
MYSQL_TYPE_TINY
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_short
MYSQL_TYPE_SHORT
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_short
MYSQL_TYPE_SHORT
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_int24
MYSQL_TYPE_INT24
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_int24
MYSQL_TYPE_INT24
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_int
MYSQL_TYPE_LONG
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_int
MYSQL_TYPE_LONG
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_bigint
MYSQL_TYPE_LONGLONG
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_bigint
MYSQL_TYPE_LONGLONG
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_decimal
MYSQL_TYPE_DECIMAL
, or MYSQL_TYPE_NEWDECIMAL
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_decimal
MYSQL_TYPE_DECIMAL
, or MYSQL_TYPE_NEWDECIMAL
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_float
MYSQL_TYPE_FLOAT
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_float
MYSQL_TYPE_FLOAT
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_double
MYSQL_TYPE_DOUBLE
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_double
MYSQL_TYPE_DOUBLE
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_date
MYSQL_TYPE_DATE
, or MYSQL_TYPE_NEWDATE
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_date
MYSQL_TYPE_DATE
, or MYSQL_TYPE_NEWDATE
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_year
MYSQL_TYPE_YEAR
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_year
MYSQL_TYPE_YEAR
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_time
MYSQL_TYPE_TIME
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_time
MYSQL_TYPE_TIME
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_datetime
MYSQL_TYPE_DATETIME
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_datetime
MYSQL_TYPE_DATETIME
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_timestamp
MYSQL_TYPE_TIMESTAMP
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_timestamp
MYSQL_TYPE_TIMESTAMP
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_string
MYSQL_TYPE_STRING
, MYSQL_TYPE_VARSTRING
, or MYSQL_TYPE_VARCHAR
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_string
MYSQL_TYPE_STRING
, MYSQL_TYPE_VARSTRING
, or MYSQL_TYPE_VARCHAR
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_blob
MYSQL_TYPE_TINY_BLOB
,
MYSQL_TYPE_MEDIUM_BLOB
,
MYSQL_TYPE_LONG_BLOB
,
or MYSQL_TYPE_BLOB
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_blob
MYSQL_TYPE_TINY_BLOB
,
MYSQL_TYPE_MEDIUM_BLOB
,
MYSQL_TYPE_LONG_BLOB
,
or MYSQL_TYPE_BLOB
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_enum
MYSQL_TYPE_ENUM
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_enum
MYSQL_TYPE_ENUM
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_set
MYSQL_TYPE_SET
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_set
MYSQL_TYPE_SET
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_geometry
MYSQL_TYPE_GEOMETRY
fetched from a normal query (MySQL text protocol).
proto_binary_fetched_geometry
MYSQL_TYPE_GEOMETRY
fetched from a prepared statement (MySQL binary protocol).
proto_text_fetched_other
MYSQL_TYPE_*
not listed previously
fetched from a normal query (MySQL text protocol).
注意: In theory, this should always be
0
.
proto_binary_fetched_other
MYSQL_TYPE_*
not listed previously
fetched from a prepared statement (MySQL binary protocol).
注意: In theory, this should always be
0
.
connect_success
注意:
connect_success
holds the sum of successful persistent and non-persistent connection attempts. Therefore, the number of successful non-persistent connection attempts isconnect_success - pconnect_success
.
pconnect_success
connect_failure
reconnect
active_connections
注意: The total number of active non-persistent connections is
active_connections - active_persistent_connections
.
active_persistent_connections
explicit_close
示例 #1 Examples of code snippets that cause an explicit close
$link = new mysqli(/* ... */); $link->close(/* ... */);
$link = new mysqli(/* ... */); $link->connect(/* ... */);
implicit_close
示例 #2 Examples of code snippets that cause an implicit close
$link = new mysqli(/* ... */); $link->real_connect(/* ... */);
unset($link)
disconnect_close
mysql_real_connect
during an attempt to
establish a connection.
in_middle_of_command_close
Unless asynchronous queries are used, this should only happen if the PHP application terminated unexpectedly, and PHP shuts down the connection automatically.
init_command_executed_count
mysqli_options(MYSQLI_INIT_COMMAND , $value)
.
The number of successful executions is
init_command_executed_count - init_command_failed_count
.
init_command_failed_count
COM_*
Command Related Statistics
com_quit
com_init_db
com_query
com_field_list
com_create_db
com_drop_db
com_refresh
com_shutdown
com_statistics
com_process_info
com_connect
com_process_kill
com_debug
com_ping
com_time
com_delayed_insert
com_change_user
com_binlog_dump
com_table_dump
com_connect_out
com_register_slave
com_stmt_prepare
com_stmt_execute
com_stmt_send_long_data
com_stmt_close
com_stmt_reset
com_stmt_set_option
com_stmt_fetch
com_daemon
COM_*
command from PHP to MySQL.
The statistics are incremented after checking the line and immediately
before sending the corresponding MySQL client server protocol packet.
If MySQLnd fails to send the packet over the wire the statistics will not be decremented.
In case of a failure MySQLnd emits a PHP warning
Error while sending %s packet. PID=%d.
示例 #3 Usage examples
Check if PHP sends certain commands to MySQL, for example,
check if a client sends COM_PROCESS_KILL
Calculate the average number of prepared statement executions
by comparing COM_EXECUTE
with
COM_PREPARE
Check if PHP has run any non-prepared SQL statements by
checking if COM_QUERY
is zero
Identify PHP scripts that run an excessive number of SQL
statements by checking COM_QUERY
and
COM_EXECUTE
explicit_stmt_close
implicit_stmt_close
注意: A prepared statement is always explicitly closed. The only time it's closed implicitly is when preparing it fails.
mem_emalloc_count
mem_emalloc_ammount
mem_ecalloc_count
mem_ecalloc_ammount
mem_realloc_count
mem_realloc_ammount
mem_efree_count
mem_malloc_count
mem_malloc_ammount
mem_calloc_count
mem_calloc_ammount
mem_ealloc_count
mem_ealloc_ammount
mem_free_count
command_buffer_too_small
COM_QUERY
(normal query),
does not fit into the buffer,
MySQLnd will grow the buffer to what is needed for sending the command.
Whenever the buffer gets extended for one connection
command_buffer_too_small
will be incremented by one.
If MySQLnd has to grow the buffer beyond its initial size of
mysqlnd.net_cmd_buffer_size
bytes for almost every connection,
considerations to increase the default size should be made to avoid
re-allocations.
connection_reused