Обсуждение: 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 --------------------------------
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) >
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
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 > --------------------------------
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
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
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 > --------------------------------