Thanks, further comments below. 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]
