Postgres Pro
Downloads
Home   >   mailing lists

Thread: Slow performance...

Slow performance...

From
wim
Date:
Hey all,

I encounter a major problem: I've running Postgres 7.1 on a Sun 220R
(with only one processor). Postgres has a database with a table with
more than 300000 rows. The lay-out of my table is:

timestamp | interface | datain | dataout

I created indexes on the first two fields (timestamp and interface).


I've written a perl script that fetches data from the DB.
I do it the following way:

SELECT SUM(datain), SUM(dataout) FROM traffic_counters WHERE timestamp
LIKE '$yr-$mon-$day $hour:$minute%' AND interface LIKE '%$billingtable%'";

Now the problem is that it takes about ten seconds to get only a single
result... There are 24 hours in one day, so you can see that my script
runs for quite a time...

Is there a way to speed up thing (a lot)?

Thanx guys!


--
Kind regards,

Wim De Hul
Belgacom Belbone
--------------------------------
  Mail   : wdh@belbone.be
  Mobile : +32 479 952004
  Ripe   : WDH25-RIPE
  Registered Linux User: #260015
--------------------------------

Re: Slow performance...

From
"PGMailList"
Date:
1)
Replace
WHERE timestamp
> LIKE '$yr-$mon-$day $hour:$minute%'

with
$minute2 =  $minute +1;
timestamp > '$yr-$mon-$day $hour:$minute'  and
timestamp <'$yr-$mon-$day $hour:$minute2'

AND
2)

Do You Realy need the % infront of %$billingtable%'
if not it will inprove your speed drastically.

3)
DO a explain verbose select .... to make postgres sure its using your index.


http://www.pgexplorer.com
GUI Tool for PostgreSQL

----- Original Message -----
From: "wim" <wdh@belbone.be>
To: <pgsql-novice@postgresql.org>
Sent: Monday, March 11, 2002 1:12 PM
Subject: [NOVICE] Slow performance...


> Hey all,
>
> I encounter a major problem: I've running Postgres 7.1 on a Sun 220R
> (with only one processor). Postgres has a database with a table with
> more than 300000 rows. The lay-out of my table is:
>
> timestamp | interface | datain | dataout
>
> I created indexes on the first two fields (timestamp and interface).
>
>
> I've written a perl script that fetches data from the DB.
> I do it the following way:
>
> SELECT SUM(datain), SUM(dataout) FROM traffic_counters WHERE timestamp
> LIKE '$yr-$mon-$day $hour:$minute%' AND interface LIKE '%$billingtable%'";
>
> Now the problem is that it takes about ten seconds to get only a single
> result... There are 24 hours in one day, so you can see that my script
> runs for quite a time...
>
> Is there a way to speed up thing (a lot)?
>
> Thanx guys!
>
>
> --
> Kind regards,
>
> Wim De Hul
> Belgacom Belbone
> --------------------------------
>   Mail   : wdh@belbone.be
>   Mobile : +32 479 952004
>   Ripe   : WDH25-RIPE
>   Registered Linux User: #260015
> --------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: Slow performance...

From
"Josh Berkus"
Date:
Wim,

> I encounter a major problem: I've running Postgres 7.1 on a Sun 220R
>  (with only one processor). Postgres has a database with a table with
>  more than 300000 rows. The lay-out of my table is:

In addition to the suggestions from PGMail, you may want to look at
 your hardware setup and PostgreSQL runtime parameters if you're
 looking for speed.  Check out this article written by Bruce Momjian:
http://www.ca.postgresql.org/docs/momjian/hw_performance/
And this one by Jean-Paul Argudo:
http://techdocs.postgresql.org/techdocs/pgsqldbtuning.php

-Josh Berkus

Re: Slow performance...

From
"PG Explorer"
Date:
Its in the way postgres utilize the index.
Try Explain Verbose Select and you will see why.

http://www.pgexplorer.com
PostgreSQL GUI




----- Original Message -----
From: "wim" <wdh@belbone.be>
To: "PGMailList" <pgmail@pgexplorer.com>
Sent: Monday, March 11, 2002 2:55 PM
Subject: Re: [NOVICE] Slow performance...


> Hey...
>
> The first topic helped me a lot, but can explain someone why?
>
>
> Thanx!
>
>
> PGMailList wrote:
>
> > 1)
> > Replace
> > WHERE timestamp
> >
> >>LIKE '$yr-$mon-$day $hour:$minute%'
> >>
> >
> > with
> > $minute2 =  $minute +1;
> > timestamp > '$yr-$mon-$day $hour:$minute'  and
> > timestamp <'$yr-$mon-$day $hour:$minute2'
> >
> > AND
> > 2)
> >
> > Do You Realy need the % infront of %$billingtable%'
> > if not it will inprove your speed drastically.
> >
> > 3)
> > DO a explain verbose select .... to make postgres sure its using your
index.
> >
> >
> > http://www.pgexplorer.com
> > GUI Tool for PostgreSQL
> >
> > ----- Original Message -----
> > From: "wim" <wdh@belbone.be>
> > To: <pgsql-novice@postgresql.org>
> > Sent: Monday, March 11, 2002 1:12 PM
> > Subject: [NOVICE] Slow performance...
> >
> >
> >
> >>Hey all,
> >>
> >>I encounter a major problem: I've running Postgres 7.1 on a Sun 220R
> >>(with only one processor). Postgres has a database with a table with
> >>more than 300000 rows. The lay-out of my table is:
> >>
> >>timestamp | interface | datain | dataout
> >>
> >>I created indexes on the first two fields (timestamp and interface).
> >>
> >>
> >>I've written a perl script that fetches data from the DB.
> >>I do it the following way:
> >>
> >>SELECT SUM(datain), SUM(dataout) FROM traffic_counters WHERE timestamp
> >>LIKE '$yr-$mon-$day $hour:$minute%' AND interface LIKE
'%$billingtable%'";
> >>
> >>Now the problem is that it takes about ten seconds to get only a single
> >>result... There are 24 hours in one day, so you can see that my script
> >>runs for quite a time...
> >>
> >>Is there a way to speed up thing (a lot)?
> >>
> >>Thanx guys!
> >>
> >>
> >>--
> >>Kind regards,
> >>
> >>Wim De Hul
> >>Belgacom Belbone
> >>--------------------------------
> >>  Mail   : wdh@belbone.be
> >>  Mobile : +32 479 952004
> >>  Ripe   : WDH25-RIPE
> >>  Registered Linux User: #260015
> >>--------------------------------
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 2: you can get off all lists at once with the unregister command
> >>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >>
> >>
> >
> >
>
>
> --
> Kind regards,
>
> Wim De Hul
> Belgacom Belbone
> --------------------------------
>   Mail   : wdh@belbone.be
>   Mobile : +32 479 952004
>   Ripe   : WDH25-RIPE
>   Registered Linux User: #260015
> --------------------------------

export everything to text file

From
Jim Lanford
Date:
Hi,
   Newbie question here:

   I had an sys admin recommend that I export my critical DB's to
a text file before the tape does it's auto backup (dump)
That way I don't have to stop the PostgreSQL each night for 30 mins.

What is this called.  What might it be called in the index of a book like:
PostgreSQL Essential Reference
or
Beginning DB with PostgreSQL

Or how about an URL for a HowTo..

TIA

- Jim

Re: export everything to text file

From
"Josh Berkus"
Date:
Jim,

>   I had an sys admin recommend that I export my critical DB's to
> atext file before the tape does it's auto backup (dump)
> That way I don't have to stop the PostgreSQL each night for 30 mins.
>
> What is this called.  What might it be called in the index of a book
>  like:
> PostgreSQL Essential Reference
> or
> Beginning DB with PostgreSQL

Look up pg_dump and pg_dumpall in either book, under "command line
 utilities."

-Josh

Re: Slow performance...

From
"Hano de la Rouviere"
Date:
Its in the way postgres utilize the index.
Try Explain Verbose Select and you will see why.
http://www.pgexplorer.com
PostgreSQL GUI




----- Original Message -----
From: "wim" <wdh@belbone.be>
To: "PGMailList" <pgmail@pgexplorer.com>
Sent: Monday, March 11, 2002 2:55 PM
Subject: Re: [NOVICE] Slow performance...


> Hey...
>
> The first topic helped me a lot, but can explain someone why?
>
>
> Thanx!
>
>
> PGMailList wrote:
>
> > 1)
> > Replace
> > WHERE timestamp
> >
> >>LIKE '$yr-$mon-$day $hour:$minute%'
> >>
> >
> > with
> > $minute2 =  $minute +1;
> > timestamp > '$yr-$mon-$day $hour:$minute'  and
> > timestamp <'$yr-$mon-$day $hour:$minute2'
> >
> > AND
> > 2)
> >
> > Do You Realy need the % infront of %$billingtable%'
> > if not it will inprove your speed drastically.
> >
> > 3)
> > DO a explain verbose select .... to make postgres sure its using your
index.
> >
> >
> > http://www.pgexplorer.com
> > GUI Tool for PostgreSQL
> >
> > ----- Original Message -----
> > From: "wim" <wdh@belbone.be>
> > To: <pgsql-novice@postgresql.org>
> > Sent: Monday, March 11, 2002 1:12 PM
> > Subject: [NOVICE] Slow performance...
> >
> >
> >
> >>Hey all,
> >>
> >>I encounter a major problem: I've running Postgres 7.1 on a Sun 220R
> >>(with only one processor). Postgres has a database with a table with
> >>more than 300000 rows. The lay-out of my table is:
> >>
> >>timestamp | interface | datain | dataout
> >>
> >>I created indexes on the first two fields (timestamp and interface).
> >>
> >>
> >>I've written a perl script that fetches data from the DB.
> >>I do it the following way:
> >>
> >>SELECT SUM(datain), SUM(dataout) FROM traffic_counters WHERE timestamp
> >>LIKE '$yr-$mon-$day $hour:$minute%' AND interface LIKE
'%$billingtable%'";
> >>
> >>Now the problem is that it takes about ten seconds to get only a single
> >>result... There are 24 hours in one day, so you can see that my script
> >>runs for quite a time...
> >>
> >>Is there a way to speed up thing (a lot)?
> >>
> >>Thanx guys!
> >>
> >>
> >>--
> >>Kind regards,
> >>
> >>Wim De Hul
> >>Belgacom Belbone
> >>--------------------------------
> >>  Mail   : wdh@belbone.be
> >>  Mobile : +32 479 952004
> >>  Ripe   : WDH25-RIPE
> >>  Registered Linux User: #260015
> >>--------------------------------
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 2: you can get off all lists at once with the unregister command
> >>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >>
> >>
> >
> >
>
>
> --
> Kind regards,
>
> Wim De Hul
> Belgacom Belbone
> --------------------------------
>   Mail   : wdh@belbone.be
>   Mobile : +32 479 952004
>   Ripe   : WDH25-RIPE
>   Registered Linux User: #260015
> --------------------------------