DELETE FROM cache_block locking issues. [#258209]

Is there any way to optimize this? Noticed excessive locking today due to this query.

# Execution count: 9 times between 2008-05-13 07:06:29 and 2008-05-13 10:11:56.
# Column : avg | max | sum
# Query time : 62.9 | 151 | 566
# Lock time : 34.0 | 148 | 306
# Rows examined: 0.0 | 0 | 0
# Rows sent : 0.0 | 0 | 0
# User@Host: dbuser[dbuser] @ localhost []

DELETE FROM cache_block WHERE cid LIKE "bc_1::%" OR cid LIKE "bc_2::%" OR cid LIKE "bc_3::%" OR cid LIKE "bc_4::%" OR cid LIKE "bc_5::%"
OR cid LIKE "bc_6::%" OR cid LIKE "bc_7::%" OR cid LIKE "bc_9::%" OR cid LIKE "bc_10::%" OR cid LIKE "bc_11::%" OR cid LIKE "bc_12::%" OR
cid LIKE "bc_13::%" OR cid LIKE "bc_14::%" OR cid LIKE "bc_15::%" OR cid LIKE "bc_16::%" OR cid LIKE "bc_17::%" OR cid LIKE "bc_18::%" O
R cid LIKE "bc_19::%" OR cid LIKE "bc_20::%" OR cid LIKE "bc_21::%" OR cid LIKE "bc_22::%" OR cid LIKE "bc_23::%" OR cid LIKE "bc_24::%"
OR cid LIKE "bc_25::%" OR cid LIKE "bc_26::%" OR cid LIKE "bc_27::%" OR cid LIKE "bc_40::%" OR cid LIKE "bc_41::%" OR cid LIKE "bc_42::%"
OR cid LIKE "bc_43::%" OR cid LIKE "bc_45::%" OR cid LIKE "bc_46::%" OR cid LIKE "bc_47::%" OR cid LIKE "bc_48::%" OR cid LIKE "bc_49::%
" OR cid LIKE "bc_50::%" OR cid LIKE "bc_51::%" OR cid LIKE "bc_52::%" OR cid LIKE "bc_53::%" OR cid LIKE "bc_54::%" OR cid LIKE "bc_56::
%" OR cid LIKE "bc_57::%" OR cid LIKE "bc_58::%" OR cid LIKE "bc_59::%" OR cid LIKE "bc_61::%" OR cid LIKE "bc_62::%" OR cid LIKE "bc_63:
:%" OR cid LIKE "bc_64::%" OR cid LIKE "bc_65::%" OR cid LIKE "bc_66::%" OR cid LIKE "bc_67::%" OR cid LIKE "bc_68::%" OR cid LIKE "bc_69
::%" OR cid LIKE "bc_70::%" OR cid LIKE "bc_71::%" OR cid LIKE "bc_72::%" OR cid LIKE "bc_73::%" OR cid LIKE "bc_74::%" OR cid LIKE "bc_7
5::%" OR cid LIKE "bc_76::%" OR cid LIKE "bc_77::%" OR cid LIKE "bc_78::%" OR cid LIKE "bc_80::%" OR cid LIKE "bc_81::%" OR cid LIKE "bc_
82::%" OR cid LIKE "bc_85::%" OR cid LIKE "bc_87::%" OR cid LIKE "bc_88::%" OR cid LIKE "bc_89::%" OR cid LIKE "bc_90::%" OR cid LIKE "bc
_91::%" OR cid LIKE "bc_92::%" OR cid LIKE "bc_93::%" OR cid LIKE "bc_97::%" OR cid LIKE "bc_98::%" OR cid LIKE "bc_99::%" OR cid LIKE "b
c_100::%" OR cid LIKE "bc_101::%" OR cid LIKE "bc_102::%" OR cid LIKE "bc_106::%" OR cid LIKE "bc_107::%" OR cid LIKE "bc_108::%" OR cid
LIKE "bc_109::%" OR cid LIKE "bc_110::%" OR cid LIKE "bc_120::%" OR cid LIKE "bc_121::%" OR cid LIKE "bc_122::%" OR cid LIKE "bc_124::%"
OR cid LIKE "bc_125::%" OR cid LIKE "bc_126::%" OR cid LIKE "bc_127::%" OR cid LIKE "bc_128::%" OR cid LIKE "bc_132::%" OR cid LIKE "bc_1
33::%" OR cid LIKE "bc_134::%" OR cid LIKE "bc_135::%" OR cid LIKE "bc_136::%" OR cid LIKE "bc_138::%" OR cid LIKE "bc_139::%" OR cid LIK
E "bc_140::%" OR cid LIKE "bc_141::%" OR cid LIKE "bc_142::%" OR cid LIKE "bc_143::%" OR cid LIKE "bc_144::%" OR cid LIKE "bc_146::%" OR
cid LIKE "bc_147::%" OR cid LIKE "bc_148::%" OR cid LIKE "bc_151::%" OR cid LIKE "bc_152::%" OR cid LIKE "bc_153::%" OR cid LIKE "bc_154:
:%" OR cid LIKE "bc_155::%" OR cid LIKE "bc_156::%" OR cid LIKE "bc_157::%" OR cid LIKE "bc_158::%" OR cid LIKE "bc_159::%" OR cid LIKE "
bc_160::%" OR cid LIKE "bc_162::%" OR cid LIKE "bc_163::%" OR cid LIKE "bc_165::%" OR cid LIKE "bc_166::%";

Comments

Comment #1

funana CreditAttribution: funana commented 23 May 2008 at 03:18

No real solution by I would recommend you to uncheck the following fields in every cached block, even if it's deactivated (!):

Refresh when:
A node is added/updated/deleted
A comment is added/updated/deleted
A user is added/updated/deleted
A user logs in our out

Maybe a lot of work but I think this will help.

See http://drupal.org/node/204029#comment-854139 for more information.

Comment #2

alimc29 CreditAttribution: alimc29 commented 28 May 2008 at 18:22

I have this query in my slowqueries log as well, but it does all of these conditions from
WHERE cid LIKE "bc_1::%" OR cid LIKE "bc_2::%" OR cid LIKE "bc_3::%" OR cid LIKE "bc_4::%" ...to... OR cid LIKE "bc_75449::%";
And I only have 8 entries in that table.

1.
Is there any reason the query can't just be something like:
WHERE cid LIKE "bc_%";
All 8 entries I have in this table start like that and would be returned as results anyway with the original query.

2.
in response to that comment, which is also on http://drupal.org/node/204029, is there a way in the code of the module to turn this OFF by default for all those options?

Comment #3

yonailo CreditAttribution: yonailo commented 4 August 2008 at 09:24

Responses:

You must delete only the appropiate 'delta' block.

The line 65 of blockcache.module should be changed to this:

        '#default_value' => variable_get('bc_refresh_'. $delta, array()),

This way, by default none of the refresh options will be selected.

Comment #4

giorgio79 CreditAttribution: giorgio79 commented 6 September 2008 at 19:44

Hi,

I am using the latest block cache code and my mysqlserver is killed because of this query.

I enabled caching a week ago, and set all my cached blocks to never expire, meaning I left all options unticked, and the lifetime blank. During this week the block cache grew to almost 500 MB, I guess because of the blob column type.

Now when I tried to login as admin a couple of days later, the server starts to hang.

I go check the mysqladmin processlist command and I see this exact same query DELETE ... running for 50-60 seconds by now.

Can anyone advice what to do apart from disabling this module?

Comment #5

giorgio79 CreditAttribution: giorgio79 commented 14 September 2008 at 14:54


Responses:

1)

You must delete only the appropiate 'delta' block.

What is a delta block?
You mean the unused blocks added by Block Cache and that have a name starting with "CACHED"?

Comment #6

giorgio79 CreditAttribution: giorgio79 commented 5 November 2008 at 14:17

I think this issue with a patch

http://drupal.org/node/148225

is directly related to this one.

Comment #7

yonailo CreditAttribution: yonailo commented 5 November 2008 at 22:30

giorgio,

I was meaning that it does not help to do something like DELETE FROM ... WHERE cid LIKE "bc_%";
because that would remove all the cached blocks... but don't take into account my previous comment (number 1) beucase now that I am reading it again I don't understand myself. I don't understand why you
are getting all those "OR"s in the query, this is sure a bug that should be fixed, but I am not using the block_cache so I can't help you more, sorry!