Copyright 1997-2008 the PHP Documentation Group.
Using Statistical Data
MySQL Native Driver contains support for gathering statistics on the communication between the client and the server. The statistics gathered are of three main types:
Client statistics
Connection statistics
Zval cache statistics
If you are using the mysqli
extension, these
statistics can be obtained through three API calls:
mysqli_get_client_stats
mysqli_get_connection_stats
mysqli_get_cache_stats
Statistics are aggregated among all extensions that use MySQL
Native Driver. For example, when compiling both
ext/mysql
and ext/mysqli
against MySQL Native Driver, both function calls of
ext/mysql
and ext/mysqli
will change 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.
You can configure the PDO MySQL Driver,
ext/mysql
and ext/mysqli
to optionally use the MySQL Native Driver. When doing so, all
three extensions will change the statistics.
Accessing Client Statistics
To access client statistics, you need to call
mysqli_get_client_stats
. The function call
does not require any parameters.
The function returns an associative array that contains the name of the statistic as the key and the statistical data as the value.
Client statistics can also be accessed by calling the
phpinfo
function.
Accessing Connection Statistics
To access connection statistics call
mysqli_get_connection_stats
. This takes the
database connection handle as the parameter.
The function returns an associative array that contains the name of the statistic as the key and the statistical data as the value.
Accessing Zval Cache Statistics
The MySQL Native Driver also collects statistics from its internal
Zval cache. These statistics can be accessed by calling
mysqli_get_cache_stats
.
The Zval cache statistics obtained may lead to a tweaking of
php.ini
settings related to the Zval cache,
resulting in better performance.
Buffered and Unbuffered Result Sets
Result sets can be buffered or unbuffered. Using default settings,
ext/mysql
and ext/mysqli
work with buffered result sets for normal (non prepared statement)
queries. Buffered result sets are cached on the client. After the
query execution all results are fetched from the MySQL Server and
stored in a cache on the client. The big advantage of buffered
result sets is that they allow the server to free all resources
allocated to a result set, once the results have been fetched by
the client.
Unbuffered result sets on the other hand are kept much longer on the server. If you want to reduce memory consumption on the client, but increase load on the server, use unbuffered results. If you experience a high server load and the figures for unbuffered result sets are high, you should consider moving the load to the clients. Clients typically scale better than servers. “Load” does not only refer to memory buffers - the server also needs to keep other resources open, for example file handles and threads, before a result set can be freed.
Prepared Statements use unbuffered result sets by default.
However, you can use mysqli_stmt_store_result
to enable buffered result sets.
Statistics returned by MySQL Native Driver
The following tables show a list of statistics returned by the
mysqli_get_client_stats
,
mysqli_get_connection_stats
and
mysqli_get_cache_stats
functions.
Network
Statistic | Scope | Description | Notes |
---|---|---|---|
bytes_sent |
Connection | Number of bytes sent from PHP to the MySQL server | Can be used to check the efficiency of the compression protocol |
bytes_received |
Connection | Number of bytes received from MySQL server | Can be used to check the efficiency of the compression protocol |
packets_sent |
Connection | Number of MySQL Client Server protocol packets sent | Used for debugging Client Server protocol implementation |
packets_received |
Connection | Number of MySQL Client Server protocol packets received | Used for debugging Client Server protocol implementation |
protocol_overhead_in |
Connection | 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 | Used for debugging Client Server protocol implementation |
protocol_overhead_out |
Connection | 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_sent * 4 | Used for debugging Client Server protocol implementation |
bytes_received_ok_packet |
Connection | 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. | Used for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
packets_received_ok |
Connection | Number of MySQL Client Server protocol OK packets received. | Used for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
bytes_received_eof_packet |
Connection | 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. | Used for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
packets_received_eof |
Connection | 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. | Used for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
bytes_received_rset_header_packet |
Connection | 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). |
Used for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
packets_received_rset_header |
Connection | Number of MySQL Client Server protocol result set header packets. | Used for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
bytes_received_rset_field_meta_packet |
Connection | Total size in bytes of MySQL Client Server protocol result set meta data (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. | Only useful for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
packets_received_rset_field_meta |
Connection | Number of MySQL Client Server protocol result set meta data (field information) packets. | Only useful for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
bytes_received_rset_row_packet |
Connection | 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. You can reverse engineer the number of error and
EOF packets by substracting
rows_fetched_from_server_normal and
rows_fetched_from_server_ps from
bytes_received_rset_row_packet . |
Only useful for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
packets_received_rset_row |
Connection | Number of MySQL Client Server protocol result set row data packets and their total size in bytes. | Only useful for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
bytes_received_prepare_response_packet |
Connection | 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: 9 bytes with
MySQL 4.1 and 12 bytes from MySQL 5.0 on. There is no safe
way to know how many errors happened. You may be able to
guess that an error has occurred if, for example, you
always connect to MySQL 5.0 or newer and,
bytes_received_prepare_response_packet
!= packets_received_prepare_response *
12. See also
ps_prepared_never_executed ,
ps_prepared_once_executed . |
Only useful for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
packets_received_prepare_response |
Connection | Number of MySQL Client Server protocol OK for Prepared Statement Initialization packets (prepared statement init packets). | Only useful for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
bytes_received_change_user_packet |
Connection | Total size in bytes of MySQL Client Server protocol COM_CHANGE_USER packets. The packet may also transport an error or EOF. | Only useful for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
packets_received_change_user |
Connection | Number of MySQL Client Server protocol COM_CHANGE_USER packets | Only useful for debugging CS protocol implementation. Note that the total size in bytes includes the size of the header packet (4 bytes, see protocol overhead). |
packets_sent_command |
Connection | 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. At its best you can use it to check if PHP has sent any commands to MySQL to know if you can consider to disable MySQL support in your PHP binary. There is also no way to reverse engineer the number of errors that may have occurred while sending data to MySQL. The only error recoded is command_buffer_too_small (see below). | Only useful for debugging CS protocol implementation. |
Result Set
Statistic | Scope | Description | Notes |
---|---|---|---|
result_set_queries |
Connection | 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. |
You may use it an an indirect measure for the number of queries PHP has sent to MySQL, for example, to identify a client that causes a high database load. |
non_result_set_queries |
Connection | Number of queries that did not generate a result set. Examples of
queries that do not generate a result set:
INSERT , UPDATE ,
LOAD DATA , SHOW . The
statistic will not be incremented if there is an error
reading the result set header packet from the line. |
You may use it an an indirect measure for the number of queries PHP has sent to MySQL, for example, to identify a client that causes a high database load. |
no_index_used |
Connection | Number of queries that have generated a result set but did not use an index (see also mysqld start option –log-queries-not-using-indexes). If you want these queries to be reported you can use mysqli_report(MYSQLI_REPORT_INDEX) to make ext/mysqli throw an exception. If you prefer a warning instead of an exception use mysqli_report(MYSQLI_REPORT_INDEX ^ MYSQLI_REPORT_STRICT). | |
bad_index_used |
Connection | Number of queries that have generated a result set and did not use a good index (see also mysqld start option –log-slow-queries). | If you want these queries to be reported you can use mysqli_report(MYSQLI_REPORT_INDEX) to make ext/mysqli throw an exception. If you prefer a warning instead of an exception use mysqli_report(MYSQLI_REPORT_INDEX ^ MYSQLI_REPORT_STRICT) |
slow_queries |
Connection | SQL statements that took more than 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 |
Connection | Number of buffered result sets returned by “normal” queries. “Normal” means “not prepared statement” in the following notes. | Examples of API calls that will buffer result sets on the client:
mysql_query ,
mysqli_query ,
mysqli_store_result ,
mysqli_stmt_get_result . Buffering
result sets on the client ensures that server resources
are freed as soon as possible and it makes result set
scrolling easier. The downside is the additional memory
consumption on the client for buffering data. Note that
mysqlnd (unlike the MySQL Client Library) respects the PHP
memory limit because it uses PHP internal memory
management functions to allocate memory. This is also the
reason why memory_get_usage reports a
higer memory consumption when using mysqlnd instead of the
MySQL Client Library.
memory_get_usage does not measure the
memory consumption of the MySQL Client Library at all
because the MySQL Client Library does not use PHP internal
memory management functions monitored by the function! |
unbuffered_sets |
Connection | Number of unbuffered result sets returned by normal (non prepared statement) queries. | Examples of API calls that will not buffer result sets on the client:
mysqli_use_result
|
ps_buffered_sets |
Connection | Number of buffered result sets returned by prepared statements. By default prepared statements are unbuffered. | Examples of API calls that will not buffer result sets on the client:
mysqli_stmt_store_result
|
ps_unbuffered_sets |
Connection | Number of unbuffered result sets returned by prepared statements. | By default prepared statements are unbuffered. |
flushed_normal_sets |
Connection | Number of result sets from normal (non prepared statement) queries with unread data which have been flushed silently for you. 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:
|
flushed_ps_sets |
Connection | Number of result sets from prepared statements with unread data which have been flushed silently for you. 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:
|
ps_prepared_never_executed |
Connection | Number of statements prepared but never executed. | Prepared statements occupy server resources. You should not prepare a statement if you do not plan to execute it. |
ps_prepared_once_executed |
Connection | Number of prepared statements executed only one. | One of the ideas behind prepared statements is that the same query gets executed over and over again (with different parameters) and some parsing and other preparation work can be saved, if statement execution is split up in separate prepare and execute stages. The idea is to prepare once and “cache” results, for example, the parse tree to be reused during multiple statement executions. If you execute a prepared statement only once the two stage processing can be inefficient compared to “normal” queries because all the caching means extra work and it takes (limited) server resources to hold the cached information. Consequently, prepared statements that are executed only once may cause performance hurts. |
rows_fetched_from_server_normal ,
rows_fetched_from_server_ps
|
Connection | Total number of result set rows successfully fetched from MySQL regardless if the client application has consumed them or not. Some of the rows may not have been fetched by the client application but have been flushed implicitly. | See also packets_received_rset_row
|
rows_buffered_from_client_normal ,
rows_buffered_from_client_ps
|
Connection | Total number of succesfully buffered rows originating from a "normal" query or a prepared statement. This is the number of rows that have been fetched from MySQL and buffered on client. Note that there are two distinct statistics on rows that have been buffered (MySQL to mysqlnd internal buffer) and buffered rows that have been fetched by the client application (mysqlnd internal buffer to client application). If the number of buffered rows is higher than the number of fetched buffered rows it can mean that the client application runs queries that cause larger result sets than needed resulting in rows not read by the client. | Examples of queries that will buffer results:
mysqli_query ,
mysqli_store_result
|
rows_fetched_from_client_normal_buffered ,
rows_fetched_from_client_ps_buffered
|
Connection | Total number of rows fetched by the client from a buffered result set created by a normal query or a prepared statement. | |
rows_fetched_from_client_normal_unbuffered ,
rows_fetched_from_client_ps_unbuffered
|
Connection | Total number of rows fetched by the client from a unbuffered result set created by a "normal" query or a prepared statement. | |
rows_fetched_from_client_ps_cursor |
Connection | Total number of rows fetch by the client from a cursor created by a prepared statement. | |
rows_skipped_normal ,
rows_skipped_ps
|
Connection | Reserved for future use (currently not supported) | |
copy_on_write_saved ,
copy_on_write_performed
|
Process | With mysqlnd, variables returned by the extensions point into mysqlnd
internal network result buffers. If you do not change the
variables, fetched data will be kept only once in memory.
If you change the variables, mysqlnd has to perform a
copy-on-write to protect the internal network result
buffers from being changed. With the MySQL Client Library
you always hold fetched data twice in memory. Once in the
internal MySQL Client Library buffers and once in the
variables returned by the extensions. In theory mysqlnd
can save up to 40% memory. However, note that the memory
saving cannot be measured using
memory_get_usage . |
|
explicit_free_result ,
implicit_free_result
|
Connection, Process (only during prepared statement cleanup) | Total number of freed result sets. | The free is always considered explicit but for result sets created by an
init command, for example,
mysqli_options(MYSQLI_INIT_COMMAND ,
...)
|
proto_text_fetched_null ,
proto_text_fetched_bit ,
proto_text_fetched_tinyint
proto_text_fetched_short ,
proto_text_fetched_int24 ,
proto_text_fetched_int
proto_text_fetched_bigint ,
proto_text_fetched_decimal ,
proto_text_fetched_float
proto_text_fetched_double ,
proto_text_fetched_date ,
proto_text_fetched_year
proto_text_fetched_time ,
proto_text_fetched_datetime ,
proto_text_fetched_timestamp
proto_text_fetched_string ,
proto_text_fetched_blob ,
proto_text_fetched_enum
proto_text_fetched_set ,
proto_text_fetched_geometry ,
proto_text_fetched_other
|
Connection | Total number of columns of a certain type fetched from a normal query (MySQL text protocol). | Mapping from C API / MySQL meta data type to statistics name:
Note that the MYSQL_*-type constants may not be associated with the very same SQL column types in every version of MySQL. |
proto_binary_fetched_null ,
proto_binary_fetched_bit ,
proto_binary_fetched_tinyint
proto_binary_fetched_short ,
proto_binary_fetched_int24 ,
proto_binary_fetched_int ,
proto_binary_fetched_bigint ,
proto_binary_fetched_decimal ,
proto_binary_fetched_float ,
proto_binary_fetched_double ,
proto_binary_fetched_date ,
proto_binary_fetched_year ,
proto_binary_fetched_time ,
proto_binary_fetched_datetime ,
proto_binary_fetched_timestamp ,
proto_binary_fetched_string ,
proto_binary_fetched_blob ,
proto_binary_fetched_enum ,
proto_binary_fetched_set ,
proto_binary_fetched_geometry ,
proto_binary_fetched_other
|
Connection | Total number of columns of a certain type fetched from a prepared statement (MySQL binary protocol). | For type mapping see proto_text_* above. |
Connection
Statistic | Scope | Description | Notes |
---|---|---|---|
connect_success , connect_failure
|
Connection | Total number of successful / failed connection attempt. | Reused connections and all other kinds of connections are included. |
reconnect |
Process | Total number of (real_)connect attempts made on an already opened connection handle. | The code sequence $link = new mysqli(...);
$link->real_connect(...) will cause a
reconnect. But $link = new mysqli(...);
$link->connect(...) will not because
$link->connect(...) will explicitly
close the existing connection before a new connection is
established. |
pconnect_success |
Connection | Total number of successful persistent connection attempts. | Note that connect_success holds the sum of successful
persistent and non-persistent connection attempts. The
number of successful non-persistent connection attempts is
connect_success -
pconnect_success . |
active_connections |
Connection | Total number of active persistent and non-persistent connections. | |
active_persistent_connections |
Connection | Total number of active persistent connections. | The total number of active non-persistent connections is
active_connections -
active_persistent_connections . |
explicit_close |
Connection | Total number of explicitly closed connections (ext/mysqli only). | Examples of code snippets that cause an explicit close :
$link = new mysqli(...); $link->close(...) $link = new mysqli(...); $link->connect(...) |
implicit_close |
Connection | Total number of implicitly closed connections (ext/mysqli only). | Examples of code snippets that cause an implicit close :
|
disconnect_close |
Connection | Connection failures indicated by the C API call
mysql_real_connect during an attempt
to establish a connection. |
It is called disconnect_close because the connection
handle passed to the C API call will be closed. |
in_middle_of_command_close |
Process | A connection has been closed in the middle of a command execution (outstanding result sets not fetched, after sending a query and before retrieving an answer, while fetching data, while transferring data with LOAD DATA). | Unless you use asynchronous queries this should only happen if your script stops unexpectedly and PHP shuts down the connections for you. |
init_command_executed_count |
Connection | Total number of init command executions, for example,
mysqli_options(MYSQLI_INIT_COMMAND ,
...) . |
The number of successful executions is
init_command_executed_count -
init_command_failed_count . |
init_command_failed_count |
Connection | Total number of failed init commands. |
COM_* Commands
Statistic | Scope | Description | Notes |
---|---|---|---|
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
|
Connection | Total number of attempts to send a certain 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.” Usage examples:
|
Miscellaneous
Statistic | Scope | Description | Notes |
---|---|---|---|
explicit_stmt_close ,
implicit_stmt_close
|
Process | Total number of close prepared statements. | A close is always considered explicit but for a failed prepare. |
mem_emalloc_count ,
mem_emalloc_ammount ,
mem_ecalloc_count ,
mem_ecalloc_ammount ,
mem_erealloc_count ,
mem_erealloc_ammount ,
mem_efree_count ,
mem_malloc_count ,
mem_malloc_ammount ,
mem_calloc_count ,
mem_calloc_ammount ,
mem_realloc_count ,
mem_realloc_ammount ,
mem_free_count
|
Process | Memory management calls. | Development only. |
command_buffer_too_small |
Connection | Number of network command buffer extensions while sending commands from PHP to MySQL. |
mysqlnd allocates an internal command/network buffer of
If mysqlnd has to grow the buffer beyond its initial
size of
The default buffer size is 2048 bytes in PHP 5.3.0. In
future versions the default will be 4kB or larger. The
default can changed either through the
It is recommended to set the buffer size to no less than
4096 bytes because mysqlnd also uses it when reading
certain communication packet from MySQL. In PHP 5.3.0,
mysqlnd will not grow the buffer if MySQL sends a packet
that is larger than the current size of the buffer. As a
consequence mysqlnd is unable to decode the packet and
the client application will get an error. There are only
two situations when the packet can be larger than the
2048 bytes default of
As of PHP 5.3.2 mysqlnd does not allow setting buffers smaller than 4096 bytes. |
connection_reused |
User Comments
Add your own comment.