pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum settings
3+ messages / 3 participants
[nested] [flat]
* Vacuum settings
@ 2008-04-20 17:48 dforums <dforums@vieonet.com>
2008-04-21 15:03 ` Re: Vacuum settings Alvaro Herrera <alvherre@commandprompt.com>
2008-04-21 15:31 ` Re: Vacuum settings Guillaume Cottenceau <gc@mnc.ch>
0 siblings, 2 replies; 3+ messages in thread
From: dforums @ 2008-04-20 17:48 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
Hello,
I need wo advice on vacuum settings.
I have a quad core X5355 @ 2.66GHz with 8 Go of memory
1Q) Why autovaccum does not work, I have set the value to on in
postgresql.conf but when the server start it's still off !!!!
2Q) Here are my settings for vacuum, could you help me to optimise those
settings, at the moment the vacuum analyse sent every night is taking
around 18 h to run, which slow down the server performance.
# - Cost-Based Vacuum Delay -
vacuum_cost_delay = 5 # 0-1000 milliseconds
vacuum_cost_page_hit = 1000 # 0-10000 credits
vacuum_cost_page_miss = 1000 # 0-10000 credits
vacuum_cost_page_dirty = 120 # 0-10000 credits
vacuum_cost_limit = 20 # 0-10000 credits
# - Background writer -
bgwriter_delay = 50 # 10-10000 milliseconds between
rounds
bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers
scanned/round
bgwriter_lru_maxpages = 25 # 0-1000 buffers max written/round
bgwriter_all_percent = 0.333 # 0-100% of all buffers
scanned/round
bgwriter_all_maxpages = 50 # 0-1000 buffers max written/round
Thanks in advance for your helps
Regards
David
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Vacuum settings
2008-04-20 17:48 Vacuum settings dforums <dforums@vieonet.com>
@ 2008-04-21 15:03 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2008-04-21 15:03 UTC (permalink / raw)
To: dforums <dforums@vieonet.com>; +Cc: pgsql-performance
dforums wrote:
> Hello,
>
> I need wo advice on vacuum settings.
>
> I have a quad core X5355 @ 2.66GHz with 8 Go of memory
>
> 1Q) Why autovaccum does not work, I have set the value to on in
> postgresql.conf but when the server start it's still off !!!!
You need to turn stats_row_level on too.
> # - Cost-Based Vacuum Delay -
>
> vacuum_cost_delay = 5 # 0-1000 milliseconds
> vacuum_cost_page_hit = 1000 # 0-10000 credits
> vacuum_cost_page_miss = 1000 # 0-10000 credits
> vacuum_cost_page_dirty = 120 # 0-10000 credits
> vacuum_cost_limit = 20 # 0-10000 credits
The cost are all too high and the limit too low. I suggest resetting to
the default values, and figuring out a reasonable delay limit (your
current 5ms value seems a bit too low, but I think in most cases 10ms is
the practical limit due to sleep granularity in the kernel. In any
case, since the other values are all wrong I suggest just setting it to
10ms and seeing what happens).
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Vacuum settings
2008-04-20 17:48 Vacuum settings dforums <dforums@vieonet.com>
@ 2008-04-21 15:31 ` Guillaume Cottenceau <gc@mnc.ch>
1 sibling, 0 replies; 3+ messages in thread
From: Guillaume Cottenceau @ 2008-04-21 15:31 UTC (permalink / raw)
To: dforums <dforums@vieonet.com>; +Cc: pgsql-performance
dforums <dforums 'at' vieonet.com> writes:
> 2Q) Here are my settings for vacuum, could you help me to optimise
> those settings, at the moment the vacuum analyse sent every night is
> taking around 18 h to run, which slow down the server performance.
It's a lot of time for a daily job (and it is interesting to
vacuum hot tables more often than daily). With typical settings,
it's probable that autovacuum will run forever (e.g. at the end
of run, another run will already be needed). You should first
verify you don't have bloat in your tables (a lot of dead rows) -
bloat can be created by too infrequent vacuuming and too low FSM
settings[1]. To fix the bloat, you can dump and restore your DB
if you can afford interrupting your application, or use VACUUM
FULL if you can afford blocking your application (disclaimer:
many posters here passionately disgust VACUUM FULL and keep on
suggesting the use of CLUSTER).
Ref:
[1] to say whether you have bloat, you can use
contrib/pgstattuple (you can easily add it to a running
PostgreSQL). If the free_percent reported for interesting
tables is large, and free_space is large compared to 8K, then
you have bloat;
another way is to dump your database, restore it onto another
database, issue VACUUM VERBOSE on a given table on both
databases (in live, and on the restore) and compare the
reported number of pages needed. The difference is the
bloat.
live=# VACUUM VERBOSE interesting_table;
[...]
INFO: "interesting_table": found 408 removable, 64994 nonremovable row versions in 4395 pages
restored=# VACUUM VERBOSE interesting_table;
[...]
INFO: "interesting_table": found 0 removable, 64977 nonremovable row versions in 628 pages
=> (4395-628)*8/1024.0 MB of bloat
(IIRC, this VACUUM output is for 7.4, it has changed a bit
since then)
--
Guillaume Cottenceau
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-04-21 15:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-20 17:48 Vacuum settings dforums <dforums@vieonet.com>
2008-04-21 15:03 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-04-21 15:31 ` Guillaume Cottenceau <gc@mnc.ch>
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