Re: Unexplained Major Vacuum Archive Activity During Vacuum - Mailing list pgsql-general

On 11/02/2012 03:08 AM, Alban Hertroys wrote:

> 150M database transactions a day sounds excessive, is there no way to
> reduce that number?

I wish. 150M is actually a conservative estimate. In fact, we average
141M, but have been as high as 270M. It's all market dependent.

Here's a quick look at the last two weeks of activity:

  reading_date | transactions
--------------+--------------
  2012-10-19   |    188548680
  2012-10-20   |     16722600
  2012-10-21   |     18326700
  2012-10-22   |    183141540
  2012-10-23   |    183269580
  2012-10-24   |    177945540
  2012-10-25   |    180901740
  2012-10-26   |    170482920
  2012-10-27   |     16103820
  2012-10-28   |     15026340
  2012-10-29   |     27431160
  2012-10-30   |     20299500
  2012-10-31   |    165263760
  2012-11-01   |    175540020

So you can see that even in the "off" days, we handle over 15M
transactions per day. Monday and Tuesday were admittedly slow, but that
means there was even less reason for VACUUM to go bonkers. We run it
*every night*. Twice, in fact. Once after the main part of the day is
done, and once after a lot of our nightly reconciliation scripts run
because of all the rows they touch.

We can probably disable that second vacuum now that we are using
autovacuum, but the first one has to stay.

> It wouldn't surprise me if you went over 650M transactions that day.

Yeah, and I thought that might be the case too. Until I looked at our
graph of transaction totals for the week. The last vacuum that would
have caused an automatic freeze happened on the 26th. So by the time the
"bad" vacuum happened on the 31st, we'd accumulated about 244M
transactions. Sure, that's quite a few, but not 6x more than usual, as
the amount of transaction logs generated during the vacuum might suggest.

> That's not what I was suggesting. I wasn't talking about vacuum
> freeze but normal autovacuum with more aggressive parameters.

It's already pretty aggressive. I cut autovacuum_vacuum_scale_factor and
autovacuum_analyze_scale_factor in half from the defaults, and tweaked a
couple tables that were getting vacuumed or analyzed every minute.

What I don't want is for it to be so aggressive that it decides to
freeze a 50M row table in the middle of an active trading day. That's
why the 10PM vacuum stays. If I just depended on
autovacuum_freeze_max_age, that would be a distinct possibility.

> Vacuum freeze was deprecated back then already.
> Knowing the devs a bit, there was a good reason to do so.

VACUUM FREEZE is no longer deprecated according to the 9.1 and 9.2 docs.
I've never used it myself, though. Besides that, autovacuum does a
FREEZE automatically if you go over autovacuum_freeze_max_age. Due to
the free space map, they also added vacuum_freeze_table_age, which
causes a regular VACUUM to be promoted to a VACUUM FREEZE if the age is
higher than that value. By default, that's 150M; just about perfect for
our system, honestly.

In pre-8.4, before the free space map, you don't need FREEZE at all. A
regular vacuum could reset all XID counters down to some minimum value.
That minimum was vacuum_freeze_min_age. But because of the visibility
map, and regular vacuums using it, older XIDs could be missed during a
regular VACUUM because it wasn't launched by the autovacuum thread. They
introduced vacuum_freeze_table_age so you could get the old
functionality back (always resetting down to vacuum_freeze_min_age
during any VACUUM) if so desired.

Basically VACUUM FREEZE doesn't mean what it once did. The free space
map made it a necessity because VACUUM doesn't always recliam XIDs anymore.

At least, that's the impression I got from the docs. I could be way off.
It bears out, though. I've got plenty of tables in our stage setup where
I'll vacuum them after setting vacuum_freeze_min_age to 100 or
something, and they just keep climbing. VACUUM FREEZE *always* resets
the value, though.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email