Re: Query Speed!!!
От | Sam Liddicott |
---|---|
Тема | Re: Query Speed!!! |
Дата | |
Msg-id | D38A0FCD5830E848992DF2D4AF5F6F4F72FD4C@conwy.leeds.ananova.internal обсуждение исходный текст |
Ответ на | Query Speed!!! (Thirumoorthy Bhuvneswari <tbhuvneswari@yahoo.com>) |
Список | pgsql-general |
> -----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
В списке pgsql-general по дате отправления: