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

Try doing it without replication, check the time.

I think your problem has nothing to do with replication. It is simply
because you have a huge one-shot transactions. Each time you run
something in transaction, db needs to perform the sql in a rollback-able
segment instead of in a permanent storage. It means that you are eating
virtual memory like nuts...
After a while page swap has to be done too frequently that your
performance drops.

Do you really have to run those 320 000 inserts in a transaction?




On Mon, 2003-03-31 at 06:33, Hervé Piedvache wrote:
> 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é
> _______________________________________________
> Pgreplication-general mailing list
> Pgreplication-general@gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/pgreplication-general