Shulou(Shulou.com)05/31 Report--

This article is to share with you about the order in which the primary key and trandata are added in OGG. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Recently, in the stress test of OGG, the source database and target database are synchronized at the table level. The source database has 20 tables, each with 30-40 columns, with a large amount of data.

During the test, the dml statement is executed in a loop to test the OGG synchronization effect. The test script is shown below:

The script is only used to illustrate the process and is not rigorous

Begin

For i in 1..100000 loop

Insert into table1 (test_id, col1,col2) values (iMagne xpene x)

Insert into table2 (test_id, col1,col2) values (iMagne xpene x)

...

Insert into table20 (test_id, col1,col2) values (iMagne xpene x)

If mod (1PM1000) = 0 then

Commit

End if

End loop

Commit

End

/

Begin

For i in 1..100000 loop

Update table1 set col1=48452 where test_id=i

Update table2 set col1=48452 where test_id=i

...

Update table20 set col1=48452 where test_id=i

If mod (1PM1000) = 0 then

Commit

End if

End loop

Commit

End

/

Begin

For i in 1..100000 loop

Delete table1 where test_id=i

Delete table2 where test_id=i

...

Delete table20 where test_id=i

If mod (1PM1000) = 0 then

Commit

End if

End loop

Commit

End

/

The test results were very poor and took as long as 10 hours! Among them, the speed of extraction and delivery are ideal, and the time-consuming is focused on the part of the replication process performing delete operations.

GGSCI > lag REPSYM_T

Sending GETLAG request to REPLICAT REPSYM_T...

Last record lag: 36481 seconds.

At EOF, no more records to process.

There are several ways to deal with this problem:

1. Set up multiple replication processes in parallel.

two。 Add the batchsql parameter to the replication process parameter file.

3. Bind variables to optimize the delete statement.

Intuitive feeling can not be solved by the above problems, but also try one by one. The effect is not obvious. There is nothing wrong with monitoring undo tablespaces and user tablespaces during testing, so that's not the problem either.

Next, we do a test, create a test table on the target side without OGG replication, insert 100000 data, and delete 10w data at a normal speed. It seems that the problem is OGG replication.

Is there no primary key? Use the following SQL statement to see the following results. It is found that all tables have primary keys.

Select owner,table_name,constraint_type,constraint_name,status

From dba_constraints

Where owner='TEST'

And constraint_type in ('pumped Magnum')

Next, check the trandata status, and the results are very unexpected to me.

GGSCI > dblogin userid ogg,password ogg

GGSCI > info trandata TEST.*

...

Logging of supplemental redo log data is disabled for table TEST.table1.

..

Seeing here, I understand what the problem is.

The synchronous table does not have a primary key, and after setting trandata, update and delete operations use all columns bound to one column as a unique identity to synchronize changes. Later, the primary key was added manually, but trandata did it the same way and did not use the primary key. The solution is simple: delete the original trandata and re-add trandata to make the primary key effective.

GGSCI > delete trandata TEST.*

GGSCI > add trandata TEST.*

The effect of the test again is remarkable, and the delay of the replication process has been reduced from 36481 to 542 seconds!

GGSCI > lag REPSYM_T

Sending GETLAG request to REPLICAT REPSYM_T...

Last record lag: 542 seconds.

At EOF, no more records to process.

Summary: you need to have a physical examination of the replicated object before deploying OGG. The most important point is that the source table needs to have a primary key or a unique key. If you find that the source table is missing a primary key or a unique key after the OGG deployment, you need to manually add and delete the original trandata, and then rebuild it to take effect. This reduces the amount of traffic when OGG synchronizes update and delete operations, so that all column packaging bindings are not applied as "key values".

The above is the order in which the primary key and trandata are added in OGG. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.