WordPress MU

Does anyone know why WordPress, specifically Mu 2.7, creates so much overhead in the database? I have a test install on a machine that has 10 blogs and 100 posts, and currently have close to 1 meg in overhead.

Has anyone tried running WordPress with the InnoDB storage engine instead of MyISAM?

I know, MySQL support isn't everyone's forte, but it's kind of frustrating to see all this overhead on such a small installation.

If you're deleting a lot of stuff, that'll do it.

Optimizing the db on a regular basis is something a lot of us do.

Has anyone tried running WordPress with the InnoDB storage engine instead of MyISAM?

MyISAM is considered to work quicker. Of course, this depends on many factors. Except DB optimizing that Andrea suggested, make sure that your MySQL runs in multi-process mode, not as a single process.

You'll get a lot of slack in the options table because that is where WP stores its admin panel news feeds so as those change the slack starts to build up.

It is certainly worth looking at a scheduled optimize script

Thanks everyone for the advice. Our test environment is running single process (it's the machine sitting on my desk in my cube :). Our production environment is multi-process, multi-cpu and load balanced, so perhaps there will be a lot less overhead. I believe it also runs an optimize script nightly, but if not I will likely add one.

andrea_r: How do you optimize mysql for WPMU? Link?

VentureMaker: Is there a link or somewhere I can read on mysql multi-process mode? I googled but didn't find anything straightforward.

TT: you go into phpmyadmin, select a bunch of tables and click "optimize" from a drop-down. :D

TTjip: completing Andrea's tip- when you login to phpmyadmin and choose a database, the last column on the right is 'Overhead'. Select the tables that have a value there and then choose Optimize Table from the action dropdown at the bottom of the screen.

Like you said though, running a daily automated cron to do this is probably a good idea

Yep, I'm sure there's plenty listed on google.

Typically one would look at the ratio of writes to reads for the particular table in order to decide on whether or not InnoDB or MyISAM is a logical choice.

MyISAM is rather quick for reads, but writes slow it down as it has to lock the entire table in order to write to it. In turn, additional requests are queued until the write request is completed.

InnoDB however allows for a single row to be locked during a write, thus leaving the rest of the table free for other requests. It also is much more stable (that's being polite) when it comes to recovery of crashed tables. The downside is that while reads are quick, they aren't as quick as MyISAM.

One final "drawback" to InnoDB is disk space. InnoDB is going to use more space to store the same data. Sometimes as much as 2 or 3 times the space of a MyISAM table.

An average consensus seems to be that the break even point is about 15% of requests being reads, with overall improvement and gains starting around 20%. YMMV.

InnoDB isn't a bad option. Far from it. If for nothing more than its crash recovery being significantly better.

Whether or not it makes sense for a particular site is something to evaluate per site. A site with low activity, but high page views may not benefit from it, although there may be a table that would (like a stats table, or one that's constantly written to).

As for a daily optimization routine, and we're talking table optimization not actually optimizing MU for MySQL, the basic command is simple. OPTIMIZE TABLE table_name.

One could put together (or find) a bash script that would roll through and optimize tables on a regular basis, and run it through cron.

Smaller sites could use PhpMyAdmin on a regular basis as well, although automation always makes life easier.

I guess this should help http://www.xaprb.com/blog/2006/07/16/how-we-enabled-threading-in-mysql/