Note: Please read the whole post before taking any action. There is some important information at the end.
If you check your Joomla site’s MySQL slow query log occasionally, you will notice that it is full of queries there are related to the #__session table (INSERTs, UPDATEs, DELETEs). This is because Joomla updates the #__session table on every page view. In fact, the most active table (write-wise) on a Joomla website is the #__session table.
Of course, if you’re using MyISAM as your database engine, then any update to the table will mean that the whole table will be locked (that’s one of MyISAM‘s drawbacks), which means that any update will have to wait until the previous update is done. On high traffic sites, this can be a big problem because it can lead to an avalanche of queries waiting to be executed, which can ultimately crash the server.
While InnoDB doesn’t lock the whole table on updates, it does also suffer from performance issues on tables with a high write rate. As such, updates to the #__session table, even when using InnoDB, can end in the MySQL slow query log.
So, what is the solution to the problem?
The solution to the #__session problem is easy: using the Memory storage engine. The Memory storage engine stores the data of the table using it in the RAM (with the exception of the table’s structure, which is always stored on the physical hard disk). This means that any queries to the #__session table will be super fast because RAM is much, much faster than hard disk (even SSD hard disks). On the flip side, this also means that restarting MySQL will automatically wipe out the content of a MEMORY table, but this is a non-issue for the #__session table because in a worst case scenario (when the table’s contents are lost), those who are logged in to the website will be logged out (temporary user preferences will also be lost, but this is a non-issue as well).
Now the question is, how to change the #__session table’s engine from MyISAM or InnoDB to MEMORY? Well, it’s delicate, but not hard…
First of all you will need to change the type of the data field in the #__session table from MEDIUMTEXT to VARCHAR. But, before doing that you will need to run the following query (in phpMyAdmin) on the #__session table:
SELECT MAX(LENGTH(`data`)) FROM `#__session` WHERE 1
The above query will return the maximum length of the data field in the #__session table. If you get something like 4,023 , then you will need to set the size of the VARCHAR to almost double that number, something like 8,192 (which is 213).
Once you change the type of the data field, you will then need to truncate the #__session table, and then you will need to change the engine of the #__session to MyISAM if it’s InnoDB (note that, for some reason, the latter step is necessary on some servers, and that’s why we have mentioned it).
Once the table’s engine is changed to MyISAM, you will then have to change it MEMORY (this can be done by clicking on the table name in phpMyAdmin, and then clicking on Operations on the top, and then finally choosing MEMORY as storage engine).
Now, the first time we optimized the #__session table by following the above guide, the website ran smoothly for about 30 minutes, and then it showed a blank page. It turned out that the #__session table became full because the RAM assigned to MySQL’s temporary tables was exhausted. Here’s how we fixed the problem:
- We logged in through ssh to the server.
-
We opened the file /etc/my.cnf (which is the MySQL‘s configuration file).
-
We changed the values of tmp_table_size and max_heap_table_size to 1024M (these two settings must be identical because MySQL will use the lesser of the two).
-
We restarted MySQL and everything was OK.
Changing the engine of the #__session table to MEMORY will certainly have a huge positive effect on your site’s performance (and will substantially reduce the size of the MySQL slow query log on your server). Doing it can be a bit tricky though, but, as usual, we’re here to help in case you need help. All you need to do is to contact us and we’ll take care of the rest. Oh, and by the way, our fees are super affordable and we are very, very friendly!