Thread: Index Corruption
We moved to PostgreSQL about 2 years ago and have been very happy with it overall. The only major issue that we've had is intermittent index corruption. This manifests itself as either "duplicate key value violates unique constraint" or "could not read block 37422 of relation 1663/18663/19063: read only 0 of 8192 bytes." REINDEXing the table solves the problem. We do sometimes have bugs that cause unique index violations, so the first error is especially annoying. We've encountered the problem with both JDBC and libpq clients. The problem has persisted through upgrades to our database servers (from 32bit CentOS 5.3 with PostgreSQL 8.3.9 to 64bit CentOS 5.6 with PostgreSQL 8.4.8, all with stock kernels). Our database servers are fully virtualized, running under VMware Server on Dell PowerEdge Servers. We use battery backed raid controllers (PERC4/5/6), configured for RAID 10. We also experienced the problem when we had physical database servers. Servers all have 2 CPUs. PostgreSQL is installed from the PGDG RPMs (yum.postgresql.org). Our only non-default postgresql.conf options are the typical tunables: shared_buffers, checkpoint_segments, effective_cache_size, default_statistics_target, etc. WAL settings are defaults (save checkpoint_segments). No clustering, no addons. We've tuned autovacuum to be more aggressive in an attempt to address some bloat issues; this didn't seem to have any impact on the frequency of index corruption. The databases are unloaded nightly. We do a weekly pg_dumpall as an integrity check. We've only seen the problem in our many weekly batch processes. Typically, these processes DELETE a large subset of data from a table and then repopulate with the same values in indexed fields. Depending on the dataset, they run from 30 minutes to 8 hours. These processes are scheduled so that only one program (which is single threaded) is updating any given table at a time. We have 12 database servers and we usually have about 1 incident per week. Sometimes we'll go for weeks without any occurrences, and then we'll have a flurry of them. My primary question: is this normal? There isn't an overwhelming amount of messages in the archives about index corruption, which leads me to think that there's something with our configuration or our processes that is making us more susceptible. Is there something we should be doing to make index corruption less likely? Is there anyway to do an index integrity check so we can be more proactive with REINDEXing? Thanks, dylan
Dylan Adams <dylan.adams.work@gmail.com> writes:
> [ persistent occurrences of index corruption ]
> My primary question: is this normal?
No. It does sound like you're managing to tickle some bug or other.
Can you extract a test case of any kind? We could fix it if we could
see it happening, but there's not enough information here for that.
regards, tom lane
Hi. Do you have triggers on corrupted tables? 2011/9/12, Dylan Adams <dylan.adams.work@gmail.com>: > We moved to PostgreSQL about 2 years ago and have been very happy with it > overall. The only major issue that we've had is intermittent index > corruption. > This manifests itself as either "duplicate key value violates unique > constraint" > or "could not read block 37422 of relation 1663/18663/19063: read only 0 of > 8192 > bytes." REINDEXing the table solves the problem. We do sometimes have bugs > that > cause unique index violations, so the first error is especially annoying. > > We've encountered the problem with both JDBC and libpq clients. The problem > has > persisted through upgrades to our database servers (from 32bit CentOS 5.3 > with > PostgreSQL 8.3.9 to 64bit CentOS 5.6 with PostgreSQL 8.4.8, all with stock > kernels). > > Our database servers are fully virtualized, running under VMware Server on > Dell > PowerEdge Servers. We use battery backed raid controllers (PERC4/5/6), > configured for RAID 10. We also experienced the problem when we had physical > database servers. Servers all have 2 CPUs. > > PostgreSQL is installed from the PGDG RPMs (yum.postgresql.org). Our only > non-default postgresql.conf options are the typical tunables: > shared_buffers, > checkpoint_segments, effective_cache_size, default_statistics_target, etc. > WAL > settings are defaults (save checkpoint_segments). No clustering, no addons. > We've tuned autovacuum to be more aggressive in an attempt to address some > bloat > issues; this didn't seem to have any impact on the frequency of index > corruption. > > The databases are unloaded nightly. We do a weekly pg_dumpall as an > integrity > check. > > We've only seen the problem in our many weekly batch processes. Typically, > these > processes DELETE a large subset of data from a table and then repopulate > with > the same values in indexed fields. Depending on the dataset, they run from > 30 > minutes to 8 hours. These processes are scheduled so that only one program > (which is single threaded) is updating any given table at a time. > > We have 12 database servers and we usually have about 1 incident per week. > Sometimes we'll go for weeks without any occurrences, and then we'll have a > flurry of them. > > My primary question: is this normal? There isn't an overwhelming amount of > messages in the archives about index corruption, which leads me to think > that > there's something with our configuration or our processes that is making us > more > susceptible. > > Is there something we should be doing to make index corruption less likely? > Is > there anyway to do an index integrity check so we can be more proactive with > REINDEXing? > > Thanks, > dylan > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- ------------ pasman
On Mon, Sep 12, 2011 at 9:41 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dylan Adams <dylan.adams.work@gmail.com> writes: >> [ persistent occurrences of index corruption ] > >> My primary question: is this normal? > > No. It does sound like you're managing to tickle some bug or other. > Can you extract a test case of any kind? We could fix it if we could > see it happening, but there's not enough information here for that. I haven't been able to come up with a self contained test case. There have been a few instances where a particular series of batch processes which, when run repeatedly on a particular data set, will reproduce the problem consistently. But it's not possible to release the required code and data. dylan
2011/9/12 pasman pasmański <pasman.p@gmail.com>: > Hi. Do you have triggers on corrupted tables? I haven't checked all the occurences, but at least some of the tables that have had corrupted index have no triggers. dylan
On 9/12/2011 1:10 PM, Dylan Adams wrote: > On Mon, Sep 12, 2011 at 9:41 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote: >> Dylan Adams<dylan.adams.work@gmail.com> writes: >>> [ persistent occurrences of index corruption ] >> >>> My primary question: is this normal? >> >> No. It does sound like you're managing to tickle some bug or other. >> Can you extract a test case of any kind? We could fix it if we could >> see it happening, but there's not enough information here for that. > > I haven't been able to come up with a self contained test case. > > There have been a few instances where a particular series of batch > processes which, when run repeatedly on a particular data set, will > reproduce the problem consistently. But it's not possible to release > the required code and data. > > dylan > How about some specifics about the process? Maybe I can work up a look-a-like. Something like: we have two clients that insert as fast as possible into this temp table: create table....; we have 5 clients select/insert/delete from temp into live table that looks like : create table....; I'll post my scripts and you can yea/nea them until we get close, maybe find the problem along the way. I would not need data or code, but actual table structure's sure would be swell. -Andy
On Mon, Sep 12, 2011 at 1:20 PM, Andy Colson <andy@squeakycode.net> wrote: > On 9/12/2011 1:10 PM, Dylan Adams wrote: >> >> On Mon, Sep 12, 2011 at 9:41 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote: >>> >>> Dylan Adams<dylan.adams.work@gmail.com> writes: >>>> >>>> [ persistent occurrences of index corruption ] >>> >>>> My primary question: is this normal? >>> >>> No. It does sound like you're managing to tickle some bug or other. >>> Can you extract a test case of any kind? We could fix it if we could >>> see it happening, but there's not enough information here for that. >> >> I haven't been able to come up with a self contained test case. >> >> There have been a few instances where a particular series of batch >> processes which, when run repeatedly on a particular data set, will >> reproduce the problem consistently. But it's not possible to release >> the required code and data. >> >> dylan >> > > How about some specifics about the process? Maybe I can work up a > look-a-like. I sincerely appreciate your offer, but I don't think it will help. I've tried to replicate what these are processes are doing as a standalone program and haven't been able to replicate the problem. Maybe there's something about the order or timing of the operations that I'm not capturing. I'll take another look at my test program and see if I can get it to trigger the problem. dylan
On Mon, Sep 12, 2011 at 8:03 AM, Dylan Adams <dylan.adams.work@gmail.com> wrote: > We moved to PostgreSQL about 2 years ago and have been very happy with it > overall. The only major issue that we've had is intermittent index corruption. > This manifests itself as either "duplicate key value violates unique constraint" > or "could not read block 37422 of relation 1663/18663/19063: read only 0 of 8192 > bytes." REINDEXing the table solves the problem. We do sometimes have bugs that > cause unique index violations, so the first error is especially annoying. > > We've encountered the problem with both JDBC and libpq clients. The problem has > persisted through upgrades to our database servers (from 32bit CentOS 5.3 with > PostgreSQL 8.3.9 to 64bit CentOS 5.6 with PostgreSQL 8.4.8, all with stock > kernels). > > Our database servers are fully virtualized, running under VMware Server on Dell > PowerEdge Servers. We use battery backed raid controllers (PERC4/5/6), > configured for RAID 10. We also experienced the problem when we had physical > database servers. Servers all have 2 CPUs. Are you sure you aren't having either server or RAID problems of some kind? Single bit memory errors or bad sectors not getting remapped before corrupting data etc? Have you torture tested your hardware to ensure it's rock solid stable? Dell's insistence on using non buffered memory has cause me untold problems with single bit errors in the past. I don't know if they still use unbuffered memory in their servers or not as I gave up on Dell three or four years ago for servers and support.
On Mon, Sep 12, 2011 at 3:51 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > Are you sure you aren't having either server or RAID problems of some > kind? Single bit memory errors or bad sectors not getting remapped > before corrupting data etc? Have you torture tested your hardware to > ensure it's rock solid stable? Dell's insistence on using non > buffered memory has cause me untold problems with single bit errors in > the past. I don't know if they still use unbuffered memory in their > servers or not as I gave up on Dell three or four years ago for > servers and support. Current servers (R710s) use DDR3 - Synchronous Registered (Buffered) ECC memory. We don't think there's anything wrong with the hardware, as the problem has persisted across various physical servers (the R710s replaced 2950s). We also don't have any other intermittent issues (postgres crashes, application server crashes, etc) that would indicate random bit errors. Thanks, dylan