Enterprise Replication initial load performance
A user on Informix 11.70 on AIX found 'cdr sync' far too slow for the initial load of a 390-million-row replicated table (only ~107M rows after 25 hours) and asked whether HPL could be used instead, plus what to tune. Art Kagel and Madison Pruet advised adding the ifx_replcheck shadow column with an index on the primary key (plus WITH CRCOLS) to speed sync/check, and said you can do the initial copy with HPL or external tables over pipes, then run 'cdr check' with repair to reconcile. For tables with ERKEY shadow columns, ERKEY values can't be preserved by unload/load unless ERKEY was added before replication was set up; ERKEY is unnecessary where a unique index already exists. The poster thanked them; no performance results were reported back.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Informix 11.70 FC7 - AIX 1.Is there a method to use HPL (High Performance Loader)and avoid having to CDR SYNC or CDR CHECK ? 2.and/or what configuration parameters should we tune for higher throughput? 390,000,000 row table with a single index, the primary key. cdr sync repl performed 96,000,000 rows in the first 18 hours however after 25 hours only 107,000,000 rows. The target system is immediately applying any records it receives without queuing them so the bottleneck is on the source system.. Both source & target databases reside on the same AIX server which is only moderately busy from a disk & cpu perspective using tcp/ip connections
Does the table have the REPLCHECK column added with the appropriate index on the primary key plus ifx_replcheck? This is important for the speed of sync and check operations. You can use the HPL or external tables across pipes to make the initial copy and force ER to accept the current state of the table as OK. Don't know if it will be faster than a sync though. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Dec 23, 2015 at 12:19 AM, ALI SHAHNAZI <ali@datasync.com.au> wrote: > Informix 11.70 FC7 - AIX > > 1.Is there a method to use HPL (High Performance Loader)and avoid having to > CDR SYNC or CDR CHECK ? > 2.and/or what configuration parameters should we tune for higher > throughput? > > 390,000,000 row table with a single index, the primary key. > cdr sync repl performed 96,000,000 rows in the first 18 hours however > after 25 hours only 107,000,000 rows. > > The target system is immediately applying any records it receives without > queuing them so the bottleneck is on the source system.. > Both source & target databases reside on the same AIX server which is > only > moderately busy from a disk & cpu perspective using tcp/ip connections > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0103e4ce8fed6b05278f8e5c
Oh, also the WITH CRCOLS option helps. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Dec 23, 2015 at 7:06 AM, Art Kagel <art.kagel@gmail.com> wrote: > Does the table have the REPLCHECK column added with the appropriate index > on the primary key plus ifx_replcheck? This is important for the speed of > sync and check operations. > > You can use the HPL or external tables across pipes to make the initial > copy and force ER to accept the current state of the table as OK. Don't > know if it will be faster than a sync though. > > Art > > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > > On Wed, Dec 23, 2015 at 12:19 AM, ALI SHAHNAZI <ali@datasync.com.au> > wrote: > >> Informix 11.70 FC7 - AIX >> >> 1.Is there a method to use HPL (High Performance Loader)and avoid having >> to >> CDR SYNC or CDR CHECK ? >> 2.and/or what configuration parameters should we tune for higher >> throughput? >> >> 390,000,000 row table with a single index, the primary key. >> cdr sync repl performed 96,000,000 rows in the first 18 hours however >> after 25 hours only 107,000,000 rows. >> >> The target system is immediately applying any records it receives without >> queuing them so the bottleneck is on the source system.. >> Both source & target databases reside on the same AIX server which is >> only >> moderately busy from a disk & cpu perspective using tcp/ip connections >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --047d7bd6b7400cfdc505278f96fb
blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } You can use HPL or external tables to create an initial copy of the table. The only problem is that would not be a consistant copy. In order to get consistency you would need run 'cdr check' with the repair option after loading the data. This can be run while the original source is active. Since the majority of the rows would be consistant, the check repair should not take as long. Sent from Yahoo Mail for iPad On Tuesday, December 22, 2015, 11:22 PM, ALI SHAHNAZI <ali@datasync.com.au> wrote: Informix 11.70 FC7 - AIX 1.Is there a method to use HPL (High Performance Loader)and avoid having to CDR SYNC or CDR CHECK ? 2.and/or what configuration parameters should we tune for higher throughput? 390,000,000 row table with a single index, the primary key. cdr sync repl performed 96,000,000 rows in the first 18 hours however after 25 hours only 107,000,000 rows. The target system is immediately applying any records it receives without queuing them so the bottleneck is on the source system.. Both source & target databases reside on the same AIX server which is only moderately busy from a disk & cpu perspective using tcp/ip connections ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks for your advice Art. The replication is setup as a master-slave relationship so I dont believe crcols will assist ? We have some 30 tables that dont have primary keys.. We have implemented erkeys for them. Can we use ifx_replcheck with erkeys and gain the advantage?
Yes. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Jan 3, 2016 at 8:05 PM, ALI SHAHNAZI <ali@datasync.com.au> wrote: > Thanks for your advice Art. > > The replication is setup as a master-slave relationship so I dont believe > crcols will assist? > > We have some 30 tables that dont have primary keys.. We have implemented > erkeys for them. > > Can we use ifx_replcheck with erkeys and gain the advantage? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3b5429cf641052877befa
Thanks again. Your reply has made me question my understanding of the Informix manual If a table that you plan to replicate includes ERKEY shadow columns, you cannot unload and then load the data from these columns and preserve the original values. If you need to preserve the values of the ERKEY shadow columns, use synchronization to propagate the values My understanding was replication would use the erkey as primary access to target row therefore the erkey had to be a duplicate of the source. Ie You MUST preserve the value and hence unload/load is not an option for tables using erkeys Correct or not?
Correct with a proviso. If you add ERKEY to a table BEFORE it is replicated using ER, then you could unload it, load it on the replicant to be, then replicate the tables and "check" them. Remember that the primary purpose of the ERKEY shadow columns is to provide a primary key that is unique to a table that does not have a unique index or constraint already. If you already have a unique key on the table, there is no purpose or advantage to adding ERKEY to it. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Jan 3, 2016 at 9:19 PM, ALI SHAHNAZI <ali@datasync.com.au> wrote: > Thanks again. > > Your reply has made me question my understanding of the Informix manual > > If a table that you plan to replicate includes ERKEY shadow columns, you > cannot unload and then load the data from these columns and preserve the > original values. If you need to preserve the values of the ERKEY shadow > columns, use synchronization to propagate the values > > My understanding was replication would use the erkey as primary access to > target row therefore the erkey had to be a duplicate of the source. Ie You > MUST preserve the value and hence unload/load is not an option for tables > using erkeys Correct or not? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1141c1e028323a0528799f0e
blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } Or unique index Sent from Yahoo Mail for iPad On Sunday, January 3, 2016, 9:22 PM, Art Kagel <art.kagel@gmail.com> wrote: Correct with a proviso. If you add ERKEY to a table BEFORE it is replicated using ER, then you could unload it, load it on the replicant to be, then replicate the tables and "check" them. Remember that the primary purpose of the ERKEY shadow columns is to provide a primary key that is unique to a table that does not have a unique index or constraint already. If you already have a unique key on the table, there is no purpose or advantage to adding ERKEY to it. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Jan 3, 2016 at 9:19 PM, ALI SHAHNAZI <ali@datasync.com.au> wrote: > Thanks again. > > Your reply has made me question my understanding of the Informix manual > > If a table that you plan to replicate includes ERKEY shadow columns, you > cannot unload and then load the data from these columns and preserve the > original values. If you need to preserve the values of the ERKEY shadow > columns, use synchronization to propagate the values > > My understanding was replication would use the erkey as primary access to > target row therefore the erkey had to be a duplicate of the source. Ie You > MUST preserve the value and hence unload/load is not an option for tables > using erkeys Correct or not? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1141c1e028323a0528799f0e ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } Never mind. You already mentioned about using an unique index. Sent from Yahoo Mail for iPad On Sunday, January 3, 2016, 10:11 PM, Madison Pruet <madison_pruet@yahoo.com> wrote: blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } Or unique index Sent from Yahoo Mail for iPad On Sunday, January 3, 2016, 9:22 PM, Art Kagel <art.kagel@gmail.com> wrote: Correct with a proviso. If you add ERKEY to a table BEFORE it is replicated using ER, then you could unload it, load it on the replicant to be, then replicate the tables and "check" them. Remember that the primary purpose of the ERKEY shadow columns is to provide a primary key that is unique to a table that does not have a unique index or constraint already. If you already have a unique key on the table, there is no purpose or advantage to adding ERKEY to it. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Jan 3, 2016 at 9:19 PM, ALI SHAHNAZI <ali@datasync.com.au> wrote: > Thanks again. > > Your reply has made me question my understanding of the Informix manual > > If a table that you plan to replicate includes ERKEY shadow columns, you > cannot unload and then load the data from these columns and preserve the > original values. If you need to preserve the values of the ERKEY shadow > columns, use synchronization to propagate the values > > My understanding was replication would use the erkey as primary access to > target row therefore the erkey had to be a duplicate of the source. Ie You > MUST preserve the value and hence unload/load is not an option for tables > using erkeys Correct or not? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1141c1e028323a0528799f0e ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Art and Madison for your helpful comments.