pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedmaintenance_work_mem + create index
7+ messages / 4 participants
[nested] [flat]
* maintenance_work_mem + create index
@ 2011-03-24 13:56 Uwe Bartels <uwe.bartels@gmail.com>
2011-03-24 14:13 ` Re: maintenance_work_mem + create index Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 7+ messages in thread
From: Uwe Bartels @ 2011-03-24 13:56 UTC (permalink / raw)
To: pgsql-performance
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-MEMthis
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: maintenance_work_mem + create index
2011-03-24 13:56 maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
@ 2011-03-24 14:13 ` Stephen Frost <sfrost@snowman.net>
2011-03-24 14:40 ` Re: maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Stephen Frost @ 2011-03-24 14:13 UTC (permalink / raw)
To: Uwe Bartels <uwe.bartels@gmail.com>; +Cc: pgsql-performance
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
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: maintenance_work_mem + create index
2011-03-24 13:56 maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
2011-03-24 14:13 ` Re: maintenance_work_mem + create index Stephen Frost <sfrost@snowman.net>
@ 2011-03-24 14:40 ` Uwe Bartels <uwe.bartels@gmail.com>
2011-03-24 15:14 ` Re: maintenance_work_mem + create index Shaun Thomas <sthomas@peak6.com>
2011-03-24 17:35 ` Re: maintenance_work_mem + create index Euler Taveira de Oliveira <euler@timbira.com>
0 siblings, 2 replies; 7+ messages in thread
From: Uwe Bartels @ 2011-03-24 14:40 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-performance
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 24 March 2011 15:13, Stephen Frost <sfrost@snowman.net> wrote:
> 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
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEARECAAYFAk2LUW8ACgkQrzgMPqB3kigZMwCfUVL/5nSdK5xiV+/SjWB6BG9B
> Fm0An2V5Tald8PUYXc5VIuKL/C1WNYTp
> =MSxh
> -----END PGP SIGNATURE-----
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: maintenance_work_mem + create index
2011-03-24 13:56 maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
2011-03-24 14:13 ` Re: maintenance_work_mem + create index Stephen Frost <sfrost@snowman.net>
2011-03-24 14:40 ` Re: maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
@ 2011-03-24 15:14 ` Shaun Thomas <sthomas@peak6.com>
2011-03-24 15:28 ` Re: maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Shaun Thomas @ 2011-03-24 15:14 UTC (permalink / raw)
To: Uwe Bartels <uwe.bartels@gmail.com>; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: maintenance_work_mem + create index
2011-03-24 13:56 maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
2011-03-24 14:13 ` Re: maintenance_work_mem + create index Stephen Frost <sfrost@snowman.net>
2011-03-24 14:40 ` Re: maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
2011-03-24 15:14 ` Re: maintenance_work_mem + create index Shaun Thomas <sthomas@peak6.com>
@ 2011-03-24 15:28 ` Uwe Bartels <uwe.bartels@gmail.com>
2011-03-24 15:38 ` Re: maintenance_work_mem + create index Shaun Thomas <sthomas@peak6.com>
0 siblings, 1 reply; 7+ messages in thread
From: Uwe Bartels @ 2011-03-24 15:28 UTC (permalink / raw)
To: sthomas@peak6.com; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-performance
OK. sounds promising. On my machine this looks similar.
I'll try this.
Thanks,
Uwe
On 24 March 2011 16:14, Shaun Thomas <sthomas@peak6.com> wrote:
> 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: maintenance_work_mem + create index
2011-03-24 13:56 maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
2011-03-24 14:13 ` Re: maintenance_work_mem + create index Stephen Frost <sfrost@snowman.net>
2011-03-24 14:40 ` Re: maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
2011-03-24 15:14 ` Re: maintenance_work_mem + create index Shaun Thomas <sthomas@peak6.com>
2011-03-24 15:28 ` Re: maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
@ 2011-03-24 15:38 ` Shaun Thomas <sthomas@peak6.com>
0 siblings, 0 replies; 7+ messages in thread
From: Shaun Thomas @ 2011-03-24 15:38 UTC (permalink / raw)
To: Uwe Bartels <uwe.bartels@gmail.com>; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: maintenance_work_mem + create index
2011-03-24 13:56 maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
2011-03-24 14:13 ` Re: maintenance_work_mem + create index Stephen Frost <sfrost@snowman.net>
2011-03-24 14:40 ` Re: maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
@ 2011-03-24 17:35 ` Euler Taveira de Oliveira <euler@timbira.com>
1 sibling, 0 replies; 7+ messages in thread
From: Euler Taveira de Oliveira @ 2011-03-24 17:35 UTC (permalink / raw)
To: Uwe Bartels <uwe.bartels@gmail.com>; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-performance
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-03-24 17:35 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-24 13:56 maintenance_work_mem + create index Uwe Bartels <uwe.bartels@gmail.com>
2011-03-24 14:13 ` Stephen Frost <sfrost@snowman.net>
2011-03-24 14:40 ` Uwe Bartels <uwe.bartels@gmail.com>
2011-03-24 15:14 ` Shaun Thomas <sthomas@peak6.com>
2011-03-24 15:28 ` Uwe Bartels <uwe.bartels@gmail.com>
2011-03-24 15:38 ` Shaun Thomas <sthomas@peak6.com>
2011-03-24 17:35 ` Euler Taveira de Oliveira <euler@timbira.com>
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