On Closing MySQL Connections on Joomla Sites

If you’re an avid reader of our blog, then you may already know that we have a passion for optimizing Joomla sites. A passion so strong, that we spend the absolute majority of our spare time researching new optimization techniques. More often than not, our research yields interesting results.

For example, about a week ago, we were studying the queries generated by an already optimized Joomla website to see how we can further optimize the website. We were doing that with the help of MySQL’s general_log server system variable (in the my.cnf file, which is located under the /etc folder), which, when set to “on”, will log all the generated queries to the file defined in the general_log_file server system variable. We did that on a dev server by adding the following lines to the beginning of the my.cnf file:

general_log = on

The above lines logged every query issued on the MySQL server to the all-queries.log file. After doing the above, we loaded the website, and all the queries issued by the website were logged to the aforementioned file. After a few tests, we noticed something interesting… At the end of each page load, we saw the following line:

74008 Statistics

We did a little research and we discovered that the above line is generated by the stat() function on the mysqli static class. A quick search into the Joomla code revealed the location of the stat() method call. It was in the disconnnect method which is located in the mysqli.php file which, in turn, is located under the libraries/joomla/database/driver folder. It was in the following line:

if ($this->connection instanceof mysqli && $this->connection->stat() !== false)

Obviously, it was the $this->connection->stat() code in the above line that caused that Statistics line to appear in the general log.

Of course, you have 2 questions right now: What does the stat() method do and why should anyone care about the Statistics line?

To answer the first question, the stat() method on the mysqli static class is equivalent to the mysqli_stat function (which takes the connection link as parameters), which returns the output from the following shell command:

mysqladmin status

The output of the above command is something like the following:

Uptime: 2208760 Threads: 1 Questions: 157907223 Slow queries: 557 Opens: 251845 Flush tables: 1 Open tables: 1024 Queries per second avg: 71.491

In short, the output consists of the uptime (in seconds), the number of threads, the number of commands issued on the server (including queries), the number of slow queries, the number of tables that have been opened, the number of flushed tables, the number of tables currently open, and finally the average number of queries per second. All of that is unnecessary information – not only that, even closing the connection is also unnecessary, because Joomla does not use persistent connections. So, in short, the whole disconnect function is pure overhead, and, on large Joomla sites, it is wise to get rid of that overhead by simply adding the following line to very beginning of the disconnect function:


So why should anyone care about the “Statistics” line?

Well, on small sites, nobody should really care. But, on large sites it is just (as mentioned above) an unnecessary overhead, and the website is better off without it (and without the overhead of closing the connection).

Is not closing the connections a good thing?

No, it is not. But, although we are no longer explicitly closing the connection, we’re not leaving it open either. Why? Because, by default, the connection is closed in Joomla by the function destruct which is located in the driver.php file (under the libraries/joomla/database), and the destruct function is called (once) when the script has finished execution. However, PHP automatically closes the MySQL connection when the script has ended, and thus, there is no need for Joomla to explicitly close the connection.

We hope that you found this post useful. If you want help optimizing the large Joomla website of your business, then please contact us. We are always eager and happy to help, our fees are super affordable, and our work is super clean.

No comments yet.

Leave a comment