Re: Postgres slave not catching up (on 9.2)

From: Ruben Domingo Gaspar Aparicio
Subject: Re: Postgres slave not catching up (on 9.2)
Date: ,
Msg-id: 6FCBD86BDC789C43AC696969EC34022801093A2225@CERNXCHG44.cern.ch
(view: Whole thread, Raw)
In response to: Re: Postgres slave not catching up (on 9.2)  (Robert Klemme)
List: pgsql-performance

Tree view

Postgres slave not catching up (on 9.2)  (Ruben Domingo Gaspar Aparicio, )
 Re: Postgres slave not catching up (on 9.2)  (Robert Klemme, )
  Re: Postgres slave not catching up (on 9.2)  (Ruben Domingo Gaspar Aparicio, )
 Re: Postgres slave not catching up (on 9.2)  (Andres Freund, )
  Re: Postgres slave not catching up (on 9.2)  (Ruben Domingo Gaspar Aparicio, )
 Re: Postgres slave not catching up (on 9.2)  (Ruben Domingo Gaspar Aparicio, )
 Re: Postgres slave not catching up (on 9.2)  (Ruben Domingo Gaspar Aparicio, )

Indeed I could save some IO with noatime. I must say I haven’t found any recommendation about mount options for
postgresql,likely because this is not encourage. The ones you see are taking from a Oracle cluster configuration where
severalnodes see the same files. It's not the case on this setup.
 

The IO is not an issue. The storage is not at all saturated. Slave gets streaming perfectly but the apply is quite
slow,looks like always working with pages of 8k at a time:
 

--datafiles
[root@ ~]# /ORA/dbs01/syscontrol/projects/dfm/bin/smetrics -i 5 -n 100 -o vol dbnasg403-12a:/vol/dodpupdbtst03 Instance
total_opsread_ops write_ops read_data write_data avg_latency read_latency write_latenc
 
                /s       /s        /s       b/s        b/s          us           us           us
dodpupdbtst03      6466        0       162         0    2350619       31.53            0       764.70
dodpupdbtst03      6762        0       843         0    8751169       48.32            0       263.10
dodpupdbtst03      7023        0      1547         0   14914498      112.88            0       303.16
dodpupdbtst03      5373        0       321      6501    3809930       58.44     11287.75       467.21
dodpupdbtst03      5618        0       183         0    1661200       20.91            0       265.61
dodpupdbtst03      5538        0       214         0    3471380       29.24            0       374.27
dodpupdbtst03      5753        0       425         0    4973131       45.36            0       351.08
dodpupdbtst03      6110        0       142         0    2331695       20.96            0       378.95

--WALs
Bye Bye[root@ ~]# /ORA/dbs01/syscontrol/projects/dfm/bin/smetrics -i 5 -n 100 -o vol dbnasg401-12a:/vol/dodpupdbtst02
Instancetotal_ops read_ops write_ops read_data write_data avg_latency read_latency write_latenc
 
                /s       /s        /s       b/s        b/s          us           us           us
dodpupdbtst02      1017      202        93   5915899    2637111     2033.22     10116.09       172.61
dodpupdbtst02      1284      242       141   7368712    4309409     1235.11      6306.37       172.89
dodpupdbtst02      1357      231       268   6869816    8489466      957.55      5104.09       192.26
dodpupdbtst02      1566      264       288   8142965    9008529      747.96      4069.78       180.00
dodpupdbtst02      1333      235       153   7601051    4755791      993.81      5394.99       176.99
dodpupdbtst02      1261      199       287   6124821    9075170      896.32      5150.28       203.81
dodpupdbtst02       963      161       192   4955996    6066333     1757.66     10035.06       213.12
dodpupdbtst02       924      159       157   4782617    4807262     1092.61      5804.85       236.91
dodpupdbtst02       591       97       137   2899085    4275046     1218.24      6980.66       190.20

Writes are usually fast (us as they use the NVRAM )and reads are about 5 ms which is quite ok considering SATA disks
(theyhave a flash cache of 512GB, this is why we get this average).
 

Thank you,
Ruben


pgsql-performance by date:

From: Ruben Domingo Gaspar Aparicio
Date:
Subject: Re: Postgres slave not catching up (on 9.2)
From: Eric Ramirez
Date:
Subject: updating statistics on slow running query