Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 610709FA618 for ; Mon, 18 Jun 2007 16:42:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 59528-05 for ; Mon, 18 Jun 2007 16:42:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id 983609FA589 for ; Mon, 18 Jun 2007 16:42:04 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Mon, 18 Jun 2007 14:42:03 -0500 Message-Id: <467699B7.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Mon, 18 Jun 2007 14:41:59 -0500 From: "Kevin Grittner" To: Subject: Explained by known hardware failures, or keep looking? Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/152 X-Sequence-Number: 25820 Linux version 2.6.5-7.252-bigsmp (geeko@buildhost) (gcc version 3.3.3 = (SuSE Linux)) #1 SMP Tue Feb 14 11:11:04 UTC 2006 PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 = (SuSE Linux) Database 212GB on RAID 5. 4 x Intel(R) XEON(TM) MP CPU 2.00GHz =20 total used free shared buffers cached Mem: 6213064 5884120 328944 0 0 5135240 -/+ buffers/cache: 748880 5464184 Swap: 6293496 8212 6285284 =20 pg_control version number: 822 Catalog version number: 200611241 Database system identifier: 5005146534765255671 Database cluster state: in production pg_control last modified: Mon 18 Jun 2007 01:29:57 PM CDT Current log file ID: 471 Next log file segment: 217 Latest checkpoint location: 1D7/D832669C Prior checkpoint location: 1D7/D81753D8 Latest checkpoint's REDO location: 1D7/D832669C Latest checkpoint's UNDO location: 0/0 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/612647174 Latest checkpoint's NextOID: 2450764 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Time of latest checkpoint: Mon 18 Jun 2007 01:29:57 PM CDT Minimum recovery ending location: 118/99F15C2C Maximum data alignment: 4 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Date/time type storage: 64-bit integers Maximum length of locale name: 128 LC_COLLATE: C LC_CTYPE: C =20 listen_addresses =3D '*' # what IP address(es) to listen = on; max_connections =3D 200 # (change requires restart) shared_buffers =3D 160MB # min 128kB or max_connections*16= kB temp_buffers =3D 50MB # min 800kB work_mem =3D 10MB # min 64kB maintenance_work_mem =3D 160MB # min 1MB max_fsm_pages =3D 800000 # min max_fsm_relations*16, 6 bytes each bgwriter_lru_percent =3D 20.0 # 0-100% of LRU buffers = scanned/round bgwriter_lru_maxpages =3D 200 # 0-1000 buffers max written/roun= d bgwriter_all_percent =3D 10.0 # 0-100% of all buffers = scanned/round bgwriter_all_maxpages =3D 600 # 0-1000 buffers max written/roun= d wal_buffers =3D 160kB # min 32kB checkpoint_segments =3D 10 # in logfile segments, min 1, = 16MB each archive_command =3D '' random_page_cost =3D 2.0 # same scale as above effective_cache_size =3D 5GB redirect_stderr =3D on # Enable capturing of stderr = into log log_line_prefix =3D '[%m] %p %q<%u %d %r> ' # = Special values: stats_block_level =3D on stats_row_level =3D on autovacuum =3D on # enable autovacuum subprocess? autovacuum_naptime =3D 10s # time between autovacuum runs autovacuum_vacuum_threshold =3D 1 # min # of tuple updates before autovacuum_analyze_threshold =3D 1 # min # of tuple updates before datestyle =3D 'iso, mdy' lc_messages =3D 'C' # locale for system error = message lc_monetary =3D 'C' # locale for monetary formatting lc_numeric =3D 'C' # locale for number formatting lc_time =3D 'C' # locale for time formatting escape_string_warning =3D off standard_conforming_strings =3D on sql_inheritance =3D off =20 First off, we're not looking for help with recovery of the data -- we run = several copies of the failed database and we've load shifted to work = around the failure until we can do a PITR-style recovery from one of the = working copies. I'm just trying to determine whether the problems we're = seeing sound likely to be caused by a known event, or whether we should be = casting about for other possible causes. =20 On May 30th, a database server suffered a failure in its battery-backed = RAID controller card. We replaced it and brought the database back up. = It seemed to come up clean, and we didn't have any noticeable problems. =20 On June 14th, the server abruptly lost power. When power was restored = PostgreSQL came back up with these messages: =20 [2007-06-14 11:31:05.949 CDT] 6781 LOG: database system was interrupted = at 2007-06-14 11:18:53 CDT [2007-06-14 11:31:05.958 CDT] 6781 LOG: checkpoint record is at 1D2/6CB2EF= A0 [2007-06-14 11:31:05.958 CDT] 6781 LOG: redo record is at 1D2/6C739064; = undo record is at 0/0; shutdown FALSE [2007-06-14 11:31:05.958 CDT] 6781 LOG: next transaction ID: 0/607370870; = next OID: 2336076 [2007-06-14 11:31:05.958 CDT] 6781 LOG: next MultiXactId: 1; next = MultiXactOffset: 0 [2007-06-14 11:31:05.958 CDT] 6781 LOG: database system was not properly = shut down; automatic recovery in progress [2007-06-14 11:31:05.986 CDT] 6781 LOG: redo starts at 1D2/6C739064 [2007-06-14 11:31:46.533 CDT] 6781 WARNING: invalid page header in block = 182566 of relation "1523860"; zeroing out page [2007-06-14 11:31:46.533 CDT] 6781 CONTEXT: xlog redo split_r: rel = 1663/16386/1523860; tid 182566/92; oth 182563; rgh 115741 [2007-06-14 11:31:56.228 CDT] 6781 WARNING: invalid page header in block = 182567 of relation "1523860"; zeroing out page [2007-06-14 11:31:56.229 CDT] 6781 CONTEXT: xlog redo split_r: rel = 1663/16386/1523860; tid 182567/94; oth 182128; rgh 114655 [2007-06-14 11:32:04.964 CDT] 6781 WARNING: invalid page header in block = 123644 of relation "1524189"; zeroing out page [2007-06-14 11:32:04.964 CDT] 6781 CONTEXT: xlog redo split_r: rel = 1663/16386/1524189; tid 123644/101; oth 123634; rgh 106665 [2007-06-14 11:32:11.327 CDT] 6781 WARNING: invalid page header in block = 356562 of relation "1524219"; zeroing out page [2007-06-14 11:32:11.327 CDT] 6781 CONTEXT: xlog redo split_r: rel = 1663/16386/1524219; tid 356562/58; oth 356549; rgh 34892 [2007-06-14 11:32:14.795 CDT] 6781 LOG: record with zero length at = 1D2/70C31890 [2007-06-14 11:32:14.795 CDT] 6781 LOG: redo done at 1D2/70C31868 [2007-06-14 11:32:33.833 CDT] 6781 LOG: database system is ready =20 Over the weekend it fell apart. The problems occurred right at the end of = a weekly maintenance process which builds a new version of a table based = on records retention rules. It is built under a temporary name; then the = previous version of the table is dropped and the new table is renamed. = This leaves a fraction of a second during which queries may fail on the = missing table, but it seems to be better than the alternatives. (If a = query doesn't complete within 20 seconds, it is an error for the users of = these tables, since our web app times out. The alternatives which blocked = rather than giving outright errors blocked for more than 20.25 seconds, so = this alternative generates the fewest errors from a user perspective.) =20 [2007-06-17 08:22:41.925 CDT] 10674 NOTICE: = ALTER TABLE / ADD PRIMARY KEY will create implicit index "HiddenCase_pkey" = fo r table "HiddenCaseTT" [2007-06-17 08:24:10.511 CDT] 6868 LOG: checkpoints are occurring too = frequently (20 seconds apart) [2007-06-17 08:24:10.511 CDT] 6868 HINT: Consider increasing the = configuration parameter "checkpoint_segments". [2007-06-17 17:05:26.707 CDT] 11860 = ERROR: invalid page header in block 148284 of relation "RcptTender" [2007-06-17 17:05:26.707 CDT] 11860 = STATEMENT: SELECT "amtPaid", "rcptNo", "tenderCode", "tenderSeqNo", = "ccAuthN o", "ccExp", "ccNo", "ccType", "comments", "countyNo" FROM "RcptTender" = WHERE ("countyNo"=3D37) AND ("rcptNo" >=3D '07R 012287' AND ("rcptNo" > = '07R 0122 87' OR "tenderSeqNo" >=3D 1)) AND ("rcptNo" <=3D '07R 012783' AND = ("rcptNo" < '07R 012783' OR "tenderSeqNo" <=3D 1)) ORDER BY "rcptNo", = "tenderSeqNo", "cou ntyNo" [2007-06-17 17:05:33.346 CDT] 11854 = ERROR: duplicate key violates unique constraint "DocImageMetaData_pkey" [2007-06-17 17:05:33.347 CDT] 11854 = STATEMENT: INSERT INTO "DocImageMetaData" ("isConfidential", "docId", = "insert edDate", "docSource", "sectionNo", "countyNo", "eFilingRcvdDate", = "caseNo", "docHash", "isEFiling", "imageSize", "filedReceived", "isSignatPr= ocessed" , "insertedBy", "isDuplex", "newDocId", "docName", "histSeqNo", "orientatio= n", "isSignatRequired", "eFilingFiledDate", "paperSize", "isHidden", "page s", "dateLastAccessed") VALUES ($1, $2, $3, $4, $5, $6, NULL, $7, $8, $9, = $10, $11, $12, $13, $14, NULL, $15, $16, $17, $18, NULL, $19, $20, $21, = $22 ) [2007-06-17 17:05:34.070 CDT] 6779 LOG: server process (PID 11854) was = terminated by signal 11 [2007-06-17 17:05:34.070 CDT] 6779 LOG: terminating any other active = server processes =20 We are now getting "invalid page header" messages on at least five = different tables and at least 10 different indexes. We're seeing signal = 11 from time to time. We're seeing the database, while still running, = report that it is starting up or that it is in recovery mode for periods = of time. We're seeing a variety of strange messages indicating corruption = at startup -- I can post more detail if someone thinks these would help = diagnose the issue, but they include: =20 The most recent start included these lines: =20 [2007-06-18 13:24:37.490 CDT] 11981 LOG: redo starts at 1D7/D7DC8D8C [2007-06-18 13:24:37.526 CDT] 11981 LOG: unexpected pageaddr 1D7/C1FE8000 = in log file 471, segment 215, offset 16678912 [2007-06-18 13:24:37.526 CDT] 11981 LOG: redo done at 1D7/D7FE6730 [2007-06-18 13:24:37.895 CDT] 11981 LOG: database system is ready [2007-06-18 13:24:57.380 CDT] 12034 = ERROR: could not access status of transaction 10 [2007-06-18 13:24:57.380 CDT] 12034 = DETAIL: Could not open file "pg_clog/0000": No such file or directory. =20 Could all of this be reasonably explained by the controller failure and/or = the subsequent abrupt power loss, or should I be looking for another = cause? Personally, as I look at this, I'm suspicious that either the = controller didn't persist dirty pages in the June 14th failure or there is = some ongoing hardware problem. =20 Thoughts or suggestions? =20 -Kevin =20