[development] Tip for large site scaling: tracker.module considered harmful

[John Handelaar]

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