Msg#: 4549528 posted 6:16 pm on Feb 27, 2013 (gmt 0)
The problem is related to Wordpress sql database queries acceding limitation, causing high CPU usage during updating the site (Wordpress posts). It doesn't appear on the regular run of the site. During updates, it appears that the database hit the I/O wait time limit. I/O wait refers to the CPU waiting for input and output operations to the hard disk or storage device most likely due to a large amount of data being processed from the database. I made many changes to resolve this issue: 1. Deactivating plugins during updates. 2. Increasing WP_MEMORY_LIMIT’ to ‘256M’ 3. Stopping pingbacks/trackbacks/enclosures adding php remove_action('do_pings', 'do_all_pings', 10, 1); to function.php 4. Deleting unused rows on wp_options table on database making it 1Mb. 5. Optimizing database using db-manager plugin. I suspect it is something related to Wordpress and not the MySQL database. It is because, the database is not so heavy. This can be exacerbated by queries that do not retrieve this data efficiently. Could it be that deactivating suspected plugins is not enough and there’s a need to disable (remove) them completely? (disabling any plugins that work with the posts table). Any thoughts, advice, suggestions are welcome.
Msg#: 4549528 posted 7:12 pm on Mar 6, 2013 (gmt 0)
I haven't looked at Wordpress internals for a while. Are you using MyISAM or InnoDB tables?
If you install this on another machine, say your local workstation or some cheap shared hosting, do you have the same problems? By default, mysql ships with completely unacceptable options for InnoDB tables. If you are setting up the server yourself, you MUST tune your InnoDB settings, otherwise MySQL will do a huge amount of I/O and slow to a crawl.
- plugins - might help, but I gather not. Still, if you have adequate server permissions, you could enable the slow query log. Sometimes a plugin can add a complicated JOIN that will slow things down a lot.
- memory - this should be more of a crash when out of memory thing assuming that it's actually just a wrapper for the PHP memory limit setting.
- unused rows in wp_options - 1MB still sounds excessive for the wp_options table (or is that your whole DB?)