Re: [Pgreplication-general] DBMIRROR and INSERT transactions lose

On Mon, 31 Mar 2003, [iso-8859-15] Hervé Piedvache wrote:

> Hi Ezra,
>

> hum ... I look to my checker for yesterday ... no memory trouble I only lose
> 5% of memory I have 2Gb of RAM ... swap was at 100% free during all this time
> ... (9h of treatment)

Hmmm.

>
> Why using transaction ? ... To have a secured activity on my database ;*)
>
> > Do you really have to run those 320 000 inserts in a transaction?

DBMirror does 2 additional inserts for every insert that your program
does. So you are really talking about closer to a million inserts on the
database. But you still wouldn't expect 7 minutes vs 9 hours. The two
tables DBMirror does inserts onto (Pending andPendingData) also have
indexes on them.

You could try removing the DBMirror indexes on Pending and PendingData
Pending_XID_Index,Pending_pkey and PendingData_pkey

How does that change things?

>
> What other idea do you have ? In mean secured system please ... I remind you
> that during this time ... other actions, like users, automate etc. are using
> my database ... and once a month I have to insert those data ... so how to do
> that securly without transaction ? ...

You might want to consider turning mirroring off,(Disabling the
recordchange trigger) Do your monthly update and then do a pg_dump and
restore to your slaves. This might work depending on your circumstances.
Mirroring 320,000 records to a slave will take some time as well.

--
Steven Singer ssinger(at)navtechinc(dot)com
Dispatch Systems Phone: 519-747-1170 ext 282
Navtech Systems Support Inc. AFTN: CYYZXNSX SITA: YYZNSCR
Waterloo, Ontario ARINC: YKFNSCR