This is the first post of an series on the MySQL native driver or mysqlnd. In particualr I hope to cover the creation of a mysqlnd plugin both as a C extension to PHP and also as PHP code using mysqlnd_uh.
What is mysqlnd?
Quite simply mysqlnd is a PHP extension that handles talking to MySQL databases. Previously this was done via a C library from MySQL called libmysql. This library was used by many different languages to talk to a MySQL server and it worked well. However, with MySQL and PHP being so popular the MySQL team looked at improving the performance of the combination as well as being more compatible with the PHP license. In particular in the traditional setup every result set was being copied twice once within libmysql and once in the PHP mysql extensions. Mysqlnd is a PHP extension that exports no new PHP functions it has the sole job of talking to mysql servers. If installed it will be used by all the other mysql libraries such as mysqli etc as the client. It has the added benefit of not needing to be built against any one version of libmysql making it more portable.
Where can I get my mysqlnd?
You may already be using it! Mysqlnd has been in the PHP code base since PHP 5.3. It is the default mysql client on PHP windows binaries.
to find out do
php -m
to list all installed modules
[PHP Modules] apc bcmath bz2 .... mysqlnd ...
or look at the output of phpinfo(); in a browser.
If you don’t have it then I’m afraid the only way is to recompile PHP with the correct flags. These are
--with-mysql=mysqlnd \ --with-mysqli=mysqlnd \ --with-pdo-mysql=mysqlnd \
Distro packagers have been reluctant to switch from libmysql as stability is of prime importance to them. If you want to build php on ubuntu to include mysqlnd see this post.
What now, I can’t see anything?
As mentioned before mysqlnd adds no new PHP functions. There is however, one PHP function that will only work if you are using this newer client. mysqli_result::fetch_all gets all the results from a results set as an array which is assoivative, numeric or both. If you try using this function with libmysql you will get an error.
<?php
$mysqli = new mysqli("localhost", "nat", "supersecretpassword", "mysql");
$query = "SELECT * from db";
$result = $mysqli->query($query);
$rows = $result->fetch_all();
var_dump($rows);
Fatal error: Call to undefined method mysqli_result::fetch_all() in /home/nat/test.php on line 8
The function is useful in some cases but putting large result sets into memory is generally not the best idea as it will consume large amounts of memory (not true see Ulf’s comments) and there are already functions to seek, iterate and find the size of results sets.
10 June 2011 at 9:33 am
Thanks for the post!
“The function is useful in some cases but putting large result sets into memory is generally not the best idea as it will consume large amounts of memory and there are already functions to seek, iterate and find the size of results sets.”
Every medal has two sides.
If using buffered result sets, as shown in your example, mysqlnd has to fetch all results into the clients memory. Always. It happens inside the query() call. if using query(), buffering happens regardless of which fetch function you choose: fetch_all(), fetch_assoc() in a loop.
fetch_all() is just a convenience function to transfer the already fetched result set into a PHP hash in just one step. You save a few PHP user space function calls that’s about it.
If using unbuffered results (real_query()), the result set is not fetched into client memory as part of real_query(). It remains on the server until you start fetching. This may be desired for very large result sets and clients operating under memory constrains. The downside of unbuffered result sets is that they occupy server resources. And, usually, the server is the hardest part to scale.
Ulf
10 June 2011 at 9:53 am
Thanks for clearing that up Ulf excellent information keep up the good work.
Pingback: Ubuntu: MariaDB и PHP 5.3 – Unix4Me