On Tue, 2006-05-23 at 00:04, Jim C. Nasby wrote:
> I can't imagine how bad this would be if the database actually had
> hour-long reports that had to run... and luckily the system is quiet at
> night when pg_dump runs.
BTW, pg_dump is the only thing that stays in my way with the CLUSTER
strategy against long running transactions, because it locks (in shared
mode, but that's just enough) all dumped tables at the beginning. It
would be nice to have an "exclude-table" option on it. I actually
started working on a patch to allow that, I will make it just good
enough for my purpose (very poor C skills here). Would that be
interesting for others ?
> > Regardless, this type of queue problem can also be tackled by having your data layer persisting the input from the
webin memory (which maintains a low perceived response time to the client) and posting to the table as fast as the
databaseallows.
>
> Uh, and just what happens when your web front-end crashes then??
Well, actually we do have some processes where we use a buffer in the
web layer which collects ~1000 entries (or 5 minutes worth, whichever
comes first) in memory and then flushes them to the DB. But the
processing still happens in the DB, as we have a sizable cluster of web
machines and it is still more efficient to handle the processing
centrally than by each machine itself. So we still insert a batch of raw
entries and bulk process them on the DB using stored procedures.
If the web machine crashes, we loose some entries. But in those cases we
have bigger problems too...
We do have however a job table where the whole cluster puts tasks and
the machine which has free resources takes some to process. We first
used JMS for this purpose, but that was just not flexible enough to
accommodate our constraints and rules of load distribution across the
cluster. This is the real bitchy queue table.
Cheers,
Csaba.