Re: Replaying 48 WAL files takes 80 minutes

Alvaro Herrera wrote:
>> I am configuring streaming replication with hot standby
>> with PostgreSQL 9.1.3 on RHEL 6 (kernel 2.6.32-220.el6.x86_64).
>> PostgreSQL was compiled from source.
>>
>> It works fine, except that starting the standby took for ever:
>> it took the system more than 80 minutes to replay 48 WAL files
>> and connect to the primary.
>>
>> Can anybody think of an explanation why it takes that long?
>
> Can you do a quick xlogdump of those files? Maybe there is something
> unusual (say particular types of GIN/GiST index updates) on the files
> that take longer.

There are no GIN and GiST indexes in this cluster.

Here's the output of "xlogdump -S" on one of the WAL files
that took over 4 minutes:

00000001000001D1000000EF:

Unable to read continuation page?
** maybe continues to next segment **
---------------------------------------------------------------
TimeLineId: 1, LogId: 465, LogSegment: 239

Resource manager stats:
[0]XLOG : 2 records, 112 bytes (avg 56.0 bytes)
checkpoint: 2, switch: 0, backup end: 0
[1]Transaction: 427 records, 96512 bytes (avg 226.0 bytes)
commit: 427, abort: 0
[2]Storage : 0 record, 0 byte (avg 0.0 byte)
[3]CLOG : 0 record, 0 byte (avg 0.0 byte)
[4]Database : 0 record, 0 byte (avg 0.0 byte)
[5]Tablespace: 0 record, 0 byte (avg 0.0 byte)
[6]MultiXact : 0 record, 0 byte (avg 0.0 byte)
[7]RelMap : 0 record, 0 byte (avg 0.0 byte)
[8]Standby : 84 records, 1352 bytes (avg 16.1 bytes)
[9]Heap2 : 325 records, 9340 bytes (avg 28.7 bytes)
[10]Heap : 7611 records, 4118483 bytes (avg 541.1 bytes)
ins: 2498, upd/hot_upd: 409/2178, del: 2494
[11]Btree : 3648 records, 120814 bytes (avg 33.1 bytes)
[12]Hash : 0 record, 0 byte (avg 0.0 byte)
[13]Gin : 0 record, 0 byte (avg 0.0 byte)
[14]Gist : 0 record, 0 byte (avg 0.0 byte)
[15]Sequence : 0 record, 0 byte (avg 0.0 byte)

Backup block stats: 2600 blocks, 11885880 bytes (avg 4571.5 bytes)

Yours,
Laurenz Albe