Re: Hardware suggestions for maximum read performance

On Thu, May 2, 2013 at 6:35 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Thu, May 2, 2013 at 5:11 PM, Mike McCann <mccann(at)mbari(dot)org> wrote:
> > Hello,
> >
> > We are in the fortunate situation of having more money than time to help
> > solve our PostgreSQL 9.1 performance problem.
> >
> > Our server hosts databases that are about 1 GB in size with the largest
> > tables having order 10 million 20-byte indexed records. The data are
> loaded
> > once and then read from a web app and other client programs. Some of the
> > queries execute ORDER BY on the results. There are typically less than a
> > dozen read-only concurrent connections to any one database.
>

I wouldn't count on this being a problem that can be fixed merely by
throwing money at it.

How many rows does any one of these queries need to access and then ORDER
BY?

...

>
> > HP ProLiant DL360p Gen 8
> > Dual Intel Xeon 2.4GHz 4-core E5-2609 CPUs
> > 64GB RAM
> > 2x146GB 15K SAS hard drives
> > 3x200GB SATA SLC SSDs
> > + the usual accessories (optical drive, rail kit, dual power supplies)
>
> If your DB is 1G, and will grow to 10G then the IO shouldn't be any
> problem, as the whole db should be cached in memory.

But it can take a surprisingly long time to get it cached in the first
place, from a cold start.

If that is the problem, pg_prewarm could help.

Cheers,

Jeff