Thread: ideal server

On Friday 17 October 2008, "Brian Modra" <epailty@googlemail.com> wrote:
> I'm thinking of a 4xCPU and 20 Gigs and one of those large ram disks
> which has its own battery and writes all RAM to hard disk in the event of
> power failure.

Hey, if you really have enough cash for a RamSan you can do pretty much
anything ...


--
Alan

On Oct 17, 2008, at 2:19 PM, Brian Modra wrote:

> Hi,
> I have a server in production running postgresql, receiving 110 rows
> inserted per second, with some pretty intense queries involving long
> plpgsql.
> This server has 4Gigs of RAM and dual processor. Disk is Raid 5.
> I need more power, and am wondering what is the place really I need
> to put more emphasis? CPU, RAM, or disk?

Monitoring what your performance bottlenecks are on the current system
(after tuning postgresql reasonably) is probably the best way to get
an answer to that for your workload.

> I'm thinking of a 4xCPU and 20 Gigs and one of those large ram disks
> which has its own battery and writes all RAM to hard disk in the
> event of power failure.
>
> Obviously I need to first get a good sysadmin guy to configure
> postgres properly, but I am sure I also need a bigger host.
> Any advice will be appreciated.

Tune first. Then monitor. Then think about what hardware changes (or
system configuration changes) might be worth looking at.

I'm betting that at that point you'll find the problem is disk I/O,
but it may turn out not to be.

RAID5 isn't considered well suited to typical database I/O, compared
to RAID10 or similar.

At the moment you have system logging, database WAL and database data
all competing for the same I/O, meaning the drives are probably
seeking a lot. Some ways around that are to use a decent disk
controller with a (battery-backed) writeback cache to aggregate
writes, or to divide your disk traffic across different disks - for
instance, a dedicated RAID pair for the database WAL.

Cheers,
   Steve

On Fri, Oct 17, 2008 at 3:19 PM, Brian Modra <epailty@googlemail.com> wrote:
> Hi,
> I have a server in production running postgresql, receiving 110 rows
> inserted per second, with some pretty intense queries involving long
> plpgsql.
> This server has 4Gigs of RAM and dual processor. Disk is Raid 5.

You've hamstrung your RAID array right there with RAID-5, unless it's
almost all reads, in which case it might be ok, but not great.

> I need more power, and am wondering what is the place really I need to put
> more emphasis? CPU, RAM, or disk?

Ram > disk > cpu

> I'm thinking of a 4xCPU and 20 Gigs and one of those large ram disks which
> has its own battery and writes all RAM to hard disk in the event of power
> failure.

Hard to say if a RAM disk would help.  If your dataset already fits in
ram and you don't write much then a ram disk won't help.  What do
vmstat and iostat have to say on the issue?

> Obviously I need to first get a good sysadmin guy to configure postgres
> properly, but I am sure I also need a bigger host.
> Any advice will be appreciated.

Well, don't buy anything before you've identified your biggest
performance killer, memory, io, or CPU.  Memory is cheap, you might
find that putting 16 Gigs into your current server the performance
improves enough to keep you from needing a big shiny new server.  If
you do decide to get a shiny new server, get it with enough RAM to
cache your dataset, and a RAID controller with as many disks as you
can afford.  4 to 8 cpus are usually plenty unless you have a really
large parallel workload going on.

--
When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis

non-existent and/or improper DB design can impede your progress

You'll need to get stats pack configured, run explain plain on your selects to
determine which columns you will to need to index..

Martin Gainty
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.


> From: steve@blighty.com
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] ideal server
> Date: Fri, 17 Oct 2008 14:46:13 -0700
>
>
> On Oct 17, 2008, at 2:19 PM, Brian Modra wrote:
>
> > Hi,
> > I have a server in production running postgresql, receiving 110 rows
> > inserted per second, with some pretty intense queries involving long
> > plpgsql.
> > This server has 4Gigs of RAM and dual processor. Disk is Raid 5.
> > I need more power, and am wondering what is the place really I need
> > to put more emphasis? CPU, RAM, or disk?
>
> Monitoring what your performance bottlenecks are on the current system
> (after tuning postgresql reasonably) is probably the best way to get
> an answer to that for your workload.
>
> > I'm thinking of a 4xCPU and 20 Gigs and one of those large ram disks
> > which has its own battery and writes all RAM to hard disk in the
> > event of power failure.
> >
> > Obviously I need to first get a good sysadmin guy to configure
> > postgres properly, but I am sure I also need a bigger host.
> > Any advice will be appreciated.
>
> Tune first. Then monitor. Then think about what hardware changes (or
> system configuration changes) might be worth looking at.
>
> I'm betting that at that point you'll find the problem is disk I/O,
> but it may turn out not to be.
>
> RAID5 isn't considered well suited to typical database I/O, compared
> to RAID10 or similar.
>
> At the moment you have system logging, database WAL and database data
> all competing for the same I/O, meaning the drives are probably
> seeking a lot. Some ways around that are to use a decent disk
> controller with a (battery-backed) writeback cache to aggregate
> writes, or to divide your disk traffic across different disks - for
> instance, a dedicated RAID pair for the database WAL.
>
> Cheers,
> Steve
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

You live life beyond your PC. So now Windows goes beyond your PC. See how

On Fri, Oct 17, 2008 at 4:31 PM, Martin Gainty <mgainty@hotmail.com> wrote:
> non-existent and/or improper DB design can impede your progress
>
> You'll need to get stats pack configured, run explain plain on your selects
> to
> determine which columns you will to need to index..

I think you're confusing oracle and pgsql a bit there.  But yeah, he
needs to get all the stats collection stuff running and he should
consider a migration to 8.3 if he's not already there.  At least 8.2
where current_query in the pg_stats_activity table is very cheap and
can be turned on without slowing down an already slow server.

-- When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis