V
Valeriy Bashkatov
Guest
[quote user="temays"] We seem to have multiple topics going on within this thread and it doesn't appear ALL are related to bulk copy. If this doesn't answer your question, then you might want to start a new thread. [/quote] I will create a separate topic for the performance of replication threads [quote user="temays"] First...Bulk loading a table potentially can be split across "threads." I say potentially as really the index structure of the source table is the primary factor when determining if this is possible ,and really the piece to check is whether an index is already defined to logically break up the source side data. For example, is there a good date index that would allow multiple threads to properly bracket the reading of source data? Is the data separated by warehouse? Then threading makes sense, one thread per warehouse. This analysis really is the key to success when multi-threading the bulk load. Pending that analysis is good and you have properly identified a good method of separation, then you simply pull up the generated bulk load procedure for the table(s) in question (normally to bprepl/repl_mproc), find how the generated procedure is navigating the source records (using standard templates this would be FOR EACH bfrSrcTABLENAME NO-LOCK
and add a WHERE clause that makes sense based on the index evaluation done earlier. Save that modified version of the bulk load procedure to something like originalname1.p. Change the where clause again and again save it as originalname2.p sort of scheme. Then simply open a Pro2 editor session for each of the saved "threaded" procedures and run them individually. [/quote] Yes, I know about it, and I did it. But the problem is still in the speed of loading into Oracle, because on the Oracle side for each record a separate commit is made. For example. For loading 10 000 000 records in Oracle made 10 000 000 commit. Today I will test your template and see how it will affect performance [quote user="temays"] Be aware that "threading" a bulk load does NOT affect how replication handles the copy of data. Bulk Load threads and Replication threads are not the same thing. [/quote] I understood it. [quote user="temays"] Also you should know that splitting tables at the replication level requires a fully custom effort, as you have to change standard Pro2 code to traverse the ReplQueue table differently than normal. To split tables at replication also requires certain trigger dependencies, etc. so this really should be done by someone with experience in Pro2 implementations. [/quote] As mentioned above, I used a template "tplt_repltrig_with_compression.p". I understand that it can be used to reduce the number of notes in the queue. This is especially important when the application performs massive data changes in a short period of time. For example, closing an operating day in the Bank. Also, due to this, on one thread we can start multiple replication processes (1, 1-2, 1-3, etc.) - this is especially important for heavily loaded and often changing tables. But as it is seen above, I could not do it. But we will discuss this in a separate topic.
Continue reading...
and add a WHERE clause that makes sense based on the index evaluation done earlier. Save that modified version of the bulk load procedure to something like originalname1.p. Change the where clause again and again save it as originalname2.p sort of scheme. Then simply open a Pro2 editor session for each of the saved "threaded" procedures and run them individually. [/quote] Yes, I know about it, and I did it. But the problem is still in the speed of loading into Oracle, because on the Oracle side for each record a separate commit is made. For example. For loading 10 000 000 records in Oracle made 10 000 000 commit. Today I will test your template and see how it will affect performance [quote user="temays"] Be aware that "threading" a bulk load does NOT affect how replication handles the copy of data. Bulk Load threads and Replication threads are not the same thing. [/quote] I understood it. [quote user="temays"] Also you should know that splitting tables at the replication level requires a fully custom effort, as you have to change standard Pro2 code to traverse the ReplQueue table differently than normal. To split tables at replication also requires certain trigger dependencies, etc. so this really should be done by someone with experience in Pro2 implementations. [/quote] As mentioned above, I used a template "tplt_repltrig_with_compression.p". I understand that it can be used to reduce the number of notes in the queue. This is especially important when the application performs massive data changes in a short period of time. For example, closing an operating day in the Bank. Also, due to this, on one thread we can start multiple replication processes (1, 1-2, 1-3, etc.) - this is especially important for heavily loaded and often changing tables. But as it is seen above, I could not do it. But we will discuss this in a separate topic.Continue reading...