Обсуждение: Strange..!!! Can u tell me why this ?
Hello Friends, I found very a strange thing about postgres Database. I am running postgres under Linux operating system. I am having a website in PHP and having one perl program which runs every friday to generate a report. The size of thsi postgres DB is near about 5-7 MB. In the start when records were less in the DB the file too around 3 to mins to generate a report we called as Friday report. The web pages were also coming fast in the browser. So I was quite happy abt the performanance. But Later on as the DB size get increased the time was also get increased. And at one point i noticed the time required to gererate this report is very high near abt 10 to 15 hrs. So I checked for performanance. Site was also become very slow. A simple page which is fetching just 20 records, for that also I got the error page not displayed.I tried my level best to find out the problem. But I couldn't...!!! :-(( So at last I decided to drop DB and recreate once more after taking backup of data. And I did that. After that i was surprised that site become very very fast. And the pages for which I got the error comes in less than 10 seconds. So can u tell me why this happens ? Why suddenly DB performanance get increased so fastly ? Prev.ly I tried for vacuumdb also.... If anybody there knows the reason for this please reply me. Thanx in advance. Pandurang pandurang@manas-solutions.com http://www.geocities.com/pandurang_g/
Hi Pandurang, Are you sure your queries are optimised and using the right indexes whereever applicable? Try the EXPLAIN command with the slow queries is there a sequence scan on a large table? Pg does not at all detoriate like u have described if you do the rite mantainence like VACUUMMING for instance regularly. I personally use Pg for past 1 year with data as much as 1000 MB without major problems Regds MAllah. > Hello Friends, > I found very a strange thing about postgres Database. I am running postgres under Linux > operating system. > > I am having a website in PHP and having one perl program which runs every friday to generate a > report. The size of thsi postgres DB is near about 5-7 MB. > > In the start when records were less in the DB the file too around 3 to mins to generate a > report we called as Friday report. The web pages were also coming fast in the browser. So I was > quite happy abt the performanance. > > But Later on as the DB size get increased the time was also get > increased. And at one point i noticed the time required to gererate this report is very high > near abt 10 to 15 hrs. So I checked for > performanance. > > Site was also become very slow. A simple page which is fetching just 20 records, for that also > I got the error page not displayed.I tried my level best to find out the problem. But I > couldn't...!!! :-(( > > So at last I decided to drop DB and recreate once more after taking backup of data. And I did > that. After that i was surprised that site become very very fast. And the pages for which I got > the error comes in less than 10 seconds. > > So can u tell me why this happens ? Why suddenly DB performanance get increased so fastly ? > Prev.ly I tried for vacuumdb also.... > > If anybody there knows the reason for this please reply me. > > Thanx in advance. > Pandurang > pandurang@manas-solutions.com > http://www.geocities.com/pandurang_g/ > > ---------------------------(end of broadcast)--------------------------- TIP 5: Have you > checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
On 26 Oct 2002, Pandurang wrote: > But Later on as the DB size get increased the time was also get > increased. And at one point i noticed the time required to gererate > this report is very high near abt 10 to 15 hrs. So I checked for > performanance. > > Site was also become very slow. A simple page which is fetching just > 20 records, for that also I got the error page not displayed.I tried > my level best to find out the problem. But I couldn't...!!! :-(( > > So at last I decided to drop DB and recreate once more after taking > backup of data. And I did that. After that i was surprised that site > become very very fast. And the pages for which I got the error comes > in less than 10 seconds. > > So can u tell me why this happens ? Why suddenly DB performanance get > increased so fastly ? Prev.ly I tried for vacuumdb also.... You tried vacuuming, but were you doing analyze (I believe -z to vacuumdb)? What does explain show for the queries in question?