GoldenGate vs DBVisit Replicate – Extract methods and replication performance

One of the (I think) interesting distinctions between DBVisit Replicate and GoldenGate is the method of redo extraction each uses.

DBVisit has chosen to use what they call optimistic commit. They assume that a long-running transaction being written to the redo stream is going to be committed, and therefore start extracting and writing it to their trail file (PLOG) without waiting for it to be committed. This differs from the GoldenGate approach, which waits for the transaction to be committed prior to writing it to the trail file.

Knowing this, I wanted to do some testing to see how this affects performance. I was interested to see how much quicker the DBVisit approach was over GoldenGate. The results were somewhat unexpected.

I was using a very simple test table –

SQL> desc million_rows
Name Null? Type
----------------------- -------- ----------------------------
COL1 NUMBER
COL2 VARCHAR2(50)

And some simple SQL to insert the data –

begin
for counter in 1..1000000
loop
insert into million_rows values (millionrowid.nextval, dbms_random.string('A',dbms_random.value(15,40)));
end loop;
end;
/

Prior to each test I used RMAN to delete all archive logs from disk, and truncated the table on both source and target.

First I tested DBVisit. Timings were as follows –

0m 0s – Transaction started, MINE begins straight away
4m 30s – Transaction completed and committed (370,000 rows mined so far at this point)
8m 45s – All rows replicated and committed on target

Next I tested GoldenGate. I used the traditional extract (not integrated) and the traditional apply (again not integrated) –

0m 0s – Transaction started, EXTRACT idle
3m 30s – Transaction completed and committed (EXTRACT kicks in)
8m 15s – All rows replicated and committed on target

I looked at the replicat apply rate in GoldenGate while it was still working, and saw that it was inserting at around 435,000 rows per minute. Apparently, although it has to sit around and wait for 3.5 minutes for the transaction to be committed, it is very quick at applying once it does finally get started. Again to clarify this isn’t using the new integrated apply (where we can use parallelism) and it isn’t using the BATCHSQL or INSERTAPPEND options.

So how can GoldenGate be quicker despite not being able to start work until after the commit is issued? The answer is, it couldn’t. It turns out that GoldenGate does actually read the uncommitted transactions into a “read ahead buffer” in memory. This is all to do with the CACHEMGR parameters which can (but shouldn’t usually) be customised. The reference guide has a lot more information about how it works and how to monitor it. I was interested to see it in action, so I monitored the memory usage of extract while I ran a couple of million-row inserts –


[oracle@host ~]$ ps -eo pid,%mem,rss,vsz,args|egrep '(extract|PID)'|grep -v grep|grep -v dp01|cut -c-150
PID %MEM RSS VSZ COMMAND
27191 6.8 279940 578720 /u01/app/oracle/gg01/extract PARAMFILE /u01/app/oracle/gg01/dirprm/ext01.prm REPORTFILE /u01/app/oracle/gg01/dirrpt/EXT01.rpt

RSS is the actual non-swapped physical memory in use, and VSZ is the total virtual memory size (which might include swap). Actually it only increased by 2MB during the first insert, but I hadn’t stopped/started the extract prior to the test so I ran another insert in the same transaction. This is when the memory allocation started growing quickly.

You can see that the first insert simply re-uses the allocated but unused memory already available to the extract process. The second insert necessitates a need to increase the size of the read ahead cache and the memory usage grows. The commit has no impact on the memory allocated (but I imagine the memory becomes free) and it takes an extract restart to free up the memory. On a more heavily used server I would expect the OS to reclaim this memory automatically.

Anyway, back on topic. I wanted to continue on this path and make a few further comparisons on the products. So far I’m not using any of the performance benefits of GoldenGate 11.2 (Integrated capture) and 12.1 (Integrated replicat). First I tried enabling integrated replicat (very straightforward) –


GGSCI > alter replicat RP01 INTEGRATED
REPLICAT (Integrated) altered.

I then performed the usual prep, and re-ran the test with the following results –

0m 0s – Transaction started, EXTRACT idle
3m 30s – Transaction completed and committed (EXTRACT kicks in)
5m 15s – All rows replicated and committed on target

A further 3 minutes saved by using integrated replicat (therefore parallel apply).

Finally we can make use of integrated extract since our source database is Oracle 11.2.0.4. I enable that on the extract, and run the usual prep and test –

0m 0s – Transaction started, EXTRACT idle
3m 30s – Transaction completed and committed (EXTRACT kicks in)
4m 30s – All rows replicated and committed on target

It is interesting that we see a further performance increase in the apply rate even once the transaction has been committed and transferred to the target. I can’t explain that behaviour yet – perhaps the trail format is more efficient?

So, with a slight detour in understanding the behaviour of the GoldenGate read ahead cache we come to the following results in our tests –

It is interesting that DBVisit Replicate seems to affect the speed at which the transaction can work on the source. I suspect this could be a bit of I/O contention (as it is writing the PLOG file to the same disks as the redo and data files) but there also seems to be a much higher overhead in running the MINE processes with DBVisit. I re-ran the test at the end to be sure the results were not a mistake (the results were the same) but this time I looked at the CPU and memory utilisation from the MINE process – it peaked at around 33% total CPU available while using 700MB of physical memory and 1.2GB total VM size. At least knowing this, when using DBVisit Replicate I think I’d put the PLOG files on a different set of disks to the database and I’d also make sure that the server wasn’t already maxed out on CPU and memory prior to installing it.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: