Thanks Andres! See inline... Andres Freund <andres@xxxxxxxxxxx> writes: > Hi, > > On 2017-09-19 17:00:05 -0500, Jerry Sievers wrote: >> Briefly, just curious if legacy max values for shared_buffers have >> scaled up since 8G was like 25% of RAM? > > It's very workload dependent. I've successfully used PG with roughly 1TB > of shared buffers, where that performed better than lower > settings. Wow! Ok > > >> Pg 9.3 on monster 2T/192 CPU Xenial thrashing > > Not sure what the word "thrashing" in that sentence means. Cases of dozens or hundreds of sessions running typical statements for this system but running 100% on their CPUs. Seems to be triggered by certain heavy weight batch jobs kicking off on this generally OLTP system. ISTM there might be LW lock contention happening around some sort of shared resource where the lock wait implementation is a CPU spinner. > > Things have improved a lot since 9.3 WRT to scalability, so I'd not > infer too much from 9.3 performance on a larger box. Understood. The situation got worse when we moved to the even bigger box also running a 4.x kernel which I presume was no where near existent when 9.3 was our current Pg version. > > >> Upgrade pending but we recently started having $interesting performance >> issues at times looking like I/O slowness and other times apparently >> causing CPU spins. > > That's not something we can really usefully comment on given the amount > of information. Ack'd. I'd like to strace some of the spinning backends when/if we get another opportunity to observe the problem to see if by syscall or libfunc name we can learn more about what's the cause. > >> Anyway, shared_buffer coherency generally high but does take big dips >> that are sometimes sustained for seconds or even minutes. > > "shared_buffer coherency"? As measured querying pg_stat_databases and comparing total reads to read hits. Run frequently such as once /5-seconds and factored into a hit percentage. May stay up around 100% for several ticks but then go way down which may or not sustain. This is an OLTP app using Rails with hundreds of tables both trivial n structure as well as having partitions, large payloads... TOAST and the like. TPS can measure in the ~5-10k range. Thx again > > > Greetings, > > Andres Freund -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@xxxxxxxxxxx p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
