From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org, Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> |
Subject: | Re: pg_multixact not getting truncated |
Date: | 2014-11-08 01:29:15 |
Message-ID: | 20141108012915.GW1791@alvin.alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Josh Berkus wrote:
> On 11/07/2014 04:43 PM, Alvaro Herrera wrote:
> > This says that the live multixact range goes from 123 million to 162
> > million; roughly 40 million values. (The default value for
> > vacuum_multixact_freeze_table_age is 150 million, which is what
> > determines how many values are kept.)
> >
> > You gist.github paste tells us there are 4598 members files. Each file
> > has 32 pages, and each page hosts 2045 members; so there are 32 * 2045 *
> > 4598 members, or somewhat about 300 million. For 40 million
> > multixacts, this means there are about 7 members per multixact, in
> > average, which seems a reasonable number to me.
>
> So the basic problem is that multixact files are just huge, with an
> average of 35 bytes per multixact?
The more members the multixacts have, the more space they occupy. I
would have thought this was obvious enough.
> > If you want to have vacuum truncate pg_multixact more aggresively, you
> > need to decrease vacuum_multixact_freeze_table_age and
> > vacuum_multixact_freeze_min_age.
>
> If that's the case, then we need to set the defaults more aggressively.
> I suggest maybe 10 million. The alternative is allowing it to creep up
> to 150million, which would be 5GB. I don't see adding 5GB to user
> databases without warning them as good behavior.
>
> Of course, this will lead to LOTs of additional vacuuming ...
There's a trade-off here: more vacuuming I/O usage for less disk space
used. How stressed your customers really are about 1 GB of disk space?
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2014-11-08 02:03:07 | Re: pg_basebackup fails with long tablespace paths |
Previous Message | Josh Berkus | 2014-11-08 01:20:44 | Re: pg_multixact not getting truncated |