Tracker.module kills MySQL on large sites which permit commenting [#39351]

Site description: 8576 forum posts, with 63015 comments.

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
An INSERT/UPDATE on {comments} while those queries are trying to complete will be stalled, waiting for a full-table lock (MySQL + MyISAM).
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.

Comment	File	Size	Author
#5	ktracker.tgz	3.1 KB	dormael

Comments

Comment #1

magico commented 19 August 2006 at 19:10

Some feedback please...

Comment #2

magico commented 31 August 2006 at 14:46

Status:

Active

» Closed (fixed)

Comment #3

FlemmingLeer commented 18 September 2006 at 18:12

Version:	4.6.3	» 4.6.9
Status:	Closed (fixed)	» Active

possibly this is the cause of my errors on my site.

Even though I only some 2610 entries in the blogs section I get

too many connections from 80 - 120 visitors

and just tonight I got the the error
use databasename has already more than 'max_user_connections' active connections

That with only some 30 visitors !

I have disable the tracker.module as to see if that is the solution.

Comment #4

FlemmingLeer commented 24 September 2006 at 10:40

I have not seen the
too many connections
or
user databasename has already more than 'max_user_connections' active connections

since I disabled the tracker.module

I use php5 and mysql 4.0.24 running on debian

Status	File	Size
new	ktracker.tgz	3.1 KB

I had a similar problem with large number of nodes and comments like handelaar has.
It took so long to see tracker page, because mysql server was making large temporary disk table in tracker module page hooks.

So I set mysql server to use memory table as much as possible.
It seemed better but with many logged in users same problem happened for lack of temporary memory table.

I'm trying to make a tracker module replacement which is using single table for tracking page and no temporary table is required.
But my poor english and knowledge about drupal makes it longer to finish it. And my job too.

I attatch this sample(unfinished) module called ktracker.
In this module 'my recent posts' is not working yet.

This module is fully based on tracker.module except page, node, comment hooks.

I expect someone can improve tracker module better than this ktracker.