Re: pg_multixact issues

Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> · Thu, 11 Feb 2016 10:19:39 +0200



    Καλημέρα Κυριάκο

      
      We have been running 9.3.4 as our test system for quite some
      years, and 9.3.10 on production for a month or so (little less
      than 1T size, WAL changes worth of 2-5GB/day, about 250K
      xations/day) we never experienced any problems with
      data/pg_multixact.

      
      In our 9.3.4 :

      % pg_controldata data | grep -i multi

      Latest checkpoint's NextMultiXactId:  69

      Latest checkpoint's NextMultiOffset:  135

      Latest checkpoint's oldestMultiXid:   1

      Latest checkpoint's oldestMulti's DB: 0

      % du -h data/pg_multixact/

       10K    data/pg_multixact/members

       10K    data/pg_multixact/offsets

       22K    data/pg_multixact/

      
      In our prod 9.3.10:

      ~> pg_controldata data | grep -i multi

      Latest checkpoint's NextMultiXactId:  12404

      Latest checkpoint's NextMultiOffset:  356

      Latest checkpoint's oldestMultiXid:   12232

      Latest checkpoint's oldestMulti's DB: 16426

      ~> du -h data/pg_multixact/

      12K     data/pg_multixact/members

      24K     data/pg_multixact/offsets

      40K     data/pg_multixact/

      
      Our system comprises of a JEE installation with ~ 500 uesrs, and
      at least 2 other applications hitting the same tables at the same
      time, we have about one case of deadlocks per week.

      
      What could be different on yours?

      
      On 10/02/2016 20:52, Kiriakos Georgiou wrote:

    
    Hello,
      

      Our pg_multixact
          directory keeps growing.  I did a "vacuum freeze” which didn’t
          help.  I also did a "vacuum full” which didn’t help either.
      We had this
          condition with 9.3.4 as well.  When I upgraded our cluster to
          9.4.5 (via plain sql dump and load) as expected the issue was
          resolved but now it’s happening again.  Luckily it has no ill
          effect other than consuming 4G of space for an otherwise 1G
          database.
      

      Can you offer any
          hints as to how I can cure this?
      

      thanks,
      Kiriakos Georgiou
      

      pg_controldata
          output:
      

        pg_control
            version number:            942
        Catalog version
            number:               201409291
        Database system
            identifier:           6211781659140720513
        Database cluster
            state:               in production
        pg_control last
            modified:             Wed Feb 10 13:45:02 2016
        Latest
            checkpoint location:           D/FB5FE630
        Prior checkpoint
            location:            D/FB5FE558
        Latest
            checkpoint's REDO location:    D/FB5FE5F8
        Latest
            checkpoint's REDO WAL file:    000000010000000D000000FB
        Latest
            checkpoint's TimeLineID:       1
        Latest
            checkpoint's PrevTimeLineID:   1
        Latest
            checkpoint's full_page_writes: on
        Latest
            checkpoint's NextXID:          0/3556219
        Latest
            checkpoint's NextOID:          2227252
        Latest
            checkpoint's NextMultiXactId:  2316566
        Latest
            checkpoint's NextMultiOffset:  823062151
        Latest
            checkpoint's oldestXID:        668
        Latest
            checkpoint's oldestXID's DB:   1
        Latest
            checkpoint's oldestActiveXID:  3556219
        Latest
            checkpoint's oldestMultiXid:   1
        Latest
            checkpoint's oldestMulti's DB: 1
      
      
          Time of latest checkpoint:            Wed Feb 10
            13:45:02 2016
          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 wal_log_hints setting:        off
          Current max_connections setting:      100
          Current max_worker_processes setting: 8
          Current max_prepared_xacts setting:   0
          Current max_locks_per_xact setting:   1024
          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
          Size of a large-object chunk:         2048
          Date/time type storage:               64-bit
            integers
          Float4 argument passing:              by value
          Float8 argument passing:              by value
          Data page checksum version:           0
          

          the offsets directory:
          

            -rw-------   1 postgres dba       262144 Nov
               3 15:22 0000
            -rw-------   1 postgres dba       262144 Nov
               5 12:45 0001
            -rw-------   1 postgres dba       262144 Nov
               9 14:25 0002
            -rw-------   1 postgres dba       262144 Nov
              13 10:10 0003
            -rw-------   1 postgres dba       262144 Nov
              16 15:40 0004
            -rw-------   1 postgres dba       262144 Nov
              20 09:55 0005
            -rw-------   1 postgres dba       262144 Dec
               1 08:00 0006
            -rw-------   1 postgres dba       262144 Dec
               9 11:50 0007
            -rw-------   1 postgres dba       262144 Dec
              16 08:14 0008
            -rw-------   1 postgres dba       262144 Dec
              21 09:40 0009
            -rw-------   1 postgres dba       262144 Dec
              31 09:55 000A
            -rw-------   1 postgres dba       262144 Jan
               4 21:17 000B
            -rw-------   1 postgres dba       262144 Jan
               6 10:50 000C
            -rw-------   1 postgres dba       262144 Jan
               7 18:20 000D
            -rw-------   1 postgres dba       262144 Jan
              13 13:55 000E
            -rw-------   1 postgres dba       262144 Jan
              15 11:55 000F
            -rw-------   1 postgres dba       262144 Jan
              22 07:50 0010
            -rw-------   1 postgres dba       262144 Jan
              26 16:35 0011
            -rw-------   1 postgres dba       262144 Jan
              29 10:16 0012
            -rw-------   1 postgres dba       262144 Feb
               3 13:17 0013
            -rw-------   1 postgres dba       262144 Feb
               3 16:13 0014
            -rw-------   1 postgres dba       262144 Feb
               4 08:24 0015
            -rw-------   1 postgres dba       262144 Feb
               5 13:20 0016
            -rw-------   1 postgres dba       262144 Feb
               8 11:26 0017
            -rw-------   1 postgres dba       262144 Feb
               8 11:46 0018
            -rw-------   1 postgres dba       262144 Feb
               8 12:25 0019
            -rw-------   1 postgres dba       262144 Feb
               8 13:19 001A
            -rw-------   1 postgres dba       262144 Feb
               8 14:23 001B
            -rw-------   1 postgres dba       262144 Feb
               8 15:32 001C
            -rw-------   1 postgres dba       262144 Feb
               8 17:01 001D
            -rw-------   1 postgres dba       262144 Feb
               8 19:19 001E
            -rw-------   1 postgres dba       262144 Feb
               8 22:11 001F
            -rw-------   1 postgres dba       262144 Feb
               9 01:44 0020
            -rw-------   1 postgres dba       262144 Feb
               9 05:57 0021
            -rw-------   1 postgres dba       262144 Feb
               9 10:45 0022
            -rw-------   1 postgres dba        98304 Feb
              10 13:35 0023
          
          
          the members directory has 15723 files:
          
            ls -l|wc -l
               15723
          
        
    -- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt