Re: Advice sought : new database server

On Wed, Mar 7, 2012 at 12:18 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Mon, Mar 5, 2012 at 10:56 AM, Craig James <cjames(at)emolecules(dot)com>
> wrote:
> > On Sun, Mar 4, 2012 at 10:36 AM, Rory Campbell-Lange
> > <rory(at)campbell-lange(dot)net> wrote:
> >>
> >> We do have complex transactions, but I haven't benchmarked the
> >> performance so I can't describe it. Few of the databases are at the many
> >> million row size at the moment, and we are moving to an agressive scheme
> >> of archiving old data, so we hope to keep things fast.
> >>
> >> However I thought 15k disks were a pre-requisite for a fast database
> >> system, if one can afford them? I assume if all else is equal the 1880
> >> controller will run 20-40% faster with 15k disks in a write-heavy
> >> application. Also I would be grateful to learn if there is a good reason
> >> not to use 2.5" SATA disks.
> >
> >
> > Without those benchmarks, you can't really say what "fast" means. There
> are
> > many bottlenecks that will limit your database's performance; the disk's
> > spinning rate is just one of them. Memory size, memory bandwidth, CPU,
> CPU
> > cache size and speed, the disk I/O bandwidth in and out, the disk RPM,
> the
> > presence of a BBU controller ... any of these can be the bottleneck. If
> you
> > focus on the disk's RPM, you may be fixing a bottleneck that you'll never
> > reach.
> >
> > We 12 inexpensive 7K SATA drives with an LSI/3Ware 9650SE and a BBU, and
> > have been very impressed by the performance. 8 drives in RAID10, two in
> > RAID1 for the WAL, one for Linux and one spare. This is on an 8-core
> system
> > with 12 GB memory:
> >
> > pgbench -i -s 100 -U test
> > pgbench -U test -c ... -t ...
> >
> > -c -t TPS
> > 5 20000 3777
> > 10 10000 2622
> > 20 5000 3759
> > 30 3333 5712
> > 40 2500 5953
> > 50 2000 6141
>
>
> those numbers are stupendous for 8 drive sata. how much shared
> buffers do you have?
>

It's actually 10 disks when you include the RAID1 for the WAL. Here are
the non-default performance parameters that might be of interest.

shared_buffers = 1000MB
work_mem = 128MB
synchronous_commit = off
full_page_writes = off
wal_buffers = 256kB
checkpoint_segments = 30

I also do this at boot time (on a 12GB system):

echo 4294967296 >/proc/sys/kernel/shmmax # 4 GB shared memory
echo 4096 >/proc/sys/kernel/shmmni
echo 1572864 >/proc/sys/kernel/shmall # 6 GB max shared mem
(block size is 4096 bytes)

We have two of these machines, and their performance is almost identical.
One isn't doing much yet, so if you're interested in other benchmarks (that
don't take me too long to run), let me know.

Craig