Here comes the next open source mysqlnd plugin prototype: PECL/mysqlnd_mux. The PHP mysqlnd connection multiplexing plugin shares a connection to MySQL between multiple user connection handles. Connection multiplexing aims to reduce connect time and lowers the total number of concurrenly opened connections. The first is primarily a benefit for the client, the latter is great for the server. Multiplexing may be most desired in environments with many short-lived connections and weak client-side pooling - such as a PHP web application.

PHP mysqlnd connection multiplexing plugin . More about PHP …

Psst, you want some client-side MySQL load balancing with and without sharding for your PHP application? PHP 5.3 has something to offer for you. It is free. It requires no to very little changes to your applications. It is open source but works with closed source applications as well. You won’t find this information in any book, because some book authors still do not cover "mysqlnd". Read on for 30+ lines of PHP to add round-robin connection load balancing and, 70+ lines of PHP to add MySQL master slave replication or sharding support to your application without changing the application. Black magic from Manmuckiel.

The goal

Psst, you want some client-side MySQL load balancing with and without sharding for your PHP application? PHP 5.3 has something to offer for you. It is free. It requires no to very little changes to your applications. It is open source but works with closed source applications as well. You won’t find this information in any book, because some book authors still do not cover "mysqlnd". Read on for 30+ lines of PHP to add round-robin connection load balancing and, 70+ lines of PHP to add MySQL master slave replication or sharding support to your application without changing the application. Black magic from Manmuckiel.

The goal

Recently I was forced to benchmark a mysqlnd plugin on Windows. X-Debug was no help. It worked flawless on Windows but it can’t tell you what goes on inside a PHP user space API call, for example, you will not know how much time mysqli_query() spends reading a result set. Very Sleepy is nice and the latest Microsoft Visual Studio profiler are probably wonderful but any of those external profiling tools did give me too fine-grained information. Also, they are external profiler which means you have to install extra software.

The mysqlnd statistics didn’t help me either. I didn’t need any aggregated values, I was curios if a certain function was the bottleneck. The solution:

• extra statistics for critical sections in the plugin using the mysqlnd statistics framework (C coding)
• dump profiling …

Recently I was forced to benchmark a mysqlnd plugin on Windows. X-Debug was no help. It worked flawless on Windows but it can’t tell you what goes on inside a PHP user space API call, for example, you will not know how much time mysqli_query() spends reading a result set. Very Sleepy is nice and the latest Microsoft Visual Studio profiler are probably wonderful but any of those external profiling tools did give me too fine-grained information. Also, they are external profiler which means you have to install extra software.

The mysqlnd statistics didn’t help me either. I didn’t need any aggregated values, I was curios if a certain function was the bottleneck. The solution:

• extra statistics for critical sections in the plugin using the mysqlnd statistics framework (C coding)
• dump profiling …

Did you ever want to see the database queries an arbitrary PHP MySQL application runs? It takes two free downloads and 62 characters to see them.

• Get PHP 5.3.4-dev
• Get PECL/mysqlnd_qc
• Add 19 characters to your configure line: --enable-mysqlnd-qc
• Add 43 characters at the end of applications source, e.g. using the auto_append_file configuration setting: var_dump(mysqlnd_qc_get_query_trace_log());
• Start your query analysis

array(329) {
  [0]=>
  array(8) {
    ["query"]=>
    string(27) "SET @@session.sql_mode = """
    ["origin"]=>
    string(1330) "#0 /home/nixnutz/Downloads/oxid/core/adodblite/adodbSQL_drivers/mysql/mysql_driver.inc(352): mysql_query('SET @@session.s...', Resource id #26)
#1 …

Did you ever want to see the database queries an arbitrary PHP MySQL application runs? It takes two free downloads and 62 characters to see them.

• Get PHP 5.3.4-dev
• Get PECL/mysqlnd_qc
• Add 19 characters to your configure line: --enable-mysqlnd-qc
• Add 43 characters at the end of applications source, e.g. using the auto_append_file configuration setting: var_dump(mysqlnd_qc_get_query_trace_log());
• Start your query analysis

array(329) {
  [0]=>
  array(8) {
    ["query"]=>
    string(27) "SET @@session.sql_mode = """
    ["origin"]=>
    string(1330) "#0 /home/nixnutz/Downloads/oxid/core/adodblite/adodbSQL_drivers/mysql/mysql_driver.inc(352): mysql_query('SET @@session.s...', Resource id #26)
#1 …

C and PHP MySQL clients can set a connection timeout before a connection is established to MySQL. The MySQL C API manual states about MYSQL_OPT_CONNECT_TIMEOUT , which is equal to PHPs MYSQLI_OPT_CONNECT_TIMEOUT:

MYSQL_OPT_CONNECT_TIMEOUT

Connect timeout in seconds.

From: http://dev.mysql.com/doc/refman/5.1/en/mysql-options.html

That is half of the story. The actual behaviour depends on the library you use, the operating system and the transport protocol (TCP/IP, Unix domain sockets, Windows named pipes, Shared Memory).

C users can choose between the MySQL Client Library, which ships with the MySQL server, and the …

C and PHP MySQL clients can set a connection timeout before a connection is established to MySQL. The MySQL C API manual states about MYSQL_OPT_CONNECT_TIMEOUT , which is equal to PHPs MYSQLI_OPT_CONNECT_TIMEOUT:

MYSQL_OPT_CONNECT_TIMEOUT

Connect timeout in seconds.

From: http://dev.mysql.com/doc/refman/5.1/en/mysql-options.html

That is half of the story. The actual behaviour depends on the library you use, the operating system and the transport protocol (TCP/IP, Unix domain sockets, Windows named pipes, Shared Memory).

C users can choose between the MySQL Client Library, which ships with the MySQL server, and the …

Sometimes you forget about what you did years ago, for example the ability of mysqlnd write back traces of slow queries into the PHP error log . Regardless if you use mysqlnd with ext/mysql, ext/mysqli or PDO_MYSQL, mysqlnd is able to tell you which of your code has executed a slow query. To enable the feature, you need to compile PHP with CFLAGS="-D A0". A0? Yeah, we must have had some doubts about the usefulness.

Slow queries

The MySQL server is capable of monitoring queries and recording slow queries in a slow query log. MySQL can log the following types of queries:

• SQL statements that took more than long_query_time seconds to execute
• SQL statement that required at least min_examined_row_limit rows to be examined (MySQL 5.1.21+)
• SQL statement …