[wp-hackers] Freezing $now to increase query cache benefit

[Mark Jaquith]

I've been trying to help out a client who is using WordPress on a VPS  
account with 256MB RAM.  His site is very well trafficked, and MySQL  
resource usage was going through the roof.  As it turned out, the  
biggest queries were the main page post query, and the post/category  
join query.  In some cases these queries were taking 10-15 seconds  
each!  wp-cache2 helps, but those slow queries were still going through.

The next solution I looked at was MySQL's qcache, which caches  
queries.  The problem here is that the main post query uses " AND  
post_date_gmt <= '$now' "  ... and $now changes on each load.  qcache  
requires the query to be identical for it to be served from the query  
cache.  And then I though... WHY does $now need to reflect the  
current time?  The reason that $now is there is to prevent future  
posts from appearing, so why not just make that date reflect the date/ 
time of the most recently published post?

So, I wrote a plugin to do just that.  It updates an option with the  
date/time of the most recent published post (updated on edit, save,  
publish).

It made a HELL of a difference.  The main post query, when grabbed  
from the query cache, now takes less than 1/100 of a second, as  
compared to 3-15 seconds before.

Here is the plugin:
http://txfx.net/files/wordpress/post-query-accelerator.phps

And attached is the my.cnf (MySQL configuration file) in use.  What  
do you think about putting something like this in the core?  It can't  
hurt, and it can really help people who are utilizing Qcache to  
relieve stress on high traffic sites.

--
Mark Jaquith
http://txfx.net/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: my.cnf
Type: application/octet-stream
Size: 571 bytes
Desc: not available
Url : http://comox.textdrive.com/pipermail/wp-hackers/attachments/20060106/8a2733cf/my.obj