Developing MySQL Database Applications With PHP Part 4: Using the MySQL Native Driver for PHP, mysqlnd

This series of tutorials shows the essential steps involved in installing PHP with MySQL extensions mysql, mysqli, and pdo_mysql, and with support for the MySQL native driver for PHP, mysqlnd. The tutorials also provide simple examples to connect, insert, and retrieve data from a MySQL database. Because the focus is on database connectivity from a PHP script, these tutorials assume that some kind of MySQL database is already up and accessible from the client machine.

Application developers who are new to the development of MySQL database applications with PHP are the target audience of this article.

To communicate with the MySQL database server, the extensions mysql and mysqli and the pdo_mysql driver rely on the MySQL client library, libmysql. The MySQL native driver for PHP (referred to as mysqlnd from this point) is an additional, alternative way to connect from PHP 5 and PHP 6 to the MySQL Server 4.1 or later versions. The mysqlnd driver is a replacement for the MySQL client library, libmysql, and it is tightly integrated into PHP starting with the release of PHP 5.3. That is, from PHP 5.3 onwards the developer can choose between libmysql and mysqlnd when using ext/mysql, ext/mysqli, or PDO extensions to connect to the MySQL server 4.1 or newer. Due to the tight integration into PHP 5.3 (and later), mysqlnd eliminates the dependency on the MySQL client programming support when the database extensions and the database driver are built with the support for mysqlnd.

The mysqlnd driver is not another PHP extension like mysqli, and it does not have an API that is exposed to the user. It is a library that provides similar functionality to that of the MySQL client library, libmysql. The mysqlnd and libmysql libraries implement the MySQL communication protocol, hence both of those libraries can be used to connect to the MySQL Server.

Because mysqlnd is neither a new extension nor a programming API, but just an alternative to libmysql to connect from PHP to the MySQL Server, there is no need to make changes to the existing PHP scripts. Existing scripts that are running properly with ext/mysql, ext/mysqli, and PDO extensions built with libmysql support continue to run with the exact same behavior even when the ext/mysql, ext/mysqli, and PDO extensions are built with mysqlnd support.

From the performance perspective, mysqlnd might be as fast as libmysql and might even outperform libmysql in some cases. The generic recommendation is to try mysqlnd with your PHP application and then decide based on the performance results.

Because most of the prepackaged PHP binary installations might not have the support for mysqlnd enabled by default, it is recommended to build PHP and the required database extensions with mysqlnd from the source code. The rest of this section focuses on the installation of PHP 5.3 from the source code.

As mentioned earlier, merely replacing libmysql with mysqlnd does not force PHP developers to rewrite their applications. Hence we can use any of the existing code samples in this tutorial series to demonstrate successful database connectivity with mysqlnd under the hood. The following example displays the output from a PHP interpreter that is built with mysqlnd support. The ext/mysqli extension is also built with mysqlnd support. The PHP script used in the following example was originally shown in Developing MySQL Database Applications With PHP Part 1: Using the MySQL Improved Extension, mysqli.

Notice zero references to the libmysql library in the runtime linker's diagnostic debugging information.

The extensions ext/mysql and ext/mysqli and the driver PDO_MySQL support persistent connections when built with mysqlnd. For more information about persistent connections, see the section "Persistent Database Connections" in the tutorial Developing MySQL Database Applications With PHP Part 2: Using the MySQL Extension, mysql. Note that ext/mysqli does not support persistent connections when built with libmysql. However ext/mysqli does support persistent connections when built with mysqlnd. For example, to establish a persistent connection with the MySQL Server using ext/mysqli and mysqlnd, prefix the database host with the string "p:" (p stands for persistent) as shown below.

While the persistent connections might improve the performance for some applications, especially running over slow network links, they can lead to trouble if not used properly. For example, if you lock a table and close the database connection before unlocking it, normally it is unlocked when the connection is terminated. However because persistent connections do not close, any tables you accidentally leave locked remain locked, and the only way to unlock them is probably to wait for the connection to time out or kill the process. Also due to the persistent nature of the connections, the process can consume more resources.

Calling mysqli::change_user() can alleviate the issue to some extent. Using this method always causes the current database connection to behave as if it is a completely new database connection regardless of whether the operation is completed successfully. mysqli::change_user() rolls back any active transactions, closes all temporary tables, and unlocks all the locked tables.

Alternatively, you can register a function that does an explicit rollback of non-committed transactions on script shutdown, with register_shutdown_function(). Do your due diligence in finding the right function that fits your needs in terms of the performance and the functionality.

The mysqlnd driver can save memory. In the best cases, it consumes only 50% as much memory as that of libmysql, especially when the client application does not modify the data in the result set after executing a query. Keep in mind that the client must use ext/mysqli and treat the data returned by the query as read-only to fully realize mysqlnd's memory gains. If the client application modifies any of the data, mysqlnd behaves just like libmysql.

Let's have a quick look at the memory consumption in both the cases (mysqlnd and libmysql) with an example before delving into the internals. For easy comparison, the sample PHP script does not modify any of the arrays returned from the fetch method. The following example uses DTrace on the Sun Solaris OS to monitor the calls to malloc() and prints the requested bytes of memory on the standard output.

bash# cat monitormalloc.d

#!/usr/sbin/dtrace -s

pid$1:libc:malloc:entry
{
	printf("\t\tSize : %d Bytes", arg0);
	ustack();
 	@malloc[probefunc] = quantize(arg0);
}

CASE 1: ext/mysqli with libmysql

In one terminal window:

bash# /opt/coolstack/php5/bin/php PHPmysqliClient.php

In another terminal window, run monitormalloc.d `pgrep php` and press any key to continue ..

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia

In another terminal window:

bash# ./monitormalloc.d `pgrep php`
dtrace: script './monitormalloc.d' matched 1 probe
CPU     ID                    FUNCTION:NAME
  0  80920                     malloc:entry 		Size : 964 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce23bb47
              mysqli.so`0xce11d292

  0  80920                     malloc:entry 		Size : 20 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce23da60
              mysqli.so`0xce11dc72

  0  80920                     malloc:entry 		Size : 20 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce21d991
              libmysqlclient.so.16.0.0`0xce21d9ce
              libmysqlclient.so.16.0.0`0xce23da72
              mysqli.so`0xce11dc72

  0  80920                     malloc:entry 		Size : 17 Bytes
		... elided stack traces for brevity ...
  0  80920                     malloc:entry 		Size : 152 Bytes
  0  80920                     malloc:entry 		Size : 16384 Bytes
  0  80920                     malloc:entry 		Size : 8199 Bytes
  0  80920                     malloc:entry 		Size : 7 Bytes
  0  80920                     malloc:entry 		Size : 4088 Bytes
  0  80920                     malloc:entry 		Size : 4088 Bytes
  0  80920                     malloc:entry 		Size : 4088 Bytes
  0  80920                     malloc:entry 		Size : 4088 Bytes
  0  80920                     malloc:entry 		Size : 4088 Bytes
  0  80920                     malloc:entry 		Size : 4088 Bytes
  0  80920                     malloc:entry 		Size : 4088 Bytes
  0  80920                     malloc:entry 		Size : 4088 Bytes
  0  80920                     malloc:entry 		Size : 4088 Bytes
  0  80920                     malloc:entry 		Size : 18261 Bytes
  0  80920                     malloc:entry 		Size : 58 Bytes
  0  80920                     malloc:entry 		Size : 4088 Bytes
  0  80920                     malloc:entry 		Size : 4088 Bytes
  0  80920                     malloc:entry 		Size : 120 Bytes
  0  80920                     malloc:entry 		Size : 5 Bytes
  0  80920                     malloc:entry 		Size : 6 Bytes
  0  80920                     malloc:entry 		Size : 5 Bytes
  0  80920                     malloc:entry 		Size : 56 Bytes
  0  80920                     malloc:entry 		Size : 8164 Bytes
  0  80920                     malloc:entry 		Size : 8164 Bytes
  0  80920                     malloc:entry 		Size : 92 Bytes
  0  80920                     malloc:entry 		Size : 56 Bytes
  0  80920                     malloc:entry 		Size : 8164 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce21a27b
              libmysqlclient.so.16.0.0`0xce23b8a4
              libmysqlclient.so.16.0.0`0xce23d4fa
              mysqli.so`0xce11fe56

  0  80920                     malloc:entry 		Size : 262144 Bytes
              libc.so.1`malloc
              php`0x856fb98

^C

  malloc                                            
           value  ------------- Distribution ------------- count    
               2 |                                         0        
               4 |@@@@@                                    4        
               8 |                                         0        
              16 |@@@@                                     3        
              32 |@@@@                                     3        
              64 |@@@                                      2        
             128 |@                                        1        
             256 |                                         0        
             512 |@                                        1        
            1024 |                                         0        
            2048 |@@@@@@@@@@@@@@                           11       
            4096 |@@@@                                     3        
            8192 |@                                        1        
           16384 |@@@                                      2        
           32768 |                                         0        
           65536 |                                         0        
          131072 |                                         0        
          262144 |@                                        1        
          524288 |                                         0        

CASE 2: ext/mysqli with mysqlnd

In one terminal window:

bash# /export/home/php53/bin/php PHPmysqliClient.php
In another terminal window, run monitormalloc.d `pgrep php` and press any key to continue ..

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia

In another terminal window:

bash# ./monitormalloc.d `pgrep php`
dtrace: script './monitormalloc.d' matched 1 probe
CPU     ID                    FUNCTION:NAME
  0  80920                     malloc:entry 		Size : 262144 Bytes
              libc.so.1`malloc
              php`0x82f702b
              php`0x82f80ab
              php`0x82f841f
              php`0x82f98c4
              php`0x82c7668
              php`0x83c30ae
              php`0x80c059c

^C

  malloc                                            
           value  ------------- Distribution ------------- count    
          131072 |                                         0        
          262144 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 1        
          524288 |                                         0

In the case of ext/mysqli with libmysql, there are more than 25 calls to malloc() accounting for a total size around 367 KB, whereas in the case of ext/mysqli with mysqlnd, there is only one call to malloc() with a size of 256 KB. In other words, mysqlnd is consuming 30% less memory relative to libmysql to do similar database operations. (In reality, it is incorrect to treat every byte allocated as the memory consumed unless there exists a corresponding memory mapping -- however for the sake of simplicity, let's assume that all the allocated bytes are eventually consumed.)

The memory savings in the above example are the result of mysqlnd's ability to hold the results only once in the memory. On the other hand, because libmysql is not a part of PHP, some of the results fetched by libmysql are copied into memory twice. When libmysql fetches the data from the MySQL Server, it puts the data into its own buffers. Then the data gets copied from the libmysql buffers into respective ext/mysqli data structures (often referred as zvals) before it is made available to the PHP clients to consume. So with ext/mysqli and libmysql, there might be two copies of the data in the main memory: one copy inside the libmysql buffers and the other inside zvals. With mysqlnd, there might be only one copy of the data in the memory. The mysqlnd driver also uses buffers but links the zval structures directly to the read buffers, wherever possible. Therefore in a majority of instances, mysqlnd consumes less memory relative to libmysql. In the worst case, it can consume as much memory as that of libmysql. The total memory savings depends on the size of the buffered result set.

Shown below are the simplified behind-the-scenes actions of ext/mysqli with libmysql and ext/mysqli with mysqlnd when mysqli sends a query:

In short, ext/mysqli with libmysql does the following, when compared to ext/mysqli with mysqlnd:

The mysqlnd driver extends the ext/mysqli API with one new method, mysqli_fetch_all(). The mysqli_fetch_all() method fetches all result rows and returns the result set as an associative array, a numeric array, or both. The method signature is shown below for both the procedural as well as the object-oriented style of programming.

where $result is a result set identifier returned by mysqli_query(), mysqli_store_result(), or mysqli_use_result(), and $resulttype is an optional constant indicating what type of array should be produced from the current row data. The possible values for this parameter are the constants MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH. Defaults to MYSQLI_NUM.

Because mysqli_fetch_all() returns all the rows as an array in a single step, it can consume more memory than some of its counterparts like mysqli_fetch_array(). The method mysqli_fetch_array() returns one row at a time from the result set, hence it consumes less memory relative to mysqli_fetch_array(). Besides, if you need to iterate over the result set, you might need a foreach() loop, and this approach might be little slower compared to the result set retrieval using mysqli_fetch_array(). Hence consider using mysqli_fetch_all() only in those situations where the fetched result set is sent to another layer for post processing. If you must process the fetched result set in the same layer with the help of iterators, then the benefit of using the mysqli_fetch_all() method might be minimal, if there is any.

The mysqlnd driver collects a lot of statistics that you can use to tune your application. The mysqlnd driver enhances the ext/mysqli API with three mysqli_get_XX_stats() methods for easy monitoring and to simplify the bottleneck analysis. For example, using a combination of mysqli_get_XX_stats() methods, you can easily identify a script that is opening more database connections than it needs or selecting more rows than it consumes.

To access per-process client statistics, call mysqli_get_client_stats() with no arguments. Similarly, to access client statistics per connection, call mysqli_get_connection_stats() with the database connection handle as the argument. Both of these methods return an associated array with the name of the statistic parameter as the key and the corresponding data as the value.

Alternatively, per-process client statistics can be accessed by calling the phpinfo() method.

All the methods mentioned above return statistics, such as bytes_sent and bytes_received, which return the number of bytes sent to and received from the MySQL Server, result_set_queries, which shows the number of queries that generated a result set, and buffered_sets and unbuffered_sets, which show the number of buffered and unbuffered result sets for the queries that generate a result set but are not run as a prepared statement. The rows_fetched_from_server_normal method shows the number of rows that have been fetched from the server using buffered and unbuffered result sets. The rows_buffered_from_client_normal method shows the number of rows fetched from the server and buffered on the client side. The rows_skipped_normal method shows the number of rows generated by the server but not read from the client.

The mysqlnd driver collects statistics from its internal zval cache, which you can access with the help of the mysqli_get_cache_stat() method. This method returns an associative array with the name of the statistic as the key and the corresponding data as the value. The zval cache statistics might be useful to tweak the zval cache-related php.ini settings for better performance.

The purpose of the sample code is only to illustrate the syntactical use of new features of mysqlnd. The sample code does not represent any real-world scenarios.


bash# cat PHPmysqliClientmysqlnd.php

<?php

	/* create a persistent connection to the MySQL server */
	$cxn = new mysqli("p:localhost", "root", "admin", "test", 3306, "/tmp/mysql.sock")
		or die ('Could not connect to the database server' . mysqli_connect_error());

	$query = "SELECT * FROM City";
	
	/* execute the query */
	if ($cxn->real_query ($query)) {

		/* initiate the result set retrieval */
		if ($result = $cxn->store_result()) {

			/* find the number of rows in the result set */
			$nrows = $result->num_rows;

			echo "\nRetrieved $nrows row(s).\n\n";
			echo "CityName\n--------\n";

			$all_rows = $result->fetch_all(MYSQLI_ASSOC);

			for($i = 0; $i < count($all_rows); $i++) {
				echo $all_rows[$i][CityName] . "\n";
			}
		}

		/* close the result set */
		$result->close();
	}

	echo "\n\nClient Statistics After One Query\n---------------------------------";
	$client_stats = mysqli_get_client_stats();
	#var_dump($client_stats);
	foreach ($client_stats as $key=>$value) {
		if ($value > 0) {
                       	echo "\n$key : $value";
		}
	}

	echo "\n\nStatistics for Connection #1\n----------------------------";
	$conn_stats = mysqli_get_connection_stats($cxn);
	#var_dump($conn_stats);
	foreach ($conn_stats as $key=>$value) {
		if ($value > 0) {
                       	echo "\n$key : $value";
		}
	}

	echo "\n\nCache Statistics After One Query\n--------------------------------";
	$cache_stats = mysqli_get_cache_stats();
	#var_dump($cache_stats);
	foreach ($cache_stats as $key=>$value) {
		if ($value > 0) {
                       	echo "\n$key : $value";
		}
	}

	echo "\n\n=================================\n\n";
	echo "\nEstablishing connection #2 to the MySQL server ..\n\n";

	/* create a non-persistent connection to the MySQL server */
	$cxn2 = new mysqli("localhost", "root", "admin", "mysql", 3306, "/tmp/mysql.sock")
		or die ('Could not connect to the database server' . mysqli_connect_error());

	$query = "SELECT Host, User FROM user";

	/* execute the query */
	if ($cxn2->real_query ($query)) {

		/* initiate the result set retrieval */
		if ($result = $cxn2->store_result()) {

			/* find the number of rows in the result set */
			$nrows = $result->num_rows;
			echo "\nRetrieved $nrows row(s).\n\n";

			echo "Host\t\tUser\n----\t\t----\n";

			$all_rows = $result->fetch_all(MYSQLI_ASSOC);

			for($i = 0; $i < count($all_rows); $i++) {
				echo $all_rows[$i][Host] . "\t" . $all_rows[$i][User] . "\n";
			}
		}

                /* close the result set */
		$result->close();
	}

	echo "\n\nClient Statistics After Two Queries\n-----------------------------------";
	$client_stats = mysqli_get_client_stats();
	#var_dump($client_stats);
	foreach ($client_stats as $key=>$value) {
		if ($value > 0) {
			echo "\n$key : $value";
		}
	}

	echo "\n\nStatistics for Connection #2\n----------------------------";
	$conn_stats = mysqli_get_connection_stats($cxn2);
	#var_dump($conn_stats);
	foreach ($conn_stats as $key=>$value) {
		if ($value > 0) {
			echo "\n$key : $value";
		}
	}

	echo "\n\nCache Statistics After Two Queries\n----------------------------------";
	$cache_stats = mysqli_get_cache_stats();
	#var_dump($cache_stats);
	foreach ($cache_stats as $key=>$value) {
		if ($value > 0) {
			echo "\n$key : $value";
		}
	}

	echo "\n";

	//phpinfo();

	/* close the database connections */
	$cxn->close();
	$cxn2->close();
?>

bash# /export/home/php53/bin/php PHPmysqliClientmysqlnd.php

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia


Client Statistics After One Query
---------------------------------
bytes_sent : 90
bytes_received : 222
packets_sent : 2
packets_received : 9
protocol_overhead_in : 36
protocol_overhead_out : 8
bytes_received_ok_packet : 11
bytes_received_eof_packet : 9
bytes_received_rset_header_packet : 5
bytes_received_rset_field_meta_packet : 54
bytes_received_rset_row_packet : 70
packets_sent_command : 1
packets_received_ok : 1
packets_received_eof : 1
packets_received_rset_header : 1
packets_received_rset_field_meta : 1
packets_received_rset_row : 4
result_set_queries : 1
buffered_sets : 1
rows_fetched_from_server_normal : 3
rows_buffered_from_client_normal : 3
rows_fetched_from_client_normal_buffered : 3
rows_skipped_normal : 3
copy_on_write_performed : 3
connect_success : 1
active_connections : 1
active_persistent_connections : 1
explicit_free_result : 1
mem_erealloc_count : 1
mem_efree_count : 2
mem_realloc_count : 1
proto_text_fetched_string : 3

Statistics for Connection #1
----------------------------
bytes_sent : 90
bytes_received : 222
packets_sent : 2
packets_received : 9
protocol_overhead_in : 36
protocol_overhead_out : 8
bytes_received_ok_packet : 11
bytes_received_eof_packet : 9
bytes_received_rset_header_packet : 5
bytes_received_rset_field_meta_packet : 54
bytes_received_rset_row_packet : 70
packets_sent_command : 1
packets_received_ok : 1
packets_received_eof : 1
packets_received_rset_header : 1
packets_received_rset_field_meta : 1
packets_received_rset_row : 4
result_set_queries : 1
buffered_sets : 1
rows_fetched_from_server_normal : 3
rows_buffered_from_client_normal : 3
rows_skipped_normal : 3
connect_success : 1
active_connections : 1
active_persistent_connections : 1
explicit_free_result : 1
proto_text_fetched_string : 3

Cache Statistics After One Query
--------------------------------
put_misses : 3
get_hits : 3
size : 2000
free_items : 1997
references : 3

=================================


Establishing connection #2 to the MySQL server ..


Retrieved 5 row(s).

Host		User
----		----
127.0.0.1	root
localhost	
localhost	root
unknown	
unknown	root


Client Statistics After Two Queries
-----------------------------------
bytes_sent : 190
bytes_received : 501
packets_sent : 4
packets_received : 21
protocol_overhead_in : 84
protocol_overhead_out : 16
bytes_received_ok_packet : 22
bytes_received_eof_packet : 18
bytes_received_rset_header_packet : 10
bytes_received_rset_field_meta_packet : 148
bytes_received_rset_row_packet : 157
packets_sent_command : 2
packets_received_ok : 2
packets_received_eof : 2
packets_received_rset_header : 2
packets_received_rset_field_meta : 3
packets_received_rset_row : 10
result_set_queries : 2
buffered_sets : 2
rows_fetched_from_server_normal : 8
rows_buffered_from_client_normal : 8
rows_fetched_from_client_normal_buffered : 8
rows_skipped_normal : 8
copy_on_write_performed : 13
connect_success : 2
active_connections : 2
active_persistent_connections : 1
explicit_free_result : 2
mem_erealloc_count : 1
mem_efree_count : 2
mem_realloc_count : 4
proto_text_fetched_string : 13

Statistics for Connection #2
----------------------------
bytes_sent : 100
bytes_received : 279
packets_sent : 2
packets_received : 12
protocol_overhead_in : 48
protocol_overhead_out : 8
bytes_received_ok_packet : 11
bytes_received_eof_packet : 9
bytes_received_rset_header_packet : 5
bytes_received_rset_field_meta_packet : 94
bytes_received_rset_row_packet : 87
packets_sent_command : 1
packets_received_ok : 1
packets_received_eof : 1
packets_received_rset_header : 1
packets_received_rset_field_meta : 2
packets_received_rset_row : 6
result_set_queries : 1
buffered_sets : 1
rows_fetched_from_server_normal : 5
rows_buffered_from_client_normal : 5
rows_skipped_normal : 5
connect_success : 1
active_connections : 1
explicit_free_result : 1
proto_text_fetched_string : 10

Cache Statistics After Two Queries
----------------------------------
put_misses : 13
get_hits : 13
size : 2000
free_items : 1987
references : 4

Be aware that some of the experimental functions that are available with ext/mysqli and libmysql are not available with ext/mysqli and mysqlnd, for example, mysqli_embedded_*() and mysqli_*rpl*_().

To find out more about Developing MySQL Database Applications With PHP, see all tutorials in the series:

Developing MySQL Database Applications With PHP Part 4:Using the MySQL Native Driver for PHP, mysqlnd

Developing MySQL Database Applications With PHP Part 4:
Using the MySQL Native Driver for PHP, `mysqlnd`