Re: Advice sought : new database server - Mailing list pgsql-performance

On 04/03/12, Scott Marlowe (scott.marlowe@gmail.com) wrote:
> On Sun, Mar 4, 2012 at 11:36 AM, Rory Campbell-Lange
> <rory@campbell-lange.net> wrote:
> > On 04/03/12, Scott Marlowe (scott.marlowe@gmail.com) wrote:
...
[Description of system with 2 * 4 core Xeons, 8GB RAM, LSI card with
4*15K SCSI drives in R10. We are looking for a new server to partner
with this one.]
...

> > We do have complex transactions, but I haven't benchmarked the
> > performance so I can't describe it.
>
> Yeah try to get a measurement of how many transactions per second
> you're running at peak load, and if you're currently IO bound or CPU
> bound.

Our existing server rarely goes above 7% sustained IO according to SAR.
Similarly, CPU at peak times is at 5-7% on the SAR average (across all 8
cores). I'm not clear on how to read the memory stats, but the average
kbcommit value for this morning's work is 12420282 which (assuming it
means about 12GB memory) is 4GB more than physical RAM. However the
system never swaps, probably due to our rather parsimonious postgres
memory settings.

> > 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.
>
> The key here is that your whole db can fit into memory.  48G is
> cutting it close if you're figuring on being at 40G in a year.  I'd
> spec it out with 96G to start.  That way if you want to set work_mem
> to 8 or 16M you can without worrying about running the machine out of
> memory / scramming your OS file system cache with a few large queries
> etc.

Thanks for this excellent point.

> > However I thought 15k disks were a pre-requisite for a fast database
> > system, if one can afford them?
>
> The heads have to seek, settle and THEN you ahve to wait for the
> platters to rotate under the head i.e. latency.
>
> > 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.
>
> The 10k 2.5 seagate drives have combined seek and latency figures of
> about 7ms, while the15k 2.5 seagate drives have a combined time of
> about 5ms.  Even the fastest 3.5" seagates average 6ms average seek
> time, but with short stroking can get down to 4 or 5.
>
> Now all of this becomes moot if you compare them to SSDs, where the
> seek settle time is measured in microseconds or lower.  The fastest
> spinning drive will look like a garbage truck next to the formula one
> car that is the SSD.  Until recently incompatabilites with RAID
> controllers and firmware bugs kept most SSDs out of the hosting
> center, or made the ones you could get horrifically expensive.  The
> newest generations of SSDs though seem to be working pretty well.

From your comments it appears there are 3 options:

    1. Card + BBU + SAS disks (10K/15K doesnt matter) + lots of RAM
    2. Card + BBU + Raptors + lots of RAM
    3. SSDs + lots of RAM

Is this correct? If my databases are unlikely to be IO bound might it not
be better to go for cheaper drive subsystems (i.e. option 2) + lots of
RAM, or alternatively SSDs based on the fact that we don't require much
storage space? I am unclear of what the options are on the
highly-reliable SSD front, and how to RAID SSD systems.

An ancillary point is that our systems are designed to have more rather
than fewer databases so that we can scale easily horizontally.

--
Rory Campbell-Lange
rory@campbell-lange.net

Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928