From: Dave Cramer <pg@fastcrypt.com>
To: Vivek Khera <vivek@khera.org>
Cc: Pgsql-Performance ((((E-mail)))) <pgsql-performance@postgresql.org>
Subject: Re: performance problems.
Date: Thu, 31 Aug 2006 14:28:31 -0400
Message-ID: <43D1B021-7BDB-4CC4-BB7A-A6DFDAEF1537@fastcrypt.com> (raw)
In-Reply-To: <A313FD0D-0D27-4022-9C8F-6A0EE7C3E7AC@khera.org>
References: <44F55A7F.40305@sorbs.net>
<BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
<BC2892F0-2DF0-4DB4-85A7-C80832181FBD@fastcrypt.com>
<A313FD0D-0D27-4022-9C8F-6A0EE7C3E7AC@khera.org>
On 31-Aug-06, at 2:15 PM, Vivek Khera wrote:
>
> On Aug 30, 2006, at 7:48 PM, Dave Cramer wrote:
>
>> Actually unless you have a ram disk you should probably leave
>> random_page_cost at 4, shared buffers should be 2x what you have
>> here, maintenance work mem is pretty high
>> effective cache should be much larger 3/4 of 4G or about 360000
>>
>
> I've been pondering bumping up SHM settings more, but it is a very
> big imposition to have to restart the production server to do so.
> This weekend being a long weekend might be a good opportunity to
> try it, though...
>
> As for maintenence mem, when you have HUGE tables, you want to give
> a lot of memory to vacuum. With 4GB of RAM giving it 512MB is not
> an issue.
>
> The effective cache size is the big issue with FreeBSD. There are
> opposing claims of how much memory it will use for cache, and throw
> in the kern.ipc.shm_use_phys sysctl which causes SHM to bypass the
> VM system entirely, and who knows what's going on.
Yes, I have to admit, the setting I proposed works well for linux,
but may not for bsd.
>
>> Setting work _mem this high should be done with caution. From the
>> manual "Note that for a complex query, several sort or hash
>> operations might be running in parallel; each one will be allowed
>> to use as much memory as this value specifies before it starts to
>> put data into temporary files. Also, several running sessions
>> could be doing such operations concurrently. So the total memory
>> used could be many times the value of work_mem"
>
> Again, with boat-loads of RAM why not let the queries use it? We
> only have a handful of connections at a time so that's not eating
> up much memory...
>
As long as you are aware of the ramifications....
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, vivek@khera.org
Subject: Re: performance problems.
In-Reply-To: <43D1B021-7BDB-4CC4-BB7A-A6DFDAEF1537@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