From: Mark Kirkwood <markir@paradise.net.nz>
To: andy rost <andy.rost@noaa.gov>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: pgsql-performance@postgresql.org
Cc: Bill.Sites@noaa.gov
Subject: Re: Opteron/FreeBSD/PostgreSQL performance poor
Date: Thu, 06 Jul 2006 11:49:40 +1200
Message-ID: <44AC5014.1070605@paradise.net.nz> (raw)
In-Reply-To: <44AC00B5.6030207@noaa.gov>
References: <44ABCFF4.3010007@noaa.gov>
<20060705154656.GS22057@kenobi.snowman.net>
<44AC00B5.6030207@noaa.gov>
andy rost wrote:
>
>>> effective_cache_size = 27462 # typically 8KB each
>>
>>
>> This seems like it might be a little low... How much memory do you have
>> in the system? Then again, with your shared_mem set so high, perhaps
>> it's not that bad, but it might make sense to swap those two settings,
>> or at least that'd be a more common PG setup.
>
> Oops, forgot to mention that we have 6 Gigs of memory. This value was
> set based on sysctl -n vfs.hibufspace / 8192
>
That vfs.hibufspace sysctl is a little deceptive IMHO - e.g on my
FreeBSD 6.1 system with 2G of ram it says 117276672 (i.e. about 112M),
but I have a 1G file cached entirely in ram at the moment... In FreeBSD
file pages are actually kept in the 'Inactive' section of memory, the
'Buffer' section is used as a 'window' to read 'em. For instance on my
system I see:
Mem: 4192K Active, 1303M Inact, 205M Wired, 12K Cache, 112M Buf, 491M Free
So my 1G file is cached in the 1303M of 'Inactive', but I have 112M of
buffer window for accessing this (and other) cached files. Now, I may
not have explained this that well, and it is quite confusing... but
hopefully you get the idea!
Now on the basis of the figures provided:
- max_connections=102 , each with work_mem=10000 (approx 1G in total)
- shared buffers=125000 (1G total)
it looks like you are only using about 2G of your 6G, so there is a lot
left for caching file pages (lets say 3-4G or so).
I would think you can happily set effective_cache_size=393216 (i.e.
3G/8192). This will have the side effect of encouraging more index scans
(probably what you want I think).
Best wishes
Mark
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: markir@paradise.net.nz, andy.rost@noaa.gov, sfrost@snowman.net, Bill.Sites@noaa.gov
Subject: Re: Opteron/FreeBSD/PostgreSQL performance poor
In-Reply-To: <44AC5014.1070605@paradise.net.nz>
* 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