pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Shridhar Daithankar <shridhar@frodo.hserus.net>
To: Rosser Schwarz <rschwarz@totalcardinc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: atrocious update performance
Date: Tue, 16 Mar 2004 13:08:49 +0530
Message-ID: <4056AF09.9030003@frodo.hserus.net> (raw)
In-Reply-To: <001401c40acc$1e4e7180$2500fa0a@CardServices.TCI.com>
References: <001401c40acc$1e4e7180$2500fa0a@CardServices.TCI.com>

Rosser Schwarz wrote:

  > shared_buffers = 4096
> sort_mem = 32768
> vacuum_mem = 32768
> wal_buffers = 16384
> checkpoint_segments = 64
> checkpoint_timeout = 1800
> checkpoint_warning = 30
> commit_delay = 50000
> effective_cache_size = 131072

You didn't mention the OS so I would take it as either linux/freeBSD.

First of all, your shared buffers are low. 4096 is 64MB with 16K block size. I 
would say at least push them to 150-200MB.

Secondly your sort mem is too high. Note that it is per sort per query. You 
could build a massive swap storm with such a setting.

Similarly pull down vacuum and WAL buffers to around 512-1024 each.

I know that your problem is solved by using insert rather than updates. But I 
just want to point out that you still need to analyze the table to update the 
statistics or the further queres will not be exactly good.

And lastly, you can bundle entire thing including creating duplicate table, 
populating it, renaming original table etc in a single transaction and nobody 
will notice it. I am almost sure MS-SQL can not do that. Not many databases have 
trasact-safe DDLs out there..

  HTH

  Shridhar



view thread (39+ messages)  latest in thread

Message-ID: <4056AF09.9030003@frodo.hserus.net>
Permalink:  ../4056AF09.9030003@frodo.hserus.net/
Also on:    postgresql.org/message-id/4056AF09.9030003@frodo.hserus.net

 · 

reply

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: shridhar@frodo.hserus.net, rschwarz@totalcardinc.com
  Subject: Re: atrocious update performance
  In-Reply-To: <4056AF09.9030003@frodo.hserus.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