Обсуждение: Shared_buffers hint

On Mon, Oct 6, 2008 at 5:52 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> HI people,
> I'm tuning a server that is a long time abandoned, and this server is a
> little slow.

What version pgsql is it running?  If it's 7.4 or before, you should
look at upgrading it.  If you can't then a shared buffer setting in
the 1000 to 10000 range is generally reasonable, but large
shared_buffer settings (i.e. over 10000) are counterproductive for 7.4
and before.

> Now, in my postgresql.conf I have:
> shared_buffers = 100Mb

So it's a pretty modern version, since old ones can't read 100Mb as a setting.

> mas_fsm_relations = 15000
> mas_fsm_pages = 1600000
> max_connections = 300
> And I changin' to:
> shared_buffers = 1000Mb
>    (The server has 2 Gb of memory)

50% is kind of big.  Generally you either want it small enough that
the OS can do the majority of the caching (it's usually better at
caching large amounts of data) or large enough that the kernel cache
doesn't come into play much.  50% means that everything is buffered
exactly twice.

> mas_fsm_relations = 15000
>    (The Vacuum noticed me 608 relations)
> mas_fsm_pages = 1600000
>    (The Vacuum noticed me 500800 pages)
> max_connections = 300
>    (I did a "ps axf | grep postgres | wc -l" and the bash brings to me the
> number of 120 and all the people is not online at now)
> My qustion is about shared_buffers, in postgresql.conf I have: min 128kB or
> max_connections*16kB  # (change requires restart)
> So I did:
> 300 * (16Kb / 1024) = 4,69?
> What this result means?

That's just the minimum the server needs to operate.  Not operate
well, just operate.

On Mon, Oct 6, 2008 at 7:56 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> I'm using 8.3.3 version in this machine..!
> So, the shared_buffers set to 100Mb is ok? Or 500Mb is better?
> This server we call "Reporter Server", so it's used to do heavy reports for
> a few users only (I could say 5 users).
> What could be the best config for my server with:
> 2 Gb RAM
> 300 GB HD
> Postgres 8.3
> Dual Core

100M is probably adequate.  2G is a pretty small database server
memory wise.  I assume by 300G HD you mean a single hard drive.  Since
a single hard drive is going to limit the speed at which you can
access data from it, I'd leave shared_buffers at 100M and let the OS
cache data for you.  Also, look at work_mem.  You might want to set
those few heavy users to have more work_mem than the other users.

alter user heavyuser set work_mem=128000;

note that work_mem is per user sort, so it's quite possible to exhaust
main memory if you set it high for everybody and they all do sorts on
large sets suddenly.