Hi Jakub,
On Wed, 5 May 2021 at 20:16, Jakub Wartak <Jakub.Wartak@tomtom.com> wrote:
> I might be a little bit out of the loop, but as Alvaro stated - Thomas did plenty of excellent job related to
recoveryperformance in that thread. In my humble opinion and if I'm not mistaken (I'm speculating here) it might be
*not*how Smgr hash works, but how often it is being exercised and that would also explain relatively lower than
expected(?)gains here. There are at least two very important emails from him that I'm aware that are touching the topic
ofreordering/compacting/batching calls to Smgr:
> https://www.postgresql.org/message-id/CA%2BhUKG%2B2Vw3UAVNJSfz5_zhRcHUWEBDrpB7pyQ85Yroep0AKbw%40mail.gmail.com
> https://www.postgresql.org/message-id/flat/CA%2BhUKGK4StQ%3DeXGZ-5hTdYCmSfJ37yzLp9yW9U5uH6526H%2BUeg%40mail.gmail.com
I'm not much of an expert here and I didn't follow the recovery
prefetching stuff closely. So, with that in mind, I think there are
lots that could be done along the lines of what Thomas is mentioning.
Batching WAL records up by filenode then replaying each filenode one
by one when our batching buffer is full. There could be some sort of
parallel options there too, where workers replay a filenode each.
However, that wouldn't really work for recovery on a hot-standby
though. We'd need to ensure we replay the commit record for each
transaction last. I think you'd have to batch by filenode and
transaction in that case. Each batch might be pretty small on a
typical OLTP workload, so it might not help much there, or it might
hinder.
But having said that, I don't think any of those possibilities should
stop us speeding up smgropen().
> Another potential option that we've discussed is that the redo generation itself is likely a brake of efficient
recoveryperformance today (e.g. INSERT-SELECT on table with indexes, generates interleaved WAL records that touch often
limitedset of blocks that usually put Smgr into spotlight).
I'm not quite sure if I understand what you mean here. Is this
queuing up WAL records up during transactions and flush them out to
WAL every so often after rearranging them into an order that's more
optimal for replay?
David
