Wednesday 10 October 2012

Wordpress Database Optimization | Database Bloat

The Problem: Wordpress is notorious for "database bloat", in particular the "wp-options" table. This is where Wordpress (WP) stores all info for plugins and themes, etc. There are built-in functions for storing data in this table, so a lot of developers use it to store data that really should be in a separate table. Also, most plugins don't clean up after themselves when you delete them. To make things worse, Wordpress stores a draft copy of EVERY post revision you make. This results in bloating of the "wp-posts" table. Some Culprits: I've come across TWO plugins that massively contribute to bloated wp-options tables: FeedWordpress (FWP) and Artiss Social Bookmarks. Don't get me wrong, FWP is a fantastic plugin, BUT it stores "transient" records in wp-options, and then never cleans them up. Artiss does something similar. PHASE 1: Database Cleanup. A Solution: There's a plugin called "Clean Options", which scans for wp-options records which are never referenced by your WP installation. It calls these "orphans", and you can tell it to remove them for you. Be careful, though: take a database backup before you delete ANYTHING. Select any options belonging to plugins you don't have installed any more. Be careful! You should also be able to delete anything which starts with "_transient_". A fly in the ointment: If you have a wp-options table with 30000 records in it, then "Clean Options" isn't going to be able to cope. It will freeze-up. You won't be able to select records to delete. Drastic Measures: So where does that leave us? Manually deleting records from the wp-options table. Yes. That's scary. But it's the only way. MAKE A BACKUP OF YOUR WP-OPTIONS TABLE Use phpmyadmin to directly access your database, select the wp-options table, and use the "export" option to make a backup to your local machine. Now execute the following sql statements: select * from wp-options where wp-name like '_transient_%' This will return all the transient records with names starting with "_transient_", and give you a tally of how many there are. Check these records to make sure there's nothing else in there before you do anything drastic. Like this: delete from wp-options where wp-name like '_transient_%' This deletes ALL records starting with "_transient_". You did check the results of the select statement, above, didn't you? No? That's OK, you DID make a backup copy. Didn't you? You have just deleted (possibly) thousands of wp-options records (my record is 28000). Now, at this point, one of three things will happen: 1. Nothing. That's great. 2. Your website will go offline for a while as the database server deals with the changes. No worries - it will come back on it's own.. 3. Your website will go offline and stay offline. :( If this happens, try adding "/license.txt" to the end of your site URL. If it displays the WP license agreement, your hosting is Ok, but WP is having a breakdown. Fix this by accessing the "active plugins" record in wp-options, (usually on page 2) and copying the contents somewhere safe. Now delete the contents (NOT the record itself!) Now try accessing your site again. You should find that you can get into the admin panel and re-activate the plugins one-by-one until everything's back up. (or paste the copy you made earlier back in) Phase 2: Database Optimization. You should now have a nice slimline wp-options table. Now we need to "optimize" the database. By this, we mean delete all post revisions (you don't need them. You didn't know you HAD them, right?), spam comments, etc. We also need to run standard database optimization routines on the core WP tables, remoce table overheads etc.. Make a COMPLETE database backup before you start messing with this. (Same routine as before, but for ALL tables together). We use the "wp-optimize" plugin, but there are others. WP-Optimize has checkboxes for deleting post revisions, spam, and optimization. We always run the optimization option ON IT'S OWN.. You DO NOT want a timeout at this point! That's it. Hopefully nothing went horribly wrong. If it did, it's YOUR problem. We told you to make backups several times.

1 comment:

  1. Those transients keep track of the feeds being polled by FWP. You can move all transients to memory-based cache (faster) by using APC and the WP APC object cache.

    ReplyDelete