Slow write queries for groups.drupal.org
moshe weitzman - March 16, 2008 - 14:01
| Project: | Drupal.org infrastructure |
| Component: | Database |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Narayan Newton |
| Status: | active |
Description
I often see significant delays when adding/inserting nodes to groups.drupal.org. Attached is a screenshot of the query log when I submitted a node edit to http://groups.drupal.org/node/9189/edit.
killes has admin to groups.drupal.org so he is able to see the query log after a similar edit. Others are welcome to test this. Please send me your groups.drupal.org user id so i can make you an admin.
Note that groups.drupal.org runs stock drupal5 - no master/slave. All queries hit masterdb. Further, I am apparently using innodb - even the search tables are innodb. Tweaks are welcome. I'd rather not run with the master/slave patch for a year.
| Attachment | Size |
|---|---|
| node edit.jpg.jpg | 299.4 KB |
#1
nnewton is now an admin as well. just submit that node edit and review the query log at the bottom. slow queries are in red. look for massively slow queries like the UPDATE node query.
#2
Thanks for this. I expected a slowdown soon, because of the number of test sites we have setup. This will soon be solved "correctly", but for the moment I'm going to take dumps of three of the test sites that arn't being used and will remove them from the DB pool.
Let me know if this starts causing issues for you, as I'll accelerate the DB restructuring that will fix this.
Also, when did you first notice this? (just for my own info)
#3
I can't really pinpoint a time when it started. Sorry.
Thanks for paying attention to this.
#4
Updated screenshot attached after getting rid of some cruft. Somewhat better times.
Note: Posting so I have a record to look at after I split things up..
#5
I am actually seeing similar delays when editing a node on drupal.org. This is a complete shot in the dark, but I wonder if an InnoDB config tweak is needed. For debugging this, it is probably easier to focus on groups.drupal.org since it receives less requests and thus should have less contention. it also is not running patched drupal.
#6
This seems intermittent, but I am fairly sure of the cause. I have three migrations planned and the one that should fix this was going to be third, but I think I'll be upping the priority of that if we are starting to see delays. If you see any exceptionally long delays, please let me know the time you saw them so I can make sure there arn't any other causes.
Thanks again for reporting this.
#7
Update on this. The redesign is complete and I'm now collecting stats to rebalance the resource allocations.