Re: Database possible corruption , unsolvable mystery

From: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>
To: Eric Lauzon <eric(dot)lauzon(at)abovesecurity(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Database possible corruption , unsolvable mystery
Date: 2006-03-29 22:16:51
Message-ID: 1143670611.5634.11.camel@archimedes
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Can you post an explain analyze for the delete query? That will at
least tell you if it is the delete itself which is slow, or a trigger /
referential integrity constraint check. Which version of PG is this?

-- Mark Lewis

On Wed, 2006-03-29 at 12:58 -0500, Eric Lauzon wrote:
> Greetings,
>
> We have an issue where we have a database with many tables.
> The layout of the database is 3 set of look alike tables with different names.
> Each set of tables has some referential integrety that point back to the main
> control table.
>
> On two set of tables we are able to efficiently delete referential and main record
> without a problems, but on the 3rd set we have an issue where the control table is clugged
> and delete seem to take forever , as example on the first two set a delete of 60K record take about
> 4 second to 10 second but on the 3rd set it can take as long as 3hours.
>
> This seem to be only affecting one database , the schema and way of doing is replicated elsewhere
> and if efficient.
>
> The question is, even after droping 3rd set integrity , dumping the table data , deleting the table,
> doing a manual checkpoint and recreating the table with the dump data , with or without referential
> integrity , the problems still araise.
>
> If we copy the data from the live table and do a create table aaa as select * from problematic_table;
> the table aaa operations are normal and efficient.
>
> This is why our investigation brought us to the folowing questions:
>
> 1. Are postgresql data file name are hashed references to table name(as oracle)? [~path to data EX:/var/log/pgsql/data/[arbitraty numbers]/[datafile]]?
>
> 2. If the data files are corrupted and we re-create is it possible it uses the same files thus creating the same issue?
>
> 3. Since we know that all the tables has that problems is there an internal table with undisclosed references to tables data files?
>
> I hope the questions were clear.
>
> Have a good day, and thank you in advance.
>
>
> Eric Lauzon
> [Recherche & Développement]
> Above Sécurité / Above Security
> Tél : (450) 430-8166
> Fax : (450) 430-1858
>
> AVERTISSEMENT CONCERNANT LA CONFIDENTIALITÉ
>
> Le présent message est à l'usage exclusif du ou des destinataires mentionnés ci-dessus. Son contenu est confidentiel et peut être assujetti au secret professionnel. Si vous avez reçu le présent message par erreur, veuillez nous en aviser immédiatement et le détruire en vous abstenant d'en faire une copie, d'en divulguer le contenu ou d'y donner suite.
>
> CONFIDENTIALITY NOTICE
>
> This communication is intended for the exclusive use of the addressee identified above. Its content is confidential and may contain privileged information. If you have received this communication by error, please notify the sender and delete the message without copying or disclosing it.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Eric Lauzon 2006-03-29 22:31:17 Re: Database possible corruption , unsolvable mystery
Previous Message Richard Huxton 2006-03-29 22:09:52 Re: Database possible corruption , unsolvable mystery