Re: Index Corruption

Hi. Do you have triggers on corrupted tables?

2011/9/12, Dylan Adams <dylan(dot)adams(dot)work(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
------------
pasman