On Thu, May 2, 2013 at 5:11 PM, Mike McCann <mccann@mbari.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.
>
> SELECTs for data are taking 10s of seconds. We'd like to reduce this to web
> app acceptable response times (less than 1 second). If this is successful
> then the size of the database will grow by a factor of ten - we will still
> want sub-second response times. We are in the process of going through the
> excellent suggestions in the "PostgreSQL 9.0 High Performance" book to
> identify the bottleneck (we have reasonable suspicions that we are I/O
> bound), but would also like to place an order soon for the dedicated server
> which will host the production databases. Here are the specs of a server
> that we are considering with a budget of $13k US:
>
> 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. I'd look at
whether or not you've got good query plans or not, and tuning them.
Things like setting random_cost to 1.something might be a good start,
and cranking up work mem to ~16M or so.