That Special Relationship With variable_set

Submitted by exaboy on Fri, 07/25/2014 - 11:01
That Special Relationship With variable_set

I find over and over many performance issues come down to this particular function - variable_set(). In all cases it is a module invoking this function, even under minimal load that proceeds to cause a Database stampede, backend 503s and outages. How can this be? let me show you. This is going to get deep quickly so do your best to follow along since this is really important to understand.

function variable_set($name, $value) { global $conf; db_merge('variable')->key(array('name' => $name))->fields(array('value' => serialize($value)))->execute(); cache_clear_all('variables', 'cache_bootstrap'); $conf[$name] = $value; }

Let's step through what this function is doing in Drupal 7. Firstly its calling a global on the $conf variable which ironically is an array representation of the variables table that Drupal uses though-out the life-time of the page execution. On every bootstrap this is key-value array is loaded in from the cache_bootstrap table if it exists if not the variables table.

The second line of code is doing an update on the variables table, although you wouldn't know that simply looking at the command. Using the db abstraction layer the db_merge generates a query that looks like this to the db:

SELECT 1 AS expressionFROM variable variableWHERE ( (name = 'drupal_css_cache_files') ) FOR UPDATE

So at this point we have updated the database with the latest version of the variable, next comes the crippling part. Then the entire variable cache is cleared since we have a new value by invoking cache_clear_all() emptying the cache_bootstrap table where the serialised variable array was cached.

The final line of code innocently sets the global $config key to the new value and the call is completed, so what is the problem here? Well basically now the function has exited and the page has hopefully rendered successfully we have been left with a empty cache_bootstrap bin.

If this were to happen on every single page request we are rendering our variable (bootstrap) caching completely useless. Queue the Achilles heal of Drupal caching, since if this event happens when you have multiple servers serving many PHP processes then your Database is going to get slammed with those special queries above. It is a best practice to use Innodb for your Drupal databases, however Innodb uses row level locks. The worse part is that because the SELECT FOR UPDATE creates a lock on the variables table every subsequent query has to wait for the lock to release, eventually you end up with a DEADLOCK and a website returning 503s. How DEADLOCKS occur read through this comment by Heine

So really variable_set is a very destructive method, which must only be called from a responsible operation triggered from the administration console or another event that controls the fallout that follows.