Re: [HACKERS] Version 7.2.3 Vacuum abnormality
On Mon, Feb 17, 2003 at 05:42:21PM -0500, Tom Lane wrote: Andrew Sullivan [EMAIL PROTECTED] 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=enlr=ie=UTF-8threadm=200301251026.14193.mallah%40trade-india.comrnum=5prev=/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 [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Version 7.2.3 Vacuum abnormality
Andrew Sullivan [EMAIL PROTECTED] 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=enlr=ie=UTF-8threadm=200301251026.14193.mallah%40trade-india.comrnum=5prev=/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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Version 7.2.3 Vacuum abnormality
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 [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Version 7.2.3 Vacuum abnormality
Andrew Sullivan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] 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, integers and one key ( SERIAL ) - PostgreSQL 7.2.3 ( yes, i'll update to 7.2.4 in the next day or so ) - Pentium III 1.2Ghz with 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 approximately 12 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 question returned 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.20 sec. - 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 Danielshttp://www.pldaniels.com Linux/Unix systemsInternet Development ICQ#103642862,AOL:cinflex,IRC:inflex A.B.N. 19 500 721 806 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
