Обсуждение: Query Speed!!!
hi, I am using Postgresql-7.1.3 with RedHat Linux-7.1 and JDBC. I am having an indexed table with nearly 3,000 records for a month. My machine is with 256 MB RAM, 600 MHz. I am having a report from the abovesaid table, which displays 1 month's records at a stretch using Swing components. If I run the query from the hard-disk it takes about 5 seconds to display all the 3,000 records. If I connect 3 nodes to it and run the query from a node, it takes about 1.5 minutes. I don't know where I am wrong. Please do reply. thanks. bhuvana. __________________________________________________ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com
> -----Original Message----- > From: Thirumoorthy Bhuvneswari [mailto:tbhuvneswari@yahoo.com] > Sent: 08 July 2002 13:24 > To: pgsql-general@postgresql.org > Subject: [GENERAL] Query Speed!!! > > > hi, > I am using Postgresql-7.1.3 with RedHat Linux-7.1 and > JDBC. I am having an indexed table with nearly 3,000 > records for a month. My machine is with 256 MB RAM, > 600 MHz. I am having a report from the abovesaid > table, which displays 1 month's records at a stretch > using Swing components. If I run the query from the > hard-disk > it takes about 5 seconds to display all the 3,000 > records. If I connect 3 nodes to it and run the query > from a node, it takes about 1.5 minutes. I don't know > where I am wrong. Please do reply. thanks. You might want to get a shell on the box and run: iostat 1 or something and see how much disk i/o you are getting - a lot I'm guessing. What you have is disk-head contention, each "node" wants to be reading a different part of the disk and so most of the time is spent with the disk moving the head from one part of the disk to the next. You may also be swapping out if you don't have enough RAM, and this would make it worse, esp. if the swap device is the same disk as the DB. Two solutions I know of: 1) Get LOADS of ram so most of the interesting parts of the disk are bufferred in RAM - less disk contention We have 8GB RAM for such purposes 2) Use raid. I'm no great expert on this but if you mirror then you have two copies and can read from two parts of the disk at the same time, or in otherwords run 2 nodes without disc contention. We do this too. Perhaps for you the real solution is to "explain analyse <your query>" and look at what it is doing; if you have much seq_scan which could be using an index then there's something you may want to change; but if you query is selecting pretty much everything instead of certain records then this won't help. Sam
On Mon, Jul 08, 2002 at 05:24:28AM -0700, Thirumoorthy Bhuvneswari wrote: > I am using Postgresql-7.1.3 with RedHat Linux-7.1 and > JDBC. I am having an indexed table with nearly 3,000 > records for a month. How many months are you storing? i.e. how large is the entire table? > If I run the query from the hard-disk it takes about > 5 seconds to display all the 3,000 records. If I > connect 3 nodes to it and run the query from a node, > it takes about 1.5 minutes. Not sure what you mean by "running it from the hard disk", versus "connecting nodes". If you mean that the query takes longer when executed by clients on a LAN, sounds like a network problem. If not, then you need to provide a LOT more information. As is, we can't help you. Have you VACUUM ANALYZE'd the tables in question? How much data (# of rows) is involved? What queries are being executed? What does EXPLAIN show for these queries? What is the definition of the tables involved, as well as any applicable indexes? Under what exact circumstances does the performance problem occur? Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Thirumoorthy Bhuvneswari wrote:
> hi,
> I am using Postgresql-7.1.3 with RedHat Linux-7.1 and
> JDBC. I am having an indexed table with nearly 3,000
> records for a month. My machine is with 256 MB RAM,
> 600 MHz. I am having a report from the abovesaid
> table, which displays 1 month's records at a stretch
> using Swing components. If I run the query from the
> hard-disk
> it takes about 5 seconds to display all the 3,000
> records. If I connect 3 nodes to it and run the query
> from a node, it takes about 1.5 minutes. I don't know
> where I am wrong. Please do reply. thanks.
>
> bhuvana.
>
Did you define indexes? did you vacuum the database?
if that doesn't work try to give the optimizer some hints (see manual).
HAns
--- Hans-Juergen Schoenig <hs@cybertec.at> wrote: > Thirumoorthy Bhuvneswari wrote: > > hi, > > I am using Postgresql-7.1.3 with RedHat Linux-7.1 and > > JDBC. I am having an indexed table with nearly 3,000 > > records for a month. My machine is with 256 MB RAM, > > 600 MHz. I am having a report from the abovesaid > > table, which displays 1 month's records at a stretch > > using Swing components. If I run the query from the > > hard-disk > > it takes about 5 seconds to display all the 3,000 > > records. If I connect 3 nodes to it and run the query > > from a node, it takes about 1.5 minutes. I don't know > > where I am wrong. Please do reply. thanks. Please tell us two more things: 1) If you enter the query by hand at the psql prompt how long does the query take? 2) What is the SQL query? Show us the SQL you entered. I would expect much beter 10x to 100x speeds for most "normal" queries. But do the above _after_ a "vacuum full analyze" ===== Chris Albertson Home: 310-376-1029 chrisalbertson90278@yahoo.com Cell: 310-990-7550 Office: 310-336-5189 Christopher.J.Albertson@aero.org __________________________________________________ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com
hi, my query looks like this: select * from loom_details where date>='2002-06-01' and date<='2002-06-30'. * I do give 'select *' since all the columns are required for the report. * When I give the above query in the 'psql' prompt it waits for 1 second to fetch the records and displays the entire rows(about 3000). * There are about 10,000 records total in the table. * When I run the report, it actually does not increase the RAM usage but takes about 90% of the CPU time leaving all the other queries behind it. Please tell me how can I increase the speed. thanks. regards, T.Bhuvaneswari. --- Chris Albertson <chrisalbertson90278@yahoo.com> wrote: > > --- Hans-Juergen Schoenig <hs@cybertec.at> wrote: > > Thirumoorthy Bhuvneswari wrote: > > > hi, > > > I am using Postgresql-7.1.3 with RedHat > Linux-7.1 and > > > JDBC. I am having an indexed table with nearly > 3,000 > > > records for a month. My machine is with 256 MB > RAM, > > > 600 MHz. I am having a report from the abovesaid > > > table, which displays 1 month's records at a > stretch > > > using Swing components. If I run the query from > the > > > hard-disk > > > it takes about 5 seconds to display all the > 3,000 > > > records. If I connect 3 nodes to it and run the > query > > > from a node, it takes about 1.5 minutes. I don't > know > > > where I am wrong. Please do reply. thanks. > > Please tell us two more things: > > 1) If you enter the query by hand at the psql prompt > how > long does the query take? > > 2) What is the SQL query? Show us the SQL you > entered. > > I would expect much beter 10x to 100x speeds for > most "normal" > queries. But do the above _after_ a "vacuum full > analyze" > > ===== > Chris Albertson > Home: 310-376-1029 > chrisalbertson90278@yahoo.com > Cell: 310-990-7550 > Office: 310-336-5189 > Christopher.J.Albertson@aero.org > > __________________________________________________ > Do You Yahoo!? > Sign up for SBC Yahoo! Dial - First Month Free > http://sbc.yahoo.com > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org __________________________________________________ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com
Try this then re-run the querry. CREATE INDEX date_indx ON loom_details (date); VACUUM FULL ANALYZE; I don't like using "SELECT *" in code because if later I add a new column I have to go back and fix all those querries. And also the querry is hard to read later with out looking up the table's structure. But clearly "select * works and I use i at the psql prompt all the time. --- Thirumoorthy Bhuvneswari <tbhuvneswari@yahoo.com> wrote: > hi, > my query looks like this: > > select * from loom_details where date>='2002-06-01' > and date<='2002-06-30'. > > * I do give 'select *' since all the columns are > required for the report. > > * When I give the above query in the 'psql' prompt it > waits for 1 second to fetch the records and displays > the entire rows(about 3000). > > * There are about 10,000 records total in the table. > > * When I run the report, it actually does not increase > the RAM usage but takes about 90% of the CPU time > leaving all the other queries behind it. > > Please tell me how can I increase the speed. thanks. > > regards, > T.Bhuvaneswari. > > --- Chris Albertson <chrisalbertson90278@yahoo.com> > wrote: > > > > --- Hans-Juergen Schoenig <hs@cybertec.at> wrote: > > > Thirumoorthy Bhuvneswari wrote: > > > > hi, > > > > I am using Postgresql-7.1.3 with RedHat > > Linux-7.1 and > > > > JDBC. I am having an indexed table with nearly > > 3,000 > > > > records for a month. My machine is with 256 MB > > RAM, > > > > 600 MHz. I am having a report from the abovesaid > > > > table, which displays 1 month's records at a > > stretch > > > > using Swing components. If I run the query from > > the > > > > hard-disk > > > > it takes about 5 seconds to display all the > > 3,000 > > > > records. If I connect 3 nodes to it and run the > > query > > > > from a node, it takes about 1.5 minutes. I don't > > know > > > > where I am wrong. Please do reply. thanks. > > > > Please tell us two more things: > > > > 1) If you enter the query by hand at the psql prompt > > how > > long does the query take? > > > > 2) What is the SQL query? Show us the SQL you > > entered. > > > > I would expect much beter 10x to 100x speeds for > > most "normal" > > queries. But do the above _after_ a "vacuum full > > analyze" > > > > ===== > > Chris Albertson > > Home: 310-376-1029 > > chrisalbertson90278@yahoo.com > > Cell: 310-990-7550 > > Office: 310-336-5189 > > Christopher.J.Albertson@aero.org > > > > __________________________________________________ > > Do You Yahoo!? > > Sign up for SBC Yahoo! Dial - First Month Free > > http://sbc.yahoo.com > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > __________________________________________________ > Do You Yahoo!? > Sign up for SBC Yahoo! Dial - First Month Free > http://sbc.yahoo.com ===== Chris Albertson Home: 310-376-1029 chrisalbertson90278@yahoo.com Cell: 310-990-7550 Office: 310-336-5189 Christopher.J.Albertson@aero.org __________________________________________________ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com
hi Chris, Thanks for your idea. But, if I give the command 'VACUUM FULL ANALYZE', it says a parsing error. Actually, I use to give 'vacuumdb databasename' from outside the psql prompt weekly. Whether it is 'VACUUM FULL ANALYZE' or I use to give 'VACUUM ANALYZE tablename' it is the one. Please let me know. In 'man vacuum' it just says 'VACUUM VERBOSE ANALYZE' and not 'VACUUM FULL ANALYZE'. Please forgive if I am wrong somewhere. thanks. regards, bhuvana. --- Chris Albertson <chrisalbertson90278@yahoo.com> wrote: > > --- Thirumoorthy Bhuvneswari > <tbhuvneswari@yahoo.com> wrote: > > <SNIP> > > > ....Also, what is 'VACUUM FULL ANALYZE'. > > THat's it. If you don't know that you must not have > done it. Do it and things will speed up. Run it as > a nightly cron job and it will stay fast. > > See "man vacuum" > > It is a SQL statment that compacts the tables and > populates > statics about the table that the querry optimizer an > use. > > > ===== > Chris Albertson > Home: 310-376-1029 > chrisalbertson90278@yahoo.com > Cell: 310-990-7550 > Office: 310-336-5189 > Christopher.J.Albertson@aero.org > > __________________________________________________ > Do You Yahoo!? > Sign up for SBC Yahoo! Dial - First Month Free > http://sbc.yahoo.com __________________________________________________ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com
vacuum full is supported in 7.2.x, not any prior release. ----- Original Message ----- From: "Thirumoorthy Bhuvneswari" <tbhuvneswari@yahoo.com> To: "Chris Albertson" <chrisalbertson90278@yahoo.com> Cc: <pgsql-general@postgresql.org> Sent: Friday, July 12, 2002 10:54 AM Subject: Re: [GENERAL] Query Speed!!! > hi Chris, > Thanks for your idea. But, if I give the command > 'VACUUM FULL ANALYZE', it says a parsing error. > Actually, I use to give > > 'vacuumdb databasename' from outside the psql prompt > weekly. Whether it is 'VACUUM FULL ANALYZE' or I use > to give 'VACUUM ANALYZE tablename' it is the one. > Please let me know. In 'man vacuum' it just says > 'VACUUM VERBOSE ANALYZE' and not 'VACUUM FULL > ANALYZE'. Please > forgive if I am wrong somewhere. thanks. > > regards, > bhuvana. > > > --- Chris Albertson <chrisalbertson90278@yahoo.com> > wrote: > > > > --- Thirumoorthy Bhuvneswari > > <tbhuvneswari@yahoo.com> wrote: > > > <SNIP> > > > > > ....Also, what is 'VACUUM FULL ANALYZE'. > > > > THat's it. If you don't know that you must not have > > done it. Do it and things will speed up. Run it as > > a nightly cron job and it will stay fast. > > > > See "man vacuum" > > > > It is a SQL statment that compacts the tables and > > populates > > statics about the table that the querry optimizer an > > use. > > > > > > ===== > > Chris Albertson > > Home: 310-376-1029 > > chrisalbertson90278@yahoo.com > > Cell: 310-990-7550 > > Office: 310-336-5189 > > Christopher.J.Albertson@aero.org > > > > __________________________________________________ > > Do You Yahoo!? > > Sign up for SBC Yahoo! Dial - First Month Free > > http://sbc.yahoo.com > > > __________________________________________________ > Do You Yahoo!? > Sign up for SBC Yahoo! Dial - First Month Free > http://sbc.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >