Debezium Performance Impact

[Mike Seddon]

Hi guys,

I was being hassled by my DBA team as to the performance impact of Debezium on their databases so I did some measurements:

https://reorchestrate.com/posts/debezium-performance-impact/

Not sure if this will help others win their arguments but I think it removes at least one.

Cheers

Mike

[Gunnar Morling]

Hi Mike,

That's a great write-up, thanks for sharing! Nicely confirms other
reports we've seen before, source database overhead typically isn't
something one needs to be concerned about.

Best,

--Gunnar

> --
> You received this message because you are subscribed to the Google Groups "debezium" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/ab173a64-9d8f-482e-8600-67de6a895031n%40googlegroups.com.

[Mike Seddon]

Hi Gunnar

Thanks. 

If you would like it would be good to crosspost to the debezium blog as I found it hard to find anything material when I hunted.

Good work 👍

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CADGJaX-oVSLneB0zPV0DUrMogdMWS2ct9qMRvJDXU9M1CqThYA%40mail.gmail.com.

[Milo van der Zee]

Hello,

Same for Oracle. We did not notice any significant impact on the database. Only a big impact on storage requirements for the archive log files.

MAG,

Milo

Op wo 3 mrt. 2021 om 09:24 schreef Mike Seddon <sedd...@gmail.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CAGkRQroouwUDOZJyJ0nJ5bhBeeab4-8Bm4ub3ydgcEc2O5MUeA%40mail.gmail.com.

[Fisher Joe]

Thanks for the writeup

[Milo van der Zee]

Hello all,

Today somebody did a different check on system impact and actually did find quite some impact on our Oracle databases. From what I see the impact is actually quite high. But I'm not a DBA and so I'm asking questions to get that clear.

I'll send my findings here. But the earlier estimate seems to be wrong... So there might be a need for offloading the mining...

MAG,

Milo

Op zo 7 mrt. 2021 om 21:12 schreef Fisher Joe <fisher...@gmail.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/401b0ec6-9e38-4356-9417-3a32bf0f77f0n%40googlegroups.com.

[Chris Cranford]

Hi Milo -

I wouldn't be surprised if the concern is mostly around the PGA space consumed by the LogMiner session itself since many of the tables it populates are in-memory backed by the PGA pool.  Depending on the options passed to LogMiner, I could foresee large PGA space consumed.  Debezium specifically tries to guard against this by using an adaptive SCN window algorithm so that even if we have to initiate multiple LogMiner sessions, we try and keep the data that's to be loaded into these in-memory tables bounded to a reasonable PGA size.

Either way, I'll await your findings.

Thanks,
Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CAO5pNXLtSrW4X9KjOi-MVGg4cmLLtFCsT0Pazd_--vOdDP1sYg%40mail.gmail.com.

[Milo van der Zee]

Hello Chris,

What do you mean? Do you mean that you try to align multiple mining sessions to use the same range of SCNs? That is actually what I started doing. I try to keep all mining sessions at more or less the same SCN range. Not because of the PGA but because of keeping the transaction storage manageable. I try to keep transactions together and only handle the COMMIT/ROLLBACK after all tables that could impact the transaction are read. I used to do this independently and read a table and send it to Kafka as soon as the COMMIT comes in. And then for another tables as well. Because this was another mine session the same COMMIT would be seen. The end result is the same. Only the order of messages is different.

I also never give an end SCN to the miner. Might that also help? And that indeed needs to be adaptive.

I might also optimize the reading of the log. Currently multiple tables have their own session and all read the log independently from each other. I'm thinking of making a local copy before handling. In that way the reading of the log is done once and the rest of the processing is offloaded.

I today found out that every mining session takes a full CPU. Our DB has 32 CPUs and I was starting 10 mining sessions... People were not happy...

IO impact is very low. If I understand correctly this might be expected with your PGA insight.

MAG,

Milo

Op wo 10 mrt. 2021 om 18:48 schreef Chris Cranford <cran...@gmail.com>:

[Chris Cranford]

Hi Milo -

No the Debezium Oracle connector doesn't perform any concurrent mining sessions.  What we do is get the latest SCN written to the redo logs and prevent the range of [start,end] from being too large by applying an upper bounds batch size limit.  This prevents loading too many records from disk, consuming too much memory, etc. 

I do think reading the redo logs multiple times concurrently will cause some contention as you've said.  I do wonder if copying contents to a staging table would be somewhat disruptive too?  I tried an incarnation of this with Debezium and it ended up being more like the "Robbing Peter to Pay Paul" idiom because I traded the performance impact of reading the contents from V$LOGMNR_CONTENTS and streaming it over the wire to the actual copy process.  Even marking the staging table as NOLOGGING, the copy process often was longer than having actually just reading the rows from the in-memory table; however in hindsight I see advantages.

A staging table comes with the benefit that once you've mined [start,end] range and the contents have been copied, you can immediately update the offsets to avoid re-mining things if the connector fails.  It also means that long running transactions can be easily supported and the in-memory footprint of the connector is drastically reduced since we're relying on the persistence of the database for that.  We've discussed using Infinispan for this rather than the source database, but I could see both being viable alternatives.  It would also likely shift how you process the results because streaming from a logical table is drastically faster than an in-memory table from my experience, such that a single thread might be adequate now rather than multiple.

As for CPU usage, that's what I would have expected for that preparatory work to happen when initiating a session.  If your DBA didn't mention any concerns with PGA space, then it sounds like that's the least or not a concern at all, which is great news. 

Chris

[Ignatenko, Andrey]

Hi, Chris. 

Based on my experimenting, direct reading using cursor from logminer view is the fastest way.

The second place is taken by SP:

/**
 * create global temporary table andrey_tabl_created (
 * seq_number number(19,0),
 * SCN number(19,0),
 * SQL_REDO varchar2(4000 CHAR),
 * OPERATION_CODE number(19,0),
 * TIMESTAMP timestamp(6),
 * XID varchar2(50 char),
 * CSF number(19,0),
 * TABLE_NAME varchar2(30 char),
 * SEG_OWNER varchar2(30 char))
 * ON COMMIT PRESERVE ROWS;
 * --ON COMMIT DELETE ROWS;
 *
 * CREATE SEQUENCE  andrey_seq  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10000
 *      ORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;
 *
 * CREATE OR REPLACE PROCEDURE SYS.FETCH_LOGMINER_VIEW(
 *  startScn IN NUMBER, endScn IN NUMBER) AS
 *  BEGIN
 *  INSERT INTO andrey_tabl_created SELECT ANDREY_SEQ.nextVal, SCN, SQL_REDO, OPERATION_CODE, TIMESTAMP, XID, CSF, TABLE_NAME, SEG_OWNER
 *     FROM V$LOGMNR_CONTENTS WHERE  OPERATION_CODE in (1,2,3,5)
 *     AND SEG_OWNER = 'SYS'
 *     AND SCN >= startScn AND SCN <  endScn
 *     OR OPERATION_CODE IN (1, 7,36);
 *  END;
 *  /

 *

So far I have no luck to reduce DB impact and keep the same throughput.

A

 

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/1458d88e-ec66-3bd7-d173-8db131a1cc46%40gmail.com.

--

Andrey Ignatenko
Staff Engineer
tel: +15102675105 /// mobile: /// email: andrey.i...@navis.com
Navis LLC /// 55 Harrison Street, Suite 600 Oakland CA 94607 UNITED STATES
www.navis.com




CONFIDENTIAL – Information in this email, including any attachments, is confidential, may be legally privileged and may contain proprietary information. If you are not the intended recipient, please immediately notify the sender by reply email and then delete this email message and any attachments. You should not copy, use or disclose to any other person this message or any attachments. Thank you.

[Milo van der Zee]

Hello Chris and Andrey,

I'm currently using an external Postgres database for caching the transactions and am now thinking of also using that postgres for intermediate storage of the logminer results. Only thing is that this is actually the same as using an additional Oracle engine purely for reading the archive log files...

Adding the log files in my case often takes minimal time in comparison to the actual querying. I checked this with a backlog of 1000 archive log files in comparison to just adding the redo log. Reading the view seems to consume one complete CPU, independent of how much must be returned. Optimizing the consumption of the data so that the archive only has to be read once could help lower the impact on the database. In my case where I have to mine hundreds of tables I have to do something.

Using a staging table in the same database with the same engine to me seems not like a solution. In that case I would just use an external database.

As Andrey indicates reading the view with the cursor seems to be the fastest way. But I'm not impressed by the speed. It does not seem to be limited by network bandwidth but rather by CPU of the database. And seems to be single threaded.

Offloading the archive logs and using multiple DB engines to read the files might make it more scalable but also probably very expensive. I'm no expert on Oracle licencing but most things are not free. Maybe reading archive logs is? I don't know.

And instead of offloading we could also just accept that the mining takes resources. Having an external DB engine running also takes CPUs. So why not just add those CPUs to the existing DB and let them be used by the mining process. I saw the graphs and IO does not seem to be impacted.

Having an external transaction/staging table helps with debugging. I keep the staging for a couple of hours and so can exactly see what is happening.

In summary:

- I'm trying to minimize having to read the archive and redo logs multiple times

- I might try to use the external database not only for the transactions but also as a staging area

- Storing transactions in an external DB helps reduce memory footprint of connector

- The external DB also helps during debugging and monitoring

I'll keep you informed about my findings. Disabling continuous mining by Oracle in v19+ has made things much more difficult... To say the least...

MAG,

Milo

Op do 11 mrt. 2021 om 20:26 schreef Chris Cranford <cran...@gmail.com>:

[Chris Cranford]

Milo -

I think whether you are mining 1 or 1000 logs, restricting the connector to 1 mining session is the right path.  If multiple connectors are defined to monitor differing tables, that would imply multiple mining sessions on the database and that would ultimately be no different than setting up multiple XStream outbound server processes with Golden Gate as you cannot share an Outbound Server across multiple XStream clients.

As for the staging table, I don't think it really ultimately matters where it resides.  That can be encapsulated behind a strategy pattern and simply be an implementation detail, perhaps its in the source database or a cache provider. 

In terms of read speed, are you bounding the SCN range by chance? 
What options are you providing to LogMiner when you start the session? 
How many rows are you typically iterating over per query?

As for offloading to another Oracle instance, that might not be doable.  In the past when I dealt with log shipping for failovers, the database was open but not mounted iirc.  This meant that only certain operations were permissible while the database was in auto-recovery mode apply changes from the primary.  This was with Oracle 8i and perhaps things have improved since then.  But I wouldn't be surprised if you couldn't start LogMiner on the secondary and that would make it a non-option.  It's something to investigate either way.

[Milo van der Zee]

Hello Chris,

I'm currently mining groups of tables and that does not lower the speed but does lower the impact on the database. So that is good. There somewhere is an optimum. Too much parallelism hurts. Maybe just one mining session and handling all tables at once might be th optimum. You could be right there.

This helps scalability because I then don't need a staging table. Just have one (or a limited count) mining session and handle the data in parallel. But I would love to use multiple CPUs of the Oracle database. A mining session runs on a single CPU and so is bad for scaling. That is the benefit of using multiple mining sessions. Then at least multiple CPUs start doing something. And hopefully something useful :)

I'm only bounding the SCN range at the start. I always read to the end.

I'm using the same options as Debezium.

val LOGMINER_START_OPTIONS = "" +
        "dbms_logmnr.SKIP_CORRUPTION + " +
        "dbms_logmnr.NO_SQL_DELIMITER + " +
        "dbms_logmnr.NO_ROWID_IN_STMT + " +
        "dbms_logmnr.DICT_FROM_ONLINE_CATALOG + " +
        (if (USE_CONTINUOUS_MINING) "dbms_logmnr.CONTINUOUS_MINE + dbms_logmnr.COMMITTED_DATA_ONLY + " else "") +
        "dbms_logmnr.STRING_LITERALS_IN_STMT"

I limit the number of rows to 500.000 or max 5 minutes. Whichever comes first. Most of the time the 500.000 mark.

I read somewhere that it is possible to access the same archive log files from another DB engine and use that engine to query the log files. So no offloading or shipping required. Just mount the same folders in read only and read the archive logs. We might investigate that solution.

Changes I did that seem to help:

What helped me the last days was that I now only start mining when the age of the most recent read record is at least one minute. That way I prevent rereading the same logs over and over again. I lose the near real time fashion of mining but this saves a lot of CPU. My DBA is very happy with this change.

And I'm now using a postgres DB for the administration and transaction cache and that also seems to work very nice. I have to optimize it a bit but debugging and following what is happening now is very easy.

MAG,

Milo

Op vr 12 mrt. 2021 om 16:04 schreef Chris Cranford <cran...@gmail.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/197a1483-99b1-e3c4-4c6c-c3a6204ec90f%40gmail.com.

[Martin Perez]

Just reviving this old thread to say that I liked the article :)

Well, not only that. We are actually running Debezium on integration. But don't be mislead by that, our "integration" servers are running 700 tx/s where 1/3 of those are writes. 2 massive Oracle instances with 300Gb of ram and 24 cores. 

Debezium overhead in such system is 2% - 2.5%... 

Can't wait to see it in production ( 10x more load ).

Cheers,

Martín

[Chris Cranford]

Martin -

If you don't mind elaborating a bit more, I'd be interested in a few more details about your systems:

    * How many logs and their respective sizes
    * How many log switches in an hour on average
    * Which mining strategy are you using?
    * You mentioned 2 instances, I assume 2 RAC nodes for the same database?

I want to try and understand what's the worse case load being put on LogMiner with such little overhead overhead. 

Thanks for the insight as always!  Much appreciated!
Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/f86f7fcc-ec84-4e1e-bbc0-f1df333f0bccn%40googlegroups.com.

[Milo van der Zee]

Hello Chris,

It would help me if you have some queries you want me to run. Maybe also helpful for others. With those queries we then get a standardized output as well :)

MAG,

Milo

Op wo 14 apr. 2021 om 19:49 schreef Chris Cranford <cran...@gmail.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/9bad86ea-f988-0c55-de27-57e368496443%40gmail.com.

[Martin Perez]

Sure thing Chris. Happy to help. 

My theory with the little overhead is that the server is very large. What we certainly observed is that the logminer process would almost take care of one of the CPUs. But this server has 24 cores so the impact might ends up not being that big. 

    * How many logs and their respective sizes

Good question. I'm not that familiar with the setup but if I query v$log I can see 1 active, 2 current, and another 17 in inactive state. All 1Gb files, which is likely too small for the Mb/s that we are logging:

Filetype Name	Reads: Data	Reqs per sec	Data per sec	Writes: Data	Reqs per sec	Data per sec	Small Read	Large Read
Data File	546.8G	413.92	31.051M	28.9G	148.42	1.642M	32.36us	2.68ms
Temp File	87.6G	23.73	4.977M	94.4G	25.73	5.362M	831.71us	1.30ms
Log File	50.2G	3.08	2.849M	13.4G	59.90	.761M	2.83ms	729.14ms
Archive Log	20.9G	0.33	1.186M	13.8G	0.78	.781M	35.17us	42.61ms

This very likely needs to be tuned.

    * How many log switches in an hour on average

On average we are seeing 5 log switches per hour. This again likely needs to be tuned. 

    * Which mining strategy are you using?

We are using the online catalog strategy. DBAs weren't very keen on using catalog on redo mode as there are several Oracle articles warning about it. 

I am going to get a more recent AWR report anyways as that is two months old and we have upgraded Debezium a few times since. 

Martín