Drupal performance and the variable cache

Lately, several of the Drupal sites I work on with large amounts of traffic have been showing many sluggish queries. One query that kept constantly showing up was this one:

SELECT * FROM variable;

Drupal stores most of it's settings in the variable table, but to improve query performance it stores all the variables from this table into a single record inside the cache table. However, when a variable is changed, added or deleted that cache must be updated.

The reason the above query was getting executed so frequently was because of a variable_set() call inside the Views module that was run on every page load.

I've seen this mistake more than once, so keep in mind that you should only add, change or delete variables during administrative tasks.

Comments

Caching

I noticed when caching is enabled I get errors with certain image modules and an unstable production environment, so I am at a point where I am ready to turn caching off completely and deal with a slower drupal site that is more stable.

Good to see qualified drupal guys here in this area :) - I just migrated a pretty big site from a very old phpnuke install to Drupal 6. I love it!

errors with caching on

I can't ever recall seeing errors crop up with caching turned on. I'd be happy to help you try to figure out the source of the problem.

My very first CMS site used PHP Nuke. I believe that was circa 2003. That is a long time in internet years!

PHP Nuke was the thing back

PHP Nuke was the thing back in 2000 or so - I've had it running for nearly 10 years and it served me well, but it was time to move on.

The caching issues are probably related to other modules but not being an actual Drupal expert, I don't have the time to look into it and can't find the answer quickly. Maybe we can do lunch and discuss how you can help, if you are interested in at least taking a look.