InnoDB Crashed My VPS

Mediawiki uses InnoDB for its MySQL database by default due to it scalability. For small wiki sites, using InnoDB may not be a good decision. I will speak through my experience. Many comparison websites favor InnoDB over MyISAM due to performance and scalability advantages. Nothing is mentioned about resource usage (i.e. disk, memory) until InnoDb crashed my VPS crawling to its knees.

My wiki site uses a lowend VPS with 256MB RAM (512 MB Burst) and 25GB disk. Properly configured this specification is more than enough for the site requirement. In terms of resource usage, with InnoDb activated, memory usage could go as high as 400MB. If not checked, a warning from your hosting provider is eminent. Deactivating InnoDB, reduces memory usage to less than 200MB. That is a big reduction in memory usage.

To disable InnoDB, open your MySQL configuration file /etc/my.cnf by adding a line skip-innodb under [mysql]. Restart MySQL and the memory usage reduction is noticeable.

For disk usage, InnoDB uses a file ibdata1 to store its system tablespace. In my case, it has grown to about 17GB in several months. My disk usage rose to 100% which made my VPS unusable.

I have search the net on how to optimize the databse and reclaim disk space, nothing really worked. My ultimate solution was to delete the database totally and the ibdata1 file including the ib_*_log files. I restored my database from a backup using MyISAM.

From above, I was able to reduce disk usage to about 30%.

Lessons Learnt: If you have a small site with very little resource, MyISAM is definitely the way to go.