Now that I have a VPS with limited memory available for my ‘main server’, I’ve been looking at how to optimize usage to make sure I keep well under the memory limit. I found a nice article Optimizing Apache and MySQL for Low Memory Usage, Part 2 which explains some great ways of reducing your mysql memory usage. One of their best suggestions is to disable InnoDB which can save up to 100M of memory.
I didn’t think I was using InnoDB at all but it turns out that Mediawiki by default sets up some tables using InnoDB even though most are setup as MyISAM. The Mediawiki developers note that InnoDB is better for those tables for high load wikis, but since mine doesn’t exactly qualify as high load, I thought it would be safe to convert.
But how to do it? Some sites recommended dumping the db, changing the ENGINE type for the tables in the dump and then reimport it. I’m sure that works, but it seems like a fairly crude way of going about it. Turns out there’s a better way.
First run the ‘mysql’ commend line program and connect to your database. If you have more than one database on your mysql server, you’ll need to check them each one by one:
mysql
use database;
Then get a list of databases using the InnoDB engine:
show table status where engine="innodb";
For each table change the type as follows:
alter table tablename engine="myisam";
For good measure look at the table types again to make sure you got them all:
show table status where engine="innodb";
If it doesn’t show any, then you can go ahead and put ‘skip-innodb’ in your my.cnf as outlined in the document about optimizing mysql above and you’ll suddenly have an extra 100M of memory available!
As I had exactly the same problem with MediaWiki, but am running a number of instances, I created the following shell snippet to do it automatically:
for i in `find /var/lib/mysql/ -mindepth 1 -type d -printf “%f\n”`; do
echo “—— $i ———”
pushd /var/lib/mysql/$i > /dev/null
for t in `ls -1 *.frm *.MYD *.MYI | sed -e “s/\..*$//” | sort | uniq -u`; do
echo “Found InnoDB table: base $i table $t. Converting to MyISAM…”
echo alter table “$t” engine=myisam | mysql $i
done
popd > /dev/null
done