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