Re: sniff test on some PG 8.4 numbers - Mailing list pgsql-performance

On Sun, Mar 10, 2013 at 11:28 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> On 3/10/13 9:18 PM, Jon Nelson wrote:
>
>> The following is with ext4, nobarrier, and noatime. As noted in the
>> original post, I have done a fair bit of system tuning. I have the
>> dirty_bytes and dirty_background_bytes set to 3GB and 2GB,
>> respectively.
>
>
> That's good, but be aware those values are still essentially unlimited write
> caches.  A server with 4 good but regular hard drives might do as little as
> 10MB/s of random writes on a real workload.  If 2GB of data ends up dirty,
> the flushing that happens at the end of a database checkpoint will need to
> clear all of that out of RAM.  When that happens, you're looking at a 3
> minute long cache flush to push out 2GB.  It's not unusual for pgbench tests
> to pause for over a minute straight when that happens.  With your setup,
> where checkpoints happen every 5 minutes, this is only happening once per
> test run.  The disruption isn't easily visible if you look at the average
> rate; it's outweighed by the periods where writes happen very fast because
> the cache isn't full yet.  You have to get pgbench to plot latency over time
> to see them and then analyze that data.  This problem is the main reason I
> put together the pgbench-tools set for running things, because once you get
> to processing the latency files and make graphs from them it starts to be a
> pain to look at the results.

I'll try to find time for this, but it may need to wait until the weekend again.

>> I built 9.2 and using 9.2 and the following pgbench invocation:
>>
>> pgbench  -j 8  -c 32 -M prepared -T 600
>>
>> transaction type: TPC-B (sort of)
>> scaling factor: 400
>
>
> I misread this completely in your message before; I thought you wrote 4000.
> A scaling factor of 400 is making a database that's 6GB in size.  Your test
> is basically seeing how fast the system memory and the RAID cache can move
> things around.  In that situation, your read and write numbers are
> reasonable.  They aren't actually telling you anything useful about the
> disks though, because they're barely involved here. You've sniffed the CPU,
> memory, and RAID controller and they smell fine.  You'll need at least an
> order of magnitude increase in scale to get a whiff of the disks.

LOL! Your phrasing is humourous and the information useful.

I ran for 8.0 hours and go this:

transaction type: TPC-B (sort of)
scaling factor: 400
query mode: prepared
number of clients: 32
number of threads: 8
duration: 28800 s
number of transactions actually processed: 609250619
tps = 21154.058025 (including connections establishing)
tps = 21154.075922 (excluding connections establishing)

> pgbench scale numbers give approximately 16MB per scale factor.  You don't
> actually stress the drives until that total number is at least 2X as big as
> RAM.  We had to raise the limit on the pgbench scales recently because it
> only goes up to ~20,000 on earlier versions, and that's not a big enough
> scale to test many servers now.
>
> On the select-only tests, much of the increase from ~100K to ~200K is
> probably going from 8.4 to 9.2.  There's two major and several minor tuning
> changes that make it much more efficient at that specific task.
>
>
>> These are the *only* changes I've made to the config file:
>>
>> shared_buffers = 32GB
>> wal_buffers = 16MB
>> checkpoint_segments = 1024
>
>
> Note that these are the only changes that actually impact pgbench results.
> The test doesn't stress very many parts of the system, such as the query
> optimizer.
>
> Also be aware these values may not be practical to use in production. You
> can expect bad latency issues due to having shared_buffers so large.  All
> that memory has to be reconciled and written to disk if it's been modified
> at each checkpoint, and 32GB of such work is a lot.  I have systems where we
> can't make shared_buffers any bigger than 4GB before checkpoint pauses get
> too bad.
>
> Similarly, setting checkpoint_segments to 1024 means that you might go
> through 16GB of writes before a checkpoint happens.  That's great for
> average performance...but when that checkpoint does hit, you're facing a
> large random I/O backlog.

I thought the bgwriter mitigated most of the problems here? Often I'll
see the actual checkpoints with 'sync' times typically below a few
seconds (when there is anything to do at all). I can't say I've seen
checkpoint pauses in my workloads.

> There's not much you can do about all this on the Linux side.  If you drop
> the dirty_* parameters too much, maintenance operations like VACUUM start to
> get slow.  Really all you can do is avoid setting shared_buffers and
> checkpoint_segments too high, so the checkpoint backlog never gets gigantic.
> The tuning you've done is using higher values than we normally recommend
> because it's not quite practical to deploy like that.  That and the very
> small database are probably why your numbers are so high.

Mostly I do data warehouse type of workloads with very little (if any)
data modification after initial load time. Extensive benchmarking of
the actual applications involved has shown that - for me - a large
(but not too large) shared_buffers (32GB is right about the sweet spot
for me, perhaps a bit on the high side) works well. Additionally, the
large checkpoint_segments value really appears to help as well (again,
this is very workload dependent).

>> Note: I did get better results with HT on vs. with HT off, so I've
>> left HT on for now.
>
>
> pgbench select-only in particular does like hyper-threading.  We get
> occasional reports of more memory-bound workloads actually slowing when it's
> turned on.  I think it's a wash and leave it on.  Purchasing and management
> people tend to get annoyed if they discover the core count of the server is
> half what they thought they were buying.  The potential downside of HT isn't
> so big that its worth opening that can of worms, unless you've run real
> application level tests to prove it hurts.

Glad to get an "it's a wash" confirmation here.


--
Jon