Message-ID: <4D8B5FB9.7030704@peak6.com>
Date: Thu, 24 Mar 2011 10:14:01 -0500
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: sthomas@peak6.com
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.7) Gecko/20100720 Lightning/1.0b2 Thunderbird/3.1.1
MIME-Version: 1.0
To: Uwe Bartels <uwe.bartels@gmail.com>
CC: Stephen Frost <sfrost@snowman.net>, "pgsql-performance@postgresql.org"
	<pgsql-performance@postgresql.org>
Subject: Re: maintenance_work_mem + create index
References: <AANLkTik1GzFrcAaYNPDxLgc7LFcfPK6wx0rEmtKgp2OO@mail.gmail.com>
	<20110324141303.GE4116@tamriel.snowman.net>
	<AANLkTikQO+y2nWz4SjjUMKkGDCVFFL0Z_8bu9poUyk=9@mail.gmail.com>
In-Reply-To: <AANLkTikQO+y2nWz4SjjUMKkGDCVFFL0Z_8bu9poUyk=9@mail.gmail.com>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

On 03/24/2011 09:40 AM, Uwe Bartels wrote:

> Does anybody know of a solution out of that on Linux?
> Or is there a dynamic way to put $PGDATA/base/pgsql_tmp into RAM without
> blocking it completely like a ram disk?

We put this in our startup script just before starting the actual database:

for x in $(find ${PGDATA}/base -mindepth 1 -maxdepth 1 -type d); do
   nDBNum=${x##*/}
   sDir=${DBSHM}/${nDBNum}

   if [ ! -d "$sDir" ]; then
     su -c "mkdir $sDir" - $PGUSER
   fi
done

Where PGDATA, DBSHM, and PGUSER are all set in 
/etc/sysconfig/postgresql. But DBSHM defaults to /dev/shm/pgsql_tmp on 
our Linux box.

Basically what this does is ensures a directory exists for each of your 
databases in shared memory. Then all we did was symlink the pgsql_tmp 
folder to point to those shared-memory directories. Many systems default 
so that up to half of total RAM can be used this way, so we're not at 
any risk with 64GB on our main nodes.

We already run a custom init.d script anyway because we needed something 
LSB compatible for Pacemaker. I highly recommend it. :)

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email