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

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

From
Piotr Gasidło
Date:
Hello,

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?

# ls -1 members/[0-9A-F]* | wc -l
     894

# ls -la members | head
total 127064
drwx------  2 pgsql  pgsql     896 Mar 31  2014 .
drwx------  4 pgsql  pgsql       4 Dec 11  2013 ..
-rw-------  1 pgsql  pgsql  262144 Dec 11  2013 0000
-rw-------  1 pgsql  pgsql  262144 Dec 11  2013 0001
-rw-------  1 pgsql  pgsql  262144 Dec 11  2013 0002
-rw-------  1 pgsql  pgsql  262144 Dec 11  2013 0003
-rw-------  1 pgsql  pgsql  262144 Dec 11  2013 0004
-rw-------  1 pgsql  pgsql  262144 Dec 11  2013 0005
-rw-------  1 pgsql  pgsql  262144 Dec 11  2013 0006

# ls -la members | tail
-rw-------  1 pgsql  pgsql  262144 Mar 30  2014 0374
-rw-------  1 pgsql  pgsql  262144 Mar 30  2014 0375
-rw-------  1 pgsql  pgsql  262144 Mar 30  2014 0376
-rw-------  1 pgsql  pgsql  262144 Mar 30  2014 0377
-rw-------  1 pgsql  pgsql  262144 Mar 30  2014 0378
-rw-------  1 pgsql  pgsql  262144 Mar 30  2014 0379
-rw-------  1 pgsql  pgsql  262144 Mar 31  2014 037A
-rw-------  1 pgsql  pgsql  262144 Mar 31  2014 037B
-rw-------  1 pgsql  pgsql  262144 Mar 31  2014 037C
-rw-------  1 pgsql  pgsql  253952 Jun 24 01:58 037D

As shown, only file 037D in members was recently changed.

The same in offsets:

# ls -1 offsets/[0-9A-F]* | wc -l
      60

# ls -la offsets | head
total 15795
drwx------  2 pgsql  pgsql      62 Mar 30  2014 .
drwx------  4 pgsql  pgsql       4 Dec 11  2013 ..
-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

# ls -la offsets | tail
-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.

# pg_controldata /usr/local/pgsql/logs/data

pg_control version number:            937
Catalog version number:               201306121
Database system identifier:           5896471202307701721
Database cluster state:               in production
pg_control last modified:             Thu Jun 25 14:42:42 2015
Latest checkpoint location:           26FF/685373F0
Prior checkpoint location:            26FF/680609E0
Latest checkpoint's REDO location:    26FF/680658A0
Latest checkpoint's REDO WAL file:    00000005000026FF00000068
Latest checkpoint's TimeLineID:       5
Latest checkpoint's PrevTimeLineID:   5
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          1/3898164439
Latest checkpoint's NextOID:          23515628
Latest checkpoint's NextMultiXactId:  3908584
Latest checkpoint's NextMultiOffset:  46799749
Latest checkpoint's oldestXID:        3698648295
Latest checkpoint's oldestXID's DB:   131438
Latest checkpoint's oldestActiveXID:  3898164439
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 131438
Time of latest checkpoint:            Thu Jun 25 14:42:39 2015
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            hot_standby
Current max_connections setting:      500
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0

--
Piotr Gasidło


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

From
Alvaro Herrera
Date:
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