RE: dramatic slowdown in selects after pg has been runni ng for a while
От | patrick.wolf@Aerojet.com (WOLF, PATRICK) |
---|---|
Тема | RE: dramatic slowdown in selects after pg has been runni ng for a while |
Дата | |
Msg-id | 63A19D0F08E6D211AD740008C7B1C47B02FE1648@APD-MAIL1 обсуждение исходный текст |
Ответы |
Re: dramatic slowdown. . .fixed by vacuum
Third email on compilining 7.0.2 on Solaris 2.5.1 |
Список | pgsql-novice |
Try running vacuum on the table or the database. Here's an excerpt from the man on vacuum: Description VACUUM serves two purposes in Postgres as both a means to reclaim storage and also a means to collect information for the optimizer. VACUUM opens every class in the database, cleans out records from rolled back transactions, and updates statistics in the system catalogs. The statistics maintained include the number of tuples and number of pages stored in all classes. VACUUM ANALYZE collects statistics representing the disbursion of the data in each column. This information is valuable when several query execution paths are possible. Running VACUUM periodically will increase the speed of the database in processing user queries. Patrick C. Wolf Test Manager Aerojet Socorro Plant -----Original Message----- From: Joe Slag [mailto:jslag@visi.com] Sent: Friday, July 21, 2000 1:05 PM To: pgsql-novice@postgresql.org Subject: [NOVICE] dramatic slowdown in selects after pg has been running for a while I'm evaluating pg for use in my company, and have run into a bit of a snag. One of the tests I've been running is a loop of 10,000 "select * from foo" statements from a perl program, where foo is: Table "foo" Attribute | Type | Modifier -----------+---------+---------- bar | integer | zag | text | When I initially ran this test on my workstation (500 mhz PIII, 128 meg ram, debian 2.2 w/2.2.16 kernel) the whole process took around 10 seconds. After getting results from my select test, I did 10,000 updates (which took an average of 37 seconds), and then deleted the rows I'd updated (from psql). Now, when I rerun the "select" test (against the same data that was there before the updates), it takes forever - results have varied from 300-some seconds to over 700. To make sure that the whole pg process wasn't screwed up, I created another similar table and ran my 10,000 select script against it - and results are back down to 10 seconds. So, it seems that somewhere in the process of running a bunch of updates to "foo" (and deleteing them) things have become screwed up. What could be slowing selects against this table down, and how would I proceed to investigate the matter further? I've been reading through the pg docs, and haven't seen much performance monitoring other than "explain" (which says exactly the same thing about both the fast and slow tables). Is there a log somewhere, or a command that would further show me what's going on? TIA Joe Slag Wagpaw, inc.
В списке pgsql-novice по дате отправления:
Предыдущее
От: Alfred PerlsteinДата:
Сообщение: Re: dramatic slowdown in selects after pg has been running for a while