WordPress Auto DB Optimize

[Jeffro]

Dave Coveney on Twitter mentioned that what WordPress needed was an auto-optimize Database feature. I thought I heard rumors of this in 2.9 or 3.0 and when I mentioned those on twitter, Westi chimed in and confirmed what I heard.

westi @wptavern @davecoveney Indeed it does - {$your_site}/wp-admin/maint/repair.php - add define('WP_ALLOW_REPAIR', true) to wp-config.php first

Just wanted to put it here in the forums to hear how this is helpful.

[chipbennett]

Dave Coveney on Twitter mentioned that what WordPress needed was an auto-optimize Database feature. I thought I heard rumors of this in 2.9 or 3.0 and when I mentioned those on twitter, Westi chimed in and confirmed what I heard.

westi @wptavern @davecoveney Indeed it does - {$your_site}/wp-admin/maint/repair.php - add define('WP_ALLOW_REPAIR', true) to wp-config.php first

Just wanted to put it here in the forums to hear how this is helpful.

Looking forward to this funtionality. I've always been hesitant to let WP-DB-Manager plugin do it, and don't poke around PHPMyAdmin too much, to do it manually.

Jeff, if I understand correctly, optimizing just removes the overhead (bloat/cruft) from a database table. Others more well-versed in MySQL can elaborate, I'm sure.

[Jeffro]

I have no idea what it does. On the surface, I just know that optimizing database tables after awhile is a good thing. I occasionally run the optimizer built into Vbulletin probably once a month just to do it.

[Len]

I'm always leary of these types of scripts. I prefer to do things manually. I don't even remember what Fantastico looks like.

It's amazing the mess some plugins leave behind in the DB.

[chrishajer]

Looks like it runs some MySQL commands:

• CHECK TABLE
• REPAIR TABLE

and optionally:

• ANALYZE TABLE
• OPTIMIZE TABLE

http://core.trac.wordpress.org/brows...int/repair.php

[Jeffro]

Yep, that about describes the process the optimize command in Vbulletin does to all the tables throughout the forum.

[ScottHack]

Is this similar to defragging your hard drive? As a new entry is made in the database and given a unique id and then deleted, you have empty rows in the database. Does this just delete the empty rows put everything together neatly basically?

[davecoveney]

A database optimize effectively sorts out the indexes.

In brief, say you have an index of (unique) post slugs, we'll shorten them to single characters:

a,b,c,d,e,f,g,h,i,j,k

You add five posts, with two letter characters in a random order, and the index goes like this:

a,b,c,d,e,f,g,h,i,j,k,aa,ff,dd,cc,kk

An index search is done by dividing up indexes in halves and seeing where you are.

Now, when you're retrieving post f, a typical search on the first index will go to f immediately, because it just happens to be the middle post. In the second index it will also go to f, because it looks in the ordered index first. If you're looking for row c, for example, it'll look at f first, disregard everything above it, then go to the middle of what's left and hit c. That's two hits on the DB. Still very fast. At worst on this table you'll get three hits. The amount of data you can store for the average maximum hits on an index goes up in powers of two - 1,2,4,8,16,32,etc. It's why you can put vast amount of data into a database whilst maintaining good performance.

But what happens if you look for something recently added? In bold are the hits when searching for kk:

a,b,c,d,e,f,g,h,i,j,k,aa,ff,dd,cc,kk
a,b,c,d,e,f,g,h,i,j,k,aa,ff,dd,cc,kk
a,b,c,d,e,f,g,h,i,j,k,aa,ff,dd,cc,kk
a,b,c,d,e,f,g,h,i,j,k,aa,ff,dd,cc,kk
a,b,c,d,e,f,g,h,i,j,k,aa,ff,dd,cc,kk
a,b,c,d,e,f,g,h,i,j,k,aa,ff,dd,cc,kk
a,b,c,d,e,f,g,h,i,j,k,aa,ff,dd,cc,kk
a,b,c,d,e,f,g,h,i,j,k,aa,ff,dd,cc,kk

In effect, at the end there was a scan of the additional index entries. There's a proper term for these but it's momentarily slipped my mind - if someone can remind me, I'd appreciate it!

As you can see, there were 8 hits against the index. Now, do an optimize and the index will look like this:

a,aa,b,c,cc,d,dd,e,f,ff,g,h,i,j,k,kk

Now a search for kk looks like this:

a,aa,b,c,cc,d,dd,e,f,ff,g,h,i,j,k,kk
a,aa,b,c,cc,d,dd,e,f,ff,g,h,i,j,k,kk
a,aa,b,c,cc,d,dd,e,f,ff,g,h,i,j,k,kk
a,aa,b,c,cc,d,dd,e,f,ff,g,h,i,j,k,kk
a,aa,b,c,cc,d,dd,e,f,ff,g,h,i,j,k,kk

And that was about the worst possible case - five hits instead of eight. Typically will be 3-4 hits for the above.

Modern DBs actually use some slightly more sophisticated search algorithms to be even quicker, but at their heart are indexes and finding things in them.

So, every time a bit of data is added to a DB without an optimize, the performance drops off slightly. In simple and not terribly popular blogs (ie, 95% of them) this isn't a massive issue. But each and every new post, comment, taxonomical entry, etc will add to the number of hits in a search. This is because for each one added, any search for the above will result in an extra hit for every row added.

But WP's DB handling historically has been pretty dreadful - the text search is especially awful on a large installation and really only works because people actually do rather few searches on blogs. But if you do search with ordering then in effect the DB has to bring into memory every index row. Thankfully it can leave behind the data, or performance would be turgid on anything but the smallest site. That, incidentally, is why our next released plugin is going to be a high speed database search plugin that sorts out the indexes and changes the behaviour of the search functionality.

But without a regular optimize your site will perform slightly more slowly every time something is added. We've found that with large sites of over 15,000 posts and lots being added daily the performance deterioration comes very quickly - usually within two weeks.

[andrea_r]

I woudl rather do this via phpmyadmin. It has an option to select all tables with overhead, then click optimize table. Done.

Not sure I'd want WP or anything else to be going in and doing that for me.

[davecoveney]

It's the same command - what does it matter?

Obviously, can be better to schedule it for a quiet time, but that's OK.

Our forthcoming fast search plugin will run an optimize on a regular basis regardless, because if it didn't it wouldn't necessarily be so fast after a while :-)