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

From: Piotr Gasidło <quaker(at)barbara(dot)eu(dot)org>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: When files in pg_multixact/{members,offsets} get recycled?
Date: 2015-06-25 12:54:47
Message-ID: CAF8akQu0iX_oKurrd9nBNL=ZHzsjB102Bw_XqOAiEoPhRaoA1g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2015-06-25 15:17:12 Re: When files in pg_multixact/{members,offsets} get recycled?
Previous Message Andomar 2015-06-25 08:04:31 Re: Include.d and warnings