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

[Khalid B]

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 at 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
>
>