CATEGORY / OpenLogReplicator

OpenLogReplicator – replication latency

Replication latency is one of the key indicators of good replication.

When I started working on OpenLogReplicator one of my key tasks was to keep the latency as low as possible. What does that mean?

When a client commits a transaction and the database confirms that the transaction is committed – it means that the client can be sure that even if the database breaks the transaction is saved.

Internally the database uses many optimization techniques to speed the process – for example it may be grouping transactions from many clients and write them together – to reduce the delay of confirming them their transactions.

When OpenLogReplicator reads the transaction log – even it it would see a COMMIT Op Code (5.4) in the redo log it can not be absolutely sure that it has already read all the peaces of the whole transaction. Oracle database can change the order of the transaction chunks in the redo log and they might appear in not strict order on disk.

Let’s try to measure how low can be the latency – between the actual commit of the transaction and the moment that it would be read by OpenLogReplicator and sent to Kafka (the time of logging to the Kafka queue).

But before you look at results I want to make a disclaimer:

  1. The test have been run on home PC lab – not very fancy – your results can vary
  2. I have not made any special optimizations – just used default parameters for the database and OpenLogReplicator
  3. What I am actually measuring is the latency between the INSERT operation and commit to Kafka (not the time of the COMMIT in the database)

The test is very simple:

  1. Database box – Intel i5 with SSD drive running Linux64 and Oracle 19c database
  2. 1G LAN
  3. Kafka box – Intel i7 with SSD drive running Linux64, OpenLogReplicator and Kafka

I have run Kafka on Docker and mounted database disk (containing redo logs) using sshfs (wiht no compression aes128-ctr cipher) and so that OpenLogReplicator box could read database redo logs.

The test is just a simple:

create table usr1.adam1(a numeric, b number(10), c number(10, 2), d char(10), e varchar2(10), f timestamp(9), g date);
alter table usr1.adam1 add constraint adam1_pk primary key(a);

And filled with single-row transactions:

declare
    tmstp timestamp(9);
begin
    select current_timestamp into tmstp from dual;
	for i in 1 .. 1000000 loop
		insert into usr1.adam1 values(i, 0010, 10.22, '010', '010', tmstp, null);
		commit;
		select current_timestamp into tmstp from dual;
	end loop;
end;
/

The SQL file generates 1M to be inserted and on my test box takes about 1m54s to complete. It generates 1.143G of redo logs which roughly gives 10MB/s redo generation. Just single thread, not much.

I have measured the actual speed of processing redo logs on my laptop and OpenLogReplicator can process redo logs in about 32s which gives speed about 35MB/s. This is just my laptop and home lab with Kafka running on the same box, so don’t expect great results. Please have in mind that the result of run 32s comes from a second run – when the test was completed – and reading all transactions from archived redo logs.

But what would be the latency?

To measure the latency I am using the timestamp for the record measured just after running the COMMIT command. There is no other good way of measuring when exactly COMMIT has finished.

It looks like that for a typical workload the replication latency is about 11,7ms, but when the database increase checkpoint blocks – spikes appear. This is something I can’t care anything about.

More performance tests to come.

This is an updated version of the graph. The previous test result was considered spoiled since it did not measure transaction latency but commit latency of the database itself.

OpenLogReplicator – logdump functionality

A side product of OpenLogReplicator is the ability to create logs which are in a very simular form as the logdump command from Oracle database.

To create such dump you need to configure parameter in the OpenLogReplicator CFG file.

"dumplogfile": "2",

There are 2 allowed values:

  1. create classic ‘logdump’ output
  2. create classic ‘logdump’ output enhanced with interpretation of additional fields for which logdump does not print any information – like supplemental log data

You can also configure a second parameter:

"dumpdata": "1",

This parameter would cause to add a binary dump of the redo log vector before the text interpretation of the data. Here is an example of the output – the binary log has been highlighted with reverse colors:

logdump output from OpenLogReplicator

For every redo log file OpenLogReplicator would create a separate logdump-alike dump named DUMP-xxx.trace where xxx is the redo log sequence number.

Of course OpenLogReplicator displays interpretation for very few Op codes – which itself would be able to analyze and use for CDC replication. In next versions the list of supported Op codes may increase.

OpenLogReplicator – chained row support

The OpenLogReplicator with the new release 0.2.0 has been extended to support chained rows.

A chained row can appear when there is a bigger DML operation or the table uses more than 254 columns.

Example showing that OpenLogReplicator can support tables containing more than 254 columns

This is an example of a table which uses 1000 columns. The table definition is:

CREATE TABLE SYSTEM.ADAM1(
  a000 numeric,
  a001 numeric,
  a002 numeric,
...
  a999 numeric
);

CFG definition file of OpenLogReplicator contains information to read DML changes for this table:

  "tables": [
     {"table": "SYSTEM.ADAM%"}]

Let’s test with the following DML:

insert into system.adam1(a000, a100, a999) values(0, 1, 2);
commit;

The following JSON message is sent to Kafka:

{"scn": "17817076", "xid": "0x0002.006.00002524", dml: [{"operation":"insert", "table": "SYSTEM.ADAM1", "rowid": "AAAJ49AABAAANH5AAD", "after": {"A000": "0", "A100": "1", "A999": "2"}}]}

You can also test the tool using table definition with big columns (many char(2000) for example).

OpenLogReplicator – Delete row piece (OP 11.3) and Truncate table (OP 24.1)

The OpenLogReplicator has been a new release 0.0.8 with new functionality:

  • Added rowid field to JSON (Kafka target)
  • Support for OP code 11.3 – Delete Row Piece
  • Support for OP code 24.1 – DDL: Truncate table (only Kafka target)

Here is an example of usage. Let’s create a table:

CREATE TABLE SYSTEM.ADAM3(
  A NUMERIC
);

Next add to the CFG file in the tables section:

{"table": "SYSTEM.ADAM3"}

Next run OpenLogReplicator and execute some SQL:

INSERT INTO SYSTEM.ADAM3 VALUES(100);
COMMIT;
TRUNCATE TABLE SYSTEM.ADAM3;
INSERT INTO SYSTEM.ADAM3 VALUES(200);
COMMIT;
DELETE FROM SYSTEM.ADAM3;
COMMIT;

The following JSON message is sent to Kafka:

{"scn": "8849551", dml: [{"operation":"insert", "table": "SYSTEM.ADAM3", "rowid": "AAAIDRAAFAAAACFAAA", "after": {"A": "100"}}]}
 {"scn": "8849568", dml: [{"operation":"truncate", "table": "SYSTEM.ADAM3"}]}
 {"scn": "8849573", dml: [{"operation":"insert", "table": "SYSTEM.ADAM3", "rowid": "AAAIDSAAFAAAACFAAA", "after": {"A": "200"}}]}
 {"scn": "8849580", dml: [{"operation":"delete", "table": "SYSTEM.ADAM3", "rowid": "AAAIDSAAFAAAACFAAA", "before": {"A": "200"}}]}

I have just adjusted the formatting to make the JSON more readable. This is exactly what is expected. This functionality is available with the latest build (0.0.8). Stay tuned for next OP codes to be implemented too.

PS. I know, that the JSON attribute is named dml even though truncate is actually a ddl operation in Oracle, but it’s actually a matter of naming fields. Maybe it should be changed to something more generic…

PS2. You can notice that after running truncate table newly inserted rows contain a different Data Object ID in the rowid.

OpenLogReplicator – Insert multiple rows (OP 11.11)

The OpenLogReplicator has been extended to support also OP code 11.11 – Insert Multiple Rows.

OpenLogReplicator supports OP code 11.11 - insert multiple rows

This type of redo record is used when an INSERT is used with a set of rows to be inserted. For example with INSERT … SELECT … .

Here is an example of usage. Let’s create a table:

CREATE TABLE SYSTEM.ADAM5(
A NUMERIC,
B NUMBER(10)
C NUMBER(10, 2),
D CHAR(10),
E VARCHAR2(10),
F TIMESTAMP,
G DATE
);

Next add to the CFG file in the tables section:

    {"table": "SYSTEM.ADAM5"}

Next run OpenLogReplicator and execute some SQL:

INSERT INTO SYSTEM.ADAM5 
SELECT OBJECT_ID, DATA_OBJECT_ID, 1.0, STATUS, STATUS, CREATED, CREATED FROM ALL_OBJECTS WHERE ROWNUM <= 2;
COMMIT;

The following JSON message is sent to Kafka:

{"scn": "8568574", dml: [
{"operation":"insert", "table": "SYSTEM.ADAM5", "after": {"A": "20","B": "2","C": "1","D": "VALID ","E": "VALID","F": "2018-07-29T21:16:55","G": "2018-07-29T21:16:55"}},
{"operation":"insert", "table": "SYSTEM.ADAM5", "after": {"A": "46","B": "46","C": "1","D": "VALID ","E": "VALID","F": "2018-07-29T21:16:55","G": "2018-07-29T21:16:55"}}
]

I have just adjusted the formatting to make the JSON more readable. This is exactly what is expected. This functionality is available with the latest build (0.0.5). Stay tuned for next OP codes to be implemented too.

OpenLogReplicator – CDC replication from Oracle to Redis

OpenLogReplicator – the first open-source transaction replicator (CDC) to Kafka now supports also Redis as a target.

Check the new released version 0.0.3 (compiled for x86_64). It requires additionally the hiredis library

To run:

export LD_LIBRARY_PATH=/opt/oracle/instantclient_11_2:/usr/local/lib:/opt/hiredis
Debug/OpenLogReplicator

Testing with Redis:

create table adam5(a numeric, b number(10), c number(10, 2), d char(10), e varchar2(10), f timestamp, g date);
alter table adam5 add constraint idx1 primary key(a);

insert into adam5 values(100, 999, 10.22, 'xxx', 'yyy', sysdate, null);
commit;
insert into adam5 values(101, 999, 10.22, 'xxx', 'yyy', sysdate, null);
commit;
insert into adam5 values(102, 999, 10.22, 'xxx', 'yyy', sysdate, null);
insert into adam5 values(103, 999, 10.22, 'xxx', 'yyy', sysdate, null);
commit;

Redis:

127.0.0.1:6379> KEYS *
1) "SYSTEM.ADAM5.\"100\""
2) "SYSTEM.ADAM5.\"103\""
3) "SYSTEM.ADAM5.\"101\""
4) "SYSTEM.ADAM5.\"102\""
127.0.0.1:6379> GET "SYSTEM.ADAM5.\"100\""
"\"100\",\"999\",\"10.22\",\"xxx       \",\"yyy\",\"2019-01-05T11:21:54\",NULL"

All transactions are replicated with consistency (MULTI/EXEC).

Enjoy!

Demo of OpenLogReplicator

OpenLogReplicator – first log-based open source Oracle to Kafka CDC replication

I have figured out that writing about somebody else’s software is boring. Why not create your own.

So here it is:

  • All code GPL v.3
  • Just C++ code – absolutely no Java/Python or any other interpreted language
  • Purely reading Oracle Redo Log from disk – zero additional load to the database instance
  • High performance architecture from the beginning – no lazy slow code
  • Minimum latency approac
  • Memory-based approach – no storing of intermediate files on disk

Currently is implemented:

  • Possible to compile for Linux x/64 only
  • Supported only Oracle 11.2.0.4
  • Replicated Redo Codes: just single row Insert (OpCode 11.2)
  • Supported full transactionality: begin, commit, rollback, savepoint
  • Supported types: numeric, char, varchar2, timestamp, date

If I have more time, more documentation will appear here.

How to run?

git clone https://github.com/bersler/OpenLogReplicator
vi Debug/makefile
make
cp OpenLogReplicator.json.example OpenLogReplicator.json
vi OpenLogReplicator.json
./OpenLogReplicator

Make sure that you set the proper paths for needed dependencies for libraries:

  • Oracle client 11.2.0.4
  • RapidJson library
  • The Apache Kafka C/C++ library

Make sure the JSON file contains proper information. Everything is very easy and logical.

For who are inpatient and don’t want to compile – here is release 0.0.3 compiled for Linux x64. To execute it run:

export LD_LIBRARY_PATH=/opt/oracle/instantclient_11_2:/usr/local/lib
Debug/OpenLogReplicator

Have fun, but please do not send me any complains about not working code. I will maybe write here some help&docs when I have time. I could of course add more functionality, but I didn’t have time. You have the code – you can do it by yourself!

Sample input is:

create table adam3(a numeric, b number(10), c number(10, 2), d char(10), e varchar2(10), f timestamp, g date);
insert into adam3 values(100, 999, 10.22, 'xxx', 'yyy', sysdate, null);
commit;

In Kafka you should have:

{"scn": "4856388", dml: [{"operation":"insert", "table": "SYSTEM.ADAM3", "after": {"A": "100", "B": "999", "C": "10.22", "D": "xxx       ", "E": "yyy", "F": "2018-12-10T21:27:42"}}]}

Please have in mind that only single row insert operations are now supported. insert .. select, insert append, etc. would not work. Just 1-row INSERT operations.

loading
×