Thread: slowing down too fast - why ?
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/
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/
h012@ied.com writes:
> while sleep 10 ; do echo 'insert into times values( now(), count(file.id));' | psql -d filerian ; done 2>&1
>/dev/null &
Apparently the file table is getting larger. That means the count()
aggregate will take more time to run.
regards, tom lane
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
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/
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