Re: When files in pg_multixact/{members,offsets} get recycled?

Piotr Gasidło wrote:

> Running latest 9.3.9. Database was never pg_upgraded. I list
> pg_multixact/offsets and pg_multixact/members and I see many files
> with dates more than one year old. Is that ok?

Yes, it's okay, if a bit annoying. You can decrease the number of files
by reducing the freeze age for multixacts; files get removed as
the earliest of pg_database.datminmxid moves forward (actually they are
removed by the checkpoint immediately following vacuum, though that's
likely to change in the next set of minor releases.) You can wait for
autovacuum to do it for you, or you can vacuum databases yourself.

(pg_database.datminmxid is the lowest of all pg_class.relminmxid in that
particular database.)

> -rw------- 1 pgsql pgsql 262144 Dec 11 2013 0000
> -rw------- 1 pgsql pgsql 262144 Dec 11 2013 0001
> -rw------- 1 pgsql pgsql 262144 Dec 21 2013 0002
> -rw------- 1 pgsql pgsql 262144 Jan 12 2014 0003
> -rw------- 1 pgsql pgsql 262144 Jan 29 2014 0004
> -rw------- 1 pgsql pgsql 262144 Feb 6 2014 0005
> -rw------- 1 pgsql pgsql 262144 Feb 9 2014 0006
...
> -rw------- 1 pgsql pgsql 262144 Mar 24 2014 0032
> -rw------- 1 pgsql pgsql 262144 Mar 24 2014 0033
> -rw------- 1 pgsql pgsql 262144 Mar 25 2014 0034
> -rw------- 1 pgsql pgsql 262144 Mar 26 2014 0035
> -rw------- 1 pgsql pgsql 262144 Mar 27 2014 0036
> -rw------- 1 pgsql pgsql 262144 Mar 28 2014 0037
> -rw------- 1 pgsql pgsql 262144 Mar 28 2014 0038
> -rw------- 1 pgsql pgsql 262144 Mar 29 2014 0039
> -rw------- 1 pgsql pgsql 262144 Mar 30 2014 003A
> -rw------- 1 pgsql pgsql 172032 Jun 24 01:58 003B
>
> As shown, only file 003B in members was recently changed.

This says that your usage of multixacts is bursty -- you use many of
them in some periods, and little or none in others.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services