MySQL persistent connection in Magento
Thursday, 03 November 2011We thought it would be awsome: MySQL/PHP offers the ability to open up MySQL persistent connections (mysql_pconnect) and to use this with Magento would give a tremenduous performance boost. Here's the result.
MySQL persistent connections
Whenever PHP needs to connect to the MySQL database, a MySQL connection needs to be opened up - per request. If you have a lot of different requests (because of large amounts of visitors) then this could require tuning of MySQL parameters. In some cases, the actual creation of those connections already creates such an overhead, that MySQL persistent connections might be a good option.
With MySQL persistent connections, connections are not dropped at the end of the request, but they remain open. When a new PHP-call for MySQL is made (by a new request), the old connection is reused as long the MySQL login-credentials (host, username, password) are the same. So with a Magento shop on a dedicated server, this might lead to a single MySQL connection used for 100s of requests per minute - something that might give a very good boost to the MySQL performance. But the number of available connections might easily run out on a shared hosting server.
Magento configuration
The Magento documentation lists nothing on the usage of MySQL persistent connections, so to see how to accomplish this, we reviewed in the Magento core-classes. In the end, it was fairly easy: There is only a slight modification in the app/etc/local.xml needed. Within the connection chapter of your database, just add a persistent-tag:
<connection>
<host><![CDATA[localhost]]></host>
<username><![CDATA[root]]></username>
<password><![CDATA[root]]></password>
<dbname><![CDATA[magento]]></dbname>
<active>1</active>
<persistent>1</persistent>
</connection>
Done.
The result
Well, the result was actually that we saw no change. We ran some benchmarks (which are so not-serious that we did not bother to post them here) and found almost no difference between a webserver with and without persistent connections. However, our benchmarks were actually on a webserver that had nothing to do. We might see in the future whether stressing the webserver (with a tool like Siege) might show a difference. Or it is up to you to try yourself.
Note that MySQL persistent connections are pointless if you are using PHP through some kind of CGI-wrapper: In that case, different calls to PHP do not share the same resources, so a MySQL connection can not be reused either. In other words, MySQL persistent connections only offer a benefit when PHP is used as module (for instance, Apache mod_php).