Greetings, * Konstantin Knizhnik (k.knizh...@postgrespro.ru) wrote: > There was very interesting presentation at pgconf about pg_prefaulter: > > http://www.pgcon.org/2018/schedule/events/1204.en.html
I agree and I've chatted a bit w/ Sean further about it. > But it is implemented in GO and using pg_waldump. Yeah, that's not too good if we want it in core. > I tried to do the same but using built-on Postgres WAL traverse functions. > I have implemented it as extension for simplicity of integration. > In principle it can be started as BG worker. I don't think this needs to be, or should be, an extension.. If this is worthwhile (and it certainly appears to be) then we should just do it in core. > First of all I tried to estimate effect of preloading data. > I have implemented prefetch utility with is also attached to this mail. > It performs random reads of blocks of some large file and spawns some number > of prefetch threads: > > Just normal read without prefetch: > ./prefetch -n 0 SOME_BIG_FILE > > One prefetch thread which uses pread: > ./prefetch SOME_BIG_FILE > > One prefetch thread which uses posix_fadvise: > ./prefetch -f SOME_BIG_FILE > > 4 prefetch thread which uses posix_fadvise: > ./prefetch -f -n 4 SOME_BIG_FILE > > Based on this experiments (on my desktop), I made the following conclusions: > > 1. Prefetch at HDD doesn't give any positive effect. > 2. Using posix_fadvise allows to speed-up random read speed at SSD up to 2 > times. > 3. posix_fadvise(WILLNEED) is more efficient than performing normal reads. > 4. Calling posix_fadvise in more than one thread has no sense. Ok. > I have tested wal_prefetch at two powerful servers with 24 cores, 3Tb NVME > RAID 10 storage device and 256Gb of RAM connected using InfiniBand. > The speed of synchronous replication between two nodes is increased from 56k > TPS to 60k TPS (on pgbench with scale 1000). I'm also surprised that it wasn't a larger improvement. Seems like it would make sense to implement in core using posix_fadvise(), perhaps in the wal receiver and in RestoreArchivedFile or nearby.. At least, that's the thinking I had when I was chatting w/ Sean. Thanks! Stephen
signature.asc
Description: PGP signature