[drupal-devel] [feature] Block query consolidation

[m3avrck]

Issue status update for 
http://drupal.org/node/30801
Post a follow up: 
http://drupal.org/project/comments/add/30801

 Project:      Drupal
 Version:      cvs
 Component:    block.module
 Category:     feature requests
 Priority:     normal
 Assigned to:  Anonymous
 Reported by:  Allie Micka
 Updated by:   m3avrck
 Status:       patch (ready to be committed)
 Attachment:   http://drupal.org/files/issues/drupal_14.patch (3.31 KB)

Ok here is an updated patch, along with the blocks table modification of
adding a PRIMARY KEY(module,delta). Spent sometime on #mysql discussing
best practice for this table and adding a primary key is the best bet.
Any indexes would not improve performance since this table will
probably almost never have more than 30 rows in it. The primary key
itself is slightly neglible as well, however, if any query were to
perform advanced queries on this table, the primary key would benefit,
so in best practice, we should have this as well.


I'll try to get some benchmarks up soon backing these simple changes.




m3avrck



Previous comments:
------------------------------------------------------------------------

Fri, 09 Sep 2005 20:12:03 +0000 : Allie Micka

Attachment: http://drupal.org/files/issues/block_query.patch (1.25 KB)

The blocks module is running a separate query for each region, e.g.:


   SELECT * FROM blocks WHERE theme = 'bluemarine' AND status = 1 AND
region='left' ORDER BY weight, module
   SELECT * FROM blocks WHERE theme = 'bluemarine' AND status = 1 AND
region='right' ORDER BY weight, module
   SELECT * FROM blocks WHERE theme = 'bluemarine' AND status = 1 AND
region='header' ORDER BY weight, module
   SELECT * FROM blocks WHERE theme = 'bluemarine' AND status = 1 AND
region='footer' ORDER BY weight, module


Ultimately, we're going to need all of these so let's save a few
queries.  I have changed the query to:


   SELECT * FROM blocks WHERE theme = 'bluemarine' AND status = 1 ORDER
BY region, weight, module


And set all results to the static $blocks array on the first pass.




------------------------------------------------------------------------

Sun, 11 Sep 2005 23:50:53 +0000 : m3avrck

Attachment: http://drupal.org/files/issues/block.module_5.patch (1.27 KB)

Awesome patch! Noticed the version was set wrong so changed this to CVS.


Also, rerolled the patch, there was an extraneous $region in there no
longer needed. Reducing 4 queries is definetly a great start in
optimizing Drupal :)


Take this patch along with this one (might need to be updated now with
this query rewrite I'm thinking): http://drupal.org/node/27157 ... and
we'll have Drupal's loading of blocks pretty much maxed out with
optimizations, then on to the slooow alias ones... ;)




------------------------------------------------------------------------

Mon, 12 Sep 2005 14:24:56 +0000 : Souvent22

Gotta love when something is just logical. Makes sense, and the patch
worked. I'd say this is ready. Get these blocks sped up a bit. This
coupled with 'block primary key' patch should make the blocks solid.




------------------------------------------------------------------------

Mon, 12 Sep 2005 14:36:00 +0000 : Dries

Has this been benchmarked?  I would expect this to be faster, but it's
good practice nonetheless.  Feel free to throw that other block query
patch into the mix.




------------------------------------------------------------------------

Mon, 12 Sep 2005 14:49:15 +0000 : m3avrck

Dries, I ran some simple benchmarks with the devel.module. Queries with
a default install were reduced from 125 to 121. Query times averaged
around 50ms and this was reduced to roughly 47-48ms. It's not a *huge*
savings, but on a a dev machine, with only one user, this is still good
nonetheless. I imagine on a hard hit Drupal site this would start to
scale up nicely with time/load savings. I'll reroll a patch that
combines the two in a few.




------------------------------------------------------------------------

Mon, 12 Sep 2005 15:26:08 +0000 : Dries

This patch reduces the SQL overhead but adds some CPU-overhead and
memory-overhead.  Hence, it's better to benchmark the number of
requests/second (for example using ab or ab2) ...