Tip for large site scaling: tracker.module considered harmful
First off, apols for the 'considered harmful' cliché. Description: http://mbr.org Forum posts: 8576, with 63015 comments 1. Some tracker module queries, particularly the one which shows 'all my posts' to individual users, stop functioning on large data sets. In this case, queries ran for >600s 2. An INSERT/UPDATE on {comments} while those queries are trying to complete will be stalled, waiting for a full-table lock (MySQL + MyISAM). 3. While there is an INSERT/UPDATE waiting to execute, *all* other SELECTS on the same table are frozen and marked as 'Locked' in MySQL's process list until 1) and 2) are complete. Add 100 logged-in users, simmer for about 3 minutes -> MySQL server drops dead with 'Too many connections' (whether using mysql_pconnect() or not). Workaround: under *no* circumstances enable tracker.module on a heavily-used site which gives posting rights to visitors. There is, of course, a fix out there waiting to be written in either the query or the schema, but while I'm OK with adding indexes to tables, multi-table indexing is outside my comfort zone. [cross-posted to tracker: http://drupal.org/node/39351 ] The *good* news: disabling the tracker has reverted server load back down to 0.1 - 0.3 (from >30.00) and MBR is once again serving about 60,000 pages per day without complaint. Serious request for Infrastructure people: this sort of thing doesn't affect many of us, but it kills us when bad things happen. I'm not the first person to ask --- can we please have a "Scaling / Large site issues" forum on Drupal.org to collect war stories? jh
Seems like your site should move to a more modern ACID table database, for example switching from MyISAM to Inno should help, since it provides row level locking. In conjunction with this, a few changes to the db layer will be needed (e.g. locking the whole table should not be done). On 11/29/05, John Handelaar <john@userfrenzy.com> wrote:
First off, apols for the 'considered harmful' cliché.
Description: http://mbr.org Forum posts: 8576, with 63015 comments
1. Some tracker module queries, particularly the one which shows 'all my posts' to individual users, stop functioning on large data sets. In this case, queries ran for >600s
2. An INSERT/UPDATE on {comments} while those queries are trying to complete will be stalled, waiting for a full-table lock (MySQL + MyISAM).
3. While there is an INSERT/UPDATE waiting to execute, *all* other SELECTS on the same table are frozen and marked as 'Locked' in MySQL's process list until 1) and 2) are complete.
Add 100 logged-in users, simmer for about 3 minutes -> MySQL server drops dead with 'Too many connections' (whether using mysql_pconnect() or not).
Workaround: under *no* circumstances enable tracker.module on a heavily-used site which gives posting rights to visitors.
There is, of course, a fix out there waiting to be written in either the query or the schema, but while I'm OK with adding indexes to tables, multi-table indexing is outside my comfort zone.
[cross-posted to tracker: http://drupal.org/node/39351 ]
The *good* news: disabling the tracker has reverted server load back down to 0.1 - 0.3 (from >30.00) and MBR is once again serving about 60,000 pages per day without complaint.
Serious request for Infrastructure people: this sort of thing doesn't affect many of us, but it kills us when bad things happen. I'm not the first person to ask --- can we please have a "Scaling / Large site issues" forum on Drupal.org to collect war stories?
jh
Khalid B wrote:
Seems like your site should move to a more modern ACID table database, for example switching from MyISAM to Inno should help, since it provides row level locking.
Well, yeah, but as I tried to get across, there are two parts to this. Fixing MyISAM is one (not something we can or should address), fixing indexless mega-queries is quite another. tracker.module (on this site) hit over 9 million rows on a query which may return none. Ideally it should hit indexes and return none right now, not in 11 minutes' time. Comment module, as shipped, is similarly expensive, but more easily fixed (a simple "add index" or two fixed that). I know Dries is having a think about my forum request before tomorrow, so I'm hoping for a yea then. Meanwhile I think: that, generally speaking, we *do* have the answers for these performance bumps-in-the-road; that it would help us to make a place to consolidate those discussions rather than having them dotted around private blogs, bug reports and uncommitted patches; and that making that place visible might (as a side effect) make it more obvious to the casual observer that Drupal can punch at this weight. OK. That's one more argument in favour than I intended to make today. :) jh [ Responsible for evolt.org , mbr.org , abctales.com ]
On 29-Nov-05, at 5:22 PM, John Handelaar wrote:
I know Dries is having a think about my forum request before tomorrow, so I'm hoping for a yea then. Meanwhile I think: that, generally speaking, we *do* have the answers for these performance bumps-in-the-road; that it would help us to make a place to consolidate those discussions rather than having them dotted around private blogs, bug reports and uncommitted patches; and that making that place visible might (as a side effect) make it more obvious to the casual observer that Drupal can punch at this weight.
Yep. I'm in favour of the forum, and I think a *private* mailing list would be useful as well. The forum to discuss and toss options back and forth, the private mailing list (scale@drupal.org? :P) for allowing lots of big site maintainers to have private conversations. OK, ok....the mailing list doesn't HAVE to be private (I wouldn't mind either way), just thinking it might allow things to be a little more open. What do you think, John? -- Boris Mann Vancouver 778-896-2747 San Francisco 415-367-3595 SKYPE borismann http://www.bryght.com
Boris Mann wrote:
Yep. I'm in favour of the forum, and I think a *private* mailing list would be useful as well. The forum to discuss and toss options back and forth, the private mailing list (scale@drupal.org? :P) for allowing lots of big site maintainers to have private conversations.
I'd settle for a list if it's what we can get. Don't really see a need for privacy though. jh
At 6:02 PM -0800 29/11/05, Boris Mann wrote:
Yep. I'm in favour of the forum, and I think a *private* mailing list would be useful as well. The forum to discuss and toss options back and forth, the private mailing list (scale@drupal.org? :P) for allowing lots of big site maintainers to have private conversations.
I used to participate in the "biglinux" mailing list. It was for mainly ISPs trying to roll out reliable infrastructure based on the 1.0 and 1.2 kernels. We found that almost all our hacks and "war stories" translated into committed improvements to the kernel. There are very few optimizations that, if beneficial to large sites, wouldn't also make small sites faster. If they do happen to make a small site a little slower, well, it's only a small site and doesn't generate much load anyway :) I guess what I'm getting at is that rather than a list or a book chapter on drupal.org I would prefer to see war stories posted as bugs to the issues tracker with either a patch or a description of the work-around. Things like tables missing indexes on key columns, un-optimized database queries, wasteful algorithms... they're all bugs. ...Richard.
Richard Archer wrote:
I guess what I'm getting at is that rather than a list or a book chapter on drupal.org I would prefer to see war stories posted as bugs to the issues tracker with either a patch or a description of the work-around.
I'd assume we'd do that too. But that on its own is *not* an answer for those of us who run into this stuff when a site suddenly stops functioning. Needles, haystacks. Bugs are (rightly) filed against their components, not their target audience. jh
Things like tables missing indexes on key columns, un-optimized database queries, wasteful algorithms... they're all bugs.
I agree that these are bugs, and that they need fixing. John, can you file issues for those, and mark them critical? You indicated that you fixed a comment.module issue, but we haven't seen an issue or patch yet? Please work with us to fix these bugs. Thanks. I'm still undecided about creating a dedicated mailing list or forum for this, but I'm looking to hear people opinion on this. That said, I'm going to setup two more mailing lists: consulting@drupal.org and themes@drupal.org. An overview of the existing list can be found at http://lists.drupal.org/. -- Dries Buytaert :: http://www.buytaert.net/
Op woensdag 30 november 2005 09:11, schreef Dries Buytaert:
I'm still undecided about creating a dedicated mailing list or forum for this, but I'm looking to hear people opinion on this.
I think in this case the "reference" part is more important (Where others can read what has been done and tried). Hence I think a forum is a better choice then a ML I am very interested in a forum where I can read other peoples tricks and stories. But, maybe we should make it a bit more general (the con is that stuff is still spread around then). And make a forum dedicated for large Drupal deployments. Whether that is optimisation of one big site or a hosting provider sharing tips on how to deal with hosting issues. Bèr -- | Bèr Kessels | webschuur.com | website development | | Jabber & Google Talk: ber@jabber.webschuur.com | http://bler.webschuur.com | http://www.webschuur.com |
On 11/30/05 3:11 AM, Dries Buytaert wrote:
Things like tables missing indexes on key columns, un-optimized database queries, wasteful algorithms... they're all bugs.
I agree that these are bugs, and that they need fixing. John, can you file issues for those, and mark them critical? You indicated that you fixed a comment.module issue, but we haven't seen an issue or patch yet? Please work with us to fix these bugs. Thanks.
yes please.
I'm still undecided about creating a dedicated mailing list or forum for this, but I'm looking to hear people opinion on this. That said, I'm going to setup two more mailing lists: consulting@drupal.org and themes@drupal.org. An overview of the existing list can be found at http://lists.drupal.org/.
I agree with the others that having a forum or someplace more readily available / properly archived is most useful (i.e. over a mailing list)... but one thing that Boris mentioned about privacy is an interesting point, as larger installations may (for good reason) be unwilling to discuss issues until they've been resolved... in which case a they might be more inclined to share details to a closed audience ... but perhaps IRC (or something more "real time" ) is sufficient (with results / recap then posted to the forum or wherever is deemed appropriate). basically i see 2 issues : 1) large site owners / admins / maintainers having a resource for "prior art" best practices, etc. 2) a place for those people to get attention to fix a current issue. Though perhaps #2 isn't the community's problem ... (i.e. for the realm of the marketplace). Bottom line is, large installations tell a lot about where weaknesses are because they generate scenarios that are hard to duplicate in a pure testing environment... capturing this info is pretty important, imo. -- James Walker :: http://walkah.net/ :: xmpp:walkah@walkah.net
This is a good example of why one might want to use PostgreSQL instead of MySQL for a Drupal site. ..chrisxj Khalid B wrote:
Seems like your site should move to a more modern ACID table database, for example switching from MyISAM to Inno should help, since it provides row level locking.
In conjunction with this, a few changes to the db layer will be needed (e.g. locking the whole table should not be done).
On 11/29/05, John Handelaar <john@userfrenzy.com> wrote:
1. Some tracker module queries, particularly the one which shows 'all my posts' to individual users, stop functioning on large data sets. In this case, queries ran for >600s
2. An INSERT/UPDATE on {comments} while those queries are trying to complete will be stalled, waiting for a full-table lock (MySQL + MyISAM).
3. While there is an INSERT/UPDATE waiting to execute, *all* other SELECTS on the same table are frozen and marked as 'Locked' in MySQL's process list until 1) and 2) are complete.
Add 100 logged-in users, simmer for about 3 minutes -> MySQL server drops dead with 'Too many connections' (whether using mysql_pconnect() or not).
Workaround: under *no* circumstances enable tracker.module on a heavily-used site which gives posting rights to visitors.
participants (8)
-
Boris Mann -
Bèr Kessels -
Chris Johnson -
Dries Buytaert -
James Walker -
John Handelaar -
Khalid B -
Richard Archer