[wp-hackers] MySQL MEMORY worth using for an object cache?

[Computer Guru]

I do exactly that on my dedicated server (Window Server 2003, IIS 6, Tons of ISAPI Extensions) but the only difference being that I'm running IIS instead of Lighttpd, and eAccelerator instead of APC.

I know APC is the "standard," but it breaks many scripts (on my site, it's gallery2) so eAccelerator does the trick, and I find it to be just as fast and a bit lighter too.

In the past week, my site was (honored to be) on Slashdot's main page twice in 4 days, with the latter being a very highly popular article that's above normal for even the Slashdot effect (still, even now 3 days later). I finally had a chance to test out some of my tuning, and I'm really proud to say that WP survived without a single second of downtime :)

I'm not using WP-Cache or anything of that sort, and my blog issues 36-44 queries per requested page (thanks to WP's badly designed db layer that makes all these plugins issue separate queries for their stuff).

Just want to add a couple more things:
If you're using mod_gzip (or an IIS equivalent), make SURE it auto-disables under heavy load.
I'm using a proprietary IIS ISAPI extension that turns off gzip completely if CPU load hits 90% in order to free up cpu cycles for serving requests and dealing with the load.

On Windows, defragmenting actually makes a difference with regards to the amount of disk access needed to pull a file. I recommend PerfectDisk 8.

Switch your WP tables to Innodb. WP (by default) doesn't require any MyISAM-specific features. Innodb is far faster for most SQL usages (the exception being constant real-time logging of server requests, etc)

If you're using ISAPI PHP extensions, switch to FastCGI. If you're using CGI, that goes without saying. The first is because of reliability under load (PHP's ISAPI extensions are pure crap), the second is just pure performance. How-to: http://neosmart.net/blog/2007/opensource-on-windows-and-iis/

I benched lighttpd vs. IIS 6, and basically inconclusive results for issuing dynamic files. Once I expand to two servers however, I'll probably run lighttpd for serving downloads and images - big difference there!

I also have WP's inbuilt object caching enabled....

Thanks for your excellent tips, Robert. I think that about covers everything I've seen on the topic of enhancing WP performance without db caching..

Computer Guru
NeoSmart Technologies
http://neosmart.net/blog/


> -----Original Message-----
> From: wp-hackers-bounces at lists.automattic.com [mailto:wp-hackers-
> bounces at lists.automattic.com] On Behalf Of Robert Deaton
> Sent: Tuesday, April 17, 2007 11:25 PM
> To: wp-hackers at lists.automattic.com
> Subject: Re: [wp-hackers] MySQL MEMORY worth using for an object cache?
> 
> Okay, here's a few little tidbits from my experiences making WP scale
> while keeping it on a single server. These won't be useful for shared
> hosting, but the tidbits of information here might give you some
> insight into why I say what I say. :)
> 
> 1). APC alone will do wonders. I know this is no good for shared hosts
> but if they got the hint, it'd be absolutely wonderful. As times goes
> on, more and more shared hosts are and will be adpoting APC,
> especially around the PHP6 launch which is supposed to have APC or an
> equivalent object caching backend built in.
> 
> 2). Tweaking MySQL's internal query cache options and size can do
> wonders, however on a single box that expects to take a beating its
> not enough. Something is going to be needed on the frontend to help
> keep some of the load off the MySQL server for queries.
> 
> 3). Don't bother using memcached on a single box. The APC backend for
> WP's object cache will outperform it anyday, you don't need a seperate
> daemon running to handle the requests.
> 
> 4). Full page to disk caching does more harm than good on a server
> that is expecting a beating, especially one like digg or slashdot. The
> reason for this is that often times so many comments are posted that
> the server is constantly having to regenerate these pages and write
> them back to disk. Under heavy load, any moderately powered server
> won't usually have a hard time keeping up as far as CPU time goes, its
> file IO time, especially from disk (this is why APC helps immensely,
> WordPress loads a metric asston of files from disk otherwise). Using
> APC, the cost in CPU time for having to regenerate the pages easily
> outweighs the cost of having to pull the PHP source from disk. It
> isn't the recompilation that hurts WordPress the most, its disk IO.
> 
> 5). lighttpd > Apache. The end.
> 
> 
> Now that those are out of the way, what can we do to help the average
> user on a shared host not kill the server?
> 
> I am not opposed to checking for the existance of sysvsem, sysvshm,
> shmop, and APC, and attempting to use those as RAM storage. Writing
> backends for each of them is fairly straightforward, and when loading
> the object cache we could simply check for the existence of them and
> automatically load the first one that matches what we need. A constant
> in wp-config.php could be used to stop WordPress from automatically
> loading one of these backends.
> 
> MySQL MEMORY for an object cache is also an option, but a potentially
> risky one, especially for things like posts as they differ largely in
> size, and we do have the fixed row limitation. I guess we could
> implement it as an object cache and see how it performs, and try to
> find a good balance between a size for all user's posts and a size
> that doesn't eat too much unnecessary memory. I'm not sure, due to the
> nature of only being able to store 255 chars in column, if using it to
> do full page caches ala WP-Cache is a good option.
> 
> I believe there's some smaller optimizations that still may help. I
> personally believe throwing all the files together into one giant
> include would likely help. I also have a feeling that what Andy and I
> have been discussing recently as far as on-demand function loading may
> also help.
> 
> Our MySQL queries can still likely be optimized a bit, and this is key.
> 
> WP-Cache could use a rewrite from the ground up as well, imho. I wish
> I had more time to take this project under, but perhaps I will anyways
> in the coming weeks, or once summer starts.
> 
> There's a few other things but I think I've fueled this fire plenty
> for the moment.
> 
> --
> --Robert Deaton
> http://lushlab.com
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers