On 29/08/2013 15:57, Leyne, Sean wrote:
>
> Tim,
>
> > We're currently running Classic, and I'm looking into the possibility of
> > switching to Superserver for the following reasons:
> >
> > (1) Garbage collection - we sometimes get queries, that normally
> complete in
> > reasonable time, taking many times as long, and one possible
> explanation is
> > garbage collection. We understand that Superserver has a background GC
> > thread, so the chances of a two second user operation being randomly
> > delayed by minutes can be reduced or eliminated.
> >
> > (2) Cache size. With frequent operations on a particular table occupying
> > around 1,500 pages the cache size of 150 that we're currently using is
> > believed to limit performance.
> >
> > So some questions:
> >
> > (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.)
>
> 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?

Given this scenario, what "good transaction management" can avoid the
garbage collection hitting the first run of this "occasional" thing
after thousands or millions of the type (1) transactions?

Or have we completely misunderstood what's going on?
>
> Cache is and isn't a limitation, yes the engine will need to go to the
> OS for pages not in FB cache. With a reasonable amount of RAM in your
> server, however, the OS itself will have most 'hot' database pages
> cached, so you will not actually be going to disk to get the data.
> Here the faster the media, the lower the cost of the reads will be.
>
Point taken. I've been doing some experiments with clearing the Linux
file system cache between queries, and this effect could certainly
explain some, but by no means all, of our problems.

--
Tim Ward



[Non-text portions of this message have been removed]