pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAuto Vacuum question?
11+ messages / 7 participants
[nested] [flat]
* Auto Vacuum question?
@ 2007-06-04 14:12 Charles.Hou <ivan.hou@msa.hinet.net>
2007-06-04 14:49 ` Re: Auto Vacuum question? Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Charles.Hou @ 2007-06-04 14:12 UTC (permalink / raw)
To: pgsql-admin
i set the auto vacuum option to enable. but my database size(hard-
disk
space) still increased from 420MB to 440MB in 8 hours. most of the
operations in this database are the "Select" query command, just few
of "Update or Insert".
why it can be increased so strongly?
after i executed the command " vaccumdb -f -z testdb", but the size
just decreased 1 or 2MB...
what's the problem?
how to know which command(select,insert....) takes the hurge loading
to database in this time?
computer environment:
System: Red Hat Enterprise 4
Postgresql Server: 8.0.13
count of client connected : about 100 pc
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Auto Vacuum question?
2007-06-04 14:12 Auto Vacuum question? Charles.Hou <ivan.hou@msa.hinet.net>
@ 2007-06-04 14:49 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-06-05 00:07 ` Re: Auto Vacuum question? Charles.Hou <ivan.hou@msa.hinet.net>
0 siblings, 1 reply; 11+ messages in thread
From: Alvaro Herrera @ 2007-06-04 14:49 UTC (permalink / raw)
To: Charles.Hou <ivan.hou@msa.hinet.net>; +Cc: pgsql-admin
Charles.Hou wrote:
> i set the auto vacuum option to enable. but my database size(hard-
> disk
> space) still increased from 420MB to 440MB in 8 hours. most of the
> operations in this database are the "Select" query command, just few
> of "Update or Insert".
> why it can be increased so strongly?
> after i executed the command " vaccumdb -f -z testdb", but the size
> just decreased 1 or 2MB...
> what's the problem?
It is normal that the tables grow a bit to a stationary state of dead
space (generated by UPDATE and DELETE). However it is not normal if it
loses track of that dead space. One thing you can and should do is
adjust the autovacuum settings (for example lowering the vacuum scale
factor and threshold values). Another is to correctly set the FSM
values.
Have you done both?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Auto Vacuum question?
2007-06-04 14:12 Auto Vacuum question? Charles.Hou <ivan.hou@msa.hinet.net>
2007-06-04 14:49 ` Re: Auto Vacuum question? Alvaro Herrera <alvherre@commandprompt.com>
@ 2007-06-05 00:07 ` Charles.Hou <ivan.hou@msa.hinet.net>
2007-06-05 12:47 ` Re: Auto Vacuum question? Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Charles.Hou @ 2007-06-05 00:07 UTC (permalink / raw)
To: pgsql-admin
On 6 4 , 10 49 , alvhe...@commandprompt.com (Alvaro Herrera) wrote:
> Charles.Hou wrote:
> > i set the auto vacuum option to enable. but my database size(hard-
> > disk
> > space) still increased from 420MB to 440MB in 8 hours. most of the
> > operations in this database are the "Select" query command, just few
> > of "Update or Insert".
> > why it can be increased so strongly?
> > after i executed the command " vaccumdb -f -z testdb", but the size
> > just decreased 1 or 2MB...
> > what's the problem?
>
> It is normal that the tables grow a bit to a stationary state of dead
> space (generated by UPDATE and DELETE). However it is not normal if it
> loses track of that dead space. One thing you can and should do is
> adjust the autovacuum settings (for example lowering the vacuum scale
> factor and threshold values). Another is to correctly set the FSM
> values.
>
> Have you done both?
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
it's my postgresql.conf
autovacuum = on # enable autovacuum
subprocess?
autovacuum_naptime = 20 # time between autovacuum
runs, in secs
autovacuum_vacuum_threshold = 1 # min # of tuple updates
before
autovacuum_analyze_threshold = 1 # min # of tuple updates
before
autovacuum_vacuum_scale_factor = 1 # fraction of rel size before
autovacuum_analyze_scale_factor = 1 # fraction of rel size before
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay
for
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit
for
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Auto Vacuum question?
2007-06-04 14:12 Auto Vacuum question? Charles.Hou <ivan.hou@msa.hinet.net>
2007-06-04 14:49 ` Re: Auto Vacuum question? Alvaro Herrera <alvherre@commandprompt.com>
2007-06-05 00:07 ` Re: Auto Vacuum question? Charles.Hou <ivan.hou@msa.hinet.net>
@ 2007-06-05 12:47 ` Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 11+ messages in thread
From: Alvaro Herrera @ 2007-06-05 12:47 UTC (permalink / raw)
To: Charles.Hou <ivan.hou@msa.hinet.net>; +Cc: pgsql-admin
Charles.Hou wrote:
> On 6 4 , 10 49 , alvhe...@commandprompt.com (Alvaro Herrera) wrote:
> > It is normal that the tables grow a bit to a stationary state of dead
> > space (generated by UPDATE and DELETE). However it is not normal if it
> > loses track of that dead space. One thing you can and should do is
> > adjust the autovacuum settings (for example lowering the vacuum scale
> > factor and threshold values). Another is to correctly set the FSM
> > values.
> autovacuum_vacuum_scale_factor = 1 # fraction of rel size before
> autovacuum_analyze_scale_factor = 1 # fraction of rel size before
These values are rather strange; they mean it would wait until the table
is populated by as many dead tuples as there are live tuples, before
vacuuming it. This is probably too much. Try decreasing the vacuum
scale factor to 0.2 or maybe 0.1 and see how it goes.
--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"La fuerza no está en los medios físicos
sino que reside en una voluntad indomable" (Gandhi)
^ permalink raw reply [nested|flat] 11+ messages in thread
* auto_vacuum question
@ 2008-03-17 14:31 Mark Steben <msteben@autorevenue.com>
2008-03-17 14:57 ` Re: auto_vacuum question Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Mark Steben @ 2008-03-17 14:31 UTC (permalink / raw)
To: pgsql-admin
We upgraded from postgres 7.4.5 to 8.2.5 a couple weekends ago. I just
turned auto vacuum on today.
I manually inserted 4 entries in PG_AUTOVACUUM with enabled = 'f' to exclude
these larger tables
That we manually vacuum every night. But the first tables that autovacuum
chooses to vacuum are
These 4 tables. Am I missing something?
Another, possibly related question: the 4 tables mentioned above are
vacuumed via a scheduled
Daemon that invokes the VACUUMDB shell. But the LAST_VACUUM column in
PG_STAT_USER_TABLES
Is not updated to reflect this vacuum. Other tables vacuumed manually
during the day do have this
LAST_VACUUM column updated. The autovacuum does populate the
LAST_AUTOVACUUM column
For these tables.
Any insights? Thanks
Mark Steben
Senior Database Administrator
@utoRevenueT
A Dominion Enterprises Company
480 Pleasant Street
Suite B200
Lee, MA 01238
413-243-4800 Home Office
413-243-4809 Corporate Fax
msteben <blocked::mailto:name@autorevenue.com> @autorevenue.com
Visit our new website at
<blocked::http://www.autorevenue.com/; www.autorevenue.com
IMPORTANT: The information contained in this e-mail message is confidential
and is intended only for the named addressee(s). If the reader of this
e-mail message is not the intended recipient (or the individual responsible
for the delivery of this e-mail message to the intended recipient), please
be advised that any re-use, dissemination, distribution or copying of this
e-mail message is prohibited. If you have received this e-mail message in
error, please reply to the sender that you have received this e-mail message
in error and then delete it. Thank you.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: auto_vacuum question
2008-03-17 14:31 auto_vacuum question Mark Steben <msteben@autorevenue.com>
@ 2008-03-17 14:57 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-03-17 15:17 ` Re: auto_vacuum question Mark Steben <msteben@autorevenue.com>
0 siblings, 1 reply; 11+ messages in thread
From: Alvaro Herrera @ 2008-03-17 14:57 UTC (permalink / raw)
To: Mark Steben <msteben@autorevenue.com>; +Cc: pgsql-admin
Mark Steben wrote:
> We upgraded from postgres 7.4.5 to 8.2.5 a couple weekends ago. I just
> turned auto vacuum on today.
>
> I manually inserted 4 entries in PG_AUTOVACUUM with enabled = 'f' to exclude
> these larger tables
> That we manually vacuum every night. But the first tables that autovacuum
> chooses to vacuum are
>
> These 4 tables. Am I missing something?
My guess is that you set the rest of the settings to 0, particularly for
the freeze settings. In that case, try changing them to -1.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: auto_vacuum question
2008-03-17 14:31 auto_vacuum question Mark Steben <msteben@autorevenue.com>
2008-03-17 14:57 ` Re: auto_vacuum question Alvaro Herrera <alvherre@commandprompt.com>
@ 2008-03-17 15:17 ` Mark Steben <msteben@autorevenue.com>
0 siblings, 0 replies; 11+ messages in thread
From: Mark Steben @ 2008-03-17 15:17 UTC (permalink / raw)
To: 'Alvaro Herrera' <alvherre@commandprompt.com>; +Cc: pgsql-admin
Thanks Alvaro - did as you suggested and looks to be working better
Mark Steben
Senior Database Administrator
@utoRevenueT
A Dominion Enterprises Company
480 Pleasant Street
Suite B200
Lee, MA 01238
413-243-4800 Home Office
413-243-4809 Corporate Fax
msteben@autorevenue.com
Visit our new website at
www.autorevenue.com
IMPORTANT: The information contained in this e-mail message is confidential
and is intended only for the named addressee(s). If the reader of this
e-mail message is not the intended recipient (or the individual responsible
for the delivery of this e-mail message to the intended recipient), please
be advised that any re-use, dissemination, distribution or copying of this
e-mail message is prohibited. If you have received this e-mail message in
error, please reply to the sender that you have received this e-mail message
in error and then delete it. Thank you.
-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Monday, March 17, 2008 10:58 AM
To: Mark Steben
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] auto_vacuum question
Mark Steben wrote:
> We upgraded from postgres 7.4.5 to 8.2.5 a couple weekends ago. I just
> turned auto vacuum on today.
>
> I manually inserted 4 entries in PG_AUTOVACUUM with enabled = 'f' to
exclude
> these larger tables
> That we manually vacuum every night. But the first tables that autovacuum
> chooses to vacuum are
>
> These 4 tables. Am I missing something?
My guess is that you set the rest of the settings to 0, particularly for
the freeze settings. In that case, try changing them to -1.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 11+ messages in thread
* auto vacuum question
@ 2010-11-12 19:44 Dinesh Bhandary <dbhandary@iii.com>
2010-11-12 20:05 ` Re: auto vacuum question Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Dinesh Bhandary @ 2010-11-12 19:44 UTC (permalink / raw)
To: pgsql-admin
Hi All -
I am working to tune the auto vacuum in 8.4 and 8.2. I am finding that
some of the tables are not getting vacuumed even though they exceed
analyze/vacuum threshold. Do you know where auto vacuum process looks to
get the number of rows deleted/updated/inserted?
pg_stat_user_tables have only the cumulative stats. I am interested in
finding the most current ( incremental stats).
Thanks a lot.
Dinesh
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: auto vacuum question
2010-11-12 19:44 auto vacuum question Dinesh Bhandary <dbhandary@iii.com>
@ 2010-11-12 20:05 ` Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 11+ messages in thread
From: Joshua D. Drake @ 2010-11-12 20:05 UTC (permalink / raw)
To: dbhandary@iii.com; +Cc: pgsql-admin
On Fri, 2010-11-12 at 11:44 -0800, Dinesh Bhandary wrote:
> Hi All -
>
> I am working to tune the auto vacuum in 8.4 and 8.2. I am finding that
> some of the tables are not getting vacuumed even though they exceed
> analyze/vacuum threshold. Do you know where auto vacuum process looks to
> get the number of rows deleted/updated/inserted?
In 8.2 your problem is likely that autovacuum is single worker (or that
you have the idle-in-transaction problem).
In 8.4 you should not have this problem, if you do increase your
autovacuum_max_workers (and make sure you don't have an
idle-in-transaction problem).
> pg_stat_user_tables have only the cumulative stats. I am interested in
> finding the most current ( incremental stats).
Pg doesn't provide this by default, you would have to install mrtg etc..
Joshua D. Drake
>
> Thanks a lot.
>
> Dinesh
>
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
^ permalink raw reply [nested|flat] 11+ messages in thread
* Auto Vacuum Question
@ 2023-10-16 20:46 Murthy Nunna <mnunna@fnal.gov>
2023-10-16 21:40 ` Re: Auto Vacuum Question Michael Banck <mbanck@gmx.net>
0 siblings, 1 reply; 11+ messages in thread
From: Murthy Nunna @ 2023-10-16 20:46 UTC (permalink / raw)
To: pgsql-admin
Hello,
It looks like the max value of autovacuum_freeze_max_age is 2 billion.
I am wondering why anybody wants to change this setting to a lower number?
Thanks.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Auto Vacuum Question
2023-10-16 20:46 Auto Vacuum Question Murthy Nunna <mnunna@fnal.gov>
@ 2023-10-16 21:40 ` Michael Banck <mbanck@gmx.net>
0 siblings, 0 replies; 11+ messages in thread
From: Michael Banck @ 2023-10-16 21:40 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; +Cc: pgsql-admin
Hi,
On Mon, Oct 16, 2023 at 08:46:48PM +0000, Murthy Nunna wrote:
> It looks like the max value of autovacuum_freeze_max_age is 2 billion.
> I am wondering why anybody wants to change this setting to a lower number?
At a transaction age a bit above 2 billion, Postgres will stop accepting
connections and one needs to vacuum manually, usually resulting in a
long downtime.
So one needs some (or rather a lot of) daylight between the time
autovacuum decides to freeze a table and Postgres shutting down - it
could be that autovacuum is held back by long-running transactions or
unused replication slots or something.
I would consider autovacuum_freeze_max_age up to 1 billion mostly safe
(depending on the workload) and values up to 1.6 billion acceptable if
good monitoring is in place and everybody knows what is going on.
The other reason why want might not want to set
autovacuum_freeze_max_age so high is that the pg_xact and pg_commit_ts
sizes are proportional to autovacuum_freeze_max_age and take up more
space if autovacuum_freeze_max_age is higher, which might be problematic
if there is not a lot of storage present.
Michael
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2023-10-16 21:40 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-04 14:12 Auto Vacuum question? Charles.Hou <ivan.hou@msa.hinet.net>
2007-06-04 14:49 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-06-05 00:07 ` Charles.Hou <ivan.hou@msa.hinet.net>
2007-06-05 12:47 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-03-17 14:31 auto_vacuum question Mark Steben <msteben@autorevenue.com>
2008-03-17 14:57 ` Re: auto_vacuum question Alvaro Herrera <alvherre@commandprompt.com>
2008-03-17 15:17 ` Re: auto_vacuum question Mark Steben <msteben@autorevenue.com>
2010-11-12 19:44 auto vacuum question Dinesh Bhandary <dbhandary@iii.com>
2010-11-12 20:05 ` Re: auto vacuum question Joshua D. Drake <jd@commandprompt.com>
2023-10-16 20:46 Auto Vacuum Question Murthy Nunna <mnunna@fnal.gov>
2023-10-16 21:40 ` Re: Auto Vacuum Question Michael Banck <mbanck@gmx.net>
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