Thread: maintenance_work_mem + create index

Hi,

I see my application creating temporary files while creating an index.
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp7076.0", size 779853824
STATEMENT:  CREATE INDEX IDX_LPA_LINKID ON NNDB.LPA (LINK_ID);

So I checked this again and raised afterwards maintenance_work_mem step by step up 64GB.
I logged in via psql, run the following statements
set maintenance_work_mem = '64GB';
CREATE INDEX IDX_LPA_LINKID ON NNDB.LPA (LINK_ID);

But still I get that evil message in the log file about creating a temporary file.
I also raised work_mem in my session up to 32GB - again without changing the behavior.

According to the postgres docs http://www.postgresql.org/docs/8.4/static/populate.html#POPULATE-WORK-MEM this is supposed to help.
Any ideas?

I'm running postgres 8.4 64 bit on Linux from an enterprisedb package.
# file /var/lib/pgsql/bin/postgres
/var/lib/pgsql/bin/postgres: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), for GNU/Linux 2.4.0, dynamically linked (uses shared libs), not stripped


Best Regards,
Uwe

Uwe,

* Uwe Bartels (uwe.bartels@gmail.com) wrote:
> So I checked this again and raised afterwards maintenance_work_mem step by
> step up 64GB.
> I logged in via psql, run the following statements
> set maintenance_work_mem = '64GB';

I believe maintenance_work_mem suffers from the same problem that
work_mem has, specifically that PG still won't allocate more than
1GB of memory for any single operation.

    Thanks,

        Stephen

OK. I didn't now that. Thanks for sharing that information.
Can anybody tell if we have this limitation on maintenance_work_mem as well?

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?

Best Regards,
Uwe

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

OK. sounds promising. On my machine this looks similar.
I'll try this.

Thanks,
Uwe

On 03/24/2011 10:28 AM, Uwe Bartels wrote:

> OK. sounds promising. On my machine this looks similar.
> I'll try this.

I just realized I may have implied that DBSHM automatically defaults to
/db/shm/pgsql_tmp. It dosen't. I also have this at the very top of our
/etc/init.d/postgresql script:

if [ -f /etc/sysconfig/postgresql ]; then
   source /etc/sysconfig/postgresql
fi

DBSHM=${DBSHM:-/dev/shm/pgsql_tmp}
PGDATA=${PGDATA:-"/db/data/pgdata"}
PGUSER=${PGUSER:-postgres}

DBSHM doesn't exist, and the other vars will probably be empty unless
you set them in the sysconfig file. What I meant was that /dev/shm
automatically exists on our Linux box and we make use of 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

Em 24-03-2011 11:40, Uwe Bartels escreveu:
> Or is there a dynamic way to put $PGDATA/base/pgsql_tmp into RAM without
> blocking it completely like a ram disk?
>
Create a tablespace in a ram disk and set temp_tablespaces.


--
   Euler Taveira de Oliveira
   http://www.timbira.com/