Обсуждение: Vaccuum/Analyze
I am running PostgreSQL 7.2.1 on a Sun 5.8 OS. I have a database named metrics with 4 tables. A few of the tables contain approx. 10 million records. When I run a vaccuum/analyze with the following command:
vacuumdb -d metrics -z -v
the process 'hangs' for several hours and then an error message appears that says:
vacuumdb: vacuum metrics failed.
ERROR: RelationClearRelation: Relation 16599 deleted while still in use.
Watching the stdout I see the process is 'hanging' on analyzing one of my tables called file_dup. This table has no data in it. What strikes me as odd is that I ran vacuumdb against the file_dup table itself:
(vacuumdb -z -v -t 'file_dup' metrics) and it completed within seconds without errors.
I tried to run the vacuumdb against the entire database again and it 'hung' in the same place.
Any suggestions?
Jeff Brickley
Brickley Jeff-RA9607 <Jeff.Brickley@motorola.com> writes: > I am running PostgreSQL 7.2.1 on a Sun 5.8 OS. I have a database named metrics with 4 tables. A few of the tables containapprox. 10 million records. When I run a vaccuum/analyze with the following command: > vacuumdb -d metrics -z -v > the process 'hangs' for several hours and then an error message appears that says: > vacuumdb: vacuum metrics failed. > ERROR: RelationClearRelation: Relation 16599 deleted while still in use. > Watching the stdout I see the process is 'hanging' on analyzing one of my tables called file_dup. This table has no datain it. What strikes me as odd is that I ran vacuumdb against the file_dup table itself: > (vacuumdb -z -v -t 'file_dup' metrics) and it completed within seconds without errors. I'd be inclined to wonder what else is accessing that database. Could some other process be sitting with an open transaction that has that table locked? regards, tom lane
hi! i get these notices everytime i perform a vacuum: NOTICE: RegisterSharedInvalid: SI buffer overflow NOTICE: InvalidateSharedInvalid: cache state reset NOTICE: RegisterSharedInvalid: SI buffer overflow NOTICE: InvalidateSharedInvalid: cache state reset could anyone give me some insights please? thanks in advance, MEL
> I get these as well, and was told not to worry about them. >hi! > >i get these notices everytime i perform a vacuum: > >NOTICE: RegisterSharedInvalid: SI buffer overflow >NOTICE: InvalidateSharedInvalid: cache state reset >NOTICE: RegisterSharedInvalid: SI buffer overflow >NOTICE: InvalidateSharedInvalid: cache state reset > >could anyone give me some insights please? > >thanks in advance, > >MEL > >---------------------------(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
"Mel Jamero" <mel@gmanmi.tv> writes: > i get these notices everytime i perform a vacuum: > NOTICE: RegisterSharedInvalid: SI buffer overflow > NOTICE: InvalidateSharedInvalid: cache state reset > NOTICE: RegisterSharedInvalid: SI buffer overflow > NOTICE: InvalidateSharedInvalid: cache state reset > could anyone give me some insights please? (a) these are not particularly dangerous (b) you are running an obsolete Postgres version; those messages were downgraded to DEBUG level in 7.2 (c) you have clients that are laying about holding open transactions, which is not good for concurrency. First recommendation is to update, second is to see if you can't fix the clients so that their idle state isn't holding an open transaction. regards, tom lane
hi tom, thanks for your reply. i've seen these notices quite a bit so i'm convinced they're not particulary dangerous. we're using 7.1.3-2 so i'll take your advice and upgrade to 7.2 in the near future. C programs are the main clients and a few TCL pages on AOLserver; i'll have to check there regarding their idle state.. thanks again, mel jamero -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Friday, September 13, 2002 12:30 PM To: mel@gmanmi.tv Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Vaccuum/Analyze "Mel Jamero" <mel@gmanmi.tv> writes: > i get these notices everytime i perform a vacuum: > NOTICE: RegisterSharedInvalid: SI buffer overflow > NOTICE: InvalidateSharedInvalid: cache state reset > NOTICE: RegisterSharedInvalid: SI buffer overflow > NOTICE: InvalidateSharedInvalid: cache state reset > could anyone give me some insights please? (a) these are not particularly dangerous (b) you are running an obsolete Postgres version; those messages were downgraded to DEBUG level in 7.2 (c) you have clients that are laying about holding open transactions, which is not good for concurrency. First recommendation is to update, second is to see if you can't fix the clients so that their idle state isn't holding an open transaction. regards, tom lane