Replication HDR : "insert" are very very very slow

Hi, We configured a HDR replication with Informix 11.70.FC8. The secondary server is updatable (UPDATABLE_SECONDARY = 1 in the file ONCONFIG). Our database is buffered logged. We are doing some tests to update this database : By example : INSERT INTO foo2 SELECT * FROM foo1;Where the table foo1 contains 100 000 rows. - On the primary server, this query runs only 3 seconds, which is very good time ! - On the secondary server, the same query takes 3 minutes, which is very slow ! We have the same problem with "Update" and "Delete" ! Do you know why the query is very slow on the secondary server ? We tried to change UPDATABLE_SECONDARY to twice cpu vp number but we have the same run time ! PS : The both servers have the same RAM and the same CPU. -- Franck Thomas ConsultiX franck.thomas@consult-ix.fr http://www.consult-ix.fr Téléphone : 33 (0) 1 39 12 18 00 Mobile : 33 (0) 6 78 81 09 33 Fax : 33 (0) 1 39 12 18 18

Hi, just a thought (did not test it): 1) the value for parameter UPDATABLE_SECONDARY is setting the number of connections between secondary and primary server, it is not a flag (yes/no = 1/0), you can set it to a higher value to have multiple parallel connections. 2) did you check for connectivity issues like host lookup from secondary to primary (is the primary server in /etc/hosts, or does it rely on a DNS which might not be reachable ?) Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "Franck THOMAS" <franck.thomas@consult-ix.fr> An: ids@iiug.org Gesendet: Dienstag, 19. Mai 2015 17:34:20 Betreff: Replication HDR : "insert" are very very very .... [35132] Hi, We configured a HDR replication with Informix 11.70.FC8. The secondary server is updatable (UPDATABLE_SECONDARY = 1 in the file ONCONFIG). Our database is buffered logged. We are doing some tests to update this database : By example : INSERT INTO foo2 SELECT * FROM foo1;Where the table foo1 contains 100 000 rows. - On the primary server, this query runs only 3 seconds, which is very good time ! - On the secondary server, the same query takes 3 minutes, which is very slow ! We have the same problem with "Update" and "Delete" ! Do you know why the query is very slow on the secondary server ? We tried to change UPDATABLE_SECONDARY to twice cpu vp number but we have the same run time ! PS : The both servers have the same RAM and the same CPU. -- Franck Thomas ConsultiX franck.thomas@consult-ix.fr http://www.consult-ix.fr Téléphone : 33 (0) 1 39 12 18 00 Mobile : 33 (0) 6 78 81 09 33 Fax : 33 (0) 1 39 12 18 18 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Understand that an updatable secondary server doesn't actually update anything! It just allows you to enter data modification SQL when connected to it. The secondary then passes the SQL to the primary which actually performs the update/insert/delete operations sending the logical logs to the HDR and RSS secondaries (including the one that accepted the SQL). SDS secondaries receive a light weight notice that lets them know that they should invalidate any of the affected pages that they may have in their cache so they are reread from the updated copy on disk. This is the reason why, if the network connection between the primary and secondary is not lightning fast and absolutely reliable, actually updating on a secondary can be slow. Certainly large bulk updates should ALWAYS be issued on a primary server. 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 Tue, May 19, 2015 at 11:34 AM, Franck THOMAS <franck.thomas@consult-ix.fr > wrote: > Hi, > > We configured a HDR replication with Informix 11.70.FC8. > The secondary server is updatable (UPDATABLE_SECONDARY = 1 in the file > ONCONFIG). > Our database is buffered logged. > We are doing some tests to update this database : > By example : > INSERT INTO foo2 SELECT * FROM foo1;> Where the table foo1 contains 100 000 rows. > > - On the primary server, this query runs only 3 seconds, which is very > good time ! > - On the secondary server, the same query takes 3 minutes, which is very > slow ! > > We have the same problem with "Update" and "Delete" ! > > Do you know why the query is very slow on the secondary server ? > > We tried to change UPDATABLE_SECONDARY to twice cpu vp number but we > have the same run time ! > > PS : The both servers have the same RAM and the same CPU. > > -- > Franck Thomas > ConsultiX > franck.thomas@consult-ix.fr > http://www.consult-ix.fr > Téléphone : 33 (0) 1 39 12 18 00 > Mobile : 33 (0) 6 78 81 09 33 > Fax : 33 (0) 1 39 12 18 18 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113f8a9e72ecb40516736e0a