pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedslowing down too fast - why ?
5+ messages / 3 participants
[nested] [flat]
* slowing down too fast - why ?
@ 2002-08-11 04:22 h012@ied.com
2002-08-11 04:36 ` Re: slowing down too fast - why ? h012@ied.com
2002-08-11 14:43 ` Re: slowing down too fast - why ? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: h012@ied.com @ 2002-08-11 04:22 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: slowing down too fast - why ?
2002-08-11 04:22 slowing down too fast - why ? h012@ied.com
@ 2002-08-11 04:36 ` h012@ied.com
1 sibling, 0 replies; 5+ messages in thread
From: h012@ied.com @ 2002-08-11 04:36 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: slowing down too fast - why ?
2002-08-11 04:22 slowing down too fast - why ? h012@ied.com
@ 2002-08-11 14:43 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2002-08-11 14:43 UTC (permalink / raw)
To: h012@ied.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: slowing down too fast - why ?
@ 2002-08-11 18:10 h012@ied.com
2002-08-11 19:30 ` Re: slowing down too fast - why ? Marc Spitzer <marc@oscar.eng.cv.net>
0 siblings, 1 reply; 5+ messages in thread
From: h012@ied.com @ 2002-08-11 18:10 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: slowing down too fast - why ?
2002-08-11 18:10 Re: slowing down too fast - why ? h012@ied.com
@ 2002-08-11 19:30 ` Marc Spitzer <marc@oscar.eng.cv.net>
0 siblings, 0 replies; 5+ messages in thread
From: Marc Spitzer @ 2002-08-11 19:30 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-08-11 19:30 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-11 04:22 slowing down too fast - why ? h012@ied.com
2002-08-11 04:36 ` h012@ied.com
2002-08-11 14:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-11 18:10 Re: slowing down too fast - why ? h012@ied.com
2002-08-11 19:30 ` Marc Spitzer <marc@oscar.eng.cv.net>
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