CDR with many tables

Posted in 2005

Topics: High Availability & Replication, Storage & Space Management, Platform-Specific Issues

Malcolm Perrior — Fri, 11 Feb 2005 17:51:14 — source: IIUG Forums & Mailing Lists

IDS9.30HC5; HP-UX11i This is actually a follow-up to my previous regarding deferred level-1 restores, and is associated with the resultant "HDR not allowing blobspaces" threads. I know CDR allows blobs and I know it works because I've done some testing previously. But...... CDR requires a separate definition for every table in every database you want to replicate. I have scripted the process and that is also no problem. What I wondered was, how would CDR cope with upwards of 650 tables defined in an OLTP system? We can get through 50 x 10Mb logs an hour at peak times.... Network capacity isn't a problem though. Anyone doing CDR with lots of tables? Cheers Malc

↪ replying to Malcolm Perrior

Madison Pruet — Fri, 11 Feb 2005 20:53:37 — source: IIUG Forums & Mailing Lists

--0__=09BBE536DFE217738f9e8a93df938690918c09BBE536DFE21773 Content-type: multipart/alternative; Boundary="1__=09BBE536DFE217738f9e8a93df938690918c09BBE536DFE21773" --1__=09BBE536DFE217738f9e8a93df938690918c09BBE536DFE21773 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable We have folks with a lot of replicated tables. In the next release, we= have implemented templates for ER definition. You can then define replication by creating a set of tables (define demplate) and then realize the template on the various servers (i.e. nodes). Additionally, you can define the template to be all of the tables within a database. All of this means that you can pr= etty much create the whole schebang with two commands. As an example... cdr define template storestempl --database=3Dstores9 --all --- creates= a template of all of the tables within stores9 cdr realize template storestempl serv1, serv2, serv3 Alternativally, you can specify the individual tables... cdr define template storestempl -database=3Dstores9 tab1 tab2 tab3 ..= . M.P. = "Malcolm Per...." = <malc_p@btinterne = t.com> = To Sent by: ids@iiug.org = forum.subscriber@ = cc iiug.org = Subj= ect CDR with many tables [4251] = 02/11/2005 11:51 = AM = = = = = IDS9.30HC5; HP-UX11i This is actually a follow-up to my previous regarding deferred level-1 restores, and is associated with the resultant "HDR not allowing blobspaces" threads. I know CDR allows blobs and I know it works because I've done some testing previously. But...... CDR requires a separate definition for every table in every database you want to replicate. I have scripted the process and that is also no problem. What I wondered was, how would CDR cope with upwards of 650 tables defined in an OLTP system? We can get through 50 x 10Mb logs an hour at peak times.... Network capacity isn't a problem though. Anyone doing CDR with lots of tables? Cheers Malc = --1__=09BBE536DFE217738f9e8a93df938690918c09BBE536DFE21773 Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> We have folks with a lot of replicated tables. In the next release,= we have implemented templates for ER definition. You can then define replication by creating a set of tables (define dem= plate) and then realize the template on the various servers (i.e. nodes). Additionally, you can d= efine the template to be all of the tables within a database. All of this means that you can pr= etty much create the whole schebang with two commands. As an example... cdr define template storestempl --database=3Dstores9 --all --- creates= a template of all of the tables within stores9 cdr realize template storestempl serv1, serv2, serv3 Alternativally, you can specify the individual tables... cdr define template storestempl -database=3Dstores9 tab1 tab2 tab3 ..= . M.P. <img src=3D"cid:10__=3D09BBE536DFE217738f9e8a93df938@us.ibm.com" width=3D= "16" height=3D"16" alt=3D"Inactive hide details for "Malcolm Per..= .." <malc_p@btinternet.com>">"Malcolm Per...." &lt= ;malc_p@btinternet.com> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D09BBE53= 6DFE217738f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wid= th=3D"40%"> <ul> <ul> <ul> <ul>"Malcolm Per...." <malc_p@btintern= et.com> Sent by: forum.subscriber@iiug.org 02/11/2005 11:51 AM</ul> </ul> </ul> </ul> </td><td width=3D"60%"> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBE536DFE217738f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""> <div align=3D"right">To</div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D09BBE536DFE217738f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""> ids@iiug.org</td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBE536DFE217738f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""> <div align=3D"right">cc</div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D09BBE536DFE217738f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""> </td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBE536DFE217738f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""> <div align=3D"right">Subject</div></td><td widt= h=3D"100%"><img src=3D"cid:30__=3D09BBE536DFE217738f9e8a93df938@us.ibm.= com" border=3D"0" height=3D"1" width=3D"1" alt=3D""> CDR with many tables [4251]</td></tr> </table> <table border=3D"0" cellspacing=3D"0" cellpadding=3D"0"> <tr valign=3D"top"><td width=3D"58"><img src=3D"cid:30__=3D09BBE536DFE2= 17738f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt= =3D""></td><td width=3D"336"><img src=3D"cid:30__=3D09BBE536DFE217738f9= e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><= /td></tr> </table> </td></tr> </table> <tt>IDS9.30HC5; HP-UX11i This is actually a follow-up to my previous regarding deferred level-1 restores, and is associated with the resultant "HDR not allowing blobspaces" threads. I know CDR allows blobs and I know it works because I've done some testing previously. But...... CDR requires a separate definition for every table in every database you want to replicate. I have scripted the process and that is also no problem. What I wondered was, how would CDR cope with upwards of 650 tables defined in an OLTP system? We c

↪ replying to Madison Pruet

KANNAN THIRUGNANAM — Mon, 14 Feb 2005 16:47:23 — source: IIUG Forums & Mailing Lists

We had ER with 47 tables defined in two replicate sets (31 and 16). Last year it ran fine without any issue. At our peak load, we had 500MB of transactions within 10 minutes (Batch jobs). There were 50 logs of 100MB each in both the primary and target. Of course it generated log space configured too small messages. It could be ignored. The primary has 8 1.2GHZ CPUs and the target has 4 900MHz. ER wasn't able to catch up in the target. It is not because of the CPUs. There was lot of idle time in the target. CDR is single threaded while applying the Txs in the target. To have multi threaded feature (ACCORDING to IBM support) you would have to go for fragmenting the tables in Target or have 9.40FC6 version. In our case, ER wasn't able to catch up after 24 hours and it started spooling to blobspace. Went for a DDR lock. You don't want to be there. It halts your production. Then you would have to drop ER/replicateset. I had many sleepless nights. Anyway for small Txs loads, ER works perfect. If your Primary and Target has the same CPU capabilities, the Primary would not be able to generate a lot of Txs within a few minutes. For your release, don't run batch jobs with ER. I hope, I made some sense. With Regards, Kannan

↪ replying to KANNAN THIRUGNANAM

Dave Thacker — Tue, 15 Feb 2005 16:40:31 — source: IIUG Forums & Mailing Lists

I'm doing CDR with 200 tables over a WAN for disaster recovery. My peak throughput per hour is probably half of yours. We're using 9.30 UC5, moving to 9.4 as soon as possible. (bloody hardware!) I run OLTP during the day and batch jobs at night. The only problems I have had with this setup are a) network down b) hardware down c) human error (admin at remote site did not notify me of a reboot) The two servers are comparable in processing ability, and the disks on the target are slightly slower than the the source. This setup has been excersised 7 times over the last 2 1/2 years with minimal data loss. As long as the hardware is comparable on both sides, I don't think you'll have a problem. But if it was me, I'd read the replication guide, create a test lash-up, and beat the living daylights out of it. Then you can make all the mistakes before you go into production. Best of luck! DT Dave Thacker Senior Systems Administrator Omni Hotels Reservation Center V:402-952-6535 F:402-334-8013 M: 402-981-4613 (24/7)

CDR with many tables

Glossary

Your privacy choices