HOW TO: Install mysqlnd (MySQL Native Driver) for PHP 5.3 and WHY you should care

[IT_Architect]

How To:
The HOW TO is the easy part.
http://help.directadmin.com/item.php?id=252
These are the lines that change:
These are the lines you need to change.
--with-mysqli=mysqlnd \
--with-pdo-mysql=mysqlnd \

WHY you should care:
The WHY is a little more complicated. PHP has been going through a metamorphosis from a simple scripting language to something that has more in common with programming language. PHP and MySQL have been kinda joined at the hip in the ?NIX world, and it also has evolved from Mickey Mouse to real. The underlying driver in the past has been libmysql. However, libmysql has a license problem, and it also runs outside of the Zend Engine, which means PHP has less control. mysqlnd is where the development has been and where the new features of the language and database are supported. Thus, the default driver for 5.3 and 5.4 is mysqlnd. Until custombuild 2.0 is rolled out, there is a single config file per major release, which is why a standard DA install of 5.3 uses libmysql instead of mysqlnd. Dropping in huge changes between full numbered releases is a new phenomenon. It also seems to have caught on at Apache with their new 2.4. This spawned new requirements that custombuild 2.0 addresses.

The three common libraries to access MySQL data from PHP are mysql, mysqli, and pdo-mysql. New capabilities have only been added to mysqli and pdo-mysql. Thus, you need to use one of these two in order to benefit from the new language features, but they are only supported by the mysqlnd driver.

PHP 5.3 brings together the pieces that developers have longed for, and that is a way to do MYSQLI_ASYNC queries. E.G. your script can be in control while you are waiting for a query. With 5.2 and below, let's say you were to put a 1 second timeout in your PHP code so that if it doesn't complete in 1 second, exit rather than hang the web page. The problem was time stopped when the query started. So if it takes a 1/10th of a second to get to the query, and there is a 1/10th of a second of code that executes afterward, it doesn't matter if the query takes 10 minutes, the script only sees it as 1/5th of a second has elapsed. CURL is not the answer. It is far more resource intensive, and it abandons running queries that are being worked on that nobody wants anymore. With 5.3 and newer, and with an asynchronous query, the script remains alive, and you can cancel the query. With 5.3 you can also do persistent connections WITH mysqli. Those are just the advantages that come to mind. For the code that uses the old mysql library, and there is a lot of it, it doesn't help at all. However keep in mind that the people driving the changes are not the PHP authors, they are the people who write the scripts for Drupal, Wordpress, Installatron, etc. They need the changes to be able to write secure, maintainable code.

Edit: I've been asked by a couple people if it makes sense to switch to the the native driver with 5.2. I had to take some time to think about that since the main reason I wanted this driver was for the async query ability, which is only available in 5.3+. mysqlnd wasn't the default driver for 5.2, probably because it was pretty new back then. For 5.2, the biggest two advantages I can see are it uses a lot less RAM, and supports persistent connections for mysqli. Others are you don't need to link in libmysql, it uses PHP memory management, supports PHP memory limit, keeps every row only once in memory instead of twice, more performance stats, a couple more my.ini tweaks. It's unlikely to do anything one way or the other for performance. You lose the ability to support connections to MySQL servers via named pipes on Win32 systems. You would also need to set up and maintain a custom config for php instead of having DA maintain it. IMHO, for servers without 5.3, there aren't enough benefits to warrant making the change.

That's the HOW TO and the WHY you should care!

 

[Xandrios]

Thanks for sharing!

 

[Arieh]

Thanks indeed. I think I will be using this. I'm reading the new config variables here http://php.net/manual/en/mysqlnd.config.php

And one in particular is a bit confusing:

mysqlnd.net_read_timeout integer

mysqlnd and the MySQL Client Library, libmysql use different networking APIs. mysqlnd uses PHP streams, whereas libmysql uses its own wrapper around the operating level network calls. PHP, by default, sets a read timeout of 60s for streams. This is set via php.ini, default_socket_timeout. This default applies to all streams that set no other timeout value. mysqlnd does not set any other value and therefore connections of long running queries can be disconnected after default_socket_timeout seconds resulting in an error message "2006 - MySQL Server has gone away". The MySQL Client Library sets a default timeout of 365 * 24 * 3600 seconds (1 year) and waits for other timeouts to occur, such as TCP/IP timeouts. mysqlnd now uses the same very long timeout. The value is configurable through a new php.ini setting: mysqlnd.net_read_timeout. mysqlnd.net_read_timeout gets used by any extension (ext/mysql, ext/mysqli, PDO_MySQL) that uses mysqlnd. mysqlnd tells PHP Streams to use mysqlnd.net_read_timeout. Please note that there may be subtle differences between MYSQL_OPT_READ_TIMEOUT from the MySQL Client Library and PHP Streams, for example MYSQL_OPT_READ_TIMEOUT is documented to work only for TCP/IP connections and, prior to MySQL 5.1.2, only for Windows. PHP streams may not have this limitation. Please check the streams documentation, if in doubt.

First it says that it will be disconnected because of the default of 60 at default_socket_timeout, but then it says that mysqlnd now uses the same very long timeout.

So I wonder if default_socket_timeout needs to be changed from 60 to -1, so the default of mysqlnd gets used? Or perphaps default_socket_timeout should be left to 60 for other socket users, and set mysqlnd.net_read_timeout to that high value?

 

[IT_Architect]

How I decipher that is:
1. Both drivers have the same timeout, 1 year. For all intents and purposes they are both relying on something else to limit them. You can change the timeout for mysqlnd by using mysqlnd.net_read_timeout. Any change to mysqlnd also affects any driver that implements it, such as PDO etc.

2. Since libmysql doesn't use streams, the DEFAULT_SOCKET_TIMEOUT is not effective, while mysqlnd does use streams, and thus the parameter becomes effective. Unless you have another timeout set to 60 seconds or less, it probably will have an effect.

3. With libmysql MYSQL_OPT_READ_TIMEOUT, is only DOCUMENTED to work for TCP/IP while with mysqlnd it works no matter what type of connection you have. To me this means, even if it works with libmysql for other types of connections, it is not guaranteed that it always will. (Kinda like using M$ undocumented functions)

Other:
The key point is libmysql is a man in the middle between PHP and MySQL, while mysqlnd plugs into PHP. It's why with libmysql some of the settings in the php.ini were an "exercise", and with mysqlnd they are effective.

PS: If you are thinking about working with an async query, beware of their example in mysqli_poll(). Note where $processed++ occurs, and what happens when reap fails. LOL! I did submit a documentation bug report.

 

[Arieh]

The loop won't end? Is it meant to only $processed++; when it succeeds? So its supposed to keep looping until $result gets filled with a new result? So having an empty $result is normal, but when $result never gets filled it won't know?

I do wonder how these new functions benefit. With an average DA server with mysql and a wordpress site, how would that (in the future) benefit from this? For displaying a page where you got posts, a calendar and comments, it could query those 3 at the same time instead of 1 at the time? Or how should I see this.

 

[IT_Architect]

The loop won't end?

Exactly!
It's supposed to read all results, but if it fails on any entry, as would happen with bad query data, it wouldn't increment and thus would be forever behind by 1.

I do wonder how these new functions benefit. With an average DA server with mysql and a wordpress site, how would that (in the future) benefit from this?

It won't, but for a busy server with links to other servers, it's a life saver. Let's say you are doing a conditional links on a page based on information in their databases. With libmysql, the script runs until it executes a query and stops until the results come back, and takes off again after you get the results. If anything slows down on the computer with the database, it translates to a delay releasing the web process to serve another connection. Another visitor connects, you spawn another Apache process because the others are now tied up. The server load goes up and the memory down because you've added more processes, which in turn requires more processes. User's browsers abandon the connection and start another connection. Once the chain reaction starts, it takes approximately a minute to take a server from normal to a resource catastrophe.

There are plenty of busy WordPress sites out there, and Drupal is growing rapidly at everyone else's expense. They want the new PHP not only for the busy sites, but also for maintainability. Once you hit 5.4, the code and data types need to be right or it won't run. Those are things developers want to hear because if the language doesn't trap for it, they need to, or they end up with intermittent errors that are very difficult to track down. These are the drivers for change. My monitoring servers run Zabbix. I have 5.4 on one of those, and soon the other one. I wouldn't have done it voluntarily, but ports kept pushing me that way, so I set a snapshot let it go. I was shocked to death that Zabbix actually ran when I finished. LOL!

 

[Arieh]

I decided to give it a try. I copied, combined, and modified some scripts I encountered and used it on a wordpress database I happened to have.

It now uses 3 connections, fetches all the rows instead of one, and that error we early talked about isn't a problem, simply put an else under the if and count it as done. This else { block can also be used for e.g. insert queries to fetch the inserted_id.

<?php

$dbc['host'] =       '127.0.0.1';
$dbc['username'] =   'testuser_db';
$dbc['dbname'] =     'testuser_db';
$dbc['password'] =   'pass';

$shard1 = mysqli_connect($dbc['host'], $dbc['username'], $dbc['password'], $dbc['dbname']);
$shard2 = mysqli_connect($dbc['host'], $dbc['username'], $dbc['password'], $dbc['dbname']);
$shard3 = mysqli_connect($dbc['host'], $dbc['username'], $dbc['password'], $dbc['dbname']);

$shard1->query('SELECT * FROM wp_posts', MYSQLI_ASYNC);
$shard2->query('SELECT * FROM wp_link', MYSQLI_ASYNC); // should be wp_links - but we let it fail
$shard3->query('SELECT * FROM wp_terms', MYSQLI_ASYNC);

print '<pre>';
$all_links = array($shard1, $shard2, $shard3);
$processed = 0;
do {
    $links = $errors = $reject = array();
    foreach ($all_links as $link) {
        $links[] = $errors[] = $reject[] = $link;
    }
    if (!mysqli_poll($links, $errors, $reject, 1)) {
        continue;
    }
    foreach ($links as $link) {
        if ($result = $link->reap_async_query()) {
            $processed++;
            while ($row = $result->fetch_assoc()) {
                $results[] = $row;
            }
            print_r($results);
            mysqli_free_result($result);
        }
        else {
            $processed++;
            print "fail: ".$link->error."\n";
        }
    }
} while ($processed < count($all_links));
print '</pre>';

?>

Nice to have played with it, maybe one day I will use it.

 

[IT_Architect]

Nice to have played with it, maybe one day I will use it.

I see your cure. Nicely done! If you only want one row, add LIMIT 1 on the end of your query. I do that quite frequently because it stops it from looking for anymore rows. Example: We have a company that advertises with us that does hotel bookings. If he has hotels in that city, he wants his link to show. He may have 1 or 200 in that city, but it doesn't matter how many. We make up a link with the embedded query string, and when someone clicks it, they see all of the hotels for that city.

Other languages and databases have had type checking and the ability to limit query time from their inception. You can tell those who have used those languages because their ears turn red when they have to work with PHP and MySQL. In other languages you don't have to code this yourself, but at least we can now, and this method actually has a few advantages.

The reason for the recent rapid progress is there is now such an investment by so many people in PHP and MySQL, that it's cheaper to feed their developers than to start over. Thus, we've gotten some rudimentary Object Orientation capabilities, type and results checking, MySQL's InnoDB, and this capability to prevent slow queries from slaying our apps and servers. App developers aren't even trying for elegant or state of the art. They simply want usable, and maintainable. Not hanging apps and servers, type & results checking, and OOP are right up there. However, along with the changes comes fear of what it will do to the code already written, and a lot of work.

 

[smtalk]

http://www.directadmin.com/forum/showthread.php?t=43263&p=227942#post227942

mysqlnd is added to CB 2.0. This how-to won't work on CentOS/RHEL/Fedora systems because of one configuration flag missing (which defines the location on mysql socket file) and it won't have any effect if MySQL extension (not MySQLi) is used, to fix that, use the following additional flags:

	--with-mysql=mysqlnd \
	--with-mysql-sock=/var/lib/mysql/mysql.sock \

Thank you for the request (http://www.directadmin.com/forum/showthread.php?t=43642).

 

[Suurbier]

I changed from libmysql to mysqlnd, works like a charm! The only question I have is, is there also a --with-pdo-mysqli parameter or only --with-pdo-mysql?

 

[Arieh]

There is not. PDO is an alternative to MySQLi.

http://www.php.net/manual/en/mysqli.overview.php#mysqli.overview.mysqlnd

From a programmers point of view, MySQL recommends using MySQLi, while PDO could be beneficial if a developer would decide to stop using MySQL and go to another database as PDO isn't only for MySQL.

It's called --with-pdo-mysql because that way you'll get MySQL support for PDO. For instance to enable PostgreSQL it is --with-pdo-pgsql (but you would also need to have PostgreSQL installed of course).

 

[Suurbier]

Ok, thanks for your reply.

This is what I did for everybody else that wants to use mysqlnd in php >=5.3 in custombuild 1

cd /usr/local/directadmin/custombuild
mkdir -p custom/ap2
cp -fp configure/ap2/configure.php5 custom/ap2/configure.php5

Make changes to custom/ap2/configure.php5
Add the following lines

--with-mysqli=mysqlnd \
--with-pdo-mysql=mysqlnd \
--with-mysql=mysqlnd \
--with-mysql-sock=/var/lib/mysql/mysql.sock \

Then rebuild PHP via custombuild

./build php n

 

[ixlhosting]

When i compiled mysqlnd this way, PHPMYADMIN is not working anymore, i get #2002 error!
Anyone familiar with this?