From: matt <matt@ymogen.net>
To: pgsql-performance@postgresql.org
Subject: Hardware recommendations to scale to silly load
Date: 27 Aug 2003 02:35:13 +0100
Message-ID: <1061948112.15800.53.camel@gibraltar.mattclark.net> (raw)
I'm wondering if the good people out there could perhaps give me some
pointers on suitable hardware to solve an upcoming performance issue.
I've never really dealt with these kinds of loads before, so any
experience you guys have would be invaluable. Apologies in advance for
the amount of info below...
My app is likely to come under some serious load in the next 6 months,
but the increase will be broadly predictable, so there is time to throw
hardware at the problem.
Currently I have a ~1GB DB, with the largest (and most commonly accessed
and updated) two tables having 150,000 and 50,000 rows.
A typical user interaction with the system involves about 15
single-table selects, 5 selects with joins or subqueries, 3 inserts, and
3 updates. The current hardware probably (based on benchmarking and
profiling) tops out at about 300 inserts/updates *or* 2500 selects per
second.
There are multiple indexes on each table that updates & inserts happen
on. These indexes are necessary to provide adequate select performance.
Current hardware/software:
Quad 700MHz PIII Xeon/1MB cache
3GB RAM
RAID 10 over 4 18GB/10,000rpm drives
128MB battery backed controller cache with write-back enabled
Redhat 7.3, kernel 2.4.20
Postgres 7.2.3 (stock redhat issue)
I need to increase the overall performance by a factor of 10, while at
the same time the DB size increases by a factor of 50. e.g. 3000
inserts/updates or 25,000 selects per second, over a 25GB database with
most used tables of 5,000,000 and 1,000,000 rows.
Notably, the data is very time-sensitive, so the active dataset at any
hour is almost certainly going to be more on the order of 5GB than 25GB
(plus I'll want all the indexes in RAM of course).
Also, and importantly, the load comes but one hour per week, so buying a
Starfire isn't a real option, as it'd just sit idle the rest of the
time. I'm particularly interested in keeping the cost down, as I'm a
shareholder in the company!
So what do I need? Can anyone who has (or has ever had) that kind of
load in production offer any pointers, anecdotes, etc? Any theoretical
musings also more than welcome. Comments upon my sanity will be
referred to my doctor.
If the best price/performance option is a second hand 32-cpu Alpha
running VMS I'd be happy to go that way ;-)
Many thanks for reading this far.
Matt
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: matt@ymogen.net
Subject: Re: Hardware recommendations to scale to silly load
In-Reply-To: <1061948112.15800.53.camel@gibraltar.mattclark.net>
References: <1061948112.15800.53.camel@gibraltar.mattclark.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox