Re: Index Issues & ReIndex
Greetings,
We're running a system with tables that range from 300,000 to 60 Million records. Our indexes seem to be having issues. Let me please describe the symptoms.
1. Big Int Indexes point to the wrong result
2. Duplicate quries run minutes apart return different result sets but then stablize to one result.
3. Vacuum verbose shows nothing and fixes nothing (haven't done vacuum full cause the tables can be huge around 60 Million records)
4. Analyze fixes nothing
5. REINDEX fixes the issue sometimes and removes the specific result we were looking for others. IE: Makes the issue go away OR deletes the row.
6. REINDEX fix lasts until we insert more rows at which time the error will show up again.
7. ONLY Indexes are affected.
8. Running version Postgres 7.4.3, 300 gigs of storage space outstanding
Thanks for any input on what you think the issue may be,
Andrew
Andrew Sukow <creoe@shaw.ca> writes:
1. Big Int Indexes point to the wrong result
2. Duplicate quries run minutes apart return different result sets but
then stablize to one result.
You sure you don't have hardware problems? Generally, when someone
shows up reporting bizarre instability, it ends up being broken
hardware. This could be a software issue, but it doesn't smell like it.
7. ONLY Indexes are affected.
Define what you mean by that. How do you know it's only the indexes?
8. Running version Postgres 7.4.3, 300 gigs of storage space outstanding
What's the platform? What did you compile Postgres with?
regards, tom lane
Tom,
We have tested our ram, and our previous build has the exact same hardware which is stable but this does not rule out hardware. I lean towards software to the fact that we are only finding the instability in the indexes. The four million rows we write per day are perfectly fine, but just cause huge 6 gig processes randomly from the index being broken down. An hour long reindex usually fixes it until we insert more items.
We do get resulting errors like "root page level 1 expected 3" some times on the index as well. Reindex fixes it.
We're running Gento which was just compiled 1 month ago up to date with a just brand newly compiled C compiler.
Thanks,
Andrew
----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Friday, August 13, 2004 4:57 pm
Subject: Re: [GENERAL] Index Issues & ReIndex
Show quoted text
Andrew Sukow <creoe@shaw.ca> writes:
1. Big Int Indexes point to the wrong result
2. Duplicate quries run minutes apart return different resultsets but
then stablize to one result.
You sure you don't have hardware problems? Generally, when someone
shows up reporting bizarre instability, it ends up being broken
hardware. This could be a software issue, but it doesn't smell
like it.7. ONLY Indexes are affected.
Define what you mean by that. How do you know it's only the indexes?
8. Running version Postgres 7.4.3, 300 gigs of storage space
outstanding
What's the platform? What did you compile Postgres with?regards, tom lane
---------------------------(end of broadcast)----------------------
-----
TIP 8: explain analyze is your friend
Import Notes
Resolved by subject fallback