[development] Caching, caching, caching...

Gerhard Killesreiter gerhard at killesreiter.de
Fri Jul 14 16:42:04 UTC 2006

Dries Buytaert wrote:
> On 12 Jul 2006, at 00:01, Gerhard Killesreiter wrote:
>> Now the question is if we want to go down this road. To properly
>> invalidate the cache requires to implement all sorts of hooks (forum
>> module needs _comment and _user for example) which you would not need
>> otherwise. This complicates the code quite a bit.
> Personally, I'd not optimize with MySQL's query cache in mind.  If the 
> folks at MySQL improve their query cache implementation, our efforts 
> might have been moot. 

True. The most important aspect of mysql's query cache is that it isn't 
that well suited for a dynamic application such as Drupal the hit:miss 
ratio is only about 3.5 on drupal.org.

> I'd focus my energy on high-level improvements, 
> rather than low-level improvements.  That is, I'd focus architectural 
> changes that reduce the number of SQL queries or that simplify complex 
> queries (eg. less joins).  For example, the node table and 
> node_comment_statistics table have a one-on-one relation and could 
> potentially be merged.  I'm not sure what it would buy us, but it might 
> be worth it.

I think that the potential for such changes is rather small if we still 
want to keep a reasonably sane DB schema.

> Maybe we should run the modified devel.module on drupal.org to see what 
> the most expensive queries are?

SELECT * FROM url_alias, I'd be surprized if that had changed. The 
individual query is very short but there are probably 120 per page on 

>  You know, accumulate results over 
> multiple page views.  Do we have recent data query data from 
> drupal.org?  Let's start by identifying some key bottlenecks.

The forum queries that I cached in my patch are also among the more 
prominent ressource hogs on drupal.org, IIRC. Caching them might give us 
an immediate benefit. This of course only applies if the cache hit:miss 
ratio is significant. Maybe I should try them on drupal.org...


More information about the development mailing list