pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: could not read block
41+ messages / 11 participants
[nested] [flat]
* ERROR: could not read block
@ 2005-11-14 23:20 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 41+ messages in thread
From: Kevin Grittner @ 2005-11-14 23:20 UTC (permalink / raw)
To: pgsql-admin
A programmer ran a query to fix some data against two "identical"
databases -- one on Linux and one on Windows. They are both 8.1.0,
running on dual hyperthreaded Xeons, with data on RAID5. The Linux
update went fine. The Windows attempt give this:
dtr=> UPDATE
dtr-> "DbTranRepository"
dtr-> SET "userId" = UPPER("userId")
dtr-> WHERE (
dtr(> ("userId" <> UPPER("userId")) AND
dtr(> ("timestampValue" BETWEEN '2005-10-28' AND '2005-11-15'));
ERROR: could not read block 649847 of relation 1663/16385/16483:
Invalid argument
Table "public.DbTranRepository"
Column | Type | Modifiers
------------------+------------------------+-----------
countyNo | "CountyNoT" | not null
tranImageSeqNo | "TranImageSeqNoT" | not null
timestampValue | "TimestampT" | not null
transactionImage | "ImageT" |
status | character(1) | not null
queryName | "QueryNameT" |
runDuration | numeric(15,0) |
userId | "UserIdT" |
functionalArea | character varying(50) |
sourceRef | character varying(255) |
url | "URLT" |
tranImageSize | numeric(15,0) |
Indexes:
"DbTranRepositoryPK" PRIMARY KEY, btree ("countyNo",
"tranImageSeqNo") CLUSTER
"DbTranRepository_timestamp" btree ("countyNo", "timestampValue")
"DbTranRepository_userId" btree ("countyNo", "userId",
"timestampValue")
fsync is on. We have not had any crashes that I know of (one key player
here is currently unavailable, so I may get a surprise when I can talk
to him). The table being updated has about 23.3 million rows, each of
which has a bytea column which should normally be toasted. The database
was in use during this fix query. This table is normally insert-only.
The fix query was running for about an hour, during which time about
45,000 rows were inserted.
This looks to me like a database corruption. We can recover from the
Linux machine, but we're interested in the cause. (Management is
divided on using Linux versus Windows as our db server platform.) Any
suggestions on causes or what to check?
-Kevin
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-14 23:43 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 41+ messages in thread
From: Scott Marlowe @ 2005-11-14 23:43 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
On Mon, 2005-11-14 at 17:20, Kevin Grittner wrote:
> A programmer ran a query to fix some data against two "identical"
> databases -- one on Linux and one on Windows. They are both 8.1.0,
> running on dual hyperthreaded Xeons, with data on RAID5. The Linux
> update went fine. The Windows attempt give this:
>
> dtr=> UPDATE
> dtr-> "DbTranRepository"
> dtr-> SET "userId" = UPPER("userId")
> dtr-> WHERE (
> dtr(> ("userId" <> UPPER("userId")) AND
> dtr(> ("timestampValue" BETWEEN '2005-10-28' AND '2005-11-15'));
> ERROR: could not read block 649847 of relation 1663/16385/16483:
> Invalid argument
When a block is unreadable, this means that the OS is experiencing a
read error from the hard drive.
This is not necessarily related to the OS you are running so much as the
hardware you are running on top of.
If you were running on top of a RAID 1+0 or RAID 5 array, such an error
would likely never have happened, since it would have been detected by
the controller, and either the bad block would be mapped out or the
drive would be kicked out of the array and you'd get a system alert
telling you you had a bad drive in your array.
All Databases expect to run on "perfect" hardware. If the hardware gets
a read error, the database is NOT going to know what to do to fix it.
So, right now, it's not Linux 1, Windows 0, it's The box you're running
Linux on 1, the box you're running Windows on 0.
You've got a drive with a bad block.
Are you running on quality hardware (ECC memory, Server class SCSI
drives, battery backed cache hardware RAID array, etc...) or just
whatever was laying about unused. If you're not running your database
on quality hardware, you can't expect reliable results from your
testing, because what you're going to see are the hardware issues
showing up, not the database issues.
That said. PostgreSQL has been running on unix or some flavor of it
since its inception. It's been running on Windows in native mode for
about a year now. I'd say Liunx is going to be a better choice for
production for a while.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-15 00:02 Joshua Marsh <icub3d@gmail.com>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 0 replies; 41+ messages in thread
From: Joshua Marsh @ 2005-11-15 00:02 UTC (permalink / raw)
To: pgsql-admin
On 11/14/05, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> If you were running on top of a RAID 1+0 or RAID 5 array, such an error
> would likely never have happened, since it would have been detected by
> the controller, and either the bad block would be mapped out or the
> drive would be kicked out of the array and you'd get a system alert
> telling you you had a bad drive in your array.
>
>
Except that he jus said he was running on a RAID5. True this problem would
be unlikely on a hardware controlled RAID5 configuration, but not
impossible.
Does the log file tell you anymore information?
Have you tried to rerun the query? If so, did you get similar results?
-Josh
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-15 00:19 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 41+ messages in thread
From: Kevin Grittner @ 2005-11-15 00:19 UTC (permalink / raw)
To: smarlowe@g2switchworks.com; +Cc: pgsql-admin
Both machines are IBM xSeries 346 model 884042U with 6 drives
in a RAID 5 array through an IBM battery backed controller. We
had a couple of these lying around after replacing them with better,
but they have been pretty stable workhorses for us.
I'm checking on whether the RAM is ECC -- the techs available at
the moment aren't sure. The current machines are "transitional",
and it may not be too late to set the permanent servers up with ECC
memory. Is it something I should fight for?
For specs on the base machines, before we dressed them up:
http://www-132.ibm.com/webapp/wcs/stores/servlet/ProductDisplay?productId=8741193&langId=-1
-Kevin
>>> Scott Marlowe <smarlowe@g2switchworks.com> >>>
On Mon, 2005-11-14 at 17:20, Kevin Grittner wrote:
> running on dual hyperthreaded Xeons, with data on RAID5.
> ERROR: could not read block 649847 of relation 1663/16385/16483:
> Invalid argument
If you were running on top of a RAID 1+0 or RAID 5 array, such an error
would likely never have happened, since it would have been detected by
the controller, and either the bad block would be mapped out or the
drive would be kicked out of the array and you'd get a system alert
telling you you had a bad drive in your array.
Are you running on quality hardware (ECC memory, Server class SCSI
drives, battery backed cache hardware RAID array, etc...) or just
whatever was laying about unused.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-15 00:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 0 replies; 41+ messages in thread
From: Tom Lane @ 2005-11-15 00:43 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> On Mon, 2005-11-14 at 17:20, Kevin Grittner wrote:
>> ERROR: could not read block 649847 of relation 1663/16385/16483:
>> Invalid argument
> When a block is unreadable, this means that the OS is experiencing a
> read error from the hard drive.
I'd believe that explanation if the errno were EIO (I/O error), but
EINVAL isn't really a very sane error number to return for a hardware
failure. It'd be useful to find out what Windows error code is actually
being returned by the operating system. I seem to recall that we've
noted some poorly chosen Windows->errno translations before, so maybe
this is another one ... but all we can really say at the moment is
"that shouldn't be happening".
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-15 13:25 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 41+ messages in thread
From: Andrew Sullivan @ 2005-11-15 13:25 UTC (permalink / raw)
To: pgsql-admin
On Mon, Nov 14, 2005 at 06:19:16PM -0600, Kevin Grittner wrote:
> the moment aren't sure. The current machines are "transitional",
> and it may not be too late to set the permanent servers up with ECC
> memory. Is it something I should fight for?
Yes. Always.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
It is above all style through which power defers to reason.
--J. Robert Oppenheimer
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-15 15:12 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 41+ messages in thread
From: Kevin Grittner @ 2005-11-15 15:12 UTC (permalink / raw)
To: icub3d@gmail.com; pgsql-admin
It appears that the log file is not being written -- I'll start a
separate thread on that issue.
I reran the query. Same error, same relation, different block.
dtr=> UPDATE
dtr-> "DbTranRepository"
dtr-> SET "userId" = UPPER("userId")
dtr-> WHERE (
dtr(> ("userId" <> UPPER("userId")) AND
dtr(> ("timestampValue" BETWEEN '2005-10-28' AND '2005-11-15'));
ERROR: could not read block 1118215 of relation 1663/16385/16483:
Invalid argument
-Kevin
>>> Joshua Marsh <icub3d@gmail.com> >>>
Does the log file tell you anymore information?
Have you tried to rerun the query? If so, did you get similar results?
-Josh
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-15 16:29 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 41+ messages in thread
From: Kevin Grittner @ 2005-11-15 16:29 UTC (permalink / raw)
To: Qingqing Zhou <zhouqq@cs.toronto.edu>; icub3d@gmail.com; pgsql-admin; Kevin Grittner <Kevin.Grittner@wicourts.gov>
Could my issue be the same problem as this thread?:
http://archives.postgresql.org/pgsql-bugs/2005-11/msg00114.php
The references to "Invalid Argument" caught my eye. That thread
did start from a very different point, though.
-Kevin
>>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> >>>
It appears that the log file is not being written -- I'll start a
separate thread on that issue.
I reran the query. Same error, same relation, different block.
dtr=> UPDATE
dtr-> "DbTranRepository"
dtr-> SET "userId" = UPPER("userId")
dtr-> WHERE (
dtr(> ("userId" <> UPPER("userId")) AND
dtr(> ("timestampValue" BETWEEN '2005-10-28' AND '2005-11-15'));
ERROR: could not read block 1118215 of relation 1663/16385/16483:
Invalid argument
-Kevin
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-15 16:42 Joshua Marsh <icub3d@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 41+ messages in thread
From: Joshua Marsh @ 2005-11-15 16:42 UTC (permalink / raw)
To: pgsql-admin
On 11/15/05, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>
> Could my issue be the same problem as this thread?:
>
> http://archives.postgresql.org/pgsql-bugs/2005-11/msg00114.php
>
> The references to "Invalid Argument" caught my eye. That thread
> did start from a very different point, though.
>
> -Kevin
It's possible. You said that the log file wasn't working, and you can
reproduce the error, so the first thing I would do is solve the logging
problem, then reproduce the error and see what the log says.
I'm not familiar with pgsql on windows, but check to make sure you have
logging turned on in the postgresql.conf file (this is on by default). Make
sure you are logging to a file and directory that the pgsql service has
permissions to write to.
Does the windows system log give you any errors or warnings when pgsql
starts? Perhaps it will describe why it's not logging.
-Josh
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-15 19:04 Qingqing Zhou <zhouqq@cs.toronto.edu>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 41+ messages in thread
From: Qingqing Zhou @ 2005-11-15 19:04 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: icub3d@gmail.com; pgsql-admin
>
> I reran the query. Same error, same relation, different block.
>
> dtr=> UPDATE
> dtr-> "DbTranRepository"
> dtr-> SET "userId" = UPPER("userId")
> dtr-> WHERE (
> dtr(> ("userId" <> UPPER("userId")) AND
> dtr(> ("timestampValue" BETWEEN '2005-10-28' AND '2005-11-15'));
> ERROR: could not read block 1118215 of relation 1663/16385/16483:
> Invalid argument
>
So this is a big query? Somebody reported this error when IO is intensive.
To conclude the reports we have received:
(1) If IO is intensive (no matter you have anti-virus software or not),
you may encounter "invalud argument" error;
(2) In some cases, anti-virus software could cause "invalid argument"
error;
(3) You may encounter "permission denied" problem (no matter you have
anti-virus software or not) in some unknown condition;
Unfortunately we haven't found out the cause of this problem ... I am
thinking an urgent need is let PG print the GetLastError() for Windows
version as well.
Regards,
Qingqing
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-15 19:14 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 41+ messages in thread
From: Kevin Grittner @ 2005-11-15 19:14 UTC (permalink / raw)
To: zhouqq@cs.toronto.edu; +Cc: icub3d@gmail.com; pgsql-admin
The table has about 23.3 million rows, of which about 200,000 will
be affected by this update. Run time is about an hour. During the
first run, the table was the target of about 45,000 inserts. This rerun
was done as the only task. A third run (also by itself) gave this:
ERROR: could not read block 1482762 of relation 1663/16385/16483:
Invalid argument
So the block number is increasing each time. I'm inclined to think
that this is the result of the scan passing over rows added by itself.
-Kevin
>>> Qingqing Zhou <zhouqq@cs.toronto.edu> >>>
> I reran the query. Same error, same relation, different block.
>
> dtr=> UPDATE
> dtr-> "DbTranRepository"
> dtr-> SET "userId" = UPPER("userId")
> dtr-> WHERE (
> dtr(> ("userId" <> UPPER("userId")) AND
> dtr(> ("timestampValue" BETWEEN '2005-10-28' AND '2005-11-15'));
> ERROR: could not read block 1118215 of relation 1663/16385/16483:
> Invalid argument
>
So this is a big query? Somebody reported this error when IO is
intensive.
To conclude the reports we have received:
(1) If IO is intensive (no matter you have anti-virus software or not),
you may encounter "invalud argument" error;
(2) In some cases, anti-virus software could cause "invalid argument"
error;
(3) You may encounter "permission denied" problem (no matter you have
anti-virus software or not) in some unknown condition;
Unfortunately we haven't found out the cause of this problem ... I am
thinking an urgent need is let PG print the GetLastError() for Windows
version as well.
Regards,
Qingqing
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-15 19:48 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 41+ messages in thread
From: Kevin Grittner @ 2005-11-15 19:48 UTC (permalink / raw)
To: zhouqq@cs.toronto.edu; Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: icub3d@gmail.com; pgsql-admin
Correction:
dtr=> select count(*) from "DbTranRepository"
dtr-> WHERE (
dtr(> ("userId" <> UPPER("userId")) AND
dtr(> ("timestampValue" BETWEEN '2005-10-28' AND '2005-11-15'));
count
--------
611255
(1 row)
I'm becoming more convinced that this happens as the UPDATE
runs into rows inserted by itself.
To respond to a concern expressed earllier -- all of our database
servers, including these two, use ECC memory.
-Kevin
>>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> >>>
The table has about 23.3 million rows, of which about 200,000 will
be affected by this update. Run time is about an hour. During the
first run, the table was the target of about 45,000 inserts. This rerun
was done as the only task. A third run (also by itself) gave this:
ERROR: could not read block 1482762 of relation 1663/16385/16483:
Invalid argument
So the block number is increasing each time. I'm inclined to think
that this is the result of the scan passing over rows added by itself.
-Kevin
>>> Qingqing Zhou <zhouqq@cs.toronto.edu> >>>
> I reran the query. Same error, same relation, different block.
>
> dtr=> UPDATE
> dtr-> "DbTranRepository"
> dtr-> SET "userId" = UPPER("userId")
> dtr-> WHERE (
> dtr(> ("userId" <> UPPER("userId")) AND
> dtr(> ("timestampValue" BETWEEN '2005-10-28' AND '2005-11-15'));
> ERROR: could not read block 1118215 of relation 1663/16385/16483:
> Invalid argument
>
So this is a big query? Somebody reported this error when IO is
intensive.
To conclude the reports we have received:
(1) If IO is intensive (no matter you have anti-virus software or not),
you may encounter "invalud argument" error;
(2) In some cases, anti-virus software could cause "invalid argument"
error;
(3) You may encounter "permission denied" problem (no matter you have
anti-virus software or not) in some unknown condition;
Unfortunately we haven't found out the cause of this problem ... I am
thinking an urgent need is let PG print the GetLastError() for Windows
version as well.
Regards,
Qingqing
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-15 21:28 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 41+ messages in thread
From: Kevin Grittner @ 2005-11-15 21:28 UTC (permalink / raw)
To: zhouqq@cs.toronto.edu; Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: icub3d@gmail.com; pgsql-admin
I got the error log working on Windows (with redirect_stderr). I had
to stop and restart postgres to do so. I ran the query (for the fourth
time), and it completed successfully.
I'm not inclined to believe that changing the redirect_stderr setting
would change this behavior, so I guess that either it is a
coincidence or the restart cleared some bad state within postgres.
Is there anything that anyone wants me to do at this point, to try
to pin down a cause, or do I drop it here?
-Kevin
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-15 23:36 Kevin Keith <kkeith@borderware.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 41+ messages in thread
From: Kevin Keith @ 2005-11-15 23:36 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
If I have followed the chain correctly, I saw that you were trying to
run an update statement on a large number of records in a large table
right? I have changed my strategy in the past for this type of problem.
I don't know if it would have fixed this problem or not, but I have seen
with Postgres and Oracle that updates like this on a large table may
take very long and cause contention processes.
Anyway, the method I normally use is to write a script that selects the
records into a cursor and updates one at a time by the primary key, and
then committing the transaction every so often (i.e. 500 - 1000
records). This way there will not be the overhead associated with an
extremely large transaction, and the script will complete much faster (I
have seen hours vs. minutes).
Hope this helps.
Kevin
Kevin Grittner wrote:
>I got the error log working on Windows (with redirect_stderr). I had
>to stop and restart postgres to do so. I ran the query (for the fourth
>time), and it completed successfully.
>
>I'm not inclined to believe that changing the redirect_stderr setting
>would change this behavior, so I guess that either it is a
>coincidence or the restart cleared some bad state within postgres.
>
>Is there anything that anyone wants me to do at this point, to try
>to pin down a cause, or do I drop it here?
>
>-Kevin
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-16 00:07 Qingqing Zhou <zhouqq@cs.toronto.edu>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 41+ messages in thread
From: Qingqing Zhou @ 2005-11-16 00:07 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: icub3d@gmail.com; mha@sollentuna.net; pgsql-admin
On Tue, 15 Nov 2005, Kevin Grittner wrote:
> I got the error log working on Windows (with redirect_stderr). I had
> to stop and restart postgres to do so. I ran the query (for the fourth
> time), and it completed successfully.
Strange - the phyiscal read for the 2nd, 3rd, 4th time should be the
same, if we intent to think of the IO presure.
>
> Is there anything that anyone wants me to do at this point, to try
> to pin down a cause, or do I drop it here?
>
Since you can reproduce the error with big chance, I think it is a good
chance to pin down a cause now. It would be useful to know the
GetLastError() instead of a tranlated errno. Can you patch the code like
this (patch smgrread()):
http://archives.postgresql.org/pgsql-bugs/2005-10/msg00050.php
Regards,
Qingqing
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-16 00:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 41+ messages in thread
From: Tom Lane @ 2005-11-16 00:25 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: zhouqq@cs.toronto.edu; icub3d@gmail.com; pgsql-admin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> ERROR: could not read block 1482762 of relation 1663/16385/16483:
> Invalid argument
> So the block number is increasing each time. I'm inclined to think
> that this is the result of the scan passing over rows added by itself.
It's just about impossible to believe that you would get an error at the
block I/O level due to a higher-level issue like "scan passing over rows
added by itself".
What I *could* believe, on the other hand, is that there's some problem
with reading blocks recently added to the file. As I said before, we
really really need to find out what the Windows-level error code is
--- "Invalid argument" isn't telling us anything useful here.
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-16 15:38 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 41+ messages in thread
From: Kevin Grittner @ 2005-11-16 15:38 UTC (permalink / raw)
To: zhouqq@cs.toronto.edu; +Cc: icub3d@gmail.com; pgsql-admin; mha@sollentuna.net
I will patch, build, and run similar updates to try to hit the problem.
Hopefully I can have something to post later today.
-Kevin
>>> Qingqing Zhou <zhouqq@cs.toronto.edu> >>>
On Tue, 15 Nov 2005, Kevin Grittner wrote:
>
> Is there anything that anyone wants me to do at this point, to try
> to pin down a cause, or do I drop it here?
>
Since you can reproduce the error with big chance, I think it is a good
chance to pin down a cause now. It would be useful to know the
GetLastError() instead of a tranlated errno. Can you patch the code like
this (patch smgrread()):
http://archives.postgresql.org/pgsql-bugs/2005-10/msg00050.php
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-16 15:44 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 41+ messages in thread
From: Kevin Grittner @ 2005-11-16 15:44 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: zhouqq@cs.toronto.edu; icub3d@gmail.com; pgsql-admin
Is there anything you would like me to include in my build for my
test runs, or any steps you would like me to take during the tests?
-Kevin
>>> Tom Lane <tgl@sss.pgh.pa.us> >>>
As I said before, we
really really need to find out what the Windows-level error code is
--- "Invalid argument" isn't telling us anything useful here.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-16 16:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 41+ messages in thread
From: Tom Lane @ 2005-11-16 16:08 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: zhouqq@cs.toronto.edu; icub3d@gmail.com; pgsql-admin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Is there anything you would like me to include in my build for my
> test runs, or any steps you would like me to take during the tests?
You might want to insert some debugging elog's into mdread() in md.c,
rather than in its caller smgrread. I'm concerned that by the time
control comes back to smgrread, the Windows last-error might have
been changed; in any case, we'd not know exactly which of the steps
in mdread failed. I'd suggest something like
if (FileSeek(v->mdfd_vfd, seekpos, SEEK_SET) != seekpos)
+ {
+ elog(LOG, "seek failed on relation %u/%u/%u: %d",
+ reln->smgr_rnode.spcNode,
+ reln->smgr_rnode.dbNode,
+ reln->smgr_rnode.relNode,
+ GetLastError());
return false;
+ }
status = true;
if ((nbytes = FileRead(v->mdfd_vfd, buffer, BLCKSZ)) != BLCKSZ)
{
+ elog(LOG, "read failed on relation %u/%u/%u: %d bytes, %d",
+ reln->smgr_rnode.spcNode,
+ reln->smgr_rnode.dbNode,
+ reln->smgr_rnode.relNode,
+ nbytes,
+ GetLastError());
/*
* If we are at or past EOF, return zeroes without complaining. Also
* substitute zeroes if we found a partial block at EOF.
(untested, but something like this should do it)
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-16 17:22 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 41+ messages in thread
From: Kevin Grittner @ 2005-11-16 17:22 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: zhouqq@cs.toronto.edu; icub3d@gmail.com; pgsql-admin
This code generates warnings on both Linux and Windows. My C
is too rusty to feel confident of what to do.
On Linux:
md.c:445: warning: implicit declaration of function `GetLastError'
On Windows:
md.c:445: warning: int format, DWORD arg (arg 6)
md.c:457: warning: int format, DWORD arg (arg 7)
How should I proceed?
-Kevin
>>> Tom Lane <tgl@sss.pgh.pa.us> >>>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Is there anything you would like me to include in my build for my
> test runs, or any steps you would like me to take during the tests?
You might want to insert some debugging elog's into mdread() in md.c,
rather than in its caller smgrread. I'm concerned that by the time
control comes back to smgrread, the Windows last-error might have
been changed; in any case, we'd not know exactly which of the steps
in mdread failed. I'd suggest something like
if (FileSeek(v->mdfd_vfd, seekpos, SEEK_SET) != seekpos)
+ {
+ elog(LOG, "seek failed on relation %u/%u/%u: %d",
+ reln->smgr_rnode.spcNode,
+ reln->smgr_rnode.dbNode,
+ reln->smgr_rnode.relNode,
+ GetLastError());
return false;
+ }
status = true;
if ((nbytes = FileRead(v->mdfd_vfd, buffer, BLCKSZ)) != BLCKSZ)
{
+ elog(LOG, "read failed on relation %u/%u/%u: %d bytes,
%d",
+ reln->smgr_rnode.spcNode,
+ reln->smgr_rnode.dbNode,
+ reln->smgr_rnode.relNode,
+ nbytes,
+ GetLastError());
/*
* If we are at or past EOF, return zeroes without
complaining. Also
* substitute zeroes if we found a partial block at EOF.
(untested, but something like this should do it)
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-16 17:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 41+ messages in thread
From: Tom Lane @ 2005-11-16 17:25 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: zhouqq@cs.toronto.edu; icub3d@gmail.com; pgsql-admin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> On Linux:
> md.c:445: warning: implicit declaration of function `GetLastError'
Of course. This is a Windows-only hack.
> On Windows:
> md.c:445: warning: int format, DWORD arg (arg 6)
> md.c:457: warning: int format, DWORD arg (arg 7)
I think this is just cosmetic, but try %ld instead of %d in the elog
format strings.
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-16 18:11 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 41+ messages in thread
From: Kevin Grittner @ 2005-11-16 18:11 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: zhouqq@cs.toronto.edu; icub3d@gmail.com; pgsql-admin
Ran with this change. Didn't take long to hit it.
Let me know if there's anything else I can do.
[2005-11-16 11:59:29.015 ] 4904 <dtr dtr 165.219.88.22(60649)> LOG:
read failed on relation 1663/16385/1494810: -1 bytes, 1450
[2005-11-16 11:59:29.015 ] 4904 <dtr dtr 165.219.88.22(60649)> ERROR:
could not read block 25447 of relation 1663/16385/1494810: Invalid
argument
>>> Tom Lane <tgl@sss.pgh.pa.us> >>>
I think this is just cosmetic, but try %ld instead of %d in the elog
format strings.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-16 18:25 Qingqing Zhou <zhouqq@cs.toronto.edu>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 41+ messages in thread
From: Qingqing Zhou @ 2005-11-16 18:25 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: tgl@sss.pgh.pa.us; icub3d@gmail.com; pgsql-admin
On Wed, 16 Nov 2005, Kevin Grittner wrote:
> Ran with this change. Didn't take long to hit it.
>
> [2005-11-16 11:59:29.015 ] 4904 <dtr dtr 165.219.88.22(60649)> LOG:
> read failed on relation 1663/16385/1494810: -1 bytes, 1450
> [2005-11-16 11:59:29.015 ] 4904 <dtr dtr 165.219.88.22(60649)> ERROR:
> could not read block 25447 of relation 1663/16385/1494810: Invalid
> argument
>
1450 ERROR_NO_SYSTEM_RESOURCES
Insufficient system resources exist to complete the requested service
SQL Server 7.0 experienced the same problem before:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274310
Some registration value tweak to solve it (for backup service):
http://support.microsoft.com/default.aspx?scid=kb;en-us;304101
I have to go out for a while ... Kevin, can you take a look at the 2nd
thread to see if it is possible to change some reg values to temporarily
solve the problem. A more robust solution will follow a retry style
following thread 1 I guess.
Cheers,
Qingqing
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-16 18:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Qingqing Zhou <zhouqq@cs.toronto.edu>
0 siblings, 0 replies; 41+ messages in thread
From: Tom Lane @ 2005-11-16 18:58 UTC (permalink / raw)
To: Qingqing Zhou <zhouqq@cs.toronto.edu>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; icub3d@gmail.com; pgsql-admin
Qingqing Zhou <zhouqq@cs.toronto.edu> writes:
> On Wed, 16 Nov 2005, Kevin Grittner wrote:
>> [2005-11-16 11:59:29.015 ] 4904 <dtr dtr 165.219.88.22(60649)> LOG:
>> read failed on relation 1663/16385/1494810: -1 bytes, 1450
> 1450 ERROR_NO_SYSTEM_RESOURCES
> Insufficient system resources exist to complete the requested service
Hmm ... seems like win32/error.c ought to be mapping that to something
more helpful than EINVAL. The link you mention suggests that we ought
to retry the read, so maybe mapping it to EINTR would be the most useful
thing. I seem to recall that we've heard about read() sometimes failing
with EINTR on Unix-with-NFS configurations ...
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-16 20:24 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 41+ messages in thread
From: Kevin Grittner @ 2005-11-16 20:24 UTC (permalink / raw)
To: zhouqq@cs.toronto.edu; +Cc: icub3d@gmail.com; pgsql-admin; tgl@sss.pgh.pa.us
Our DBAs reviewed the Microsoft documentation you referenced,
modified the registry, and rebooted the OS. We've been beating
up on the database without seeing the error so far. We'll keep at
it for a while.
-Kevin
>>> Qingqing Zhou <zhouqq@cs.toronto.edu> >>>
On Wed, 16 Nov 2005, Kevin Grittner wrote:
> Ran with this change. Didn't take long to hit it.
>
> [2005-11-16 11:59:29.015 ] 4904 <dtr dtr 165.219.88.22(60649)> LOG:
> read failed on relation 1663/16385/1494810: -1 bytes, 1450
> [2005-11-16 11:59:29.015 ] 4904 <dtr dtr 165.219.88.22(60649)> ERROR:
> could not read block 25447 of relation 1663/16385/1494810: Invalid
> argument
>
1450 ERROR_NO_SYSTEM_RESOURCES
Insufficient system resources exist to complete the requested service
SQL Server 7.0 experienced the same problem before:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274310
Some registration value tweak to solve it (for backup service):
http://support.microsoft.com/default.aspx?scid=kb;en-us;304101
I have to go out for a while ... Kevin, can you take a look at the 2nd
thread to see if it is possible to change some reg values to temporarily
solve the problem. A more robust solution will follow a retry style
following thread 1 I guess.
Cheers,
Qingqing
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2005-11-17 09:29 Magnus Hagander <mha@sollentuna.net>
0 siblings, 1 reply; 41+ messages in thread
From: Magnus Hagander @ 2005-11-17 09:29 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; zhouqq@cs.toronto.edu; +Cc: icub3d@gmail.com; pgsql-admin; tgl@sss.pgh.pa.us; pgsql-hackers@postgresql.org
[copying this one over to hackers]
> Our DBAs reviewed the Microsoft documentation you referenced,
> modified the registry, and rebooted the OS. We've been
> beating up on the database without seeing the error so far.
> We'll keep at it for a while.
Very interesting. As this seems to be a resource error, a couple of
questions. Sorry if you've already answered some of them, couldn't find
it in the archives.
1) Is this a dedicated pg server, or does it have something else on it?
2) We have to ask this - do you run any antivirus on it, that might nto
be releasing resources the right way? Anything else that might stick in
a kernel driver?
3) Are you hitting the database with many connections, or is this a
single/few connection scenario? Are the other connections typically
active when this shows up?
Seems like we could just retry when we get this failure. The question is
we need to do a small amount of sleep before we do? Also, we can't just
retry forever, there has to be some kind of end to it...
(If you read the SQL kb, it can be read as retrying is the correct
thing, because the bug in sql was that it didn't retry)
//Magnus
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [ADMIN] ERROR: could not read block
@ 2005-11-17 14:10 Qingqing Zhou <zhouqq@cs.toronto.edu>
parent: Magnus Hagander <mha@sollentuna.net>
0 siblings, 0 replies; 41+ messages in thread
From: Qingqing Zhou @ 2005-11-17 14:10 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
""Magnus Hagander"" <mha@sollentuna.net> wrote
>
> Seems like we could just retry when we get this failure. The question is
> we need to do a small amount of sleep before we do? Also, we can't just
> retry forever, there has to be some kind of end to it...
> (If you read the SQL kb, it can be read as retrying is the correct
> thing, because the bug in sql was that it didn't retry)
>
Agree on the retry solution. Yes, two important factors are: intervals,
times. I suspect if it is a dedicated server, serveral retry can handle it.
But for a server might running backup together, who knows how long we need.
But in either way, I don't think an endless loop is needed -- at most 3
minutes (since s_lock() does this :-)).
Also, this is a partial solution to the "invalid parameter" win32 IO
problem. There are some other cases like ACESS_VIOLATION error need more
evidence to pin down.
Regards,
Qingqing
P.s. Go to be out of town for several days ...
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [HACKERS] ERROR: could not read block
@ 2005-11-17 17:39 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 41+ messages in thread
From: Kevin Grittner @ 2005-11-17 17:39 UTC (permalink / raw)
To: zhouqq@cs.toronto.edu; mha@sollentuna.net; +Cc: icub3d@gmail.com; pgsql-admin; pgsql-hackers@postgresql.org; tgl@sss.pgh.pa.us
1) We run a couple Java applications on the same box to provide
middle tier access. When the box is heavily loaded, I think I've
seen about 80% PostgreSQL, 20% Java load.
2) I checked that no antivirus software was running, and had the
techs pare down the services running on that box to the absolute
minimum after the second failure, so that we could eliminate such
issues as possible causes.
3) The aforementioned Java apps hold open 21 database
connections. (One for a software publisher to query a list of jar
files for access to the database, and 20 for a connection pool in
the middle tier.) The way the pool is configured, six of those are
used for queries of normal priority, so we rarely have more than
six connections doing anything an any one moment. During the
initial failure, the middle tier was under normal load, so 45,000
inserts were made to the table in question during the ujpdate.
After we hit the problem, we removed that middle tier from the
list of targets, so it was running, but totally idle during the
remaining tests.
None of this seems material, however. It's pretty clear that the
problem was exhaustion of the Windows page pool. Our Windows
experts have reconfigured the machine (which had been tuned
for Sybase ASE). Their changes have boosted the page pool
from 20,000 entries to 180,000 entries. We're continuing to test
to ensure that the problem is not showing up with this
configuration; but, so far, it looks good.
If we don't want to tell Windows users to make highly technical
changes to the Windows registry in order to use PostgreSQL,
it does seem wise to use retries, as has already been discussed
on this thread.
-Kevin
>>> "Magnus Hagander" <mha@sollentuna.net> >>>
[copying this one over to hackers]
> Our DBAs reviewed the Microsoft documentation you referenced,
> modified the registry, and rebooted the OS. We've been
> beating up on the database without seeing the error so far.
> We'll keep at it for a while.
Very interesting. As this seems to be a resource error, a couple of
questions. Sorry if you've already answered some of them, couldn't find
it in the archives.
1) Is this a dedicated pg server, or does it have something else on it?
2) We have to ask this - do you run any antivirus on it, that might nto
be releasing resources the right way? Anything else that might stick in
a kernel driver?
3) Are you hitting the database with many connections, or is this a
single/few connection scenario? Are the other connections typically
active when this shows up?
Seems like we could just retry when we get this failure. The question is
we need to do a small amount of sleep before we do? Also, we can't just
retry forever, there has to be some kind of end to it...
(If you read the SQL kb, it can be read as retrying is the correct
thing, because the bug in sql was that it didn't retry)
//Magnus
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [HACKERS] ERROR: could not read block
@ 2005-11-17 17:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 41+ messages in thread
From: Tom Lane @ 2005-11-17 17:51 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: zhouqq@cs.toronto.edu; mha@sollentuna.net; icub3d@gmail.com; pgsql-admin; pgsql-hackers@postgresql.org
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> None of this seems material, however. It's pretty clear that the
> problem was exhaustion of the Windows page pool.
> ...
> If we don't want to tell Windows users to make highly technical
> changes to the Windows registry in order to use PostgreSQL,
> it does seem wise to use retries, as has already been discussed
> on this thread.
Would a simple retry loop actually help? It's not clear to me how
persistent such a failure would be.
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [HACKERS] ERROR: could not read block
@ 2005-11-17 18:01 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 41+ messages in thread
From: Kevin Grittner @ 2005-11-17 18:01 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: zhouqq@cs.toronto.edu; icub3d@gmail.com; pgsql-admin; pgsql-hackers@postgresql.org; mha@sollentuna.net
I'm not an expert on that, but it seems reasonable to me that the
page pool would free space as the I/O system caught up with
the load. Also, I'm going on what was said by Qingqing and
in one of the pages he referenced:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274310
-Kevin
>>> Tom Lane <tgl@sss.pgh.pa.us> >>>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> None of this seems material, however. It's pretty clear that the
> problem was exhaustion of the Windows page pool.
> ...
> If we don't want to tell Windows users to make highly technical
> changes to the Windows registry in order to use PostgreSQL,
> it does seem wise to use retries, as has already been discussed
> on this thread.
Would a simple retry loop actually help? It's not clear to me how
persistent such a failure would be.
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [HACKERS] ERROR: could not read block
@ 2005-11-17 18:56 Magnus Hagander <mha@sollentuna.net>
0 siblings, 2 replies; 41+ messages in thread
From: Magnus Hagander @ 2005-11-17 18:56 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; tgl@sss.pgh.pa.us; +Cc: zhouqq@cs.toronto.edu; icub3d@gmail.com; pgsql-admin; pgsql-hackers@postgresql.org
> >>> Tom Lane <tgl@sss.pgh.pa.us> >>>
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > None of this seems material, however. It's pretty clear that the
> > problem was exhaustion of the Windows page pool.
> > ...
> > If we don't want to tell Windows users to make highly technical
> > changes to the Windows registry in order to use PostgreSQL, it does
> > seem wise to use retries, as has already been discussed on this
> > thread.
>
> Would a simple retry loop actually help? It's not clear to
> me how persistent such a failure would be.
(Not sure why I didn't get Toms mail - lists acting up again? Anyway, I
got Kevins response, but am responding primarily to Tom)
The way I read it, a delay should help. It's basically running out of
kernel buffers, and we just delay, somebody else (another process, or an
IRQ handler, or whatever) should get finished with their I/O, free up
the buffer, and let us have it. Looking around a bit I see several
references that you should retry on it, but nothing in the API docs.
I do think it's probably a good idea to do a short delay before retrying
- at least to yield the CPU for one slice. That would greatly increase
the probability of someone else finishing their I/O...
That's how I read it, but I'm not 100% sure.
//Magnus
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [HACKERS] ERROR: could not read block
@ 2005-11-17 19:05 Magnus Hagander <mha@sollentuna.net>
0 siblings, 0 replies; 41+ messages in thread
From: Magnus Hagander @ 2005-11-17 19:05 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; zhouqq@cs.toronto.edu; +Cc: icub3d@gmail.com; pgsql-admin; pgsql-hackers@postgresql.org; tgl@sss.pgh.pa.us
> None of this seems material, however. It's pretty clear that
> the problem was exhaustion of the Windows page pool. Our
> Windows experts have reconfigured the machine (which had been
> tuned for Sybase ASE). Their changes have boosted the page
> pool from 20,000 entries to 180,000 entries. We're
> continuing to test to ensure that the problem is not showing
> up with this configuration; but, so far, it looks good.
Nope, with these numbers it doesn't. I was looking for a reason as to
why it would exhaust the pool - such as a huge number of connections.
Which doesn't appear to be so :-(
Another thing that will affect this is if you have a lot of network
sockets open. Anything like that?
BTW; do you get any eventid 2020 in your eventlog?
> If we don't want to tell Windows users to make highly
> technical changes to the Windows registry in order to use
> PostgreSQL, it does seem wise to use retries, as has already
> been discussed on this thread.
Yeah, I think it's at least worth a try at that.
//Magnus
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [HACKERS] ERROR: could not read block
@ 2005-11-17 19:54 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 41+ messages in thread
From: Kevin Grittner @ 2005-11-17 19:54 UTC (permalink / raw)
To: zhouqq@cs.toronto.edu; mha@sollentuna.net; +Cc: icub3d@gmail.com; pgsql-admin; pgsql-hackers@postgresql.org; tgl@sss.pgh.pa.us
There weren't a large number of connections -- it seemed to be
that the one big update query, by itself, would do this. It seemed
to get through a lot of rows before failing. This table is normally
"insert only" -- so it would likely be getting most or all of the space
for inserting the updated rows from extending the table. Also, the
only reasonable plan for this update would be a table scan, so
it is possible that the failure occurred some time after the scan got
to rows added by the update statement.
It appears that the techs cleared the eventlog when they
reconfigured the machine, so I can no longer check for events
from the failures. :-(
-Kevin
>>> "Magnus Hagander" <mha@sollentuna.net> >>>
> None of this seems material, however. It's pretty clear that
> the problem was exhaustion of the Windows page pool. Our
> Windows experts have reconfigured the machine (which had been
> tuned for Sybase ASE). Their changes have boosted the page
> pool from 20,000 entries to 180,000 entries. We're
> continuing to test to ensure that the problem is not showing
> up with this configuration; but, so far, it looks good.
Nope, with these numbers it doesn't. I was looking for a reason as to
why it would exhaust the pool - such as a huge number of connections.
Which doesn't appear to be so :-(
Another thing that will affect this is if you have a lot of network
sockets open. Anything like that?
BTW; do you get any eventid 2020 in your eventlog?
> If we don't want to tell Windows users to make highly
> technical changes to the Windows registry in order to use
> PostgreSQL, it does seem wise to use retries, as has already
> been discussed on this thread.
Yeah, I think it's at least worth a try at that.
//Magnus
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [HACKERS] ERROR: could not read block
@ 2005-11-17 20:49 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 41+ messages in thread
From: Kevin Grittner @ 2005-11-17 20:49 UTC (permalink / raw)
To: zhouqq@cs.toronto.edu; mha@sollentuna.net; Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: icub3d@gmail.com; pgsql-admin; pgsql-hackers@postgresql.org; tgl@sss.pgh.pa.us
A couple clarifications:
There were only a few network sockets open.
I'm told that the eventlog was reviewed for any events which
mgiht be related to the failures before it was cleared. They
found none, so that makes it fairly certain there was no 2020
event.
-Kevin
>>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> >>>
There weren't a large number of connections -- it seemed to be
that the one big update query, by itself, would do this. It seemed
to get through a lot of rows before failing. This table is normally
"insert only" -- so it would likely be getting most or all of the space
for inserting the updated rows from extending the table. Also, the
only reasonable plan for this update would be a table scan, so
it is possible that the failure occurred some time after the scan got
to rows added by the update statement.
It appears that the techs cleared the eventlog when they
reconfigured the machine, so I can no longer check for events
from the failures. :-(
-Kevin
>>> "Magnus Hagander" <mha@sollentuna.net> >>>
> None of this seems material, however. It's pretty clear that
> the problem was exhaustion of the Windows page pool. Our
> Windows experts have reconfigured the machine (which had been
> tuned for Sybase ASE). Their changes have boosted the page
> pool from 20,000 entries to 180,000 entries. We're
> continuing to test to ensure that the problem is not showing
> up with this configuration; but, so far, it looks good.
Nope, with these numbers it doesn't. I was looking for a reason as to
why it would exhaust the pool - such as a huge number of connections.
Which doesn't appear to be so :-(
Another thing that will affect this is if you have a lot of network
sockets open. Anything like that?
BTW; do you get any eventid 2020 in your eventlog?
> If we don't want to tell Windows users to make highly
> technical changes to the Windows registry in order to use
> PostgreSQL, it does seem wise to use retries, as has already
> been discussed on this thread.
Yeah, I think it's at least worth a try at that.
//Magnus
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [ADMIN] ERROR: could not read block
@ 2005-11-21 21:07 Qingqing Zhou <zhouqq@cs.toronto.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 41+ messages in thread
From: Qingqing Zhou @ 2005-11-21 21:07 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
"Tom Lane" <tgl@sss.pgh.pa.us> wrote
>
> Would a simple retry loop actually help? It's not clear to me how
> persistent such a failure would be.
>
[with reply to all followup threads] Yeah, this is the key and we definitely
have no 100% guarantee that several retries will solve the problem - just as
the situation in pg_unlink/pg_rename. But shall we do something now? If
Kevin could help on testing(you may have to revert the registry changes :-()
, I would like to send a patch in the retry style.
Regards,
Qingqing
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [HACKERS] ERROR: could not read block
@ 2005-11-21 23:05 Jim C. Nasby <jnasby@pervasive.com>
parent: Magnus Hagander <mha@sollentuna.net>
1 sibling, 0 replies; 41+ messages in thread
From: Jim C. Nasby @ 2005-11-21 23:05 UTC (permalink / raw)
To: Magnus Hagander <mha@sollentuna.net>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; tgl@sss.pgh.pa.us; zhouqq@cs.toronto.edu; icub3d@gmail.com; pgsql-admin; pgsql-hackers@postgresql.org
On Thu, Nov 17, 2005 at 07:56:21PM +0100, Magnus Hagander wrote:
> The way I read it, a delay should help. It's basically running out of
> kernel buffers, and we just delay, somebody else (another process, or an
> IRQ handler, or whatever) should get finished with their I/O, free up
> the buffer, and let us have it. Looking around a bit I see several
> references that you should retry on it, but nothing in the API docs.
> I do think it's probably a good idea to do a short delay before retrying
> - at least to yield the CPU for one slice. That would greatly increase
> the probability of someone else finishing their I/O...
If that makes it into code, ISTM it would be good if it also threw a
NOTICE so that users could see if this was happening; kinda like the
notice about log files being recycled frequently.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [ADMIN] ERROR: could not read block
@ 2005-11-21 23:24 Qingqing Zhou <zhouqq@cs.toronto.edu>
parent: Magnus Hagander <mha@sollentuna.net>
1 sibling, 0 replies; 41+ messages in thread
From: Qingqing Zhou @ 2005-11-21 23:24 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
""Magnus Hagander"" <mha@sollentuna.net> wrote
>
> The way I read it, a delay should help. It's basically running out of
> kernel buffers, and we just delay, somebody else (another process, or an
> IRQ handler, or whatever) should get finished with their I/O, free up
> the buffer, and let us have it. Looking around a bit I see several
> references that you should retry on it, but nothing in the API docs.
> I do think it's probably a good idea to do a short delay before retrying
> - at least to yield the CPU for one slice. That would greatly increase
> the probability of someone else finishing their I/O...
>
More I read on the second thread:
" NTBackupread and NTBackupwrite both use buffered I/O. This means that
Windows NT caches the I/O that is performed against the stream. It is also
the only API that will back up the metadata of a file. This cache is pulled
from limited resources: namely, pool and nonpaged pool. Because of this,
extremely large numbers of files or files that are very large may cause the
pool resources to run low. "
So does it imply that if we use unbuffered I/O in Windows system will
elminate this problem? If so, just add FILE_FLAG_NO_BUFFERING when we open
data file will solve the problem -- but this change in fact very invasive,
because it will make the strategy of server I/O optimization totally
different from *nix.
Regards,
Qingqing
^ permalink raw reply [nested|flat] 41+ messages in thread
* ERROR: could not read block
@ 2011-05-21 12:39 Diego Fernández Slezak <dfslezak@dc.uba.ar>
0 siblings, 1 reply; 41+ messages in thread
From: Diego Fernández Slezak @ 2011-05-21 12:39 UTC (permalink / raw)
To: pgsql-admin
Hello everybody,
I had a hard drive failure last week. After lots of effort I've been able to
backup a 700GB database, with only one file with corruption.
When I do some big queries, it throws me errors on this faulty file:
could not read block 390041 of relation 1663/350994/351212: read only 0 of
8192 bytes
I would like to know if there is any way to say to postgres to drop all rows
that corresponds to that file. In that way I would save most of my data.
Thanks in advance
Diego
^ permalink raw reply [nested|flat] 41+ messages in thread
* ERROR: could not read block
@ 2011-05-21 12:41 Diego Fernández Slezak <dfslezak@gmail.com>
parent: Diego Fernández Slezak <dfslezak@dc.uba.ar>
0 siblings, 1 reply; 41+ messages in thread
From: Diego Fernández Slezak @ 2011-05-21 12:41 UTC (permalink / raw)
To: pgsql-admin
Hello everybody,
I had a hard drive failure last week. After lots of effort I've been able to
backup a 700GB database, with only one file with corruption.
When I do some big queries, it throws me errors on this faulty file:
could not read block 390041 of relation 1663/350994/351212: read only 0 of
8192 bytes
I would like to know if there is any way to say to postgres to drop all rows
that corresponds to that file. In that way I would save most of my data.
Thanks in advance
Diego
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2011-05-21 15:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Diego Fernández Slezak <dfslezak@gmail.com>
0 siblings, 1 reply; 41+ messages in thread
From: Tom Lane @ 2011-05-21 15:16 UTC (permalink / raw)
To: Diego Fernández Slezak <dfslezak@gmail.com>; +Cc: pgsql-admin
=?ISO-8859-1?Q?Diego_Fern=E1ndez_Slezak?= <dfslezak@gmail.com> writes:
> Hello everybody,
> I had a hard drive failure last week. After lots of effort I've been able to
> backup a 700GB database, with only one file with corruption.
> When I do some big queries, it throws me errors on this faulty file:
> could not read block 390041 of relation 1663/350994/351212: read only 0 of
> 8192 bytes
I'm guessing this is the result of an index entry pointing to a block
that's not there --- either the index is corrupted, or you lost some
data. In any case, reindexing the table ought to get rid of this error.
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: ERROR: could not read block
@ 2011-05-22 22:24 Diego Fernández Slezak <dfslezak@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 41+ messages in thread
From: Diego Fernández Slezak @ 2011-05-22 22:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thanks Tom.
I executed a REINDEX DATABASE and received the error:
.
.
.
NOTICE: table "pg_enum" was reindexed
NOTICE: table "pg_namespace" was reindexed
NOTICE: table "pg_conversion" was reindexed
NOTICE: table "pg_depend" was reindexed
NOTICE: table "users" was reindexed
NOTICE: table "results" was reindexed
NOTICE: table "games" was reindexed
NOTICE: table "game_status" was reindexed
ERROR: could not extend relation 1663/350994/448678: wrote only 4096 of
8192 bytes at block 44030
HINT: Check free disk space.
When I check free space, I have more than 100GB free:
$ df -h
/dev/sdc1 917G 742G 130G 86% /home
Also, when I check what does this file correspond to I get a empty answer:
/usr/lib/postgresql/8.3/bin/oid2name -d mydb -f 448678
From database "mydb":
Filenode Table Name
----------------------
Thanks in advance
Diego
On Sat, May 21, 2011 at 12:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =?ISO-8859-1?Q?Diego_Fern=E1ndez_Slezak?= <dfslezak@gmail.com> writes:
> > Hello everybody,
> > I had a hard drive failure last week. After lots of effort I've been able
> to
> > backup a 700GB database, with only one file with corruption.
>
> > When I do some big queries, it throws me errors on this faulty file:
> > could not read block 390041 of relation 1663/350994/351212: read only 0
> of
> > 8192 bytes
>
> I'm guessing this is the result of an index entry pointing to a block
> that's not there --- either the index is corrupted, or you lost some
> data. In any case, reindexing the table ought to get rid of this error.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 41+ messages in thread
end of thread, other threads:[~2011-05-22 22:24 UTC | newest]
Thread overview: 41+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-14 23:20 ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-14 23:43 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-11-15 00:02 ` Re: ERROR: could not read block Joshua Marsh <icub3d@gmail.com>
2005-11-15 00:43 ` Re: ERROR: could not read block Tom Lane <tgl@sss.pgh.pa.us>
2005-11-15 00:19 Re: ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-15 13:25 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-11-15 15:12 Re: ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-15 16:29 Re: ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-15 16:42 ` Re: ERROR: could not read block Joshua Marsh <icub3d@gmail.com>
2005-11-15 19:04 ` Re: ERROR: could not read block Qingqing Zhou <zhouqq@cs.toronto.edu>
2005-11-15 19:14 Re: ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-16 00:25 ` Re: ERROR: could not read block Tom Lane <tgl@sss.pgh.pa.us>
2005-11-15 19:48 Re: ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-15 21:28 Re: ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-15 23:36 ` Re: ERROR: could not read block Kevin Keith <kkeith@borderware.com>
2005-11-16 00:07 ` Re: ERROR: could not read block Qingqing Zhou <zhouqq@cs.toronto.edu>
2005-11-16 15:38 Re: ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-16 15:44 Re: ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-16 16:08 ` Re: ERROR: could not read block Tom Lane <tgl@sss.pgh.pa.us>
2005-11-16 17:22 Re: ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-16 17:25 ` Re: ERROR: could not read block Tom Lane <tgl@sss.pgh.pa.us>
2005-11-16 18:11 Re: ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-16 18:25 ` Re: ERROR: could not read block Qingqing Zhou <zhouqq@cs.toronto.edu>
2005-11-16 18:58 ` Re: ERROR: could not read block Tom Lane <tgl@sss.pgh.pa.us>
2005-11-16 20:24 Re: ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-17 09:29 Re: ERROR: could not read block Magnus Hagander <mha@sollentuna.net>
2005-11-17 14:10 ` Re: [ADMIN] ERROR: could not read block Qingqing Zhou <zhouqq@cs.toronto.edu>
2005-11-17 17:39 Re: [HACKERS] ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-17 17:51 ` Re: [HACKERS] ERROR: could not read block Tom Lane <tgl@sss.pgh.pa.us>
2005-11-21 21:07 ` Re: [ADMIN] ERROR: could not read block Qingqing Zhou <zhouqq@cs.toronto.edu>
2005-11-17 18:01 Re: [HACKERS] ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-17 18:56 Re: [HACKERS] ERROR: could not read block Magnus Hagander <mha@sollentuna.net>
2005-11-21 23:05 ` Re: [HACKERS] ERROR: could not read block Jim C. Nasby <jnasby@pervasive.com>
2005-11-21 23:24 ` Re: [ADMIN] ERROR: could not read block Qingqing Zhou <zhouqq@cs.toronto.edu>
2005-11-17 19:05 Re: [HACKERS] ERROR: could not read block Magnus Hagander <mha@sollentuna.net>
2005-11-17 19:54 Re: [HACKERS] ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-11-17 20:49 Re: [HACKERS] ERROR: could not read block Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-05-21 12:39 ERROR: could not read block Diego Fernández Slezak <dfslezak@dc.uba.ar>
2011-05-21 12:41 ` ERROR: could not read block Diego Fernández Slezak <dfslezak@gmail.com>
2011-05-21 15:16 ` Re: ERROR: could not read block Tom Lane <tgl@sss.pgh.pa.us>
2011-05-22 22:24 ` Re: ERROR: could not read block Diego Fernández Slezak <dfslezak@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox