An interesting problem today – WordPress posting on this site was sloooooow. Some other back-end tasks were also incredibly slow.
Adding a post or page would take about 30 to 60 seconds. Unacceptable. I did the usual job of deactivating plugins, and even resorted to a different theme for a few minutes but no, it was all still very slow.
That’s when the work starts. On this server we haven’t opened up port 3600 to allow remote connection with mySQL Administrator, so instead it’s straight into phpMyAdmin.
The process is then pretty simple – for each table you have an Operations tab. Here you can see some information about the state of the table as well as quick links at the bottom to allow you to run various tools. The process I use is to backup the site then run a Repair then an Optimize on the _posts (typically tables are prefixed WP_) , _postmeta and _options tables.
Why the Slowdown?
Well, I didn’t bother investigating properly – without full server access it’s often hard to get full timings. You can put traces into code and look at logs, or you can very quickly run the Repair process. But what I do know is that databases can slow down for inserts more easily than for reads. This is usually because something’s become messed up in an Index, or the whole table’s become inefficiently organised over time. By repairing and optimizing you get to quickly tidy things up and restore the performance of the site.
For a minor site like this, it’s about the best approach to take. If you’re running something like Facebook you’ll probably want to investigate things properly – but then you’ll have the money to be able to do so as well!