The MySQL native driver for PHP (mysqlnd) is capable of collecting some 120 performance statistics. This is about twice as much as it was when I blogged about the 59 tuning screws for mysqlnd. While the basics have not not changed and the API calls for accessing the data remained the same (see previous posting) the new figures have never been described before.
The figures are for those of you who want to squeeze the last out of the PHP. Many of the statistics have been written for those who have developed mysqlnd and not for PHP users. The data is certainly still of interest for PHP experts but let me stress out again that it is for experts.
Statistics are either aggregated on on a per-connection or per-process basis. Changes to per-connection statistics also change the corresponding per-process statistics. But per-process statistics changes are not necessarily reflected in per-connection statistics.
For thread safety statistics are protected by a mutex. There is one mutex for all statistics. Due to the high number of fine grained statistics many mutex locks will happen when collecting statistics. For performance reasons you should turn off statistics in production. Of course, no mutex locks will happen if you use CGI/Fast-CGI.
| Class: network |
|
Network related data.
Some MySQL Client Server protocol packets are not counted because they require no special en-/decoding routines. mysqlnd currently only counts those commands which require extra methods to be handled.
General notes:
- Reduce network traffic: fetch no more than required
- Reduce network traffic: free MySQL server resources as early as possible to lower server resource consumption
|
| bytes_sent, bytes_retreived |
| |
Scope: connection. Incoming and outgoing traffic: from MySQL to PHP and from PHP to MySQL.
Number of bytes sent from PHP to MySQL and number of bytes received from MySQL.
Usage examples:
- Check efficiency of compression protocol
- Check if PHP connects to MySQL
- Check if PHP needs to be compiled with MySQL support. Note API calls such as mysqli_get_client_version() that won’t cause network traffic.
|
| packets_sent, packets_received |
| |
Scope: connection. Outgoing traffic: from PHP to MySQL.
Number of MySQL Client Server protocol packages sent and received.
Only useful for debugging CS protocol implementation.
|
| protocol_overhead_in, protocol_overhead_out |
| |
Scope: connection. Incoming and outgoing traffic: from MySQL to PHP and from PHP to MySQL.
MySQL Client Server protocol overhead in bytes for incoming and outgoing traffic. Currently only the Packet Header (4 bytes) is considered as overhead. Therefore the following is true:
protocol_overhead_in = packets_received * 4protocol_overhead_out = packets_sent * 4
Only useful for debugging CS protocol implementation.
|
| bytes_received_ok_packets, packets_received_ok |
| |
Scope: connection. Incoming traffic: from MySQL to PHP.
Number of MySQL Client Server protocol OK packets and their total size in bytes. OK packages can contain a status message. The length of the status message can vary and thus the size of an OK package is not fixed.
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_eof_packets, packets_received_eof |
| |
Scope: connection. Incoming traffic: from MySQL to PHP.
Number of MySQL Client Server protocol EOF packets and their total size in bytes. EOF can vary in size depending on the server version. Also, EOF can transport an error message. 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.
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_header_packet, packets_received_reset_header |
| |
Scope: connection. Incoming traffic: from MySQL to PHP.
Number of MySQL Client Server protocol result set header packets and their total size in bytes. The size of the packets varies depending on the payload (LOAD LOCAL INFILE, INSERT/UPDATE, SELECT, error message).
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_field_meta_packet, packets_received_rset_field_meta |
| |
Scope: connection. Incoming traffic: from MySQL to PHP.
Number of MySQL Client Server protocol result set meta data (field information) packets and their total size in bytes. 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).
|
| bytes_received_rset_row_packet, packets_received_rset_row |
| |
Scope: connection. Incoming traffic: from MySQL to PHP.
Number of MySQL Client Server protocol result set row data packets and their total size in bytes. 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).
|
| bytes_received_prepare_response_packet, packets_received_prepare_response |
| |
Scope: connection. Incoming traffic: from MySQL to PHP.
Number of MySQL Client Server protocol OK for Prepared Statement Initialization packets (prepared statement init packets) and their total size in bytes. 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 occured 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).
|
| bytes_received_change_user_packet, packets_received_change_user |
| |
Scope: connection. Incoming traffic: from MySQL to PHP.
Number of MySQL Client Server protocol COM_CHANGE_USER packets and their total size in bytes. 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_sent_command |
| |
Scope: connection. Outgoing traffic: from PHP to MySQL.
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 occured while sending data to MySQL. The only error recoded is command_buffer_too_small (see below).
Only useful for debugging CS protocol implementation.
|
| Class: result set |
|
Information about result sets.
General notes:
- Identify scripts that cause high load
- Check if script makes MySQL calculate more rows than needed
- Identify slow queries
|
| result_set_queries |
| |
Scope: 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 |
| |
Scope: 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 |
| |
Scope: 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).
Aside note: see earlier (german language) blog post on undocumented C API flags.
|
| bad_index_used |
| |
Scope: 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 |
| |
Scope: connection.
This flag seems not mentioned in the MySQL documentation. Likely meaning: 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 |
| |
Scope: connection.
Number of buffered result sets returned by "normal" queries. "Normal" means "not prepared statement" in the following.
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 free’d 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 |
| |
Scope: connection.
Number of unbuffered result sets returned by "normal" queries.
Examples of API calls that will not buffer result sets on the client: mysqli_use_result().
|
| ps_buffered_sets |
| |
Scope: 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 |
| |
Scope: connection.
Number of unbuffered result sets returned by prepared statements. By default prepared statements are unbuffered.
|
| flushed_normal_sets, flushed_ps_sets |
| |
Scope: connection.
Number of result sets from "normal" queries and 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
- 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 |
| |
Scope: 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 |
| |
Scope: 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 |
| |
Scope: 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 |
| |
Scope: 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 -> mysqlnd internal buffer) and buffered rows that have been fetched by the client application (mysqlnd internal buffer -> 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 buffere results: mysqli_query(), mysqli_store_results().
|
| rows_fetched_from_client_normal_buffered, rows_fetched_from_client_ps_buffered |
| |
Scope: 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 |
| |
Scope: 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 |
| |
Scope: connection.
Total number of rows fetch by the client from a cursor created by a prepared statement.
|
| rows_skipped_normal, rows_skipped_ps |
| |
Scope: connection.
Buggy - don’t use it.
Possible future meaning: number of rows skipped when flushing a result set.
|
| copy_on_write_saved, copy_on_write_performed |
| |
Scope: 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() (read above why).
|
| explicit_free_result, implicit_free_result |
| |
Scope: connection, process (only during prepared statement cleanup).
Total number of free’d result sets. The free is always considered explicit but for result sets created by an init command (e.g. 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_intproto_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 |
| |
Scope: 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:
- MYSQL_TYPE_NULL - proto_text_fetched_null
- MYSQL_TYPE_BIT - proto_text_fetched_bit
- MYSQL_TYPE_TINY - proto_text_fetched_tinyint
- MYSQL_TYPE_SHORT - proto_text_fetched_short
- MYSQL_TYPE_INT24 - proto_text_fetched_int24
- MYSQL_TYPE_LONG - proto_text_fetched_int
- MYSQL_TYPE_LONGLONG - proto_text_fetched_bigint
- MYSQL_TYPE_DECIMAL, MYSQL_TYPE_NEWDECIMAL - proto_text_fetched_decimal
- MYSQL_TYPE_FLOAT - proto_text_fetched_float
- MYSQL_TYPE_DOUBLE - proto_text_fetched_double
- MYSQL_TYPE_DATE, MYSQL_TYPE_NEWDATE - proto_text_fetched_date
- MYSQL_TYPE_YEAR - proto_text_fetched_year
- MYSQL_TYPE_TIME - proto_text_fetched_time
- MYSQL_TYPE_DATETIME - proto_text_fetched_datetime
- MYSQL_TYPE_TIMESTAMP - proto_text_fetched_timestamp
- MYSQL_TYPE_STRING, MYSQL_TYPE_VARSTRING, MYSQL_TYPE_VARCHAR - proto_text_fetched_string
- MYSQL_TYPE_TINY_BLOB, MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB, MYSQL_TYPE_BLOB - proto_text_fetched_blob
- MYSQL_TYPE_ENUM - proto_text_fetched_enum
- MYSQL_TYPE_SET - proto_text_fetched_set
- MYSQL_TYPE_GEOMETRY - proto_text_fetched_geometry
- any MYSQL_TYPE_* not listed before (there should be none) - proto_text_fetched_other
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_intproto_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 |
| |
Scope: connection.
Total number of columns of a certain type fetched from a prepared statement (MySQL binary protocol). Type mapping see proto_text_* above
|
| Class: connection |
|
Information on connections.
|
| connect_success, connect_failure |
| |
Scope: connection.
Total number of successful / failed connection attempt. Reused connections and all otker kinds of connections are included.
|
| reconnect |
| |
Scope: 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 |
| |
Scope: 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 |
| |
Scope: connection.
Total number of active persistent and non-persistent connections.
|
| active_persistent_connections |
| |
Scope: connection.
Total number of active persistent connections. The total number of active non-persistent connections is active_connections - active_persistent_connections.
|
| explicit_close |
| |
Scope: 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 |
| |
Scope: connection.
Total number of implicitly closed connections (ext/mysqli only). Examples of code snippets that cause an implicit close :
$link = new mysqli(...); $link->real_connect(...)unset($link)- persistent connection: pooled connection has been created with real_connect and there may be unknown options set - close implicitly to avoid returning a connection with unknown options
- persistent connection: ping/change_user fails and ext/mysqli closes the connection
- end of script execution: close connections that have not been closed by the user
|
| disconnect_close |
| |
Scope: 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 |
| |
Scope: 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 |
| |
Total number of init command (e.g. mysqli_options(MYSQLI_INIT_COMMAND , ...)) executions. The number of successful executions is init_command_executed_count - init_command_failed_count.
|
| init_command_failed_count |
| |
Totoal number of failed init commands.
|
| Class: assorted |
| explicit_stmt_close, implicit_stmt_close |
| |
Scope: 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 |
| |
Scope: process.
Development only: memory management calls.
|
| command_buffer_too_small |
| |
Scope: connection.
Number of network command buffer extensions while sending commands from PHP to MySQL.
mysqlnd allocates an internal command/network buffer of mysqlnd.net_cmd_buffer_size (php.ini) bytes for every connection. If a MySQL Client Server protocol command, for example, 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 (php.ini) bytes for almost every connection, you should consider to increase the default size to avoid re-allocations.
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 php.ini setting mysqlnd.net_cmd_buffer_size or using mysqli_options(MYSQLI_OPT_NET_CMD_BUFFER_SIZE, int size).
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 mysqlnd.net_cmd_buffer_size in PHP 5.3.0: the packet transports a very long error message or the packet holds column meta data from COM_LIST_FIELD (mysql_list_fields() and the meta data comes from a string column with a very long default value (>1900 bytes). No bug report on this exists - it should happen rarely.
As of PHP 5.3.2 mysqlnd does not allow setting buffers smaller than 4096 bytes.
|
) on Linux or 
).