MySQL storage engine: InnoDB or MyISAM?

Hi all,

I am wondering whether the storage engine of MySQL should be InnoDB or MyISAM.

I noticed that it makes a lot of difference for the PubSub maintenance pool

This pool is basically a queue which inserts at regular time-intervals a batch of new published items into the ofpubsubitem table

When I use InnoDB it takes on avg 93 milliseconds for each insert (which is far too long)

Using MyISAM I easily insert 5000 records per second

Is MyISAM ok to use for OpenFire because MyISAM does not support transactions, row-level locking, and foreign keys (which InnoDB DOES support).

Will that lead to problems?

Brgds Gertjan

Hm, I have MyISAM for my openfire db. I think it’s the standard anyway (I didn’t change the engine types)

I was trying to use InnoDB for a new/clean 3.7.1 install, but I kept running into odd transaction issues. I didn’t notice them at first, but after restarting the initial install, i couldn’t log in (using LDAP or local). Other odd timing things going on.

Since our DB Server was set to InnoDB for default, I had to force the DDL scripts to use ENGINE=MyISAM. After I did that, it looked like things were working much better.

I’ve certainly not done an exhaustive comparison. Anyone else seeing similar?