[development] MYSQL queries and Modules

[nan wich]

When I develop modules, I have Devel turned on. One main thing I look for is 
repeated queries. Unfortunately, I find a lot of them, especially in other 
people's modules. I'm glad to see global caching available in D7 so that 
sub-functions don't have to repeat information gathering that another function 
has already done.

As a teacher once explained to me, "All generalities are false, including this 
one." Some of your statements are decent guidleines, but hardly universally 
true. For example, "avoid ... hook_init" -- sometimes it is necessary, so one 
must be discerning in applying this rule.

Indeed using cache is a balancing act. When I added caching to one of my 
modules, I checked its performance impact. Using the main cache table made 
things worse because cache_get queries were taking longer than no caching at 
all. So I created my own cache table and it solved the performance issue that I 
was attempting to fix. I still don't think it was a good substitue for a 
properly tuned database server, but few people have those.

 
Nancy
 
Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.



________________________________

From: Pierre Rineau (in response to Andreas Laesser)
> 
> is there a way to improve mysql queries from several modules? I've loaded 
> aprox. 40 modules and it slows the site terrible... 

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20110226/d0b3d791/attachment.html