01-16-2010, 06:30 PM
Recently I was having severe mysql problems with my biggest forum. I would have constant crashes and I tried just about everything.
Eventually I tried to alter tables from myisam to innodb and with a few minor adjustments my 1.8gb database was actually consuming 1/10th of it's previous resources. Where I was nearly about to get a new server now I have room to double or even triple in database size without a problem.
The secret why innodb was so much more efficient was how it handles table locks over mysiam. The method used by myisam to lock tables is to lock the entire table on every read/write. Innodb does row-level locking which means only the effected row is locked.
For tables that are constantly accessed that get locked very often Innodb is incredibly efficient. One important note is that innodb does take more overheard and ram but for large databases the offset could be a huge difference to your server resources.
Here is a blog post I made about my adjustments: Innodb Vs MyISAM
Eventually I tried to alter tables from myisam to innodb and with a few minor adjustments my 1.8gb database was actually consuming 1/10th of it's previous resources. Where I was nearly about to get a new server now I have room to double or even triple in database size without a problem.
The secret why innodb was so much more efficient was how it handles table locks over mysiam. The method used by myisam to lock tables is to lock the entire table on every read/write. Innodb does row-level locking which means only the effected row is locked.
For tables that are constantly accessed that get locked very often Innodb is incredibly efficient. One important note is that innodb does take more overheard and ram but for large databases the offset could be a huge difference to your server resources.
Here is a blog post I made about my adjustments: Innodb Vs MyISAM