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.