Обсуждение: Slow access with PG 7.0
Hello, I have a problem : it seems that when a script access to a db for a SELECT (or table i don't really know), postgres locks the db or table and other scripts cannot access to this DB concurrently. So it's very very slow when this script (a PHP4 script) is called by several users... it's so slow that even keyboard is slow ! My tables are not so large (about 500 records) I don't use "Begin" and "Commit" PG Version is 7.0, with SCSI HD with Adaptec 2940U2W card , on PIII 500 . I used to utilize these same scripts on another server with postgres 6.4 (on Celeron 300), and there was no such problem, it was really faster !! ---------------------------------------------------------------- Le simple fait de passer par la fenetre ne suffit pas a la transformer en porte.
* Jean-Arthur Silve <jeanarthur@eurovox.fr> [001206 01:10] wrote: > Hello, > > I have a problem : > it seems that when a script access to a db for a SELECT (or table i don't > really know), postgres locks the db or table and other scripts cannot > access to this DB concurrently. > So it's very very slow when this script (a PHP4 script) is called by > several users... > it's so slow that even keyboard is slow ! > > My tables are not so large (about 500 records) > > I don't use "Begin" and "Commit" > > PG Version is 7.0, with SCSI HD with Adaptec 2940U2W card , on PIII 500 . > > I used to utilize these same scripts on another server with postgres 6.4 > (on Celeron 300), and there was no such problem, it was really faster !! 1) when was the last time you ran "vacuum verbose analyze <tablename>" ? 2) do you have indecies on this table? 3) have you tried this: pg_exec($dbh, "set enable seqscan off;"); (*) 4) how can you expect help without going into a bit more detail about how your table is set up and what the actuall query is? (*) not sure on the syntax for that one, check the manual. -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
Thanks for your help pg_exec($dbh, "set enable_seqscan off;"); seems resolve my problem... I look at the docs, but I didn't understand very well what is the use of this variable ? thank you ! jean-arthur At 01:25 06/12/00 -0800, Alfred Perlstein wrote: >* Jean-Arthur Silve <jeanarthur@eurovox.fr> [001206 01:10] wrote: > > Hello, > > > > I have a problem : > > it seems that when a script access to a db for a SELECT (or table i don't > > really know), postgres locks the db or table and other scripts cannot > > access to this DB concurrently. > > So it's very very slow when this script (a PHP4 script) is called by > > several users... > > it's so slow that even keyboard is slow ! > > > > My tables are not so large (about 500 records) > > > > I don't use "Begin" and "Commit" > > > > PG Version is 7.0, with SCSI HD with Adaptec 2940U2W card , on PIII 500 . > > > > I used to utilize these same scripts on another server with postgres 6.4 > > (on Celeron 300), and there was no such problem, it was really faster !! > >1) when was the last time you ran "vacuum verbose analyze <tablename>" ? >2) do you have indecies on this table? >3) have you tried this: pg_exec($dbh, "set enable seqscan off;"); (*) >4) how can you expect help without going into a bit more detail about > how your table is set up and what the actuall query is? > >(*) not sure on the syntax for that one, check the manual. > >-- >-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] >"I have the heart of a child; I keep it in a jar on my desk." ---------------------------------------------------------------- Le simple fait de passer par la fenetre ne suffit pas a la transformer en porte.
* Jean-Arthur Silve <jeanarthur@eurovox.fr> [001206 04:55] wrote: > Thanks for your help > pg_exec($dbh, "set enable_seqscan off;"); > seems resolve my problem... > > I look at the docs, but I didn't understand very well what is the use of > this variable ? run: explain <your query> with and without the option, you'll see.
Can someone elaborate on this more? Is this only PHP4 related? If this improves performance why not always have it on? What are the downsides? Thanks, --Rainer -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Alfred Perlstein Sent: Thursday, December 07, 2000 2:25 AM To: Jean-Arthur Silve Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Slow access with PG 7.0 * Jean-Arthur Silve <jeanarthur@eurovox.fr> [001206 04:55] wrote: > Thanks for your help > pg_exec($dbh, "set enable_seqscan off;"); > seems resolve my problem... > > I look at the docs, but I didn't understand very well what is the use of > this variable ? run: explain <your query> with and without the option, you'll see.