About a week ago, a new client called us and told us that the her company’s corporate website is slow, very slow. So we checked the Slow Query Log (see here for more about it) on the website’s server, and we immediately noticed the issue. The log was literally over 200 MB in size and it was flooded with the below query:
INSERT INTO `#__session` (`session_id`, `client_id`, `time`) VALUES ('8ffe0d7f994614d079df29d747888213', 0, '1393575824');
Hmmm… We thought, why would such a simple query be flooding the Slow Query Log? It’s a very simple insert query, but then we checked the #__session table using phpMyAdmin, and then we discovered the real issue, the #__session table contained literally more than a million rows, and since there was a lot of indexes on that table, an insert to that table was taking a ridiculous amount of time, something like 15 seconds.
The short term solution was simple, we only needed to truncate the table (e.g. delete the all the rows in that table and completely reset its indexes), we did this by issuing the following command in phpMyAdmin:
TRUNCATE TABLE #__session
(Note: If you want to use the above command, then you will need to replace #__ with your table prefix.)
That fixed the problem! Now the question was, what caused the #__session table to contain these million rows in the first place?
Well, the site was huge; it was getting something like 30,000 visits every day, and, for those of you who don’t know, even if the visitor is a guest (e.g. not a logged in user), an entry in the #__session table is created for him. But, that still doesn’t explain why there was a million rows in that table, since Joomla should regularly delete expired sessions.
We examined the configuration settings of the website, and we noticed that the session lifetime was set to 1440, which means 1440 minutes (and not seconds, see here for more on Joomla’s inconsistency when it comes to cache time), which means a full day, which ultimately means that, since the website receives 30,000 visits a day, there shouldn’t be more than roughly 30,000 rows in the #__session table, right? Wrong!
First of all, the site receives a lot of invisible traffic (traffic from bots, spam, etc…), and second of all, Joomla is not very good at handling sessions efficiently. So, the same user can have multiple session entries in the database (this is because Joomla re-creates the session for the user on expiry, without removing the previous session).
So, what did we do to solve the problem? Well, we lowered Joomla’s session expiry date from 1440 minutes to 120 minutes (2 hours). This worked perfectly, and it wasn’t annoying to the staff working on the site, since the session is kept alive as long as the logged in staff member is not idle for more than 2 hours.
Note: We first suspected the session cleaning process had a bug in it and that it wasn’t cleaning the session table properly, but, after testing it, we discovered that we were wrong. The session cleaning process worked properly (it did work 50% of the time though because of a condition in the code, but that was more than enough).
If your website has some slowness issues, then a quick fix might be to lower the session lifetime in the configuration settings. If that didn’t work for you, then please contact us. We are experts at optimizing Joomla, we work fast, and we don’t charge much!