A WordPress Performance Killer - Automatic OPTIMIZE or REPAIR on big Databases

A WordPress Performance Killer - Automatic OPTIMIZE or REPAIR on big Databases

By Strictly-Software

I used to use the WordPress plugin WP-DBManager. I still do for the automatic backups (when they work), I have found on one site the scheduling just doesn't work so I have to do it by hand. No difference between that site and another that works but hey that's WordPress!

However one thing I found, especially once I reached 20k+ articles is that my scheduled OPTIMIZE / REPAIR jobs would totally kill my sites every Friday for an hour or so.

It took me a while to find the cause but when I used the Server Monitor in Navicat and saw that both my Databases were doing a REPAIR by SORT at the same time, I soon realised it was my scheduled job in WP-DBManager to REPAIR / OPTMIZE every Friday that was the culprit.

With MyISAM tables (the default storage engine for WordPress), an OPTIMIZE or REPAIR job will take the whole table out of action as the process is carried out. The table is locked as the sorting and repairing is done which means no other users or processes can access it.

Unlike MS SQL which lets you De-Frag a tables index whilst connections to it carry on MyISAM just locks up until the job is complete.

Symptoms

• Your website is hanging for ages, or timing out. If you are using Cloudflare you might be lucky enough to see cached pages either that a 503 service unavailable.
• Running a TOP command will show MySQL process consuming 90-100% of your CPU.
• Checking your Server Monitor in Navicat shows a SORT going on with a long execution time. The more pages and posts the longer it will take.

Risks

• If you have an scheduled job to REPAIR / OPTIMIZE your tables, either through a plugin like WP-DBManager then you could suffer this problem.
• The type and size of table matters. If it is using MyISAM it will be locked until the job is over. 
• The bigger the table e.g WP-Posts the longer it will take. 
• The more records the longer it will take. If the table is used a lot then it will be crushed until the job is over.

Solutions

• I turned off the automatic OPTIMIZE and REPAIR jobs on WP-DBManager.
• If I have scheduled downtime then I will make use of it whilst the werbserver is offline to carry out DB maintanance.
• If you are clever enough (and I don't know if MySQL supports SYNONYMS like MS SQL) but you could create copies of your existing tables with new names, let your system carry on using the old tables whilst your OPTIMIZE / REPAIR the new ones. Once finished toggle the SYNONYM back to the newly formatted tables. A SYNONYM would be perfect for this but you could write SQL to create a table, insert records, repair and then re-name. Maybe a plugin could do this (ideas for developers? I would be willing to write one for ££)
• I know use my own Strictly System Check plugin instead to handle automatic REPAIRS / OPTIMIZE as it will only carry them out if the system is already suffering. My plugin checks the server load, the time it took to load a page, the number of connections and so on. If the plugin determines that the system is having trouble you can set it to OPTIMIZE or REPAIR (after a check to see if one is needed). This way you are only fixing something that needs it rather than causing havoc for no need.

It is something to be on the lookout for. If your site suddenly acts slow with high MySQL CPU use then a scheduled REPAIR might be the cause.

If this happens it is best to turn off APACHE, let the REPAIR do it's job and then turn the webserver back on.