[wp-hackers] innodb vs myisam

[Brian Layman]

On 3/28/2011 9:15 AM, Andrew Gray wrote:
> I just wanted to let all the folks on this list know, that I fixed a client site that was getting horrible performance by switching the post and post meta tables to Innodb from MyIsam.
>
> They have over 40,000 posts in their database and publish all day long, 20-30 posts per day.  Consequently, their were constantly writing revisions to the DB. They would have 3 or 4 post edit windows open per user at once, constantly locking up the entire posts database.
>
> I am not a DB expert, but I was recommended to make the switch by the engineers at Rackspace and it made huge difference.  The stacked queries went away immediatly.
>
> Just thought I would let you know, since it would have saved me a bunch of headache if I had know this last week,,,
>
> Andrew
Yes that was a good move. InnoDB is actually the default engine in MySQL 
5.5.  The reason is sped everything up is that MyISAM locks the table 
when it does writes and you were creating a huge bottleneck.  This 
conversion is a given on just about all of the upgrade projects I'm 
involved in now.

One caution: MyISAM and InnoDB use two different sets of memory cache.  
So you're server is now using more memory than it was before. If memory 
ever becomes an issue, you might want to consider moving all of the 
tables of all of your heavily used databases over to InnoDB and reduce 
or eliminate the MyISAM cache resources.

-- 
Brian Layman