On 11/2/15 9:32 AM, Tom Dearman wrote:
> My system under load is using just over 500M of the shared_buffer at
> usage count >= 3. Our system is very write heavy, with all of the big
> tables written to but not read from (at least during the load test run).
> Although our db will grow (under load) to 100G in a few hours and keep
> growing, the data in shared_buffers - according to my observations above
> - seems low.
That query won't tell you much for this particular case. When you have a
workload that's creating tons of new pages that's going to very quickly
fill shared_buffers with a ton of dirty pages that need to be written
out. I suspect you're generating more dirty pages than the bgwriter can
keep up with. That means that the scan to find an available buffer that
I mentioned in my other email is going to become insanely expensive:
First it's going to have to run the clock sweep long enough to identify
a victim, then it's going to have to wait until that victim buffer is
flushed to disk. That flush to disk also can't happen until the WAL is
flushed, so it might have to wait on that as well.
Can you run perf or something equivalent? That would give us a much
better idea where all the time is actually being spent.
> dev8-0 to dev8-5 are the represent the disk where the WAL is. dev8-16
Are you sure that's not dev8-0 and dev8-1? Because the patterns for your
bonnie++ tests show no activity on 8-2 through 8-5.
Also, keep in mind that for the question of how long you'll wait for the
extension lock bandwidth and throughput (and even TPS) don't matter:
it's all a question of how long it takes for disk requests to be
serviced (and unfortunately iostat really sucks at measuring that).
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com