Using the Slow Query Log to Reveal Bottlenecks on Your Joomla Website

Note 1: This post is targeted at system administrators with hands-on experience with MySQL. If you’re not a technical person, then please forward this post to your system administrator (you can always hire us if you don’t have one!).

Note 2: You will need root access to your server’s shell to implement the instructions in this post.

You wake up one day refreshed and ready for action, you have your coffee and your little breakfast, and then you sit down behind your desk to start work – hoping it’ll be another productive and profitable day! You turn on your PC and browse to your Joomla website to check if everything’s OK, but, to your surprise, your website loads very, very slowly. You think it might be a problem with your connection, so you visit other websites, and they’re all good. Hmmmm!

You remember reading our post that states that slowness in Joomla might be the sign of a hacked website, so you do all the necessary checks, and you rule that out. “Might it be my host?”, you wonder… So you call your host, trying to gently blame the problem on them, and asking them politely to fix it. Your host’s support personnel points out that everything is OK from their end, but the load on your server is quite high – almost reaching 100%. Aha! Now you’re on to something, because when the load is that high on a web server, then in 99% of the cases it is a heavy query (or a series of heavy queries) being executed by MySQL and reducing the performance.

So, just to make sure, you login through ssh to your server, and your run the following command:

top
(Yes – it’s just one word!)

As expected, you see the mysqld process exhausting most of your server’s processing power. So now that you’re sure that the culprit is MySQL, the next step is to determine which query (or queries) is (are) causing the bottleneck. Thankfully, there is something called the Slow Query Log, which is a MySQL tool that logs the database queries that are taking a long time to execute to a file of your choice.

Here’s how to enable the Slow Query Log in MySQL:

• Login through ssh as root to your web server (we are assuming that your database server is the same as your web server).

• Create the file mysql_slow_queries.log under the /tmp directory. This can be done using the following command:

  touch /tmp/mysql_slow_queries.log

• Ensure that all users are able to write to the file that you have created above by issuing the following command:

  chmod 666 /tmp/mysql_slow_queries.log

• Open the file /etc/my.cnf in edit mode (through vi or nano) and add the following line (to the end of the file):

  log_slow_queries=/tmp/mysql_slow_queries.log

  This tells MySQL to write all slow queries to the file mysql_slow_queries.log located under the tmp directory.

• Restart MySQL. This is done using the following command:

  /etc/init.d/mysql restart

  Note that restarting MySQL will cause a very brief downtime on your website. If the above command hangs or fails, then contact your host immediately!

After doing the above, MySQL will log all queries which are taking more than 2 seconds (2 seconds is the default) to execute. To change the minimum amount of time for a query to considered as slow (and thus logged), you need only to change the value of long_query_time in the /etc/my.cnf file (and, of course, restart MySQL).

Now, the next time your Joomla website has performance issues because of a slow query, you will know which one it is (because it will be logged), and once you know that, all you need to do is to optimize that query. So, now your question is most likely: “How can I optimize a query?”

Well, a query is slow because of two main reasons:

• One or more fields in the WHERE or ON condition (in case of a JOIN) is not indexed.
• The query is badly written.

In the first case, you will need to add indexes to the affected field(s). In the second case, you will need to rewrite the query.

We hope this post helps you identify those queries affecting the performance of your Joomla website. If it doesn’t, of if it does, but you still need help fixing those queries, then why not contact us. We are experts in Joomla, our work is of top-notch quality, our fees are reasonable, and we are the friendliest developers on this planet!