The mysqlnd extension appeared in PHP 5.3, but is still little known among developers. However, it is indispensable if your system is based on MySQL. If you want to know why this extension is so important, what it is, how to use it and what advantages it gives - read the article.

Interaction scheme

PHP interacts with MySQL through client libraries. There are two of them - libmysql and mysqlnd. The first is licensed by Oracle, while mysqlnd is distributed under the PHP license. However, both are largely supported by Oracle employees. If the user needs to interact with MySQL using PHP, then one of the three APIs can be used for this: ext / mysql, ext / mysqli, and ext / pdo_mysql.

Libraries

libmysql. Historically, PHP needs a client library written in C to interact with MySQL. It is also known as libmysql, and can be installed on the system with the apt-get install libmysql command. It implements the MySQL API:
')

 #include <stdio.h> #include <stdlib.h> #include <mysql/mysql.h> #include "mysql_example.h" /* Pour MYSQL_HOST, MYSQL_USER, MYSQL_PASS */ int main(int argv, char *argc[]) { MYSQL_RES *results = NULL; MYSQL_ROW row; MYSQL *db = NULL; db = (MYSQL *)malloc(sizeof(MYSQL)); mysql_init(db); mysql_options(db, MYSQL_INIT_COMMAND, "SET NAMES UTF8;"); if(!mysql_real_connect(db, MYSQL_HOST, MYSQL_USER, MYSQL_PASS, NULL, 0, NULL, 0)) { fprintf(stderr, "Failed to connect to host %s (%s)", MYSQL_HOST, mysql_error(db)); exit(EXIT_FAILURE); } mysql_set_character_set(db, "utf8"); mysql_select_db(db, "my_database"); mysql_query(db , "SELECT user_id AS id FROM users WHERE user_description='member' LIMIT 10000"); results = mysql_use_result(db); while(row = mysql_fetch_row(results)) { printf("%s\n", row[0]); } mysql_free_result(results); exit(EXIT_SUCCESS); } 

You can execute this code by associating your binary file with libmysql, passing the '-lmysql' parameter to GCC. All information on working with libmysql is contained in the documentation .

As you may have noticed, the PHP extensions mysql and mysqli repeat the API for ease of use. However, using libmysql can bring some problems:

• Licensing is quite complicated. If you want to create a closed commercial product, then you have to pay for the license .
• Updating libmysql entails updating MySQL server in the case of some distributions. And this circumstance is usually very few people happy.

mysqlnd. Starting with PHP 5.3, the code for libmysql was completely rewritten, and the mysqlnd extension, the native mysql extension driver, was born. This library has a PHP license, which is preferable to an Oracle license.

The library code that was not part of PHP (libmysql) was also revised, which made it possible to improve interaction between MySQL and PHP. It must be remembered that mysqlnd does not pass classes and functions to PHP (this is not quite true, but more on that below). However, it can serve as a solid foundation for other PHP extensions — mysql, mysqli, and pdo_mysql — when interacting with MySQL servers. Starting in PHP 5.4, mysqlnd becomes the default library.

Extensions

There are three extensions with which the PHP user can communicate with MySQL servers.

mysql. This is the very first and oldest extension, it has been leading its history since the late 90s. The extension passes the mysql_ function to PHP using the C API, which was taken by the MySQL developers from version 3.23. Today it is considered obsolete and is not recommended for use, and in recent versions of PHP it leads to the appearance of errors E_DEPRECATED. Please do not use it in your projects.

mysqli. “I” at the end means improved. This extension appeared in PHP 5.0 and was intended to replace the old ext / mysql API, so it is based on a later version of MySQL 4.1 and higher. Now it supports stored procedures, secure authentication protocol, prepared expressions (prepared statements) and much more. The extension also provides the PHP user with a procedural and object-oriented API. Due to the very high similarity, migration from ext / mysql to ext / mysqli is easy.

PDO. This extension is noticeably different from mysql / mysqli, since it was designed to support relational database management systems (RDBMS) in general, and not specifically MySQL. For this reason, the PDO is far from perfect and working with it expects a large number of assumptions from the user, which sometimes results in strange extension behavior. What is meant?

PDO interacts with a SQL parser that emulates prepared expressions if the RDBMS does not support them. The problem is that the behavior of this level is different from the behavior of the RDBMS. In the case of MySQL, the PDO emulation level is active by default when you prepare a query. But he will never reach the level of the prepared expression. In fact, the PDO code will parse and collect your request without interacting with MySQL on this topic. This is a strange behavior. Therefore, disable this emulation level as soon as possible:

 /*     PDO */ $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0); 

 /*   ,       0,   1 */ $pdo->setAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY, 0); 

If the emulation level is enabled, the PDO will take care of building the request and send the traditional request to the RDBMS. But, in view of the many flaws, this can cause strange behavior. Since PDO knows nothing about table columns, its emulation level will quote each parameter, even a numeric type, in quotes. This leads to similar errors:

 $stmt = $pdo->prepare("SELECT user_id FROM users LIMIT :limit"); $stmt->bindValue('limit', 10); $stmt->execute(); $result = $stmt->fetch(); var_dump($result); /* PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10'' */ 

As you can see, PDO quotes the limit parameter, although this was not necessary. Let's try to disable the emulation level, leaving only the RDBMS level (in this case, MySQL):

 $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0); /* Disable prepared statement emulation layer */ $stmt = $pdo->prepare("SELECT user_id FROM users LIMIT :limit"); /* A true prepare() will be sent to the RDBMS, it has to support it */ $stmt->bindValue('limit', 10); $stmt->execute(); $result = $stmt->fetch(); var_dump($result); /* array(4) { ["user_id"]=> string(7) "18" [0]=> string(7) "18" } */ 

Now everything is all right. If you still need a working level of emulation, then you need to specify the PDO that you do not need to touch the numeric parameters:

 /* Tells the PDO prepared statement emulation layer that this column is of type integer (SQL type) */ $stmt->bindValue('limit', 10, PDO::PARAM_INT); 

But this is not all. Despite the fact that we have explicitly disabled the emulation level of prepared expressions in PDO, it is still a bit active. He initiated the parsing of parameters. There are two kinds of parameter syntax: anonymous parameters, represented as “?” In your fill request, and named parameters, like “: myparam”. Not every RDBMS supports both syntaxes, and MySQL does not support named, only anonymous. However, our previous request completed without problems. The thing is, the PDO request analyzer is still active, even with the emulation level turned off. He intervened and replaced each named parameter with an anonymous one, because he had previously asked the RDBMS (in this case, MySQL) whether that named syntax supports it. Since MySQL does not support, then PDO and replaced all “: myparamname” with “?”.

In fairness, it should be noted that the API in PDO is well designed and can greatly facilitate the life of a PHP developer. But if you do not know what is happening at the lower levels, then you will have problems.

More details about the mysqli extension

Today, everyone uses PDO, because it greatly facilitates the transition from one RDBMS to another. However, if you are using MySQL and are not going to change it, then give up the PDO, otherwise you will lose a lot of time and effort. Look better at how functional the mysqli API is:



mysqli is constantly accused of not generating an exception, it gives errors instead. This is not true:

 <?php mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); try { $db = mysqli_connect('myserver', 'myuser', 'secret', 'unknown_database'); } catch (mysqli_sql_exception $e) { exit($e->getMessage()); } try { mysqli_query($db, "SELECT foo FROM bar"); /* ... */ } catch(mysqli_sql_exception $e) { } 

mysqli can even tell you that you forgot about the index:

 <?php mysqli_report(MYSQLI_REPORT_INDEX); $db = mysqli_connect('myserver', 'myuser', 'secret', 'my_database'); mysqli_query($db, "SELECT photo FROM Users WHERE source ='web' LIMIT 1000"); /* PHP Warning: mysqli_query(): (00000/0): No index used in query/prepared statement ... */ 

MySQL provides client communication (in our case, PHP) with many things. For more information, read the documentation .

In mysqli, the character encoding change function is supported: mysqli_set_charset() . You should never use the "SET NAMES" query. You can read more about this on php.net or dev.mysql.com .

Now let's talk about buffered queries. When you request results in MySQL, that is, when you usually use SELECT queries, a set of data containing the results will be generated. The idea behind the buffered result sets is to organize where they are stored: in client memory (a buffered query) or leave MySQL (an unbuffered query) on the side. That's all.

Notice that we are talking about direct queries, not about prepared expressions. By default, every direct query from mysqli to MySQL is buffered. This means that by the time you make a call to mysqli_query() , the entire result set will already be transferred to memory on the PHP side and released on the MySQL side. You can count it by mysqli_num_rows() , you can search for mysqli_data_seek() , and you can also make another request until the set is released. For example:

 $mysqli = mysqli_connect(/*...*/); /*       : PHP */ $result = mysqli_query($mysqli, "SELECT id, nom, email FROM members"); $line1 = mysqli_fetch_row($result); mysqli_data_seek($result, mysqli_num_rows($result)); /*     */ $last_result = mysqli_fetch_row($result); /*    */ /*     ,   ,       :*/ mysqli_free_result($result); 

This is a classic case of "default." Do not forget that the entire set is immediately transferred from MySQL to PHP, so if it can be large, then the amount of memory for PHP will increase proportionally. However, you will not be able to see this volume using memory_get_usage() , and it will not be taken into account in memory_limit until you use mysqlnd as a low-level connector.

If you want to make the same query using an unbuffered result set, use the MYSQLI_USE_RESULT flag. But be careful, because the result will be stored in the memory of the MySQL process for your connection, and MySQL can only store one set per connection. Therefore, you will not be able to make another direct request on this connection until you release the set. In addition, it will not be possible to search or count the amount of data:

 $mysqli = mysqli_connect(/*...*/); /*        MySQL */ $result = mysqli_query($mysqli, "SELECT id, email FROM members", MYSQLI_USE_RESULT); $line1 = mysqli_fetch_row($result); /*     */ /*    ,    - “ ” */ mysqli_data_seek($result, mysqli_num_rows($result)); /*     ,          ,     */ $result2 = mysqli_query($mysqli, "SELECT name FROM membres", MYSQLI_USE_RESULT); 

You can free up data using mysqli_free_result() . By default, any direct query is done in buffered mode, because the MySQL server has something to do instead of allocating memory to store each set.

Now let's talk about prepared expressions. They are very different from traditional direct requests:

• Prepared expressions do not use the same protocol as direct queries. Instead, a binary protocol is used that is highly optimized and provides many features, such as data type binding.
• Result sets for prepared expressions are not buffered by default, unlike direct queries.

Let's start by unloading the protocol for a direct request:

 $m = mysqli_connect(/* params */); $q = mysqli_query($m, 'SELECT * FROM Users LIMIT 1'); while($r = mysqli_fetch_row($q)) { /* do something */ } mysqli_free_result($r); 

As you can see, this is a text protocol, that is, MySQL sends only text in response. In the request, you indicated a column of numbers, and in response received the text. First, it means that in MySQL you will have to take additional steps to convert the data into text. And secondly, on the PHP side, you only get the string data.

The same query in the form of a prepared expression:

 $m = mysqli_connect(/* params */); $ps = mysqli_prepare($m, 'SELECT * FROM Users LIMIT 1'); mysqli_stmt_execute($ps); while(mysqli_stmt_fetch($ps)) { /* */ } mysqli_stmt_close($ps); 

Each bind and each sample switches MySQL to receive or transmit data over the network. We do not see this in the screenshot, but the protocol was binary. This means that the data in each column is sent as is: numeric as numeric, not string. Also, the binary protocol allows to save traffic, since the transmission, for example, TINYINT 200 will pull by 1 byte, and in text form - 4 bytes. There are also other types of network replacements.

 $m = mysqli_connect(/* params */); $ps = mysqli_prepare($m, 'SELECT id FROM Users LIMIT 10'); /* id INTEGER */ mysqli_stmt_execute($ps); mysqli_stmt_bind_result($ps, $id); /*     $id */ while(mysqli_stmt_fetch($ps)) { var_dump($id); } /* int(62) int(64) */ 

From the above example, you can see that PHP receives numeric data, not string.

However, you can save data types using text protocol. To do this, the client (PHP) must convert the received string data to the correct, expected type. And only mysqlnd can do this, libmysql will not help:

 $m = mysqli_connect(/* */); $q = mysqli_query($m, 'SELECT id FROM users LIMIT 10'; while($r = mysqli_fetch_row($q)) { var_dump($r[0]); } /* string(2) "62" string(2) "64" */ $m = mysqli_connect(/* */); mysqli_options($m, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true); /*     mysqlnd */ $q = mysqli_query($m, 'SELECT id FROM users LIMIT 10'); while($r = mysqli_fetch_row($q)) { var_dump($r[0]); } /* int(62) int(64) */ 

The result sets for prepared expressions are not buffered by default, and each fetch() operation will start a network data exchange. However, you can buffer sets with mysqli_stmt_store_result() .

 $m = mysqli_connect(/* */); $ps = mysqli_prepare($m, 'SELECT id, name FROM Users LIMIT 1000'); mysqli_stmt_execute($ps); mysqli_stmt_bind_result($ps, $id, $name); /*      */ mysqli_stmt_store_result($ps); while(mysqli_stmt_fetch($ps)) { /* do something with $id and $name */ } mysqli_stmt_close($ps); 

We can buffer sets, but in the case of prepared expressions, you must bind each column with the results to a PHP variable in order to be able to read the data. And yet, if you use mysqlnd, then mysqli_stmt_get_result() is available to you, which turns the result set for the prepared expression into mysqli_result. This allows you to use the API for direct requests, but with prepared expressions:

 $m = mysqli_connect(/* params */); $ps = mysqli_prepare($m, 'SELECT id, name FROM Users LIMIT 1000'); mysqli_stmt_execute($ps); /*    mysqli_result */ $r = mysqli_stmt_get_result($ps); /*    mysqlnd */ while($result = mysqli_fetch_row($r)) { /* API   */ /*  - */ } mysqli_free_result($r); mysqli_stmt_close($ps); 

Learn more about the mysqlnd extension.

We have already figured out that mysqlnd works as a hidden extension that adds many new features to existing APIs, especially mysqli (this is also true for PDO, only to a lesser extent).

Memory saving

Recall some points:

• A buffered query retrieves all results from MySQL to PHP memory.
• A buffered result set is created by the library, which is used for communication in both mysqlnd and libmysql.
• The result set is not used directly in the PHP environment, it must first be converted into an array in a PHP structure, this operation is called fetch.

In the example below, a huge part of the memory is lost:

 $db = mysqli_connect(/* */); $result = mysqli_query($db, "SELECT very_huge_blob_column, lots, of, columns FROM foobar"); /*      */ while($results[] = mysqli_fetch_row($result)) { } mysqli_free_result($result); /*    ,      */ foreach($results as $foo) { /*  - */ } 

Let us prove this statement:

 function memory_usage() { $pid = getmypid(); $r = explode(':',shell_exec("grep VmData /proc/$pid/status")); return '->'.trim($r[1])."\n"; } $db = mysqli_connect(/* */); echo "initial memory " . memory_usage(); $result = mysqli_query($db,"SELECT very_huge_blob_column FROM foobar"); echo "resultSet stored " . memory_usage(); while($results[] = mysqli_fetch_row($result)) { } echo "query result saved " . memory_usage(); mysqli_free_result($result); echo "resultSet freed " . memory_usage(); unset($results); echo "saved result freed " . memory_usage(); unset($db); echo "Db closed " . memory_usage(); 

In libmysql:

 > phplibmysql/bin/php poc_mysqli.php initial memory ->3348 kB resultSet stored ->72724 kB query result saved ->149012 kB resultSet freed ->81156 kB saved result freed ->25348 kB Db closed ->24260 kB 

As you can see, as soon as mysqli_query() is mysqli_query() , all result sets are sent to PHP memory. Its volume increases from 3 to 70 MB. This is normal for direct buffered queries. It is important to understand that the memory buffer was allocated by the library libmysql. And when you need to convert the results into a suitable form for PHP, all the data from the result set will be duplicated into memory, which leads to its swelling.

If the buffer is allocated to libmysql, then it will not be displayed in memory_get_usage() , and you will have to monitor your processes in order to notice it in time.

Converting all the data from the set into a PHP variable just explodes your memory. At this stage, the buffer is still allocated and the data is completely duplicated into the PHP array, so 140 MB of memory is already occupied. Let's verify this by running a memory analyzer:

 99.92% (257,473,815B) (heap allocation functions) malloc/new/new[], --alloc-fns, etc. ->52.90% (136,314,880B) 0x69A01E: _zend_mm_alloc_int (zend_alloc.c:1908) | ->52.60% (135,528,448B) 0x69A1DE: _estrndup (zend_alloc.c:2503) | | ->52.60% (135,528,448B) 0x533BCE: php_mysqli_fetch_into_hash (mysqli.c:1191) | | ->52.60% (135,528,448B) 0x53F2E1: zif_mysqli_fetch_row (mysqli_nonapi.c:352) | | ->52.60% (135,528,448B) 0x70186A: zend_do_fcall_common_helper_SPEC (zend_vm_execute.h:320) | | ->52.60% (135,528,448B) 0x6D9D96: execute (zend_vm_execute.h:107) | | ->52.60% (135,528,448B) 0x6B4B98: zend_execute_scripts (zend.c:1236) | | ->52.60% (135,528,448B) 0x663D0C: php_execute_script (main.c:2308) | | ->52.60% (135,528,448B) 0x73BCDC: main (php_cli.c:1184) | | | ->00.31% (786,432B) in 1+ places, all below ms_print's threshold (01.00%) | ->45.85% (118,130,675B) 0x52DD010: my_malloc (my_malloc.c:37) | ->45.84% (118,112,344B) 0x52E0583: alloc_root (my_alloc.c:219) | | ->45.83% (118,096,024B) 0x5307A40: cli_read_rows (client.c:1418) | | | ->45.83% (118,096,024B) 0x5305955: mysql_store_result (client.c:2957) | | | ->45.83% (118,096,024B) 0x53EF09: zif_mysqli_query (mysqli_nonapi.c:540) | | | ->45.83% (118,096,024B) 0x70186A: zend_do_fcall_common_helper_SPEC (zend_vm_execute.h:320) | | | ->45.83% (118,096,024B) 0x6D9D96: execute (zend_vm_execute.h:107) | | | ->45.83% (118,096,024B) 0x6B4B98: zend_execute_scripts (zend.c:1236) | | | ->45.83% (118,096,024B) 0x663D0C: php_execute_script (main.c:2308) | | | ->45.83% (118,096,024B) 0x73BCDC: main (php_cli.c:1184) 

To release the result set, you need to call mysqli_free_result() . After that, the occupied memory will be reduced to 70 MB. And when we finally release the PHP array containing the copy of the set, the memory will return to its previous size.

Duplication can be prevented using mysqlnd.

 > phpmysqlnd/bin/php poc_mysqli.php initial memory ->3208 kB resultSet stored ->70452 kB query result saved ->71220 kB resultSet freed ->81148 kB saved result freed ->19196 kB Db closed ->19196 kB 

When a buffered set is converted to a PHP array, the amount of memory does not change. Only when you start writing to this array, that is, modifying it, will PHP double the amount of memory for the result, on a case-by-case basis. Also, mysqlnd uses a memory allocator to store the result set in its own buffer, memory is shared with PHP, memory_get_usage() will show this memory, and you can even get access to memory_limit .

Another approach prevents any “sampling of everything” operation. In PDO, $stmt->fetchAll() transforms all result sets into a PHP variable. This allows more economical use of memory for searching in a set and obtaining actual data with the subsequent conversion of any string into a section of a PHP array.

Statistics

mysqlnd sees everything - every byte passing between PHP and the MySQL server, and can collect very useful statistics. You can easily get answers to many important questions without using monitoring plugins:

• How many MySQL connections are active?
• How many mistakes were there in the mix?
• How many requests were prepared but not executed? ( )
• , ? ( )
• , ?
• MySQL-?
• ?

mysqlnd :

 $db = mysqli_connect(/* */); $result = mysqli_query($db,"SELECT user_id, email FROM users LIMIT 5"); mysqli_data_seek($result, 5); $data = mysqli_fetch_row($result); do_something($data); mysqli_free_result($result); var_dump(mysqli_get_connection_stats($db)); /* only available under mysqlnd */ /* ["buffered_sets"]=> string(1) "1" ["rows_fetched_from_server_normal"]=> string(1) "5" ["rows_buffered_from_client_normal"]=> string(1) "5" ["rows_fetched_from_client_normal_buffered"]=> string(1) "1" ["connect_success"]=> string(1) "1" ["connect_failure"]=> string(1) "0" ["connection_reused"]=> string(1) "0" ["reconnect"]=> string(1) "0" ["active_connections"]=> string(1) "1" */ 

5 , , , , . 5 ? , , rows_fetched_from_client_normal_buffered , . , MySQL PHP-.

mysqli-, :

 class JPMysqli extends Mysqli { public function __destruct() { $stats = $this->get_connection_stats(); $this->close(); if($diff = $stats["rows_fetched_from_server_normal"] - ($stats["rows_fetched_from_client_normal_unbuffered"] + $stats["rows_fetched_from_client_normal_buffered"])) { trigger_error("   *$diff*   ", E_USER_NOTICE); } } } $db = new JPMysqli(/* */); $result = mysqli_query($db,"SELECT user_id, email FROM users LIMIT 5"); mysqli_data_seek($result, 5); $data = mysqli_fetch_row($result); do_something($data); exit(); /* Notice : "    *4*    " */ 

, . Symfony2, mysqlnd-bundle mysqlnd-analytics .

Plugins

mysqlnd . . :

• mysqlnd_qc. .
• mysqlnd_ms. Master-Slave , . .
• myslnd_uh. . PHP ( ). mysqlnd- , SQL-, .

Conclusion

, , PHP MySQL-. , mysqlnd , - .

Ulf Wendel, Andrey Hristov, Georg Richter Johannes Schlüter – mysqlnd.