pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Performance suggestions for an update-mostly database?
5+ messages / 3 participants
[nested] [flat]

* Performance suggestions for an update-mostly database?
@ 2004-10-04 17:40 Steve Atkins <steve@blighty.com>
  2004-10-04 17:38 ` Re: Performance suggestions for an update-mostly database? Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Steve Atkins @ 2004-10-04 17:40 UTC (permalink / raw)
  To: pgsql-performance

I'm putting together a system where the operation mix is likely to be
>95% update, <5% select on primary key.

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?

Cheers,
  Steve



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Performance suggestions for an update-mostly database?
  2004-10-04 17:40 Performance suggestions for an update-mostly database? Steve Atkins <steve@blighty.com>
@ 2004-10-04 17:38 ` Josh Berkus <josh@agliodbs.com>
  2004-10-04 18:00   ` Re: Performance suggestions for an update-mostly database? Steve Atkins <steve@blighty.com>
  2004-10-04 19:22   ` Re: Performance suggestions for an update-mostly database? Jim C. Nasby <decibel@decibel.org>
  0 siblings, 2 replies; 5+ messages in thread

From: Josh Berkus @ 2004-10-04 17:38 UTC (permalink / raw)
  To: Steve Atkins <steve@blighty.com>; +Cc: pgsql-performance

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.

I'm a little curious as to what kind of app would be 95% writes.  A log?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Performance suggestions for an update-mostly database?
  2004-10-04 17:40 Performance suggestions for an update-mostly database? Steve Atkins <steve@blighty.com>
  2004-10-04 17:38 ` Re: Performance suggestions for an update-mostly database? Josh Berkus <josh@agliodbs.com>
@ 2004-10-04 18:00   ` Steve Atkins <steve@blighty.com>
  2004-10-04 19:02     ` Re: Performance suggestions for an update-mostly database? Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Steve Atkins @ 2004-10-04 18:00 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Performance suggestions for an update-mostly database?
  2004-10-04 17:40 Performance suggestions for an update-mostly database? Steve Atkins <steve@blighty.com>
  2004-10-04 17:38 ` Re: Performance suggestions for an update-mostly database? Josh Berkus <josh@agliodbs.com>
  2004-10-04 18:00   ` Re: Performance suggestions for an update-mostly database? Steve Atkins <steve@blighty.com>
@ 2004-10-04 19:02     ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2004-10-04 19:02 UTC (permalink / raw)
  To: Steve Atkins <steve@blighty.com>; +Cc: pgsql-performance

Steve,

> 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.

Well, if data corruption is not a concern, you can always turn off 
checkpointing.   This will save you a fair amount of overhead.

You could also look at telegraphCQ.   It's not prodcucton yet, but their idea 
of "streams" as data sources really seems to fit with what you're talking 
about.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Performance suggestions for an update-mostly database?
  2004-10-04 17:40 Performance suggestions for an update-mostly database? Steve Atkins <steve@blighty.com>
  2004-10-04 17:38 ` Re: Performance suggestions for an update-mostly database? Josh Berkus <josh@agliodbs.com>
@ 2004-10-04 19:22   ` Jim C. Nasby <decibel@decibel.org>
  1 sibling, 0 replies; 5+ messages in thread

From: Jim C. Nasby @ 2004-10-04 19:22 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Steve Atkins <steve@blighty.com>; pgsql-performance

And obviously make sure you're vacuuming frequently.

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.
> 
> I'm a little curious as to what kind of app would be 95% writes.  A log?
> 
> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2004-10-04 19:22 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-04 17:40 Performance suggestions for an update-mostly database? Steve Atkins <steve@blighty.com>
2004-10-04 17:38 ` Josh Berkus <josh@agliodbs.com>
2004-10-04 18:00   ` Steve Atkins <steve@blighty.com>
2004-10-04 19:02     ` Josh Berkus <josh@agliodbs.com>
2004-10-04 19:22   ` Jim C. Nasby <decibel@decibel.org>

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