I was suprised to see Michal Čihař suggesting that memcached wasn't helping his site enough.
I've been a huge fan of Danga's Memcached for the past year or two. I'm using it very heavily upon the Debian Administration website, and also upon my new dating site.
There are, of course, issues with making sure you flush the cache when things are updated, but I've got a good pair of test suites for that now :)
With a nice singleton accessor I can use the code as easily as:
my $cache = Singleton::Memcache->instance();
my $results = $cache->get( "recent_users" );
return( $results ) if ( $results );
# fetch from database
$cache->set( "recent_users", $results );
return( $results );
Obvious once you've done it a few times, but right now I can feel the difference if I disable the cache upon either of the sites. Page loadtime just drops.
I guess the effectiveness depends upon the site you're using, and how often things can be usefully cached. I know that for my uses I tend to have large items which would be expensive to fetch which occur on every page (such as "currently online users", or "recent weblogs".) That probably means I benefit more than others, still I've become so enamoured of the project I feel the need to pimp it a little!