Re: WAL prefetch

On 07/09/2018 02:26 AM, Sean Chittenden wrote:
>
> ... snip ...
>
> The real importance of prefaulting becomes apparent in the following two
> situations:
>
> 1. Priming the OS's filesystem cache, notably after an OS restart. This is of
> value to all PostgreSQL scenarios, regardless of whether or not it's a
> primary or follower. Reducing database startup/recovery times is very
> helpful, especially when recovering from an outage or after having performed
> planned maintenance. Little in PostgreSQL administration is more infuriating
> than watching PostgreSQL recover and seeing the CPU 100% idle and the disk IO
> system nearly completely idle (especially during an outage or when recovering
> from an outage).
> 2. When the following two environmental factors are true:
> a. the volume of writes to discrete pages is high
> b. the interval between subsequent writes to a single page is long enough
> that a page is evicted from both shared_buffers and the filesystem cache
>
> Write-heavy workloads tend to see this problem, especially if you're
> attempting to provide consistency in your application and do not read from
> the followers (thereby priming their OS/shared_buffer cache). If the
> workload is continuous, the follower may never be able overcome the write
> volume and the database never catches up.
>
> The pg_prefaulter was borne out of the last workload, namely a write-heavy, 24/7
> constant load with a large dataset.
>

Good, that generally matches the workload I've been using for testing.

>
> What pg_prefaulter does is read in the blocks referenced from the WAL stream
> (i.e. PG heap pages) and then load the referenced pages into the OS filesystem
> cache (via threaded calls to pread(2)). The WAL apply process has a cache-hit
> because the filesystem cache has been primed with the heap page before the apply
> process attempted to perform its read-modify-write of the heap.
>
> It is important to highlight that this is a problem because there is only one
> synchronous pread(2) call in flight at a time from the apply/recover/startup
> process, which effectively acts as the speed limit for PostgreSQL. The physics
> of many workloads are such that followers are unable to keep up and are thus
> destined to always fall behind (we've all seen this at some point, likely via
> apply lag from a VACUUM or pg_repack). The primary can schedule concurrent IO
> from multiple client all making independent SELECTS. Contrast that to a replica
> who has zero knowledge of the IOs that the primary recently dispatched, and all
> IO looks like random read and likely a cache miss. In effect, the pg_prefaulter
> raises the speed limit of the WAL apply/recovery process by priming the
> filesystem cache by snooping in on the WAL stream.
>
> PostgreSQL's WAL apply and recovery process is only capable of scheduling a
> single synchronous pread(2) syscall. As a result, even if you have an RAID10
> and a capable IO scheduler in the OS that is able to read form both halves of
> each mirror, you're only going to perform ~150-225 pread(2) calls per second.
> Despite the entire disk system being able to deliver something closer to
> 2400-3600 IOPS (~10ms random-read == ~150-225 IOPS * 16x disks), you'll only
> observe ~6% utilization of the random read IO capabilities of a server. When
> you realize the size of the unapplied WAL entries represents a backlog of queued
> or unscheduled IOs, the phrase "suboptimal" doesn't begin to do reality justice.
>
> One or more of the following activities can demonstrate the problem:
>
> * Natural random-write workloads at high velocity
> * VACUUM activity
> * pg_repack
> * slow IO subsystems on followers
> * synchronous apply
>
> Regarding the environment where pg_prefaulter was written, the server hardware
> was reasonably beefy servers (256GB RAM, 16x 10K SAS disks) and this database
> cluster was already in a scale-out configuration. Doubling the number of
> database servers would only spread the load out by 2x, but we'd still only be
> utilizing ~6% of the IO across the fleet. We needed ~100% IO utilization when
> followers were falling behind. In practice we are seeing orders of magnitude
> improvement in apply lag.
>

Yeah, the poor I/O utilization is annoying. Considering the storage is
often the most expensive part of the database system, it's a bit like
throwing money out of the window :-/

>
> Other points worth mentioning:
>
> * the checkpoint_interval was set to anywhere between 15s and 300s, it didn't
> matter - we did discover a new form of lag, however, checkpoint lag. Pages
> were being evicted from cache faster than checkpoints were able to execute,
> leading to unbounded checkpoints and WAL growth (i.e. writes were fast enough
> that the checkpointer was suffering from Cold RMW). iirc, pg_prefaulter reads
> in both WAL pages and WAL files that are about to be used in checkpoints (it's
> been a while since I wrote this code).
>

Hmmm, I'm not sure how a checkpointer could hit a cold RMW, considering
it merely writes out dirty pages from shared buffers. Although, perhaps
it's specific to ZFS setups with 16kB record sizes?

> * The pg_prefaulter saw the best performance when we increased the number of IO
> workers to be roughly equal to the available IO commands the OS could schedule
> and dispatch (i.e. 16x disks * 150 IOPS == ~2K).
>

Yeah. I wonder how would this work for flash-based storage that can
achieve much higher IOPS values.

> * pg_prefaulter is very aggressive about not performing work twice or reading
> the same page multiple times. pg_prefaulter uses a heap page cache to prevent
> redundant IOs for the same PG heap page. pg_prefaulter also dedupes IO
> requests in case the same page was referenced twice in short succession due to
> data locality in the WAL stream. The workload was already under cache
> pressure. Artificially promoting a page from the ARC MRU to MFU would result
> in potentially useful records in the MFU being evicted from cache.
>

Makes sense. I think the patch does that too, by keeping a cache of
recently prefetched blocks.

> * During the design phase, I looked into using bgworkers but given the number of
> in-flight pread(2) calls required to fully utilize the IO subsystem, I opted
> for something threaded (I was also confined to using Solaris which doesn't
> support posix_fadvise(2), so I couldn't sequentially dispatch async
> posix_fadvise(2) calls and hope for the best).
>

Hmm, yeah. I'm not sure what to do with this. Using many (thousands?) of
prefetch processes seems like a bad idea - we surely can't make them
regular bgworkers. Perhaps we could use one process with many threads?

Presumably if we knew about a better way to do prefetching without
posix_fadvise, we'd have implemented it in FilePrefetch(). But we just
error out instead :-(

> * In my testing I was successfully using pgbench(1) to simulate the workload.
> Increased the checkpoint_interval and segments to a very high number was
> sufficient. I could see the improvement for cold-start even with SSDs, but
> I'm not sure how big of an impact this would be for NVMe.
>

I think the impact on NVMe (particularly Optane) will be smaller,
because the devices handle low queue depths better, particularly for
reads. AFAIK it's the opposite for writes (higher queue depths are
needed), but writes are kinda throttled by reads (faster recovery means
more write requests). But then again, if you have multiple NVMe devices
in a RAID, that means non-trivial number of requests is needed.

> * My slides are posted and have graphs of the before and after using the
> pg_prefaulter, but I'm happy to provide additional details or answer more Q's.
>
> * It would be interesting to see if posix_fadvise(2) is actually harming
> performance. For example, spinning off a throw-away implementation that uses
> aio or a pthread worker pool + pread(2). I do remember seeing some mailing
> list blurbs from Mozilla where they were attempting to use posix_fadvise(2)
> and were seeing a slow down in performance on Linux (I believe this has since
> been fixed, but it wouldn't surprise me if there were still unintended
> consequences from this syscall).
>

Not sure, but in this case we can demonstrate it clearly helps. Maybe
there's an alternative way to do async prefetching, performing better
(say, aio or whatever), but I've seen plenty of issues with those too.

> * I have a port of pg_prefaulter that supports PostgreSQL >= 9 ~complete, but
> not pushed. I'll see if I can get to that this week. For "reasons" this
> isn't a high priority for me at the moment, but I'm happy to help out and see
> this move forward.
>

Good to hear that.

> * Tomas, feel free to contact me offline to discuss why the pg_prefault isn't
> working for you. I have it running on Linux, FreeBSD, illumos, and macOS.
>

Will do. It can easily be due to my lack of golang knowledge, or
something similarly silly.

> * In the graph from Tomas (nice work, btw), it's clear that the bandwidth is the
> same. The way that we verified this earlier was to run ~10-15min traces and
> capture the file and offset of every read of PostgreSQL and pg_prefaulter. We
> saw pg_prefaulter IOs be ~100% cache miss. For PostgreSQL, we could observe
> that ~99% of its IO was cache hit. We also verified that pg_prefaulter wasn't
> doing any IO that wasn't eventually performed by PostgreSQL by comparing the
> IOs performed against each heap segment.
>

I'm not sure what bandwidth?

>
> * "In this case I see that without prefetching, the replay process uses about
> 20% of a CPU. With prefetching increases this to ~60%, which is nice." With
> the pg_prefaulter, the IO should hit 100% utilization. Like I mentioned
> above, Tomas, I'd like to make sure you get this working so you can compare
> and improve as necessary. :~] I never got CPU utilization to 100%, but I did
> get disk IO utilization to 100%, and that to me was the definition of success.
> CPU utilization of the apply process could become 100% utilized with fast
> enough disks but in production I didn't have anything that wasn't spinning
> rust.
>

Not sure 100% is really achievable, but we can try. There's room for
improvement, that's for sure.

> * It looks like we're still trying to figure out the nature of this problem and
> the cost of various approaches. From a rapid prototyping perspective, feel
> free to suggest changes to the Go pg_prefaulter and toss the various
> experiments behind a feature flag.
>
> * "> But it is implemented in GO and using pg_waldump.
> Yeah, that's not too good if we want it in core."
> I fail to see the problem with a side-car in Go. *checks calendar* :~]
>

I think there's a couple of valid reasons for that. It's not that we're
somehow against Go in principle, but adding languages into a code base
makes it more difficult to maintain it. Also, if we want to integrate it
with core (start it automatically on replicas, make it access internal
state etc.) it's just easier to do that from C.

It can be done from a standalone tool (say, an extension written in Go).
But then why make it part of core at all? That has disadvantages too,
like coupling the release cycle etc.

> * pg_prefaulter will not activate if the number of unapplied WAL pages is less
> than the size of 1 WAL segment (i.e. 16MB). This could be tuned further, but
> this low-water mark seems to work well.
>
> * pg_prefaulter won't read-ahead more than 100x WAL segments into the future. I
> made the unverified assumption that PostgreSQL could not process more than
> 1.6GB of WAL (~1/2 of the memory bandwidth available for a single process) in
> less than the rotational latency of a random IO (i.e. ~10ms), and that
> pg_prefaulter could in theory stay ahead of PG. PostgreSQL normally overtakes
> pg_prefaulter's ability to fault in random pages due to disk IO limitations
> (PostgreSQL's cache hit rate is ~99.9%, not 100% for this very reason). In
> practice this has worked out, but I'm sure there's room for improvement with
> regards to setting the high-watermark and reducing this value. #yoloconstants
>

I think there's a stable state where the recovery reaches maximum
performance and we don't prefetch pages too far ahead (at some point the
recovery speed will stop improving, and eventually start decreasing
because we'll end up pushing out pages we've prefetched). I wonder how
we could auto-tune this.

> * I contemplated not reading in FPW but this would have been detrimental on ZFS
> because ZFS is a copy-on-write filesystem (vs block filesystem). For ZFS, we
> are using a 16K record size, compressed down to ~8K. We have to read the
> entire record in before we can modify half of the page. I suspect eliding
> prefaulting FPWs will always be a performance loss for nearly all hardware.
>

That's a good point - on regular filesystems with small pages we can
just skip FPW (in fact, we should treat them as prefetched), while on
ZFS we need to prefetch them. We probably need to make this configurable.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services