A somehow controversial subject in the database world is MySQL persistent connections: Some claim they have a performance benefit, others claim they’re bad and they can cause some memory issues on the server. At itoctopus, we have decided to put an end to the controversy by running experiments on a large Joomla website in order to prove either theory.
By default, Joomla sites use non-persistent connections to connect to the MySQL database. A non-persistent MySQL is automatically destroyed by PHP when the script is finished (that’s why there is no reason to explicitly close it). A persistent MySQL connection, on the other hand, is not destroyed at script end and is reused. This should mean that persistent MySQL connections should provide a performance boost to the Joomla website, as they eliminate the overhead of creating a new connection on each page execution (and destroying it when the page has finished loading). Right? Maybe, but let us first explain how to use MySQL persistent connections on a Joomla site before giving a definitive answer to the question.
How to Use MySQL Persistent Connections on a Joomla Site
In theory, switching from a non-persistent to a persistent MySQL connection is simple, all that one needs to do is to prefix the value of the host with a “p:” (without the quotes). So, on a standard Joomla website (where the database server co-exists with the web server), the host should be something like “p:localhost” instead of “localhost”.
However, things are always not that simple, as we saw the following error when we changed the value of $host in the main configuration.php file from “localhost” to “p:localhost”:
0 – Could not connect to MySQL.
This is because the function connect() which is located in the mysqli.php file (which, in turn, is located under the libraries/joomla/database/driver folder) essentially removes all the characters that are deemed “unnecessary” from the host, including the colon (“:”) through regular expressions. In fact, the colon is only useful when you want to explicitly specify the port that the MySQL database server listens to. So, if MySQL listens on the 7777 port, for example, then your host should be “localhost:7777”. Providing something like “p:localhost” will mean that the host is “p”, and the port is “localhost” (which, of course, is wrong).
We tried our best to “trick” the regular expressions into accepting our “p:localhost” host, but we failed. We then tried to override the mysqli_connect function, but we couldn’t, because overriding it meant using the override_function, which belongs to an obsolete PECL library that is no longer maintained (and possibly insecure). Overriding it using namespaces also wasn’t possible, since the Joomla application doesn’t have a global namespace that all the PHP files belong to.
So, the only viable method that we found to have a persistent database connection was to modify the core by changing the following line in the connect() function (which is located in the aforementioned mysqli.php file):
$this->connection = @mysqli_connect( $this->options['host'], $this->options['user'], $this->options['password'], null, $this->options['port'], $this->options['socket'] );
to:
$this->connection = @mysqli_connect( 'p:localhost', $this->options['user'], $this->options['password'], null, $this->options['port'], $this->options['socket'] );
As you can see in the above, we hardcoded the host in the mysqli_connect function. We then loaded the website, and this time it worked!
So, does a persistent connection offer any performance advantage on Joomla sites?
Well, we tested the performance of a huge Joomla website (after switching its connection to persistent) on a staging server, and the numbers were more or less the same when using non-persistent connections. So, in short, and from our tests, persistent connections do not offer any performance advantage over non-persistent connections. So, there is no point in modifying the Joomla core to switch to a persistent connection.
We hope that you found this post useful. If you have any questions about it, or if you need help with optimizing your Joomla website, then please contact us. Our fees are affordable, our quality is top notch, and our fees are super affordable!