Thread: When files in pg_multixact/{members,offsets} get recycled?
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
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