Note: This post only applies to Joomla websites using MySQL.
Another note: This post only applies to Joomla 2.5 and lower. Joomla 3.x uses the InnoDB engine across the board by default.
The majority of crashes on Joomla websites are caused by database issues, in particular, database corruption issues. For example, if the table #__session is corrupt (and needs repair), then the whole website will crash, and, unfortunately, that table does crash a lot on large websites.
Now, the question is, why do database crashes happen?
Database crashes happen when the application (such as Joomla) is trying to write to the database, but something unexpected happens, such as temporary loss of network connection between the database server and the application, server hiccup, loss of power, etc… When this happens, and if you’re using MyISAM (which is the default table type in Joomla), then 1) the table will remain in a locked state until it’s repaired and/or 2) table corruption may happen. Any of the previous 2 issues can make your Joomla website inoperable until your repair the corrupt tables (by issuing the REPAIR query on those tables).
So, what’s the solution?
The solution is to use InnoDB instead of MyISAM as the table type for the heavily updated tables, namely the #__assets and the #__session table. InnoDB has a couple of serious advantages over MyISAM when it comes to stability, which are:
- Row locking instead of table locking on writes: When the Joomla website is inserting a row in the #_session table (or updating a row in that table, for that matter) and if that table is a MyISAM table, then the whole table is locked. Any requests to write to that table will be denied. On the other hand, if the table is an InnoDB table, then only the affected row will be locked, which means that concurrent writes to that table are possible as long as they are not affecting that particular row. This ensures that the table will never be left in a locked state if it’s InnoDB.
-
ACID implementation: ACID stands for Atomicity, Consistency, Isolation, Durability, and this is what InnoDB tables are about: Atomicity means that any action that is done on an InnoDB table is atomic, which means that that action will be reverted if it’s not completely done. Atomicity ensures Consistency of that particular table (many references try to explain Consistency as something different, but we think that it’s just a corollary of Atomicity). Isolation means that we can have concurrent writes seamlessly, and Durability means that any sever crash will not cause corruption in the tables. Again, Durability is a corollary of the other InnoDB properties.
So, why does Joomla still use MyISAM?
Joomla still uses MyISAM by default because, ahemmm, MyISAM is lighter and faster (we know that many Database administrators will disapprove of this statement), not in all conditions, but in most conditions. According to our experience, under normal operations, reads and writes are both significantly faster on MyISAM tables. Additionally, MyISAM tables allow for full-text search indexing, which is not possible in InnoDB tables because of their structure.
And how can a table be changed from MyISAM to InnoDB?
You can change (alter) the table type from InnoDB to MyISAM by executing the following query:
ALTER TABLE [your-table] ENGINE=INNODB;
So, if you want to change the tables #__assets and #__session from MyISAM to InnoDB, then you will need to execute the following queries (while in phpMyAdmin):
ALTER TABLE #__assets ENGINE=INNODB;
and
ALTER TABLE #__session ENGINE=INNODB;
(Of course, you will need to replace #__ with your table prefix.)
We don’t think it’s a good idea to change all tables from MyISAM to InnoDB, because this will cause performance issues on your Joomla website.
If your Joomla website is crashing because of database issues, then try implementing the above table change. If you still experience the same issue, then please contact us. We’ll investigate the issue for you and we’ll definitely find and implement a solution (and we won’t charge you much!).