On an attempt to go directly to http://pirates.spy-hill.net/forum_thread.php?id=847, I've got a polite response

Database unavailable

Unable to connect to database - please try again later

Error: 1045
Access denied for user 'apache'@'localhost' (using password: NO)

Uptime

alvarez:
14:05:06 up 33 days, 6:06, 5 users, load average: 11.72, 4.91, 2.18

I'm seeing a pattern here. We get these occasional high load conditions when the database is full of work (done or otherwise). But not when the database is empty of work (as it was recently after a long lull).

It makes sense that the database daemon has to work harder when it has to sort through the Result tables, and this occasionally causes a backlog and load spike. It's intersting enough to study further. Thanks for noting it.

____________
-- Eric Myers

"Education is not the filling of a pail, but the lighting of a fire." -- William Butler Yeats

Again just now, and no load this time:

Database unavailable
Unable to connect to database - please try again later

Error: 1045
Access denied for user 'apache'@'localhost' (using password: NO)

Uptime
alvarez:
11:30:03 up 36 days, 3:31, 6 users, load average: 0.11, 0.31, 0.61

Spy Hill Research Cluster Status
Uptime & Load
alvarez up 36+03:35, 2.00,
grendel up 6+02:22, 0.35,
harrahs up 1+17:32, 0.11,
moonflower up 36+03:34, 0.13,
Wed Nov 7 11:33:56 EST 2007

Database server status
Querys/sec (avg): 14.780
Open tables: 64
Threads: 37
Uptime: 36 days 4 hours 31 min

Captain's Cabin temperature monitor
Current temperature is 69.5 F, as of 11:24 AM EST on Wednesday, 7 November 2007

Workflow status
State #
Total Workunits 36,088
Total Results 73,384
Results ready to send 0
Results in progress 37
Results ready for validation 36
Workunits waiting for validation 0
Workunits waiting for assimilation 0
Workunits waiting for deletion 472
Results waiting for deletion 982


CACHED 11:31 AM EST on 7 Nov 2007 (updated every 10 minutes)

To track this problem futher I've added the number of "slow" queries in the database section of the server status page. I previously didn't list this, as it was just a sum of all of them since the server was started, but now I divide by the uptime to get an average number of them per hour. We'll see how this does as we go through normal operations.

I have other changes in mind as well.

____________
-- Eric Myers

"Education is not the filling of a pail, but the lighting of a fire." -- William Butler Yeats

I have increased the maximum number of database connections from (presumably) 100 to 150, which I hope will cut down on the number of "database unavailable" errors and load spikes.

____________
-- Eric Myers

"Education is not the filling of a pail, but the lighting of a fire." -- William Butler Yeats

And again few times, one hour ago.

CACHED 4:59 AM EST on 12 Nov 2007 (updated every 10 minutes)
Database server status
Querys/sec (avg): 16.576
Slow queries/hr: 8.67
Threads: 34
Open tables: 64
Uptime: 2 days 14 hours 3 min

Captain's Cabin temperature monitor
Current temperature is 59.2 F,
as of 4:36 AM EST on Monday, 12 November 2007 .

Workflow status
State #
Total Workunits 25,280
Total Results 51,518
Results ready to send 0
Results in progress 682
Results ready for validation 482
Workunits waiting for **** 0

Uptime
alvarez:
05:00:05 up 40 days, 21:01, 2 users, load average: 1.07, 0.53, 0.84

Just had another one.

I couldn't even post to a thread. Got this as answer and was showing as logged off as well:

Thank for reporting these events. I wish they were not happening, and I hoped increasing the max clients to 150 would have fixed it, but it helps to know what's happening in any case. I'll have to think more about what might help fix this.

____________
-- Eric Myers

"Education is not the filling of a pail, but the lighting of a fire." -- William Butler Yeats

You could go to considerably higher max clients (500-1000 on Solaris or up to 4000 on some Linux MySQL setups), but setting the value higher will be irrelevant if your RAM is being maxed out. Since adding 50% more connections than before didn't help, it sounds like it might be specifc client activity rather than number of clients that is causing the problem. Are there any high RAM load activities that clients might be doing to eat up RAM now that wasn't happening before? Do the connection problems correlate with the addition of the mechanical turk (maybe too many clients are making rapid connections with this at times???)?


____________

You could go to considerably higher max clients (500-1000 on Solaris or up to 4000 on some Linux MySQL setups), but setting the value higher will be irrelevant if your RAM is being maxed out. Since adding 50% more connections than before didn't help, it sounds like it might be specifc client activity rather than number of clients that is causing the problem. Are there any high RAM load activities that clients might be doing to eat up RAM now that wasn't happening before? Do the connection problems correlate with the addition of the mechanical turk (maybe too many clients are making rapid connections with this at times???)?

I suspect the overall status page is the culprit. Not by itself, though it does do a lot with the database, but combined with the regular load of machines constantly hitting the scheduler to ask for new work. That, in turn, is likely higher than "normal" because Pirates workunits are both short and few...

That is why I was curious about the mechanical turk addition. It looks like the database is hit everytime a CAPTCHA is completed to award credit under the current reward scheme? If that is the case, then the additional load from the turk might be enough (in addition to the normal hits rather than by itself) to push the load over the maximum when several members of the crew are typing them in.

In the past the worst load spikes seemed to appear when we were releasing lots of work. I thought at first it was just the increased load of releasing the work, but I now think it's also the increased work required her hit to search the larger volume of data in the database.

It may also be because the clients try again sooner. When there is no work the average connection frequency is going to be close to 4 hours. When there is plenty of work that rate will drop dramatically, possibly nearly as short as the average task.