A client called us back on Friday and told us that his company was having some serious load issues on its high traffic corporate website (which was, or course, using a Joomla website). Usually, when there are performance issues on a website, the first thing that we check is the slow query log (typically located under the /var/lib/mysql directory). And so we did, and we noticed that the log was full of queries that are more or less the same as the ones below:
UPDATE #__categories SET hits=hits+1 WHERE id = 402;
UPDATE #__content SET hits=hits+1 WHERE id = 114317;
Each query was taking a few seconds (some of these queries were taking about 10 seconds) despite the fact that it updates only one row. Hmmm – but why?
We tried running one of the above queries directly in phpMyAdmin and it took more or less the same amount of seconds every time we ran it. Oh, and by the way, the website was using MyISAM as then engine for the MySQL database, which means that any update to the table (such as #_content) will lock the whole table, which means that any subsequent update to the table will have to wait on the previous update to complete before getting executed. Of course, switching to InnoDB will solve the table locking issue (since InnoDB uses row locking instead of table locking, which means that an update to a specific row will only lock that row), but may introduce a myriad of other issues, and we still won’t know what is causing the update to take a long time in the first place.
And so, we decided to stick with MyISAM and solve the problem. So, the next thing that we did was checking the MySQL configuration, which can be found in the my.cnf file which is located under the /etc folder. Luckily we immediately discovered the problem, and it was in the following lines:
query_cache_size=8G
query_cache_limit=16M
The database had a query cache of 8 GB (that’s eight gigabytes!), and, while cache is a good thing (or maybe not), it can cause some serious issues when it is above a certain limit (that limit depends on the processing power of the server, its memory, as well as other factors), because the overhead of maintaining the cache will increase substantially. So, on the website we were working on, everytime there was an update to the #__content table (which is one of the most used tables on a Joomla website) because of hit tracking, all the queries using the table #__content got invalidated, which meant that MySQL had to scan through gigabytes of data for those queries and remove them. Not only that, all these invalidated queries must be then re-executed, which created even additional load on the server. Yes – it’s a very, very vicious circle.
So, what was the solution?
The solution was to significantly reduce the query cache by replacing the above lines with the following lines (the last line will reduce the size of a cache block, which is a good idea to do if most of the cached queries are less than 2 KB in size, which is typically the case on a Joomla website) and then restarting MySQL (the below changes will not take effect unless MySQL is restarted):
query_cache_size=128M
query_cache_limit=256K
query_cache_min_res_unit=2k
By doing the above, we ensured that the cost of maintaining the query cache will not be that significant, which meant that update queries to the most used tables will no longer be heavy queries, which resulted in a much, much better load on the server (the load got reduced from an erratic 12-20 to a consistent 2-3).
Another solution, of course, was to completely disable hit tracking on the Joomla website, which would have solved the problem probably more efficiently. However, it wasn’t an option for us since the website was displaying the number of hits on each article.
If you’re having performance issues on your website because of hit tracking, then consider disabling it if you’re not using it. If you are, then you should check the configuration parameters of the MySQL database engine on your server, and ensure that you are using reasonable values for the query cache parameters. If you need help, then please contact us and we’ll do the work for you in no time and for a very reasonable fee!