4 Replies
xenial-black•2y ago
Just asked internally, seems like the theory at the moment is that we have fairly small shared buffers. We try to mitigate by having a file cache that lives in tmpfs that we can resize at will. Unfortunately this cache is single-threaded, so if multiple backends try to access the cache they'll be contending for a lock, which probably causes the slowness
How big are the shared_buffers for you?
Also note that we wait for WAL to be ack-ed by safekeepers before we consider anything committed, rather than local disk. That can also significantly increase latency.
plain-purple•2y ago
Thanks for looking, Sasha. I have
shared_buffers at 60GB. RIght, the archiecture of Neon being the "WAL is the database" implied to me that there can't really be WAL-bypass gains with things like CTAS as in vanilla PostgreSQL. No big deal. I do see some improvements with parallelism.
@SashaBiceps do y'all see this behavior for deletes, too?
I've been testing PL/pgSQL procedures like:
where table1|2 are around 128GiB each. The delete is taking as long as 17 hours.
Running compute on a dedicated VM with 256GiB of memory, and max_parallel* settings engaged. Not seeing the parallel workers engaging for the delete, and wondering if i'm missing something or some setting.xenial-black•2y ago
I think Postgres in general doesn’t use parallel workers for deletes
plain-purple•2y ago
When you were saying
shared_buffers are fairly small for y'all, about how small? Should they be in MiB to force page reconstruction and speed things up as mentioned here, or should they be larger (GiB) to do more work without the PageServer?GitHub
Increase WAL redo speed · Issue #1339 · neondatabase/neon
We are currently applying WAL records in separate process (wal-redo postgres), sending wal records through the pipe and receiving reconstructed pages as response. It adds quite significant overhead...