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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
