From: Steve Atkins <steve@blighty.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance suggestions for an update-mostly database?
Date: Mon, 4 Oct 2004 11:00:36 -0700
Message-ID: <20041004180036.GA26390@gp.word-to-the-wise.com> (raw)
In-Reply-To: <200410041038.14571.josh@agliodbs.com>
References: <20041004174019.GA25671@gp.word-to-the-wise.com>
<200410041038.14571.josh@agliodbs.com>
On Mon, Oct 04, 2004 at 10:38:14AM -0700, Josh Berkus wrote:
> Steve,
>
> > I'm used to performance tuning on a select-heavy database, but this
> > will have a very different impact on the system. Does anyone have any
> > experience with an update heavy system, and have any performance hints
> > or hardware suggestions?
>
> Minimal/no indexes on the table(s). Raise checkpoint_segments and consider
> using commit_siblings/commit_delay if it's a multi-stream application.
> Figure out ways to do inserts instead of updates where possible, and COPY
> instead of insert, where possible. Put your WAL on its own disk resource.
Thanks.
> I'm a little curious as to what kind of app would be 95% writes. A log?
It's the backend to a web application. The applications mix of queries
is pretty normal, but it uses a large, in-core, write-through cache
between the business logic and the database. It has more than usual
locality on queries over short time periods, so the vast majority of
reads should be answered out of the cache and not touch the database.
In some ways something like Berkeley DB might be a better match to the
frontend, but I'm comfortable with PostgreSQL and prefer to have the
power of SQL commandline for when I need it.
Cheers,
Steve
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: steve@blighty.com
Subject: Re: Performance suggestions for an update-mostly database?
In-Reply-To: <20041004180036.GA26390@gp.word-to-the-wise.com>
References: <20041004174019.GA25671@gp.word-to-the-wise.com> <200410041038.14571.josh@agliodbs.com> <20041004180036.GA26390@gp.word-to-the-wise.com>
* 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