pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: What *_mem to increase when running CLUSTER
3+ messages / 3 participants
[nested] [flat]
* What *_mem to increase when running CLUSTER
@ 2005-08-25 19:24 Andrew Lazarus <andrew@pillette.com>
2005-08-25 13:56 ` Re: What *_mem to increase when running CLUSTER Steve Poe <spoe@sfnet.cc>
2005-08-25 20:19 ` Re: What *_mem to increase when running CLUSTER Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Andrew Lazarus @ 2005-08-25 19:24 UTC (permalink / raw)
To: pgsql-performance
Should I temporarily increase sort_mem, vacuum_mem, neither, or both
when doing a CLUSTER on a large (100 million row) table where as many as
half of the tuples are deadwood from UPDATEs or DELETEs? I have large
batch (10 million row) inserts, updates, and deletes so I'm not sure
frequent vacuuming would help.
Attachments:
andrew.vcf (text/x-vcard, 296B) download
begin:vcard
fn:Andrew Lazarus
n:Lazarus;Andrew
org:Pillette Investment Management;Research and Development
adr;dom:;;3028 Fillmore;San Francisco;CA;94123
email;internet:andrew@pillette.com
title:Director
tel;work:800-366-0688
tel;fax:415-440-4093
url:http://www.pillette.com
version:2.1
end:vcard
* Re: What *_mem to increase when running CLUSTER
2005-08-25 19:24 What *_mem to increase when running CLUSTER Andrew Lazarus <andrew@pillette.com>
@ 2005-08-25 13:56 ` Steve Poe <spoe@sfnet.cc>
1 sibling, 0 replies; 3+ messages in thread
From: Steve Poe @ 2005-08-25 13:56 UTC (permalink / raw)
To: andrew@pillette.com; +Cc: pgsql-performance
Andrew,
On Thu, 2005-08-25 at 12:24 -0700, Andrew Lazarus wrote:
> Should I temporarily increase sort_mem, vacuum_mem, neither, or both
> when doing a CLUSTER on a large (100 million row) table where as many as
> half of the tuples are deadwood from UPDATEs or DELETEs? I have large
> batch (10 million row) inserts, updates, and deletes so I'm not sure
> frequent vacuuming would help.
You may need to experiment with both. What version of Postgres? What is
the size of your database? How many concurrent users? If you're seeing
half of the tuples are dead, I look at checking your max_fsm_pages and
max_fsm_relations after a full vacuum analyze before doing too much with
sort mem.
Your mileage may vary.
Best of luck.
Steve Poe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: What *_mem to increase when running CLUSTER
2005-08-25 19:24 What *_mem to increase when running CLUSTER Andrew Lazarus <andrew@pillette.com>
@ 2005-08-25 20:19 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-08-25 20:19 UTC (permalink / raw)
To: andrew@pillette.com; +Cc: pgsql-performance
Andrew Lazarus <andrew@pillette.com> writes:
> Should I temporarily increase sort_mem, vacuum_mem, neither, or both
> when doing a CLUSTER on a large (100 million row) table
The only part of that job that can use lots of memory is the index
rebuilds. In recent PG versions maintenance_work_mem is the thing
to increase for an index build; previously sort_mem controlled it.
I forget when the changeover was; maybe 8.0.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-08-25 20:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-25 19:24 What *_mem to increase when running CLUSTER Andrew Lazarus <andrew@pillette.com>
2005-08-25 13:56 ` Steve Poe <spoe@sfnet.cc>
2005-08-25 20:19 ` Tom Lane <tgl@sss.pgh.pa.us>
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