> > > (3) Do these motivations make sense?
> >
> > Yes and no. ;-]
> >
> > Classic does not provide the fastest performance, but it does provide
> > the most consistent multi-connection performance. It is currently (v3
> > will be changing this) the only engine which truly scales across CPUs.
> >
> This is something I don't understand. Superserver uses a thread per
> connection, no? So what is there to stop each thread running on its own CPU
> (until we run out of CPUs), thus scaling across CPUs in the expected fashion?
> - I couldn't find a clear answer to that in the documentation.
> (We're unlikely to have many more active connections than CPUs.)

At the moment, SuperServer uses pseudo-threading, not true threading.

> > Garbage Collection is performed actively, but if you have good
> > transaction management, the real overhead is not that bad. The biggest
> > cost of Garbage Collection is that it generates disk writes, which
> > will need to go to disk, which is a slow process. A good disk
> > controller cache with protected write-back cache will eliminate that cost.
> >
> Sorry, but I don't understand "if you have good transaction management the
> real overhead is not that bad".
>
> I need to do some more work, but what we think is going on at the moment
> is
>
> (1) there are lots of tiny transactions which update individual records in a
> table, and this goes on all the time
> (2) occasionally something happens which does a scan of that table (or
> otherwise looks at lots of records in a single transaction)
> (3) the first time this "occasional" thing happens it takes an appallingly long
> time and the stats show lots of page writes
> (4) if we immediately re-run this "occasional" thing it behaves more as we
> would expect.
>
> We think that the writes in (3) are a clue that there's garbage collection going
> on? Why else would a SELECT query cause lots of writes?

This sounds like you are running into the automation database sweep, what is the sweep interval for the database?

It should be disabled and replaced with schedule task/cron job, which performs a sweep during off-peak hours.


Sean