Re: slowing down too fast - why ? - Mailing list pgsql-sql

On Sun, Aug 11, 2002 at 02:10:34PM -0400, h012@ied.com wrote:
> 
>  I've even launched the backend with "-F" and removed BEGIN/COMMIT and 
> LOCK TABLE and FOR UPDATE, but I still get slow response.
> 
>  only when count(*) from file is 16000, I get about 2-3 rows / second on 
> average. When count(*) from file was 100, I get about 20-30 rows / second.
> 
>   Help !
> 
>      Thanx,
> 
>          John
> 

some qustions in no particular order

Have you tried 7.2.1?
Have you looked at the disk io performance?
Have you considdered reindexing every night?
How fast is a count on the tables primary key vs the count(*)?
You are using a foreign key in table file that can make 
things slow in 7.1.x, I think it is fixed in 7.2+

Another thing is in table am you are using 1 char fields
to represent boolean values, if they are heavily used you
might want to switch to pg's native boolean type.  It is
probably faster.

And please stop top posting, it makes it harder to
figure out what is going on(read the thread) so it is
less likely that you will get the help you want.  

I looked at your pl script it is not a good test for 
select speed, it does other stuff.

Try something like this:echo 'select now() ; select count(*) from yourtable; select now()'|psql dbname 

to try to localize the problem.

good luck 

marc



> On Sun, 11 Aug 2002 h012@ied.com wrote:
> 
> > 
> >  and I forgot to mention that my stats are available at:
> > http://John.Vicherek.com/slow/times.query.txt
> > 
> >   John
> > 
> > On Sun, 11 Aug 2002 h012@ied.com wrote:
> > 
> > > 
> > >  
> > >  Hi,
> > > 
> > >   I must be doing something silly. I have a 900MHz, 384MB RAM, and
> > > this thing is slow.  (Postgresql-7.1.2).
> > > 
> > >  And growing exponencially slower.
> > > 
> > > SQL: http://John.Vicherek.com/slow/schema.sql (save to /tmp/schema.sql)
> > > am.dat: http://John.Vicherek.com/slow/am.dat   (save to /tmp/am.dat )
> > > perl: http://John.Vicherek.com/slow/rpm2filerian.pl (save to /tmp/rpm2filerian.pl)
> > > 
> > > when I do :
> > > 
> > > createdb filerian
> > > psql -d filerian -f /tmp/schema.sql
> > > echo 'create table times (the_moment datetime, the_number int4);' | psql -d filerian
> > > cd /tmp/mdk/8.2/i586/Mandrake/RPMS # lots of RPMs here
> > > while sleep 10 ; do echo 'insert into times values( now(), count(file.id));' | psql -d filerian ; done 2>&1
>/dev/null &
 
> > > for i in *.rpm ; do echo $i ; perl /tmp/rpm2filerian.pl 0 $i ; done
> > > 
> > > 
> > >  Why are the times so bad ? Why is it slowing so fast ?
> > > 
> > > Am I missing any useful indeces ?
> > > 
> > >  This shows the slowage:
> > > select the_number,min(the_moment) from times group by the_number;
> > > 
> > > PS: if you look in the perl code for "exec", immediatelly above will you
> > > find the query it is doing.
> > > 
> > >    Thanx,
> > > 
> > >       John
> > > 
> > > 
> > > 
> > > 
> > > 
> > 
> > 
> 
> -- 
> -- Gospel of Jesus is the saving power of God for all who believe --
>                ## To some, nothing is impossible. ##
>                      http://Honza.Vicherek.com/
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html