PHP: 150 performance tuning screws for mysqlnd

2009/10/16 by admin

Yes, 150 means we added some 30 performance statistics to the MySQL native driver for PHP (mysqlnd) since monday. The new statistics are explained in three words: counting COM_* commands. COM_* commands refers to the command packets of the MySQL client server protocol. For example, COM_QUERY is used to execute nonprepared SQL statements.

Please check the earlier blog postings on how to use and access the statistics: PHP: 59 tuning screws for mysqlnd, PHP: 120 tuning screws for mysqlnd. The information given in those articles will find its way into the PHP and MySQL manual. The MySQL documentation team is working on an update. But for now, an hour after the latest commit, you are requested to check the blog postings.

The 28 `COM_*` statistics

Class: COM_* commands

Class: COM_* commands
Total number of command packets of the MySQL client server protocol sent from PHP to MySQL. The MySQL client server protocol describes some 28 commands which a client can send to MySQL. The commands serve different purposes from closing a connection to executing SQL statements.
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_deamon
	Scope: connection. Outgoing traffic: from PHP to MySQL. 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: 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 nonprepared SQL statements by checking if `COM_QUERY` is zero Identify PHP scripts that run exceedingly many SQL statements by checking `COM_QUERY` and `COM_EXECUTE`

Total number of command packets of the MySQL client server protocol sent from PHP to MySQL.

The MySQL client server protocol describes some 28 commands which a client can send to MySQL. The commands serve different purposes from closing a connection to executing SQL statements.

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_deamon

Scope: connection. Outgoing traffic: from PHP to MySQL.

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:

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 nonprepared SQL statements by checking if COM_QUERY is zero
Identify PHP scripts that run exceedingly many SQL statements by checking COM_QUERY and COM_EXECUTE

The 28 COM_* statistics

The 28 `COM_*` statistics