Re: [Pgreplication-general] DBMIRROR and INSERT transactions lose time ... - Mailing list pgsql-general

Hi Michael,

Le Lundi 31 Mars 2003 12:22, Michael Loftis a écrit :
> Couple of things here.  If I read you correctly after you COMMIT the first
> part of the transaction goes quickly, but begins to slow down?

No It's inside my transaction ... I'm not arrived to the COMMIT point ... only
INSERT command ... and it's going slow ... just doing INSERT ... and some
UPDATE, about 2000 updates are done in the time of the 320 000 inserts in the
same transaction.
I mean ... I do :
Begin;
INSERT (xxx);
INSERT (xxx);
....
UPDATE (xxx);
...
INSERT (xxx);
INSERT (xxx);
...
... x 320 000
Then I COMMIT ...

In my Perl script ... I do a FOR { ... } where are my INSERT, and I just print
each 100 loop the time passed and the current value of my loop to know where
I am ... so without DBMirror each 100 I have 0.3 seconds ... never move to
this value ... with DBMirror ... I have 0.3 for the first 1000 then I lose 1
second each 3000 ... so I get 2 seconds ... then 3 seconds etc ... and after
9 hours .. I was up to 45 seconds to passed the 100 INSERT of my loop ...
Without DBMirror (only the trigger dropped) it take about 15 min to do the
transaction fully ...

> HAve you considered removing any indexes you have on the tables prior to
> doing such a large insert and creating htem afterwards?  It's much cheaper
> like that.

I have only one index, my primary key index on a Serial ... I can't delete it
... because during this script other programs can access to the table ... for
reading ... and as I told you previusly without the DBMirror trigger it's
running perfectly ...

> Does this happen if you don't run DBMirror (IE local only) copy?  I've
> never used such large transactions myself before.

It's running perfectly if I drop the DBMirror trigger I have a constant flow
of 0.3 seconds for 100 INSERT command.

Thanks per advance for your help ... or ideas ;o)

Regards,

> --On Monday, March 31, 2003 9:46 AM +0100 Hervé Piedvache <herve@elma.fr>
>
> wrote:
> > Hi,
> >
> > Who can give us some help with DBMirror ??
> >
> > We make some test with DBMirror ... for us it's running perfectly ;o)
> >
> > Only one big trouble ... inserting data in transaction ...
> > We try to make one transaction with 320 000 inserts ... if the trigger of
> > DBMirror is not connected we have 100 inserts done in 0.3 sec, with
> > DBMirror  it start quickly but after 1000 insert we lose and lose many
> > time ... 1  second losed by 3000/4000 insert ... after 9 hours we get 45
> > sec for 100  insert ... :o(
> >
> > Any idea ? Update ? Patch ? ...
> >
> > Thanks per advance for your help ... ! :o)
> >
> > Regards,
> > --
> > Hervé
> > _______________________________________________
> > Pgreplication-general mailing list
> > Pgreplication-general@gborg.postgresql.org
> > http://gborg.postgresql.org/mailman/listinfo/pgreplication-general

--
Hervé