Since this problem occurred I've been logging everything.
Time of occurrence: Starting at 8AM!!
During night no problems. Then at 8AM EST mysql.slow.log is starting to log slow queries. This happened on two days in a row now starting at 8:02.04 and 8:04:36.
I find a lot of the long queries in my database: SELECT SELECT
SQL_CALC_FOUND_ROWS distinct wordpress_posts.* FROM wordpress_posts LEFT JOIN wordpress_term_relationships ON (wordpress_posts.ID = wordpress_term_relationships.object_id) LEFT JOIN wordpress_term_taxonomy ON wordpress_term_taxonomy.term_taxonomy_id = wordpress_term_relationships.term_taxonomy_id WHERE 1=1 AND wordpress_posts.post_type = 'post' AND (wordpress_posts.post_status = 'publish') AND NOT EXISTS (SELECT * FROM wordpress_term_relationships JOIN wordpress_term_taxonomy ON wordpress_term_taxonomy.term_taxonomy_id = wordpress_term_relationships.term_taxonomy_id WHERE wordpress_term_relationships.object_id = wordpress_posts.ID AND wordpress_term_taxonomy.term_id IN (8) ) AND TIMESTAMPDIFF(MINUTE, wordpress_posts.post_date_gmt, '2010-10-23 12:02:01') > 600 ORDER BY wordpress_posts.post_date DESC LIMIT 0, 10;
Is there a trigger for the problem?
Yes, I might be able to trigger it. This could be a coincidence, but a couple times now the SQL server crashed when I saved a Wordpress Draft.
Apache Error Log at same time: Keep in mind I did not request that graceful restart.
[Sat Oct 23 08:00:13 2010] [error] [client 18.104.22.168] (36)File name too long: access to /wp-includes/js/thickbox/thickboxion:"yes",encode:"yes".................
[Sat Oct 23 08:01:19 2010] [notice] Graceful restart requested, doing restart
[Sat Oct 23 08:01:26 2010] [warn] RSA server certificate is a CA certificate (BasicConstraints: CA == TRUE !?)
[Sat Oct 23 08:01:26 2010] [notice] Apache/2.2.16 (Unix) mod_ssl/2.2.16 OpenSSL/0.9.8e-fips-rhel5 mod_bwlimited/1.4 PHP/5.2.9 configured -- resuming normal operations
[Sat Oct 23 08:01:38 2010] [error] [client 22.214.171.124] PHP Fatal error: Call to a member function get() on a non-object in /public_html/wp-includes/cache.php on line 93
[Sat Oct 23 08:03:15 2010] [error] [client 126.96.36.199] PHP Fatal error: Call to a member function get() on a non-object in /wp-includes/cache.php on line 93
[Sat Oct 23 08:08:07 2010] [warn] RSA server certificate is a CA certificate (BasicConstraints: CA == TRUE !?)
[Sat Oct 23 08:08:07 2010] [notice] suEXEC mechanism enabled (wrapper: /usr/local/apache/bin/suexec)
[Sat Oct 23 08:08:07 2010] [notice] ModSecurity for Apache/2.5.12 (http://www.modsecurity.org/) configured.
[Sat Oct 23 08:08:08 2010] [warn] RSA server certificate is a CA certificate (BasicConstraints: CA == TRUE !?)
[Sat Oct 23 08:08:08 2010] [warn] pid file /usr/local/apache/logs/httpd.pid overwritten -- Unclean shutdown of previous Apache run?
I disabled a lot of plugins and cleaned the wordpress_options table. However, I did not update ANY plugins and this worked for months, so why should it start making problems now?
What troubles me is the fact that mysql.slow.log started to log slow queries over 1s at almost the same time for two days in a row? Coincidence? Someone exploiting a vulnerability?
I am very grateful for any help or ideas how to solve this.
RLTT.com is for SALE Realtime Technologies is the way to go !
The query itself is a monster. It *might* be accepted into production by a dba, but only under the most pressing circumstances and only after every avenue to avoid it had been exhausted.
The problem with wordpress plugins is that not all of them are well written. Some enthusiast throws something together to achieve a particular personal requirement, and then publishes it. Others come along and grab it without any thought as to whether it might be good code.