From: Rob Nagler <nagler@bivio.biz>
To: pgsql-performance@postgresql.org
Subject: Re: vacuum locking
Date: Fri, 17 Oct 2003 09:52:26 -0600
Message-ID: <16272.4154.489000.227676@gargle.gargle.HOWL> (raw)
In-Reply-To: <3F9006BF.40906@persistent.co.in>
References: <16272.318.870000.93584@gargle.gargle.HOWL>
<3F9006BF.40906@persistent.co.in>
Shridhar Daithankar writes:
> You should try 7.4 beta and pg_autovacuum which is a contrib module
> in CVS tip.
It's on our todo list. :)
How does pg_autovacuum differ from vacuumdb? I mean it seems to call
the vacuum operation underneath just as vacuumdb does. I obviously
didn't follow the logic as to how it gets there. :-)
> Make sure that you have FSM properly tuned. Bump it from defaults to
> suit your needs. I hope you have gone thr. this page for general
> purpose setting.
I didn't start vacuuming regularly until recently, so I didn't see
this problem.
> Assuming those were incremental figures, largest you have is ~8000
> tuples per 15 minutes and 26 pages. I think with proper FSM/shared
> buffers/effective cache and a pg_autovacuum with 1 min. polling
> interval, you could end up in lot better shape.
Here are the numbers that are different. I'm using 7.3:
shared_buffers = 8000
sort_mem = 8000
vacuum_mem = 64000
effective_cache_size = 40000
free says:
total used free shared buffers cached
Mem: 1030676 1005500 25176 0 85020 382280
-/+ buffers/cache: 538200 492476
Swap: 2096472 272820 1823652
It seems effective_cache_size is about right.
vacuum_mem might be slowing down the system? But if I reduce it,
won't vacuuming get slower?
max_fsm_relations is probably too low (the default in my conf file
says 100, probably needs to be 1000). Not sure how this affects disk
usage.
Here's the summary for the two active tables during a vacuum interval
with high activity. The other tables don't get much activity, and are
much smaller. As you see the 261 + 65 adds up to the bulk of the 5
minutes it takes to vacuum.
INFO: Removed 8368 tuples in 427 pages.
CPU 0.06s/0.04u sec elapsed 1.54 sec.
INFO: Pages 24675: Changed 195, Empty 0; Tup 1031519: Vac 8368, Keep 254, UnUsed 1739.
Total CPU 2.92s/2.58u sec elapsed 65.35 sec.
INFO: Removed 232 tuples in 108 pages.
CPU 0.01s/0.02u sec elapsed 0.27 sec.
INFO: Pages 74836: Changed 157, Empty 0; Tup 4716475: Vac 232, Keep 11, UnUsed
641.
Total CPU 10.19s/6.03u sec elapsed 261.44 sec.
How would vacuuming every minute finish in time? It isn't changing
much in the second table, but it's taking 261 seconds to wade through
5m rows.
Assuming I vacuum every 15 minutes, it would seem like max_fsm_pages
should be 1000, because that's about what was reclaimed. The default
is 10000. Do I need to change this?
Sorry to be so dense, but I just don't know the right values are.
Thanks muchly for the advice,
Rob
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: nagler@bivio.biz
Subject: Re: vacuum locking
In-Reply-To: <16272.4154.489000.227676@gargle.gargle.HOWL>
* 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