pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase-wide VACUUM ANALYZE
11+ messages / 7 participants
[nested] [flat]
* Database-wide VACUUM ANALYZE
@ 2007-06-21 17:09 Steven Flatt <steven.flatt@gmail.com>
2007-06-21 18:33 ` Re: Database-wide VACUUM ANALYZE Heikki Linnakangas <heikki@enterprisedb.com>
2007-06-21 18:58 ` Re: Database-wide VACUUM ANALYZE Francisco Reyes <lists@stringsutils.com>
0 siblings, 2 replies; 11+ messages in thread
From: Steven Flatt @ 2007-06-21 17:09 UTC (permalink / raw)
To: pgsql-performance
We recently upgraded a very large database (~550 GB) from 8.1.4 to 8.2.4 via
a pg_dump and pg_restore. (Note that the restore took several days.) We
had accepted the default settings:
vacuum_freeze_min_age = 100 million
autovacuum_freeze_max_age = 200 million
Due to our very high transaction rate, it appears that a database-wide
vacuum kicked off approximately 2 weeks after the restore. (Aside: after
reading the docs and considering our system characteristics, I know now that
our autovacuum_freeze_max_age should be more like 2 billion. However on
this machine I haven't changed the config settings yet.) Also, I believe,
that due to the bulk of our data having the same "age" after the restore,
the db-wide vacuum had *a lot* of rows to mark with the FrozenXID.
The good thing is that the db-wide vacuum, which ran for a long time, was
reasonably non-intrusive to other database activity (somewhat, but
reasonable for the short term). The other good thing was that concurrent
autovacuum processes were still vacuuming/analyzing tables as necessary.
The bad thing, which I don't totally understand from reading the docs, is
that another db-wide vacuum kicked off exactly 24 hours after the first
db-wide vacuum kicked off, before the first one had finished. (Note that
these vacuums seem to go through the tables alphabetically.) I managed to
explain this to myself in that there were still rows in tables not yet
touched by the first db-wide vacuum that could have XIDs older than
autovacuum_freeze_max_age. Fine, so two db-wide vacuums were now taking
place, one behind the other.
The first db-wide vacuum finished approximately 36 hours after it started.
At this point I was convinced that the second db-wide vacuum would run to
completion with little or no work to do and all would be good. The thing I
can't explain is why a third db-wide vacuum kicked off exactly 24 hours
(again) after the second db-wide vacuum kicked off (and the second vacuum
still running).
Wouldn't the first db-wide vacuum have marked any rows that needed it with
the FrozenXID? Why would a third db-wide vacuum kick off so soon after the
first db-wide vacuum had completed? Surely there haven't been 100 million
more transactions in the last two days?
Can someone explain what is going on here? I can't quite figure it out
based on the docs.
Thanks,
Steve
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database-wide VACUUM ANALYZE
2007-06-21 17:09 Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
@ 2007-06-21 18:33 ` Heikki Linnakangas <heikki@enterprisedb.com>
1 sibling, 0 replies; 11+ messages in thread
From: Heikki Linnakangas @ 2007-06-21 18:33 UTC (permalink / raw)
To: Steven Flatt <steven.flatt@gmail.com>; +Cc: pgsql-performance
Steven Flatt wrote:
> The bad thing, which I don't totally understand from reading the docs, is
> that another db-wide vacuum kicked off exactly 24 hours after the first
> db-wide vacuum kicked off, before the first one had finished. (Note that
> these vacuums seem to go through the tables alphabetically.) I managed to
> explain this to myself in that there were still rows in tables not yet
> touched by the first db-wide vacuum that could have XIDs older than
> autovacuum_freeze_max_age. Fine, so two db-wide vacuums were now taking
> place, one behind the other.
Are you sure there's no cron job starting the vacuums? 24h sounds too
good to be a coincidence, and there's no magic constant of 24h in the
autovacuum code. Besides, autovacuum can only be running one VACUUM at a
time, so there must be something else launching them.
What's your vacuuming strategy in general, before and after upgrade?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database-wide VACUUM ANALYZE
2007-06-21 17:09 Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
@ 2007-06-21 18:58 ` Francisco Reyes <lists@stringsutils.com>
2007-06-21 19:36 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Francisco Reyes @ 2007-06-21 18:58 UTC (permalink / raw)
To: Steven Flatt <steven.flatt@gmail.com>; +Cc: pgsql-performance
Steven Flatt writes:
> Can someone explain what is going on here? I can't quite figure it out
> based on the docs.
Are you on FreeBSD by any chance?
I think the FreeBSD port by default installs a script that does a daily
vacuum. If using another OS, perhaps you want to see if you used some sort
of package system and if that package added a nightly vacuum.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database-wide VACUUM ANALYZE
2007-06-21 17:09 Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 18:58 ` Re: Database-wide VACUUM ANALYZE Francisco Reyes <lists@stringsutils.com>
@ 2007-06-21 19:36 ` Steven Flatt <steven.flatt@gmail.com>
2007-06-21 19:59 ` Re: Database-wide VACUUM ANALYZE Larry Rosenman <ler@lerctr.org>
2007-06-21 20:07 ` Re: Database-wide VACUUM ANALYZE Bill Moran <wmoran@collaborativefusion.com>
0 siblings, 2 replies; 11+ messages in thread
From: Steven Flatt @ 2007-06-21 19:36 UTC (permalink / raw)
To: Francisco Reyes <lists@stringsutils.com>; +Cc: pgsql-performance
On 6/21/07, Francisco Reyes <lists@stringsutils.com> wrote:
>
> Are you on FreeBSD by any chance?
>
> I think the FreeBSD port by default installs a script that does a daily
> vacuum.
Yes, FreeBSD. Do you know what script that is? And it does a db-wide
VACUUM ANALYZE every day?! That is certainly not necessary, and in fact,
costly for us.
Hmmm... I wonder why this would just start now, three days ago. Everything
seemed to be normal for the last two weeks.
Steve
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database-wide VACUUM ANALYZE
2007-06-21 17:09 Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 18:58 ` Re: Database-wide VACUUM ANALYZE Francisco Reyes <lists@stringsutils.com>
2007-06-21 19:36 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
@ 2007-06-21 19:59 ` Larry Rosenman <ler@lerctr.org>
1 sibling, 0 replies; 11+ messages in thread
From: Larry Rosenman @ 2007-06-21 19:59 UTC (permalink / raw)
To: Steven Flatt <steven.flatt@gmail.com>; +Cc: Francisco Reyes <lists@stringsutils.com>; pgsql-performance
On Thu, 21 Jun 2007, Steven Flatt wrote:
> On 6/21/07, Francisco Reyes <lists@stringsutils.com> wrote:
>>
>> Are you on FreeBSD by any chance?
>>
>> I think the FreeBSD port by default installs a script that does a daily
>> vacuum.
>
>
> Yes, FreeBSD. Do you know what script that is? And it does a db-wide
> VACUUM ANALYZE every day?! That is certainly not necessary, and in fact,
> costly for us.
>
> Hmmm... I wonder why this would just start now, three days ago. Everything
> seemed to be normal for the last two weeks.
>
The current FreeBSD port places the script in:
/usr/local/etc/periodic/daily/502.pgsql
And it can be controlled from /etc/periodic.conf
See the top of that script.
LER
> Steve
>
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 512-248-2683 E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database-wide VACUUM ANALYZE
2007-06-21 17:09 Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 18:58 ` Re: Database-wide VACUUM ANALYZE Francisco Reyes <lists@stringsutils.com>
2007-06-21 19:36 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
@ 2007-06-21 20:07 ` Bill Moran <wmoran@collaborativefusion.com>
2007-06-21 20:37 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Bill Moran @ 2007-06-21 20:07 UTC (permalink / raw)
To: Steven Flatt <steven.flatt@gmail.com>; +Cc: Francisco Reyes <lists@stringsutils.com>; pgsql-performance
In response to "Steven Flatt" <steven.flatt@gmail.com>:
> On 6/21/07, Francisco Reyes <lists@stringsutils.com> wrote:
> >
> > Are you on FreeBSD by any chance?
> >
> > I think the FreeBSD port by default installs a script that does a daily
> > vacuum.
>
>
> Yes, FreeBSD. Do you know what script that is?
/usr/local/etc/periodic/daily/502.pgsql
> And it does a db-wide
> VACUUM ANALYZE every day?! That is certainly not necessary, and in fact,
> costly for us.
You can control it with knobs in /etc/periodic.conf (just like other
periodic job):
daily_pgsql_vacuum_enable="YES"
daily_pgsql_backup_enable="NO"
are the defaults.
> Hmmm... I wonder why this would just start now, three days ago. Everything
> seemed to be normal for the last two weeks.
Someone alter /etc/periodic.conf? Perhaps it's been running all along but
you never noticed it before now?
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database-wide VACUUM ANALYZE
2007-06-21 17:09 Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 18:58 ` Re: Database-wide VACUUM ANALYZE Francisco Reyes <lists@stringsutils.com>
2007-06-21 19:36 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 20:07 ` Re: Database-wide VACUUM ANALYZE Bill Moran <wmoran@collaborativefusion.com>
@ 2007-06-21 20:37 ` Steven Flatt <steven.flatt@gmail.com>
2007-06-22 13:55 ` Re: Database-wide VACUUM ANALYZE Alvaro Herrera <alvherre@commandprompt.com>
2007-06-26 00:00 ` Re: Database-wide VACUUM ANALYZE Jim Nasby <decibel@decibel.org>
0 siblings, 2 replies; 11+ messages in thread
From: Steven Flatt @ 2007-06-21 20:37 UTC (permalink / raw)
To: Bill Moran <wmoran@collaborativefusion.com>; +Cc: Francisco Reyes <lists@stringsutils.com>; pgsql-performance
Thanks everyone. It appears that we had hacked the 502.pgsql script for our
8.1 build to disable the daily vacuum. I was not aware of this when
building and upgrading to 8.2.
So it looks like for the past two weeks, that 36 hour db-wide vacuum has
been running every 24 hours. Good for it for being reasonably non-intrusive
and going unnoticed until now. :)
Although apparently not related anymore, I still think it was a good move to
change autovacuum_freeze_max_age from 200 million to 2 billion.
Steve
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database-wide VACUUM ANALYZE
2007-06-21 17:09 Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 18:58 ` Re: Database-wide VACUUM ANALYZE Francisco Reyes <lists@stringsutils.com>
2007-06-21 19:36 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 20:07 ` Re: Database-wide VACUUM ANALYZE Bill Moran <wmoran@collaborativefusion.com>
2007-06-21 20:37 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
@ 2007-06-22 13:55 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 11+ messages in thread
From: Alvaro Herrera @ 2007-06-22 13:55 UTC (permalink / raw)
To: Steven Flatt <steven.flatt@gmail.com>; +Cc: Bill Moran <wmoran@collaborativefusion.com>; Francisco Reyes <lists@stringsutils.com>; pgsql-performance
Steven Flatt escribió:
> Thanks everyone. It appears that we had hacked the 502.pgsql script for our
> 8.1 build to disable the daily vacuum. I was not aware of this when
> building and upgrading to 8.2.
>
> So it looks like for the past two weeks, that 36 hour db-wide vacuum has
> been running every 24 hours. Good for it for being reasonably non-intrusive
> and going unnoticed until now. :)
Looks like you have plenty of spare I/O ;-)
> Although apparently not related anymore, I still think it was a good move to
> change autovacuum_freeze_max_age from 200 million to 2 billion.
Absolutely not related. Also note that
1. autovacuum is not able (in 8.2 or older) to have more than one task
running
2. autovacuum in 8.2 doesn't ever launch database-wide vacuums. As of
8.2 it only vacuums tables that are in actual danger of Xid
wraparound (according to pg_class.relfrozenxid); tables that had been
vacuumed the day before would not need another vacuum for Xid
purposes (though if you had modified the table to the point that it
needed another vacuum, that would be another matter). Unless you
consumed 200 million (or 2 billion) transactions during the day, that
is.
--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"The only difference is that Saddam would kill you on private, where the
Americans will kill you in public" (Mohammad Saleh, 39, a building contractor)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database-wide VACUUM ANALYZE
2007-06-21 17:09 Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 18:58 ` Re: Database-wide VACUUM ANALYZE Francisco Reyes <lists@stringsutils.com>
2007-06-21 19:36 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 20:07 ` Re: Database-wide VACUUM ANALYZE Bill Moran <wmoran@collaborativefusion.com>
2007-06-21 20:37 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
@ 2007-06-26 00:00 ` Jim Nasby <decibel@decibel.org>
2007-06-26 17:25 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Jim Nasby @ 2007-06-26 00:00 UTC (permalink / raw)
To: Steven Flatt <steven.flatt@gmail.com>; +Cc: Bill Moran <wmoran@collaborativefusion.com>; Francisco Reyes <lists@stringsutils.com>; pgsql-performance
On Jun 21, 2007, at 3:37 PM, Steven Flatt wrote:
> Thanks everyone. It appears that we had hacked the 502.pgsql
> script for our 8.1 build to disable the daily vacuum. I was not
> aware of this when building and upgrading to 8.2.
Much better to change stuff in a config file than to hack installed
scripts, for this very reason. :)
> So it looks like for the past two weeks, that 36 hour db-wide
> vacuum has been running every 24 hours. Good for it for being
> reasonably non-intrusive and going unnoticed until now. :)
>
> Although apparently not related anymore, I still think it was a
> good move to change autovacuum_freeze_max_age from 200 million to 2
> billion.
If you set that to 2B, that means you're 2^31-"2 billion"-1000000
transactions away from a shutdown when autovac finally gets around to
trying to run a wraparound vacuum on a table. If you have any number
of large tables, that could be a big problem, as autovac could get
tied up on a large table for a long enough period that the table
needing to be frozen doesn't get frozen in time.
I suspect 1B is a much better setting. I probably wouldn't go past 1.5B.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database-wide VACUUM ANALYZE
2007-06-21 17:09 Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 18:58 ` Re: Database-wide VACUUM ANALYZE Francisco Reyes <lists@stringsutils.com>
2007-06-21 19:36 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 20:07 ` Re: Database-wide VACUUM ANALYZE Bill Moran <wmoran@collaborativefusion.com>
2007-06-21 20:37 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-26 00:00 ` Re: Database-wide VACUUM ANALYZE Jim Nasby <decibel@decibel.org>
@ 2007-06-26 17:25 ` Steven Flatt <steven.flatt@gmail.com>
2007-06-26 18:49 ` Re: Database-wide VACUUM ANALYZE Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Steven Flatt @ 2007-06-26 17:25 UTC (permalink / raw)
To: Jim Nasby <decibel@decibel.org>; +Cc: Bill Moran <wmoran@collaborativefusion.com>; Francisco Reyes <lists@stringsutils.com>; pgsql-performance
On 6/25/07, Jim Nasby <decibel@decibel.org> wrote:
>
> If you set that to 2B, that means you're 2^31-"2 billion"-1000000
> transactions away from a shutdown when autovac finally gets around to
> trying to run a wraparound vacuum on a table. If you have any number
> of large tables, that could be a big problem, as autovac could get
> tied up on a large table for a long enough period that the table
> needing to be frozen doesn't get frozen in time.
>
> I suspect 1B is a much better setting. I probably wouldn't go past 1.5B.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Database-wide VACUUM ANALYZE
2007-06-21 17:09 Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 18:58 ` Re: Database-wide VACUUM ANALYZE Francisco Reyes <lists@stringsutils.com>
2007-06-21 19:36 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 20:07 ` Re: Database-wide VACUUM ANALYZE Bill Moran <wmoran@collaborativefusion.com>
2007-06-21 20:37 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-26 00:00 ` Re: Database-wide VACUUM ANALYZE Jim Nasby <decibel@decibel.org>
2007-06-26 17:25 ` Re: Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
@ 2007-06-26 18:49 ` Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 11+ messages in thread
From: Alvaro Herrera @ 2007-06-26 18:49 UTC (permalink / raw)
To: Steven Flatt <steven.flatt@gmail.com>; +Cc: Jim Nasby <decibel@decibel.org>; Bill Moran <wmoran@collaborativefusion.com>; Francisco Reyes <lists@stringsutils.com>; pgsql-performance
Steven Flatt escribió:
> Most of our large (partitioned) tables are insert-only (truncated
> eventually) so will not be touched by autovacuum until wraparound prevention
> kicks in. However the tables are partitioned by timestamp so tables will
> cross the 1.9 billion marker at different times (some not at all, as the
> data will have been truncated).
Note that as of 8.3, tables that are truncated do not need vacuuming for
Xid wraparound purposes, because the counter is updated on TRUNCATE (as
it is on CLUSTER and certain forms of ALTER TABLE).
> Do you still think the 250 million transactions away from shutdown is
> cutting it too close? Recall that the unintentional db-wide vacuum analyze
> that was going on last week on our system took less than two days to
> complete.
Is this 8.1 or 8.2? In the latter you don't ever need db-wide vacuums
at all, because Xid wraparound is tracked per table, so only tables
actually needing vacuum are processed. To answer your question, the
followup question is how many transactions normally take place in two
days. If they are way less than 250 million then you don't need to
worry. Otherwise, the database may shut itself down to protect from Xid
wraparound.
--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"La soledad es compañía"
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2007-06-26 18:49 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-21 17:09 Database-wide VACUUM ANALYZE Steven Flatt <steven.flatt@gmail.com>
2007-06-21 18:33 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-06-21 18:58 ` Francisco Reyes <lists@stringsutils.com>
2007-06-21 19:36 ` Steven Flatt <steven.flatt@gmail.com>
2007-06-21 19:59 ` Larry Rosenman <ler@lerctr.org>
2007-06-21 20:07 ` Bill Moran <wmoran@collaborativefusion.com>
2007-06-21 20:37 ` Steven Flatt <steven.flatt@gmail.com>
2007-06-22 13:55 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-06-26 00:00 ` Jim Nasby <decibel@decibel.org>
2007-06-26 17:25 ` Steven Flatt <steven.flatt@gmail.com>
2007-06-26 18:49 ` Alvaro Herrera <alvherre@commandprompt.com>
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