Purchasing the correct hardware: dual-core intel? Big cache?

[Michal Mertl]

Bill Moran wrote:
> > > >
> > > > I've been asked to make some hardware recommendations, I'm hoping some
> > > > folks on the list can make some suggestions.
> > > >
> > > > We're looking hard at getting either Intel dual-core procs, or getting
> > > > hyperthreaded procs with huge (8M) caches.
> > > >
> > > > We currently have a few dual proc Intel HT machines that we can test
> > > > out our workload on, and I'm trying to get a feel for how to determine
> > > > if a larger cache size will generate better performance than replacing
> > > > HT procs with full-blown dual-core procs.  We're looking at the 6850
> > > > from Dell, which supports both processor families:

I can't answer your question either but I'd like to raise a couple of
questions. If I won't help you I would at least (I hope) learn a little
from reactions :-).

As far as I know Intel boxes scale quite badly to larger SMP
configurations because of at least partially shared FSB which limits
memory throughput and which is also consumed great deal by cache
coherency maintenance traffic I believe. Dual core may help a little I
suppose (I would expect that Intel engineers made memory snooping a
little more efficient when accesses are going through one piece of
silicon (e.g. the cache coherency traffic's pressure on FSB should be
lower between the cores on the same die in comparison to separate
cores)). As you may have guessed by now I think that there's some
possibility that you would get better performance with AMD Opteron based
solution (I know that Dell doesn't normally sell it though) which
probably scaler better or even something more "exotic" (Sun Hardware -
UltraSparc, T processors).

Even when there isn't pressure on the I/O hardware in your case you may
have suboptimally configured PostgreSQL. I believe that PostgreSQL
processes do not tend to grow much (at least in comparison to other
RDBMS engines). I think that the explanation by psql people is that the
huge amounts of memory other engines are using is often used for caching
the data and that they (psql) believe that the operating system should
be doing that (otherwise you waste memory on caching both in the OS and
in the application). With huge databases you should at the end become
I/O bound (or at least there must be big I/O traffic) and then I would
agree with psql people that there's not much point replicating OS
caching in the DB engine. But if crucial parts of working data fit into
the memory I would expect that storing them in process should be
beneficial. I expect there must be at least a little data verification
and shuffling before psql uses the pages from the DB files. Maybe the
amount of this work is negligible with real disk I/O, but it may play
some role when no real disk I/O is involved. Another explanation why
PostgreSQL doesn't grow much may be that they use a lot of shared memory
and this is in general probably rather scarce resource (at least the
users have to configure something rather low-level to have it up and
running). What are your needs regarding the SQL engine anyway? Can't the
needs be fulfilled by something other than PostgreSQL? I hate to say
that, but possibly MySQL? Or can Firebird be better? I don't know
firebird much but I think that it is quite full-featured and although it
isn't such widespread it has great performance at least in some
benchmarks. What about the operating system? I haven't seen FreeBSD
mentioned in your question but I suppose you are running it (because you
write to a FreeBSD ML). What about Linux? (Open)Solaris? I think when
you are in such big need for performance you shouldn't try just one
solution. We (FreeBSDers) would of course like to help you to get the
best performance from our favorite OS but maybe you will help make
FreeBSD better if you find your application runs considerably better on
something else and someone may later find the reason.

Last I would like to only express my belief that bigger cache may in
fact help you but that nobody can probably say it in advance.


Regards

Michal