Re: New hardware thoughts - Mailing list pgsql-performance

From Ben Suffolk
Subject Re: New hardware thoughts
Date
Msg-id 21C32FBB-479E-4AA0-BE39-B14E293A548A@vanilla.net
Whole thread Raw
In response to Re: New hardware thoughts  (Shane Ambler <pgsql@007Marketing.com>)
List pgsql-performance
Cheers Shane,

> Sounds like you have a very good idea of what to expect. Are these
> solid stats or certain estimates? Estimates can vary when it comes
> time to start.

The figures all come from how my application interacts with the
database when an event happens, so the scaling of operations to each
other is accurate, the number of operations is based on an estimate
of the user interactions with the system, and the figures I quote are
actually peak figures based on some fairly reliable research. If
anything its more likely to be lower then higher, but I like to air
on the side of caution, and so its important for know that I can
sustain this throughput, and have an easy upgrade path in the
hardware I choose now to help if I do need to be able to cope with
more load in the future.

Although I suspect the next step would be to move things like the
logging into a separate database to relieve some of the load.

> I would think 2 will cope with what you describe but what about in
> 12 months time? Can you be sure your needs won't increase? And will
> the cost of 4 CPU's cut your other options? If all 50 users may be
> running the 3rd part at the same time (or is that your 50 trans. a
> second?) then I'd consider the 4.

The 50 connections is pretty much a constant from the distributes
application servers, and only some about 10 of them will be
responsible for running the transactions , the others being more
related to the reading, and logging, and thus mainly staying in the
idle state. So I would think I am better off keeping the CPU sockets
spare, and adding them if needed. Thus enabling more budget for
memory / disks.

> 8GB is a good starting point for a busy server but a few hundred $
> on the extra ram can make more difference than extra disks (more
> for the reading part than writing).

I guess any spare budget I have after the disks should be spend on as
much memory as possible.

> What you describe plans several times 300 inserts to logging plus
> 150 inserts and 50 updates and 1 read a second plus occasional
> reads to the logging and user data.
> Will it be raw data fed in and saved or will the server be
> calculating a majority of the inserted data? If so go for the 4 cpu's.

The inserts are all raw (pre calculated) data, so not work needed by
the database server its self bar the actual insert.

> Generally more disks at slower speed - 2 10K disks in raid 0 is
> faster than 1 15K disk. More disks also allow more options.

Yes I figured striped slow disks are faster then non striped fast
disks, but what about 8 striped slow disks vs 5 striped fast disks?
How do you calculate what the maximum throughput of a disk system
would be? I know that a bit academic really as I need to split the
disks up for the transfer log and the table data, so the large number
of slower disks is as you suggest better anyway.

> I might consider RAID 5 with 8 disks but would lean more for 2 RAID
> 10 setups. This can give you the reliability and speed with system
> and xlog on one and data on the other.

Assuming I go with 8 disks, I guess the real question I have no idea
about is the speed relationship of the transfer log to the table
space data. In other words if I have 2 disks in a raid 1 mirrored
pair for the transfer log (and the O/S, but can't see it needing to
use disk once boots really - so long as it does not need swap space)
and 6 disks in a raid 1 + 0 striped mirrored pair would that be
better than having 2 equal raid 1 + 0 sets of 4 disks.

Clearly if the requirements on the transfer log are the same as the
table data then 2 equal 1+0 sets are better, but if the table data is
at least 1/3 more intensive that the transfer log I think the 2 + 6
should be better. Does anybody know which it is?

> Sounds to me like you have it worked out even if you are a little
> indecisive on a couple of finer points.

Thanks, I guess its more about validating my thoughts are more or
less right, and helping tweak the bits that could be better.

Regards

Ben



pgsql-performance by date:

Previous
From: Dave Cramer
Date:
Subject: Re: New hardware thoughts
Next
From: Ben Suffolk
Date:
Subject: Re: New hardware thoughts