krynbleHello, I'm using wordpress on a few busy sites and I'm having a HUGE performance issue.
At the begining, it was solved by using w3 total cache with memcached, but now I see that my site is really slowing down and the CPU usage is at an alarming 180% usage most of the time (it's a dual core machine) and MOST of the usage (about 160%) is going to MySQL (usage percentage is related to a single core).
Considering I'm using w3 total cache, caching queries and pages using memcached, I thought this shouldn't happen at all.
Looking at the process list and slow queries log in MySQL, I realized that most of the queries are the sql_calc_found_rows-like queries, used to show Related Posts. Sometimes, a single query to return THREE related posts takes up to FORTY FIVE seconds to run. Note that we're only using WP created queries, no hand-crafted queries were created.
I turned the debugging on and w3 total cache shows all sql_calc_found_rows were being rejected. After reading some of it's source code and googling, I found out you have disabled sql_calc_found_rows since 0.8.5.1. But no reason was given at all.
I hacked the W3TotalCache's core and removed this restriction. My website is now running a LOT faster, and I see no obvious reason for denying the cache for this kind of query.
There is no point in denying this type of query in my blog, the only usage for sql_calc_found_rows is to show related posts, and there is absolutely no need for accuracy in the recults. Also consider that I'm disabling cache when users are logged in, so it won't affect my wp-admin.
Is there a real reason for removing this? Now my website is a lot faster, the CPU usage has gone down a whole lot and everything's working.
PS: I have already tuned MySQL most I could, using great profiling tools and fine tinkering. There's a whole lot of cache for MySQL and the machine's got 8GB of ram, consider that MySQL is not the issue.
PPS: I'm using w3 total cache version 0.9.2.4.
