Sometimes when I submit a new post (story node) or update one, it take 10-15 minutes for the page to finish loading. I figured it was some problem with the database so I did a SHOW FULL PROCESSLIST the next time I noticed it happening. It was stuck deleting from the boost_cache_relationships table. Why is this taking so long and what can I do to fix this?

Here's part of the output from SHOW FULL PROCESSLIST showing the problem query:

| Id      | User    | Host      | db              | Command | Time | State          | Info                                                                                                 |
| 2208461 | upgrade | localhost | dane101_upgrade | Query   |   28 | Sending data   | SELECT SUM(score) FROM search_index WHERE word = 'when'                                              | 
| 2208478 | upgrade | localhost | dane101_upgrade | Query   | *1088* | updating       | **DELETE FROM boost_cache_relationships WHERE base_dir = 'cache/normal/www.dane101.com' AND page_callb**  | 
  • If you clear the boost cache, does saving speed up again? Are you using InnoDB for tables? Are you on shared hosting or your own box? – Nicholai Jul 30 '11 at 21:32
  • Hi Shane and welcome to Drupal Answers. A simple delete query should not be a problem, and it might not be the actual problem. Most likely there is some underlying problem somewhere. It's pretty much impossible to say what the problem is, with the current info provided. If I understand your data correct, it only took 1 sec to delete the record, long but not horrible. Anyways steps to help debugging would be to look at the error log, look at the watchdog or record queries with devel and review the time it took each query to execute. Also is this on your dev environment or a host site? – googletorp Jul 30 '11 at 21:34
  • I should have provided more information in my question. This is on a production site, private server. I'm using INNODB for the tables. The delete query took 1088 seconds which is over 15 minutes. I'll take a look at the watchdog and check the queries running with devel to get more information. THanks for the suggestions. – Shane Wealti Jul 30 '11 at 22:55

It appears you may be having the same issue as this person - http://drupal.org/node/1136822

May I suggest you apply the tweaks mentioned in the issue, and if it improves your performance, perhaps consider writing a patch to place back in the issue queue.

I have cross linked the drupal.org issue with this, so that everything is visible

|improve this answer|||||

Boost db usage is (currently) not suitable for site using Views and has large number of nodes. Check this issue http://drupal.org/node/715450 and the suggestion in #6

|improve this answer|||||
  • Giving this a try. I'm surprised the boost module page doesn't have some kind of warning about this. – Shane Wealti Aug 1 '11 at 17:20

Try to make an EXPLAIN on the DELETE query to see if it's using the index in a correct way.
Andrew Berry wrote an interesting post regarding this matter on Lullabot's blog: Slow Queries? Check the Cardinality of Your MySQL Indexes.

|improve this answer|||||

Generically & correctly handling views logic in terms of cache expiration is tricky to say the least. There's a reason that by default drupal does a clear_cache_all on node_save; makes worrying about old cached content showing up a thing of the past. I figured one could do better, and for my use case, boost's expiration logic works great with views and panels. For other peoples use cases it might not work as well. If your not using the latest dev, I recommend giving it a shot as it works better in this aspect.

Due to where I work, I have a fairly good idea in terms of what needs to happen to make this a better setup; yes we use boost, but mainly for the relational data it generates (the part your complaining about). Time is the issue. Been busy with more pressing issues; I've sunk the last 6 months into the Advanced CSS/JS Aggregation module and there is still a little bit more dev work to do on it.

The big plan is to move the complicated views/panels relational logic into sub-modules of the expire module, but thats still a long ways off. Getting one of the async js loaders working 100% with panels, wysiwyg & chrome (LABjs, HeadJS) will give us more bang for the buck from our perspective. AdvAgg is laying the foundation for better front end performance; & HeadJS is starting to take full advantage of that.

Long story short, I'm aware of the issues; but they are not affecting us at work thus I can't allocate much time to fixing them. Boost is a very critical bit of our infrastructure (controls varnish & other systems), so things will eventually happen; just don't hold your breath. BTW we are up to 600k nodes on one of our 70 databases so it really is a case of your mileage may vary.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.