Thread: pgsql_tmp and postgres settings
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
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
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
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