Re: [GENERAL] Transaction apply speed on the standby - Mailing list pgsql-general

From Jeff Janes
Subject Re: [GENERAL] Transaction apply speed on the standby
Date
Msg-id CAMkU=1w1Eg5MC5KWOwzTHjB8xTMpePLRy+1e4piFjXEqkUhHFQ@mail.gmail.com
Whole thread Raw
In response to [GENERAL] Transaction apply speed on the standby  (Rakesh Kumar <rakeshkumar464@outlook.com>)
List pgsql-general
On Thu, Jan 26, 2017 at 8:34 AM, Rakesh Kumar <rakeshkumar464@outlook.com> wrote:
Ver 9.6.1

In a streaming replication can it be assumed that if both primary and standby are of the same hardware, then the rate at which transactions are applied on the standby will be same as that on primary. Or standbys are always slower than primary in applying transactions because of the way replication works.

It could go either way.  The standby only has to apply the changes, not compute them, so if the primary does something like: 

UPDATE foobar set col1 = (<slow select query returning one row and one column>) where col2=?  ;

then the standby will replay it much faster than the primary needed to execute it.

On the other hand, replay is done single-threaded.  If the primary has a lot of active concurrent connections, replaying them serially could be much slower than it took to produce them in the first place.  This might be true of both CPU and of IO.  If your IO is a big RAID system, the primary could keep multiple spindles active simultaneously by having multiple connections waiting on different pieces of data independently, while replay will wait on them serially.  There is currently not a prefetch mechanism for replay.

Cheers,

Jeff

pgsql-general by date:

Previous
From: Rakesh Kumar
Date:
Subject: [GENERAL] Transaction apply speed on the standby
Next
From: Jonathan Vanasco
Date:
Subject: [GENERAL] recursive query too big to complete. are there any strategies to limit/partition?