Обсуждение: Version 7.2.3 Vacuum abnormality
Good evening, tonight while running my routine vacuum, the following came up on my screen:
---8<---------------
NOTICE: Rel xamefiles: Uninitialized page 708135 - fixing
NOTICE: Rel xamefiles: Uninitialized page 708136 - fixing
NOTICE: Rel xamefiles: Uninitialized page 708137 - fixing
NOTICE: Rel xamefiles: Uninitialized page 708138 - fixing
NOTICE: Rel xamefiles: Uninitialized page 708599 - fixing
---8<---------------
There were a lot more than this, several hundered.
Here's the general details of the DB:- Approximately 30,000,000 rows- No triggers- Table consists strictly of text,
integersand one key ( SERIAL )- PostgreSQL 7.2.3 ( yes, i'll update to 7.2.4 in the next day or so )- Pentium III
1.2Ghzwith 1Gb RAM running RedHat 8.0 ( Not my machine! )
Here's the lead up to events:
- Two days ago I DELETE'd approximately 7 million rows- I proceeded to vacuum, but it was 'terminated' by another admin
approximately12 hours later- I restarted the vacuum, which resulted in the following stats:
NOTICE: Pages 701193: Changed 8459, Empty 0; Tup 21042082: Vac 0, Keep 0, UnUsed 32056923
- I ran the DELETE script again which purged another 200,000 rows ( approx )- I proceeded to vacuum, the table in
questionreturned the following stats:
NOTICE: Pages 704754: Changed 9599, Empty 0; Tup 14385034: Vac 0, Keep 207650, UnUsed 38884420.
- Today I dropped an index off the table, ran the DELETE again, removing 457,636 rows- Vacuum dumped the above
NOTICES. Final output for the table is:
NOTICE: Index xamefiles_k_key: Pages 187175; Tuples 14521716: Deleted 666334. CPU 8.92s/14.11u sec elapsed
1306.20sec.- The vacuum is still going along fine.
Backups are proceeding without incident ( using pg_dump )DELETE's are proceeding without incident
Hope this is enough information.
Kind Regards.
--
Paul L Daniels http://www.pldaniels.com
Linux/Unix systems Internet Development
ICQ#103642862,AOL:cinflex,IRC:inflex
A.B.N. 19 500 721 806
On Thu, Feb 13, 2003 at 03:27:01PM +1000, Paul L Daniels wrote: > Good evening, tonight while running my routine vacuum, the following came up on my screen: > > ---8<--------------- > NOTICE: Rel xamefiles: Uninitialized page 708135 - fixing > NOTICE: Rel xamefiles: Uninitialized page 708136 - fixing > NOTICE: Rel xamefiles: Uninitialized page 708137 - fixing > NOTICE: Rel xamefiles: Uninitialized page 708138 - fixing > NOTICE: Rel xamefiles: Uninitialized page 708599 - fixing > ---8<--------------- This is a known and, it turns out, not real serious bug. See, e.g., http://archives.postgresql.org/pgsql-hackers/2002-11/msg00486.php I seem to recall Tom Lane noticing (uh, a few weeks ago, I think) that the problem happens not just on system tables. I think 7.2.4 is supposed to partially fix this, but ISTR that there is something about it which can't be fixed without forcing a catalog change (which forces initdb, and is therefore Not Allowed for dot-releases). A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110
Andrew Sullivan <andrew@libertyrms.info> writes: > On Thu, Feb 13, 2003 at 03:27:01PM +1000, Paul L Daniels wrote: >> NOTICE: Rel xamefiles: Uninitialized page 708135 - fixing >> NOTICE: Rel xamefiles: Uninitialized page 708136 - fixing >> NOTICE: Rel xamefiles: Uninitialized page 708137 - fixing > This is a known and, it turns out, not real serious bug. See, e.g., > http://archives.postgresql.org/pgsql-hackers/2002-11/msg00486.php > I seem to recall Tom Lane noticing (uh, a few weeks ago, I think) > that the problem happens not just on system tables. The mechanism I described in the above-referenced message only occurs for nailed-in-cache system tables. Given Daniels' report (and one or two others) I am suspicious that there's some path whereby rd_targblock can fail to get reset after a vacuum for non-system tables too --- but it hasn't been identified yet. If what Daniels saw is due to a problem like that, then it's pretty harmless. If it's something else, the implications might be more dire. regards, tom lane
On Mon, Feb 17, 2003 at 05:42:21PM -0500, Tom Lane wrote: > Andrew Sullivan <andrew@libertyrms.info> writes: > > On Thu, Feb 13, 2003 at 03:27:01PM +1000, Paul L Daniels wrote: > > > http://archives.postgresql.org/pgsql-hackers/2002-11/msg00486.php > > The mechanism I described in the above-referenced message only occurs > for nailed-in-cache system tables. Given Daniels' report (and one or And for ones that have been truncated? I found this reference: <http://groups.google.ca/groups?hl=en&lr=&ie=UTF-8&threadm=200301251026.14193.mallah%40trade-india.com&rnum=5&prev=/groups%3Fq%3DUninitialized%2Bpage%2Bgroup:comp.databases.postgresql.*%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D200301251026.14193.mallah%2540trade-india.com%26rnum%3D5> (Sorry about the long line. I'm still having no luck with archives.postgresql.org). A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110
Andrew Sullivan <andrew@libertyrms.info> writes: > On Mon, Feb 17, 2003 at 05:42:21PM -0500, Tom Lane wrote: >> The mechanism I described in the above-referenced message only occurs >> for nailed-in-cache system tables. Given Daniels' report (and one or > And for ones that have been truncated? I found this reference: > <http://groups.google.ca/groups?hl=en&lr=&ie=UTF-8&threadm=200301251026.14193.mallah%40trade-india.com&rnum=5&prev=/groups%3Fq%3DUninitialized%2Bpage%2Bgroup:comp.databases.postgresql.*%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D200301251026.14193.mallah%2540trade-india.com%26rnum%3D5> Sigh, I must be losing brain cells faster than I thought. I completely forgot about the TRUNCATE version of the problem. Of course, if the complainant hasn't done TRUNCATE either, then we may still have an issue ... regards, tom lane