[development] MYSQL queries and Modules

[Pierre Rineau]

Le vendredi 25 février 2011 à 10:46 +0100, Andreas Laesser a écrit :
> Hi @all
> 
> is there a way to improve mysql queries from several modules? I've loaded 
> aprox. 40 modules and it slows the site terrible... 
> 
> There is also the thing that if you load a site as an anonymous drupal does 
> also the mysql queries for logged in users.
> 
> Does anybody have such problems? 
> If yes, may we can change experiences.
> 
> Thanks for help 
> regards Andreas

The "dynamic" oriented approche makes most of the developers unable to
tell when they should or should not make queries.

The cache oriented API (i.e. using cache_get() and cache_set()
everywhere) is often wrong designed. cache_get() and cache_set() used
several time on small caches will generate a lot of queries where the
real caching points should be something like only entry and output
points of APIs. Sometime, recalculate a small amount of data is faster
than fetching the result from a cache (it needs profiling to know
exactly when and where).

To reduce queries, use modules that relies on field as often as you can,
because CCK will cache full node objects with fields content therefore
will reduce greatly number of queries.

Do profiling, remove modules that does those queries. If you use Views a
lot, it worth the shot switching to node display mode, using build
modes, it will make CCK cache everything (field content etc) instead of
using custom view based display mode that could (in certain use cases
only) generate a lot more queries.

Avoid node messing modules that uses a lot hook_nodeapi() because those
calls will never be cached anywhere, since CCK does only cache field
content and such.

When you get using more than ~30 modules, you may have to refactor your
site and write some bits of custom glue instead of using too generic
modules that attempt to do all consistency checks for you, you will gain
a lot in term of performances.

Logged in users are always a problem and there is no magical solution.
Avoid modules that uses hook_init(), they will also slow the bootstrap
process therefore create a constant bottleneck in the bootstrap itself,
even for non logged users and 404/403 hits.

Pierre.