From: Kevin Neufeld <kneufeld@refractions.net>
To: pgsql-admin@postgresql.org
Subject: Re: Configuring Shared Buffers
Date: Mon, 30 Jun 2008 19:43:11 -0700
Message-ID: <486999BF.9030306@refractions.net> (raw)
In-Reply-To: <20080701010927.GH18252@alvh.no-ip.org>
References: <3a0028490806301052y193a4aeayfcc6deafdeb1cdb2@mail.gmail.com>
<20080630182830.GA5358@easy2.in-chemnitz.de>
<3a0028490806301201m5e4c48d4qc51a880b8f80ecf5@mail.gmail.com>
<20080701010927.GH18252@alvh.no-ip.org>
I agree. I have a similar system that I use for development purposes
and have the shared_buffers sitting comfortable around 1GB. On
production systems with 16GB of RAM, I've seen this as high as 12GB.
There is talk nowadays, however, that this setting could drop back down
to defaults on modern installations and let the OS handle cached memory
as it sees fit.
In any case, the biggest performance gain I see for you would be setting
work_mem appropriately. This is the memory postgres is permitted to use
for sorts, merges, hash joins, etc. before being forced to disk. It
defaults to 1MB. In my opinion, this is far too low. This is the
memory allocated to each sort/hash/etc operation. So for a complicated
query, postgres could use several allocations. Even though, I think you
could raise this considerably. If your system is a dedicated postgres
box, I would take the total memory, subtract that needed for the OS,
subtract what you decided to use for shared_buffers, and divide the rest
by your 100 connections. So, for you, I see this around 30MB. On my
development box with only a few connections, I have this around 500MB
and sometime spike it to 1.2GB on the fly before a long running query.
Cheers,
Kevin
Alvaro Herrera wrote:
> Rafael Domiciano escribió:
>
>> The Postgres version is 8.3.3 and I am using Fedora Core 8.
>> I have in the actual server around 70 connections the same time. I am
>> assigning for this 100.
>>
>
> 100 MB? That's not very much either. You can probably get a hefty
> performance boost by upping it a lot more (depending on whether the
> machine is doing something else, or Postgres is the only service running
> on it.)
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kneufeld@refractions.net
Subject: Re: Configuring Shared Buffers
In-Reply-To: <486999BF.9030306@refractions.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox