Обсуждение: pgtune + configurations with 9.3

Tory M Blue wrote:
> I've got some beefy hardware but have some tables that are over 57GB raw and end up at 140GB size
> after indexes are applied. One index creation took 7 hours today. So it's time to dive in and see
> where i'm lacking and what I should be tweaking.
> 
> I looked at pgtune again today and the numbers it's spitting out took me back, they are huge. From all
> historical conversations and attempts a few of these larger numbers netted reduced performance vs
> better performance (but that was on older versions of Postgres).
> 
> So I come here today to seek out some type of affirmation that these numbers look good and I should
> look at putting them into my config, staged and or in one fell swoop.
> 
> I will start at the same time migrating my config to the latest 9.3 template...
> 
> Postgres Version: 9.3.4, Slony 2.1.3 (migrating to 2.2).
> CentOS 6.x, 2.6.32-431.5.1.el6.x86_64
> Big HP Boxen.
> 
> 32 core, 256GB of Ram DB is roughly 175GB in size but many tables are hundreds of millions of rows.
> 
> The pgtune configurations that were spit out based on the information above;
> 
> max_connections = 300

That's a lot, but equals what you currently have.
It is probably ok, but can have repercussions if used with large work_mem:
Every backend can allocate that much memory, maybe even several times for a complicated query.

> shared_buffers = 64GB

That seems a bit on the large side.
I would start with something like 4GB and run (realistic) performance tests, doubling the value each time.
See where you come out best.
You can use the pg_buffercache contrib to see how your shared buffers are used.

> effective_cache_size = 192GB

That should be all the memory in the machine that is available to PostgreSQL,
so on an exclusive database machine it could be even higher.

> work_mem = 223696kB

That looks ok, but performance testing wouldn't harm.
Ideally you log temporary file creation and have this parameter big enough so that
normal queries don't need temp files, but low enough so that the file system cache still has
some RAM left.

> maintenance_work_mem = 2GB

That's particularly helpful for your problem, index creation.

> checkpoint_segments = 32

Check.
You want checkpoints to be time triggered, so don't be afraid to go higher
if you get warnings unless a very short restore time is of paramount importance.

> checkpoint_completion_target = 0.7

Check.

> wal_buffers = 16MB

That's fine too, although with 9.3 you might as well leave it default.
With that much RAM it will be autotuned to the maximum anyway.

> default_statistics_target = 100

That's the default value.
Increase only if you get bad plans because of insufficient statistics.

Yours,
Laurenz Albe

On 10/29/2014 11:49 PM, Tory M Blue wrote:
> I looked at pgtune again today and the numbers it's spitting out took me
> back, they are huge. From all historical conversations and attempts a few
> of these larger numbers netted reduced performance vs better performance
> (but that was on older versions of Postgres).

Yeah, pgTune is pretty badly out of date.  It's been on my TODO list, as
I'm sure it has been on Greg's.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

> Yeah, pgTune is pretty badly out of date.  It's been on my TODO list, as
> I'm sure it has been on Greg's.

Yeah. And unfortunately the recommendations it gives have been spreading. Take a look at the online version:

http://pgtune.leopard.in.ua/

I entered a pretty typical 92GB system, and it recommended 23GB of shared buffers. I tried to tell the author the
performanceguidelines have since changed, but it didn't help. 


______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

Fri, 7 Nov 2014 14:13:20 +0000 от Shaun Thomas <sthomas@optionshouse.com>:
>> Yeah, pgTune is pretty badly out of date.  It's been on my TODO list, as
>> I'm sure it has been on Greg's.
>
>Yeah. And unfortunately the recommendations it gives have been spreading. Take a look at the online version:
>
>http://pgtune.leopard.in.ua/
>
>I entered a pretty typical 92GB system, and it recommended 23GB of shared buffers. I tried to tell the author the
performanceguidelines have since changed, but it didn't help. 
>
>
>______________________________________________
>
>See  http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
>
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance


Hello, author of http://pgtune.leopard.in.ua/ is here.

I think everyone can do pull request to it. Old one take 25% for shared_buffers and 75% for effective_cache_size. I
thinkI can even add selector with version of postgresql (9.0 - 9.4) and in this case change formulas for 9.4 (for
example).

But I don't know what type of calculation should be in this case. Does we have in some place this information? Or
someonecan provide it? Because this generator should be valid for most users. 

Thanks.
---
Alexey Vasiliev

Alexey,

The issue is that the 1/4 memory suggestion hasn't been a recommendation in quite a while. Now that much larger amounts
ofRAM are readily available, tests have been finding out that more than 8GB of RAM in shared_buffers has diminishing or
evenworse returns. This is true for any version. Further, since PostgreSQL manages its own memory, and the Linux Kernel
alsomanages various caches, there's significant risk of storing the same memory both in shared_buffers, and in file
cache.

There are other tweaks the tool probably needs, but I think this, more than anything else, needs to be updated. Until
PGsolves the issue of double-buffering (which is somewhat in progress since they're somewhat involved with the Linux
kerneldevs) you can actually give it too much memory. 


______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

Fri, 14 Nov 2014 16:28:16 +0000 от Shaun Thomas <sthomas@optionshouse.com>:
> Alexey,
>
> The issue is that the 1/4 memory suggestion hasn't been a recommendation in quite a while. Now that much larger
amountsof RAM are readily available, tests have been finding out that more than 8GB of RAM in shared_buffers has
diminishingor even worse returns. This is true for any version. Further, since PostgreSQL manages its own memory, and
theLinux Kernel also manages various caches, there's significant risk of storing the same memory both in
shared_buffers,and in file cache. 
>
> There are other tweaks the tool probably needs, but I think this, more than anything else, needs to be updated. Until
PGsolves the issue of double-buffering (which is somewhat in progress since they're somewhat involved with the Linux
kerneldevs) you can actually give it too much memory. 
>
>
> ______________________________________________
>
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Several months ago I asked question in this channel "Why shared_buffers max is 8GB?". Many persons said, what this is
apocrypha,what 8GB is maximum value for shared_buffers. This is archive of this chat:
http://www.postgresql.org/message-id/1395836511.796897979@f327.i.mail.ru

What is why so hard to understand what to do with pgtune calculation.

--
Alexey Vasiliev

Alexey,

The issue is not that 8GB is the maximum. You *can* set it higher. What I'm saying, and I'm not alone in this, is that
settingit higher can actually decrease performance for various reasons. Setting it to 25% of memory on a system with
512GBof RAM for instance, would be tantamount to disaster. A checkpoint with a setting that high could overwhelm pretty
muchany disk controller and end up  completely ruining DB performance. And that's just *one* of the drawbacks. 



______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

Fri, 14 Nov 2014 17:06:54 +0000 от Shaun Thomas <sthomas@optionshouse.com>:
> Alexey,
>
> The issue is not that 8GB is the maximum. You *can* set it higher. What I'm saying, and I'm not alone in this, is
thatsetting it higher can actually decrease performance for various reasons. Setting it to 25% of memory on a system
with512GB of RAM for instance, would be tantamount to disaster. A checkpoint with a setting that high could overwhelm
prettymuch any disk controller and end up  completely ruining DB performance. And that's just *one* of the drawbacks. 
>
>
>
> ______________________________________________
>
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Ok. Just need to know what think another developers about this - should pgtune care about this case? Because I am not
sure,what users with 512GB will use pgtune. 

--
Alexey Vasiliev

On 15/11/14 06:06, Shaun Thomas wrote:
> Alexey,
>
> The issue is not that 8GB is the maximum. You *can* set it higher. What I'm saying, and I'm not alone in this, is
thatsetting it higher can actually decrease performance for various reasons. Setting it to 25% of memory on a system
with512GB of RAM for instance, would be tantamount to disaster. A checkpoint with a setting that high could overwhelm
prettymuch any disk controller and end up  completely ruining DB performance. And that's just *one* of the drawbacks. 
>

It is probably time to revisit this 8GB limit with some benchmarking. We
don't really have a hard and fast rule that is known to be correct, and
that makes Alexey's job really difficult. Informally folk (including
myself at times) have suggested:

min(ram/4, 8GB)

as the 'rule of thumb' for setting shared_buffers. However I was
recently benchmarking a machine with a lot of ram (1TB) and entirely SSD
storage [1], and that seemed quite happy with 50GB of shared buffers
(better performance than with 8GB). Now shared_buffers was not the
variable we were concentrating on so I didn't get too carried away and
try much bigger than about 100GB - but this seems like a good thing to
come out with some numbers for i.e pgbench read write and read only tps
vs shared_buffers 1 -> 100 GB in size.

Cheers

Mark

[1] I may be in a position to benchmark the machines these replaced at
some not to distant time. These are the previous generation (0.5TB ram,
32 cores and all SSD storage) but probably still good for this test.

On 11/14/14, 5:00 PM, Mark Kirkwood wrote:
>
> as the 'rule of thumb' for setting shared_buffers. However I was recently benchmarking a machine with a lot of ram
(1TB)and entirely SSD storage [1], and that seemed quite happy with 50GB of shared buffers (better performance than
with8GB). Now shared_buffers was not the variable we were concentrating on so I didn't get too carried away and try
muchbigger than about 100GB - but this seems like a good thing to come out with some numbers for i.e pgbench read write
andread only tps vs shared_buffers 1 -> 100 GB in size. 

What PG version?

One of the huge issues with large shared_buffers is the immense overhead you end up with for running the clock sweep,
andon most systems that overhead is born by every backend individually. You will only see that overhead if your
databaseis larger than shared bufers, because you only pay it when you need to evict a buffer. I suspect you'd actually
needa database at least 2x > shared_buffers for it to really start showing up. 

> [1] I may be in a position to benchmark the machines these replaced at some not to distant time. These are the
previousgeneration (0.5TB ram, 32 cores and all SSD storage) but probably still good for this test. 

Awesome! If there's possibility of developers getting direct access, I suspect folks on -hackers would be interested.
Ifnot but you're willing to run tests for folks, they'd still be interested. :) 
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 15/11/14 15:08, Jim Nasby wrote:
> On 11/14/14, 5:00 PM, Mark Kirkwood wrote:
>>
>> as the 'rule of thumb' for setting shared_buffers. However I was
>> recently benchmarking a machine with a lot of ram (1TB) and entirely
>> SSD storage [1], and that seemed quite happy with 50GB of shared
>> buffers (better performance than with 8GB). Now shared_buffers was not
>> the variable we were concentrating on so I didn't get too carried away
>> and try much bigger than about 100GB - but this seems like a good
>> thing to come out with some numbers for i.e pgbench read write and
>> read only tps vs shared_buffers 1 -> 100 GB in size.
>
> What PG version?
>
> One of the huge issues with large shared_buffers is the immense overhead
> you end up with for running the clock sweep, and on most systems that
> overhead is born by every backend individually. You will only see that
> overhead if your database is larger than shared bufers, because you only
> pay it when you need to evict a buffer. I suspect you'd actually need a
> database at least 2x > shared_buffers for it to really start showing up.
>

That was 9.4 beta1 and2.

A variety of db sizes were tried, some just fitting inside
shared_buffers and some a bit over 2x larger, and one variant where we
sized the db to 600GB, and used 4,8 and 50GB shared_buffers (50 was the
best by a small margin...and certainly no worse).

Now we were mainly looking at 60 core performance issues (see thread "60
core performance with 9.3"), and possibly some detrimental effects of
larger shared_buffers may have been masked by this - but performance was
certainly not hurt with larger shared_buffers.

regards

Mark

On 15 November 2014 02:10, Alexey Vasiliev <leopard_ne@inbox.ru> wrote:

> Ok. Just need to know what think another developers about this - should pgtune care about this case? Because I am not
sure,what users with 512GB will use pgtune. 

pgtune should certainly care about working with large amounts of RAM.
Best practice does not stop at 32GB of RAM, but instead becomes more
and more important. I am not interested in edge cases or unusual
configurations. I am interested in setting decent defaults to provide
a good starting point to administrators on all sizes of hardware.

I use pgtune to configure automatically deployed cloud instances. My
goal is to prepare instances that have been tuned according to best
practice for standard types of load. Administrators will ideally not
need to tweak anything themselves, but at  a minimum have been
provided with a good starting point. pgtune does a great job of this,
apart from the insanely high shared_buffers. At the moment I run
pgtune, and then must reduce shared_buffers to 8GB if pgtune tried to
select a higher value. The values it is currently choosing on higher
RAM boxes are not best practice and quite wrong.

The work_mem settings also seem to be very high, but so far have not
posed a problem and may well be correct. I'm trusting pgtune here
rather than my outdated guesses.

--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/

On 15 November 2014 06:00, Mark Kirkwood <mark.kirkwood@catalyst.net.nz> wrote:

> It is probably time to revisit this 8GB limit with some benchmarking. We
> don't really have a hard and fast rule that is known to be correct, and that
> makes Alexey's job really difficult. Informally folk (including myself at
> times) have suggested:
>
> min(ram/4, 8GB)
>
> as the 'rule of thumb' for setting shared_buffers. However I was recently

It would be nice to have more benchmarking and improve the rule of
thumb. I do, however, believe this is orthogonal to fixing pgtune
which I think should be using the current rule of thumb (which is
overwhelmingly min(ram/4, 8GB) as you suggest).



> benchmarking a machine with a lot of ram (1TB) and entirely SSD storage [1],
> and that seemed quite happy with 50GB of shared buffers (better performance
> than with 8GB). Now shared_buffers was not the variable we were
> concentrating on so I didn't get too carried away and try much bigger than
> about 100GB - but this seems like a good thing to come out with some numbers
> for i.e pgbench read write and read only tps vs shared_buffers 1 -> 100 GB
> in size.

I've always thought the shared_buffers setting would need to factor in
things like CPU speed and memory access, since the rational for the
8GB cap has always been the cost to scan the data structures. And the
kernel would factor in too, since the PG specific algorithms are in
competition with the generic OS algorithms. And size of the hot set,
since this gets pinned in shared_buffers. Urgh, so many variables.

--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/