Drupal instance with over 5000 bean blocks and when a block is saved it calls block_flush_cache() which calls _block_rehash(). The query created by the _block_rehash() continues to show up in the mysqld-slow.log and may have been the catalyst for an outage.

As a result this patch transforms the query produced by the _block_rehash() function.
We’re changing the query from:

SELECT b.*
  FROM 
  block b
 WHERE (( (module = 'block') 
AND (delta = '7') )OR( (module = 'webform') 
AND (delta = 'client-block-268123') )OR( (module = 'webform') 
AND (delta = 'client-block-268129') )OR( (module = 'webform') 
AND (delta = 'client-block-292063') )OR( (module = 'apachesolr_search') 
AND (delta = 'mlt-001') )OR( (module = 'apachesolr_search') 
AND (delta = 'article_related_articles') )OR( (module = 'apachesolr_search') 
AND (delta = 'topic_related_topics')
…

to:

SELECT b.*
FROM.
block b
WHERE (( (module = 'block') 
AND (delta IN  ('7')) )OR( (module = 'webform') 
AND (delta IN  ('client-block-268129', 'client-block-292063')) )OR( (module = 'apachesolr_search')
…
Files: 
CommentFileSizeAuthor
#2 2411917_block_rehash.patch1.2 KBchaunceyt
PASSED: [[SimpleTest]]: [MySQL] 41,103 pass(es). View

Comments

chaunceyt’s picture

FileSize
1.23 KB
chaunceyt’s picture

FileSize
1.2 KB
PASSED: [[SimpleTest]]: [MySQL] 41,103 pass(es). View

The initial patch wouldn't patch core via drush make. Re-did the patch.

dcam’s picture

Version: 7.34 » 7.x-dev
Category: Feature request » Task
Status: Active » Needs review
Issue tags: +Performance

Thanks for your contribution! Just a couple of notes for the future:

1. Setting the issue status to Needs review will automatically trigger our Testbot to test any patches that you've submitted.
2. When you submit a patch for testing, make sure the Version is set to the current dev branch.

We'll see what Testbot says about your patch now.

Bogdan1988’s picture

Hi, I've been enhancing performance of one of our sites with devel query log, and found that _block_rehash() provides heavy query. We are using beans on our website. There are 2100 beans in query. Before implementing patch query takes 112ms after applying patch 13.8ms. Most probably results are not very precise but it seems to me that it is very good performance raise.

Thank you

Dane Powell’s picture

Status: Needs review » Reviewed & tested by the community

We've seen a significant performance boost as a result of this patch, on a site with ~10,000 bean blocks.

Although I would highly recommend anyone in this same situation also consider: #2577095: Move Block table integration to a submodule. Obviously it's only useful if you aren't already using beans as blocks.

David_Rothstein’s picture

Status: Reviewed & tested by the community » Fixed

Committed to 7.x - thanks!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.