From: Dave Cramer <pg@fastcrypt.com>
To: Francisco Reyes <lists@stringsutils.com>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: Vacuums on large busy databases
Date: Thu, 14 Sep 2006 20:07:09 -0400
Message-ID: <530B4050-130F-441D-9F4A-EA0AF30DADA9@fastcrypt.com> (raw)
In-Reply-To: <cone.1158277856.620206.75607.5001@35st-server.simplicato.com>
References: <cone.1158247381.60704.30981.1000@zoraida.natserv.net>
<56FA8838-6DEA-4505-990D-85D5EDA26243@fastcrypt.com>
<cone.1158255415.91768.31944.1000@zoraida.natserv.net>
<470FED70-B3BA-4B6C-A6B1-DFD8882746B2@fastcrypt.com>
<cone.1158265846.124028.75607.5001@35st-server.simplicato.com>
<716E889C-296C-4CB7-9733-0EAD1E623BFB@fastcrypt.com>
<cone.1158277856.620206.75607.5001@35st-server.simplicato.com>
On 14-Sep-06, at 7:50 PM, Francisco Reyes wrote:
> Dave Cramer writes:
>
>> personally, I'd set this to about 6G. This doesn't actually
>> consume memory it is just a setting to tell postgresql how much
>> memory is being used for cache and kernel buffers
>
> Gotcha. Will increase further.
>
>> regarding shared buffers I'd make this much bigger, like 2GB or more
>
> Will do 2GB on the weekend. From what I read this requires shared
> memory so have to restart my machine (FreeBSD).
>
> if I plan to give shared buffers 2GB, how much more over that
> should I give the total shared memory kern.ipc.shmmax? 2.5GB?
I generally make it slightly bigger. is shmmax the size of the
maximum chunk allowed or the total ?
>
> Also will shared buffers impact inserts/updates at all?
> I wish the postgresql.org site docs would mention what will be
> impacted.
Yes, it will, however not as dramatically as what you are seeing with
effective_cache
>
> Comments like: This setting must be at least 16, as well as at
> least twice the value of max_connections; however, settings
> significantly higher than the minimum are usually needed for good
> performance.
>
> Are usefull, but could use some improvement.. increase on what? All
> performance? inserts? updates? selects?
>
> For instance, increasing effective_cache_size has made a noticeable
> difference in selects. However as I talk to the developers we are
> still doing marginally in the inserts. About 150/min.
The reason is that with effective_cache the select plans changed (for
the better) ; it's unlikely that the insert plans will change.
>
> There is spare CPU cycles, both raid cards are doing considerably
> less they can do.. so next I am going to try and research what
> parameters I need to bump to increase inserts. Today I increased
> checkpoint_segments from the default to 64. Now looking at
> wall_buffers.
>
> It would be most helpfull to have something on the docs to specify
> what each setting affects most such as reads, writes, updates,
> inserts, etc..
It's an art unfortunately.
>
Dave
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-performance@postgresql.org
Cc: pg@fastcrypt.com, lists@stringsutils.com
Subject: Re: Vacuums on large busy databases
In-Reply-To: <530B4050-130F-441D-9F4A-EA0AF30DADA9@fastcrypt.com>
* 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