pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql_tmp and postgres settings
4+ messages / 3 participants
[nested] [flat]
* pgsql_tmp and postgres settings
@ 2006-06-09 12:23 Domenico - Sal. F.lli Riva <domenico.mozzanica@fratelliriva.it>
0 siblings, 1 reply; 4+ messages in thread
From: Domenico - Sal. F.lli Riva @ 2006-06-09 12:23 UTC (permalink / raw)
To: pgsql-performance
Hello,
During insert or update, potgresql write in pgsql_tmp directory and so
performance are very poor.
My configuration is:
Work mem 10240
Effective_cache_size 30000
Shared buffers 9000
Max_fsm_pages 35000
Wal Buffers 24
Autovacuum on
Manual vacuum analyze and vacuum full analyze every day
Server:
1 Xeon processor
2500 MB ram
Red Hat Enterprise ES 3
Postgresql (RPM from official website) 8.1.0
Tables are vacuumed frequently and now fsm is very low (only 3000 pages).
Updates and inserts on this database are infrequent, and files to import
aren't so big (7-50 Mb for 2000-20000 record in a txt file).
On this server are installed and active also Apache - Tomcat - Java 1.4.2
which provide data to import.
Tables interested have only max 4 index.
Are parameters adapted?
Thanks
Domenico Mozzanica
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pgsql_tmp and postgres settings
@ 2006-06-09 15:59 Jim C. Nasby <jnasby@pervasive.com>
parent: Domenico - Sal. F.lli Riva <domenico.mozzanica@fratelliriva.it>
0 siblings, 1 reply; 4+ messages in thread
From: Jim C. Nasby @ 2006-06-09 15:59 UTC (permalink / raw)
To: Domenico - Sal. F.lli Riva <domenico.mozzanica@fratelliriva.it>; +Cc: pgsql-performance
On Fri, Jun 09, 2006 at 02:23:04PM +0200, Domenico - Sal. F.lli Riva wrote:
> Hello,
>
> During insert or update, potgresql write in pgsql_tmp directory and so
> performance are very poor.
pgsql_tmp is used if a query runs out of work_mem, so you can try
increasing that.
> My configuration is:
>
> Work mem 10240
>
> Effective_cache_size 30000
You're off by a factor of 10.
> Shared buffers 9000
I'd suggest bumping that up to at least 30000.
> Postgresql (RPM from official website) 8.1.0
You should upgrade to 8.1.4. There's a number of data loss bugs waiting
to bite you.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pgsql_tmp and postgres settings
@ 2006-06-12 05:56 Gourish Singbal <gourish@gmail.com>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 4+ messages in thread
From: Gourish Singbal @ 2006-06-12 05:56 UTC (permalink / raw)
To: pgsql-performance
Where is the pgsql_tmp folder present ?. i am unable to see it in the data
directory of postgresql.
~gourish
On 6/9/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
>
> On Fri, Jun 09, 2006 at 02:23:04PM +0200, Domenico - Sal. F.lli Riva
> wrote:
> > Hello,
> >
> > During insert or update, potgresql write in pgsql_tmp directory and so
> > performance are very poor.
>
> pgsql_tmp is used if a query runs out of work_mem, so you can try
> increasing that.
>
> > My configuration is:
> >
> > Work mem 10240
> >
> > Effective_cache_size 30000
> You're off by a factor of 10.
>
> > Shared buffers 9000
> I'd suggest bumping that up to at least 30000.
>
> > Postgresql (RPM from official website) 8.1.0
>
> You should upgrade to 8.1.4. There's a number of data loss bugs waiting
> to bite you.
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Best,
Gourish Singbal
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pgsql_tmp and postgres settings
@ 2006-06-12 15:15 Jim C. Nasby <jnasby@pervasive.com>
parent: Gourish Singbal <gourish@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jim C. Nasby @ 2006-06-12 15:15 UTC (permalink / raw)
To: Gourish Singbal <gourish@gmail.com>; +Cc: pgsql-performance
On Mon, Jun 12, 2006 at 11:26:23AM +0530, Gourish Singbal wrote:
> Where is the pgsql_tmp folder present ?. i am unable to see it in the data
> directory of postgresql.
It will be under the *database* directory, under $PGDATA/base. SELECT
oid,* FROM pg_database; will tell you what directory to look in for your
database.
> On 6/9/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> >
> >On Fri, Jun 09, 2006 at 02:23:04PM +0200, Domenico - Sal. F.lli Riva
> >wrote:
> >> Hello,
> >>
> >> During insert or update, potgresql write in pgsql_tmp directory and so
> >> performance are very poor.
> >
> >pgsql_tmp is used if a query runs out of work_mem, so you can try
> >increasing that.
> >
> >> My configuration is:
> >>
> >> Work mem 10240
> >>
> >> Effective_cache_size 30000
> >You're off by a factor of 10.
> >
> >> Shared buffers 9000
> >I'd suggest bumping that up to at least 30000.
> >
> >> Postgresql (RPM from official website) 8.1.0
> >
> >You should upgrade to 8.1.4. There's a number of data loss bugs waiting
> >to bite you.
> >--
> >Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
> >Pervasive Software http://pervasive.com work: 512-231-6117
> >vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
>
> --
> Best,
> Gourish Singbal
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-06-12 15:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-09 12:23 pgsql_tmp and postgres settings Domenico - Sal. F.lli Riva <domenico.mozzanica@fratelliriva.it>
2006-06-09 15:59 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-12 05:56 ` Gourish Singbal <gourish@gmail.com>
2006-06-12 15:15 ` Jim C. Nasby <jnasby@pervasive.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