Re: Up to date conventional wisdom re max shared_buffer size?

Thanks Andres! See inline...

Andres Freund <andres(at)anarazel(dot)de> 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(dot)consulting(at)comcast(dot)net
p: 312.241.7800