Re: Re: [pgadmin-support] Issue with a hanging apply process on the replica db after vacuum works on primary - Mailing list pgsql-general

On 2/26/15 12:25 AM, Sergey Shchukin wrote:

Hi Radovan !

Thank you for the reply. The question is that this table is not a
subject for a massive updates/deletes.

Is there any additional traces except from perf or pg_top to trace what
replica is doing at the particular moment when we are lagging in replay?
To see locks or spins or sleeps etc..

Please don't top-post.

What version is this? What is max_standby_streaming_delay set to?

Thank you!

-

Best regards,
Sergey Shchukin

24.02.2015 19:05, Radovan Jablonovsky пишет:

This looks like more issue for pgsql-general mailing list.

Possible solutions
1) Set specific autovacuum parameters on the big table. The autovacuum
could vacuum table on multiple runs based on the thresholds and cost
settings
Example of setting specific values of autovacuum and analyze for
table. It should be adjusted for your system, work load, table usage, etc:
alter table "my_schema"."my_big_table" set (fillfactor = 80,
autovacuum_enabled = true, autovacuum_vacuum_threshold = 200,
autovacuum_analyze_threshold = 400, autovacuum_vacuum_scale_factor =
0.05, autovacuum_analyze_scale_factor = 0.005,
autovacuum_vacuum_cost_delay = 10, autovacuum_vacuum_cost_limit = 5000);

2) Could be to partition the large table on master site and vacuum it
partition by partition.

On Tue, Feb 24, 2015 at 6:42 AM, Sergey Shchukin
<shchukin.s.a@gmail.com <mailto:shchukin.s.a@gmail.com>> wrote:

    Hi all!

    May someone help me with the issue in the apply process on the
    replica. We have a stream replication and after vacuum stops
    working with a big table we get a "freeze" in applying data on the
    replica database. It looks like this:

    Tue Feb 24 15:04:51 MSK 2015 Stream:
    MASTER-masterdb:79607136410456 SLAVE:79607136410456
    Replay:79607136339456 :: REPLAY 69 KBytes (00:00:00.294485 seconds)
    Tue Feb 24 15:04:52 MSK 2015 Stream:
    MASTER-masterdb:79607137892672 SLAVE:79607137715392
    Replay:79607137715392 :: REPLAY 173 KBytes (00:00:00.142605 seconds)
    Tue Feb 24 15:04:53 MSK 2015 Stream:
    MASTER-masterdb:79607139327776 SLAVE:79607139241816
    Replay:79607139241816 :: REPLAY 84 KBytes (00:00:00.05223 seconds)
    Tue Feb 24 15:04:54 MSK 2015 Stream:
    MASTER-masterdb:79607141134776 SLAVE:79607141073344
    Replay:79607141080032 :: REPLAY 54 KBytes (00:00:00.010603 seconds)
    Tue Feb 24 15:04:55 MSK 2015 Stream:
    MASTER-masterdb:79607143085176 SLAVE:79607143026440
    Replay:79607143038040 :: REPLAY 46 KBytes (00:00:00.009506 seconds)
    Tue Feb 24 15:04:56 MSK 2015 Stream:
    MASTER-masterdb:79607145111280 SLAVE:79607145021384
    Replay:79607145025664 :: REPLAY 83 KBytes (00:00:00.006795 seconds)
    Tue Feb 24 15:04:57 MSK 2015 Stream:
    MASTER-masterdb:79607146564424 SLAVE:79607146478336
    Replay:79607146501264 :: REPLAY 61 KBytes (00:00:00.00701 seconds)
    Tue Feb 24 15:04:58 MSK 2015 Stream:
    MASTER-masterdb:79607148160680 SLAVE:79607148108352
    Replay:79607147369320 :: REPLAY 773 KBytes (00:00:00.449702 seconds)
    Tue Feb 24 15:04:59 MSK 2015 Stream:
    MASTER-masterdb:79607150220688 SLAVE:79607150159632
    Replay:79607150171312 :: REPLAY 48 KBytes (00:00:00.006594 seconds)
    Tue Feb 24 15:05:00 MSK 2015 Stream:
    MASTER-masterdb:79607152365360 SLAVE:79607152262696
    Replay:79607152285240 :: REPLAY 78 KBytes (00:00:00.007042 seconds)
    Tue Feb 24 15:05:02 MSK 2015 Stream:
    MASTER-masterdb:79607154049848 SLAVE:79607154012624
    Replay:79607153446800 :: REPLAY 589 KBytes (00:00:00.513637 seconds)
    Tue Feb 24 15:05:03 MSK 2015 Stream:
    MASTER-masterdb:79607155229992 SLAVE:79607155187864
    Replay:79607155188312 :: REPLAY 41 KBytes (00:00:00.004773 seconds)
    Tue Feb 24 15:05:04 MSK 2015 Stream:
    MASTER-masterdb:79607156833968 SLAVE:79607156764128
    Replay:79607156785488 :: REPLAY 47 KBytes (00:00:00.006846 seconds)
    Tue Feb 24 15:05:05 MSK 2015 Stream:
    MASTER-masterdb:79607158419848 SLAVE:79607158344856
    Replay:79607158396352 :: REPLAY 23 KBytes (00:00:00.005228 seconds)
    Tue Feb 24 15:05:06 MSK 2015 Stream:
    MASTER-masterdb:79607160004776 SLAVE:79607159962400
    Replay:79607159988888 :: REPLAY 16 KBytes (00:00:00.003162 seconds)
    *--here apply process just stops*

    Tue Feb 24 15:05:07 MSK 2015 Stream:
    MASTER-masterdb:79607161592048 SLAVE:79607161550576
    Replay:79607160986064 :: REPLAY 592 KBytes (00:00:00.398376 seconds)
    Tue Feb 24 15:05:08 MSK 2015 Stream:
    MASTER-masterdb:79607163272840 SLAVE:79607163231384
    Replay:79607160986064 :: REPLAY 2233 KBytes (00:00:01.446759 seconds)
    Tue Feb 24 15:05:09 MSK 2015 Stream:
    MASTER-masterdb:79607164958632 SLAVE:79607164904448
    Replay:79607160986064 :: REPLAY 3879 KBytes (00:00:02.497181 seconds)
    Tue Feb 24 15:05:10 MSK 2015 Stream:
    MASTER-masterdb:79607166819560 SLAVE:79607166777712
    Replay:79607160986064 :: REPLAY 5697 KBytes (00:00:03.543107 seconds)
    Tue Feb 24 15:05:11 MSK 2015 Stream:
    MASTER-masterdb:79607168595280 SLAVE:79607168566536
    Replay:79607160986064 :: REPLAY 7431 KBytes (00:00:04.589736 seconds)
    Tue Feb 24 15:05:12 MSK 2015 Stream:
    MASTER-masterdb:79607170372064 SLAVE:79607170252480
    Replay:79607160986064 :: REPLAY 9166 KBytes (00:00:05.635918 seconds)
    Tue Feb 24 15:05:13 MSK 2015 Stream:
    MASTER-masterdb:79607171829480 SLAVE:79607171714144
    Replay:79607160986064 :: REPLAY 10589 KBytes (00:00:06.688115 seconds)
    Tue Feb 24 15:05:14 MSK 2015 Stream:
    MASTER-masterdb:79607173152488 SLAVE:79607173152488
    Replay:79607160986064 :: REPLAY 11881 KBytes (00:00:07.736993 seconds)
    Tue Feb 24 15:05:15 MSK 2015 Stream:
    MASTER-masterdb:79607174149968 SLAVE:79607174149968
    Replay:79607160986064 :: REPLAY 12855 KBytes (00:00:08.78538 seconds)
    Tue Feb 24 15:05:16 MSK 2015 Stream:
    MASTER-masterdb:79607176448344 SLAVE:79607176252088
    Replay:79607160986064 :: REPLAY 15100 KBytes (00:00:09.835184 seconds)
    Tue Feb 24 15:05:17 MSK 2015 Stream:
    MASTER-masterdb:79607177632216 SLAVE:79607177608224
    Replay:79607160986064 :: REPLAY 16256 KBytes (00:00:10.926493 seconds)
    Tue Feb 24 15:05:18 MSK 2015 Stream:
    MASTER-masterdb:79607179432960 SLAVE:79607179378096
    Replay:79607160986064 :: REPLAY 18015 KBytes (00:00:11.97989 seconds)
    Tue Feb 24 15:05:19 MSK 2015 Stream:
    MASTER-masterdb:79607180893384 SLAVE:79607180874256
    Replay:79607160986064 :: REPLAY 19441 KBytes (00:00:13.028921 seconds)
    Tue Feb 24 15:05:20 MSK 2015 Stream:
    MASTER-masterdb:79607182596224 SLAVE:79607182552272
    Replay:79607160986064 :: REPLAY 21104 KBytes (00:00:14.079497 seconds)
    Tue Feb 24 15:05:21 MSK 2015 Stream:
    MASTER-masterdb:79607183935312 SLAVE:79607183902592
    Replay:79607160986064 :: REPLAY 22411 KBytes (00:00:15.127679 seconds)
    Tue Feb 24 15:05:23 MSK 2015 Stream:
    MASTER-masterdb:79607185165880 SLAVE:79607185094032
    Replay:79607160986064 :: REPLAY 23613 KBytes (00:00:16.175132 seconds)
    Tue Feb 24 15:05:24 MSK 2015 Stream:
    MASTER-masterdb:79607187196920 SLAVE:79607187169368
    Replay:79607160986064 :: REPLAY 25596 KBytes (00:00:17.221981 seconds)
    Tue Feb 24 15:05:25 MSK 2015 Stream:
    MASTER-masterdb:79607188943856 SLAVE:79607188885952
    Replay:79607160986064 :: REPLAY 27302 KBytes (00:00:18.274362 seconds)
    Tue Feb 24 15:05:26 MSK 2015 Stream:
    MASTER-masterdb:79607190489400 SLAVE:79607190443160
    Replay:79607160986064 :: REPLAY 28812 KBytes (00:00:19.319987 seconds)
    Tue Feb 24 15:05:27 MSK 2015 Stream:
    MASTER-masterdb:79607192089312 SLAVE:79607192054048
    Replay:79607160986064 :: REPLAY 30374 KBytes (00:00:20.372305 seconds)
    Tue Feb 24 15:05:28 MSK 2015 Stream:
    MASTER-masterdb:79607193736800 SLAVE:79607193690056
    Replay:79607160986064 :: REPLAY 31983 KBytes (00:00:21.421359 seconds)
    Tue Feb 24 15:05:29 MSK 2015 Stream:
    MASTER-masterdb:79607195968648 SLAVE:79607195901296
    Replay:79607160986064 :: REPLAY 34163 KBytes (00:00:22.471334 seconds)
    Tue Feb 24 15:05:30 MSK 2015 Stream:
    MASTER-masterdb:79607197808840 SLAVE:79607197737720
    Replay:79607160986064 :: REPLAY 35960 KBytes (00:00:23.52269 seconds)
    Tue Feb 24 15:05:31 MSK 2015 Stream:
    MASTER-masterdb:79607199571144 SLAVE:79607199495976
    Replay:79607160986064 :: REPLAY 37681 KBytes (00:00:24.577615 seconds)
    Tue Feb 24 15:05:32 MSK 2015 Stream:
    MASTER-masterdb:79607201206104 SLAVE:79607201100392
    Replay:79607160986064 :: REPLAY 39277 KBytes (00:00:25.624604 seconds)
    Tue Feb 24 15:05:33 MSK 2015 Stream:
    MASTER-masterdb:79607203174208 SLAVE:79607203111136
    Replay:79607160986064 :: REPLAY 41199 KBytes (00:00:26.67059 seconds)
    Tue Feb 24 15:05:34 MSK 2015 Stream:
    MASTER-masterdb:79607204792888 SLAVE:79607204741600
    Replay:79607160986064 :: REPLAY 42780 KBytes (00:00:27.719088 seconds)
    Tue Feb 24 15:05:35 MSK 2015 Stream:
    MASTER-masterdb:79607206453216 SLAVE:79607206409032
    Replay:79607160986064 :: REPLAY 44401 KBytes (00:00:28.766647 seconds)
    Tue Feb 24 15:05:36 MSK 2015 Stream:
    MASTER-masterdb:79607208225344 SLAVE:79607208142176
    Replay:79607160986064 :: REPLAY 46132 KBytes (00:00:29.811434 seconds)


    perf shows the following functions on the top
    +  22.50%  postmaster  [kernel.kallsyms]  [k] copy_user_generic_string
    +   8.48%  postmaster  postgres           [.]
    hash_search_with_hash_value


    after 10 minutes or so the apply process continue to work

    Tue Feb 24 15:13:25 MSK 2015 Stream:
    MASTER-masterdb:79608758742560 SLAVE:79608758718008
    Replay:79607160986064 :: REPLAY 1560309 KBytes (00:08:19.009653
    seconds)
    Tue Feb 24 15:13:26 MSK 2015 Stream:
    MASTER-masterdb:79608759203608 SLAVE:79608759189680
    Replay:79607160986064 :: REPLAY 1560759 KBytes (00:08:20.057877
    seconds)
    Tue Feb 24 15:13:27 MSK 2015 Stream:
    MASTER-masterdb:79608759639680 SLAVE:79608759633224
    Replay:79607160986064 :: REPLAY 1561185 KBytes (00:08:21.104723
    seconds)
    Tue Feb 24 15:13:28 MSK 2015 Stream:
    MASTER-masterdb:79608760271200 SLAVE:79608760264128
    Replay:79607160986064 :: REPLAY 1561802 KBytes (00:08:22.148546
    seconds)
    Tue Feb 24 15:13:30 MSK 2015 Stream:
    MASTER-masterdb:79608760622920 SLAVE:79608760616656
    Replay:79607160986064 :: REPLAY 1562145 KBytes (00:08:23.196645
    seconds)
    Tue Feb 24 15:13:31 MSK 2015 Stream:
    MASTER-masterdb:79608761122040 SLAVE:79608761084584
    Replay:79607160986064 :: REPLAY 1562633 KBytes (00:08:24.240653
    seconds)
    Tue Feb 24 15:13:32 MSK 2015 Stream:
    MASTER-masterdb:79608761434200 SLAVE:79608761426080
    Replay:79607160986064 :: REPLAY 1562938 KBytes (00:08:25.289429
    seconds)
    Tue Feb 24 15:13:33 MSK 2015 Stream:
    MASTER-masterdb:79608761931008 SLAVE:79608761904808
    Replay:79607160986064 :: REPLAY 1563423 KBytes (00:08:26.338498
    seconds)
    *--apply starts*
    Tue Feb 24 15:13:34 MSK 2015 Stream:
    MASTER-masterdb:79608762360568 SLAVE:79608762325712
    Replay:79607163554680 :: REPLAY 1561334 KBytes (00:08:25.702423
    seconds)
    Tue Feb 24 15:13:35 MSK 2015 Stream:
    MASTER-masterdb:79608762891224 SLAVE:79608762885928
    Replay:79607166466488 :: REPLAY 1559008 KBytes (00:08:25.011046
    seconds)
    Tue Feb 24 15:13:36 MSK 2015 Stream:
    MASTER-masterdb:79608763681920 SLAVE:79608763667256
    Replay:79607167054056 :: REPLAY 1559207 KBytes (00:08:25.827531
    seconds)
    Tue Feb 24 15:13:37 MSK 2015 Stream:
    MASTER-masterdb:79608764207088 SLAVE:79608764197744
    Replay:79607175610296 :: REPLAY 1551364 KBytes (00:08:21.182428
    seconds)
    Tue Feb 24 15:13:38 MSK 2015 Stream:
    MASTER-masterdb:79608764857920 SLAVE:79608764832432
    Replay:79607183599632 :: REPLAY 1544197 KBytes (00:08:16.742467
    seconds)
    Tue Feb 24 15:13:39 MSK 2015 Stream:
    MASTER-masterdb:79608765323360 SLAVE:79608765281408
    Replay:79607186862176 :: REPLAY 1541466 KBytes (00:08:15.569874
    seconds)
    Tue Feb 24 15:13:40 MSK 2015 Stream:
    MASTER-masterdb:79608765848240 SLAVE:79608765824520
    Replay:79607186862176 :: REPLAY 1541978 KBytes (00:08:16.620932
    seconds)


    All this is a result of completion of  "vacuum verbose analyze
    master_table" on the master site

    Any help would be appreciated

    --
    Best regards,
    Sergey Shchukin




--

*Radovan Jablonovsky* | SaaS DBA | Phone 1-403-262-6519 (ext. 7256) |
Fax 1-403-233-8046

- 
Best regards,
Sergey Shchukin