Forum Stats

  • 3,827,870 Users
  • 2,260,836 Discussions
  • 7,897,400 Comments

Discussions

Minimal Supplemental Logging IMPROVES Performance

658997
658997 Member Posts: 2
edited Sep 10, 2008 6:05PM in Streams
I have a rather unusual question.

I have been examining the use of Asynchronous Autolog Change Data Capture. Our main database system is under load, and we want to install a secondary system to take on the reporting duties. We need to extract the daily changes with absolute minimal impact on the source system. For this reason, AACDC seems the best approach.

I have been attempting to measure the performance difference for bulk updates in three cases.

a) No Supplemental Logging on Source.
b) Minimal Supplemental Logging on Source.
c) Logging ALL columns ALWAYS on our source table.

The shock result is this. Enabling Minimal Supplemental Logging actually makes the updates FASTER and makes the redo/archive logs SMALLER.

I inserted 500k rows into a table. Each batch updated 30k rows. A run consisted of 10 batches, with a commit and then a 5 second pause between each one.

To disable supplemental logging I used:
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

To enable it with minimal logging, I used:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

To enable it always I used:
ALTER TABLE my_table
ADD SUPPLEMENTAL LOG GROUP my_log_group
(my_col1, my_col2, ...) ALWAYS;

I ran each test several times in a row, repeating until the figures stabilized (which they did very quickly) and until I got three consistent tests in a row. I measured the total size of the archive logs generated by a test (using ALTER SYSTEM SWITCH LOGFILE; to force a flush). I had plenty of decent sized redo logs, and the alert log showed no problems there.

For each scenario, I obtained three consistent runs (each of 10 batch updates). Here are the numbers.

NO SUPPLEMENTAL LOGGING
Average Batch Update Time (update + commit 50k) = 8.03 seconds
Total Size of Archive Logs Generated = 118Mb

MINIMAL SUPPLEMENTAL LOGGING
Average Batch Update Time (update + commit 50k) = 7.17 seconds
Total Size of Archive Logs Generated = 87Mb

ALL SUPPLEMENTAL LOGGING
Average Batch Update Time (update + commit 50k) = 18.93 seconds
Total Size of Archive Logs Generated = 183Mb

It's not at all surprising that the ALL LOGGING generates much larger log files and us much slower, but it's very surprising indeed that the MINIMAL logging is faster and smaller than the NO SUPPLEMENTAL.

Can anybody throw some light onto this?

I would mention that this table is unusual because it has NO primary key, (If it did have a primary key, I would have considered materialized views, but it doesn't, and tests show that adding a sequence-driven primary key and a materialized view log drops insert performance by 15%).
Tagged:

Answers

  • user535365
    user535365 Member Posts: 72 Blue Ribbon
    That is interesting test about your supp log testing.

    Another option for your reporting purpose beside the use of Asynchronous Autolog Change Data Capture is the Downward Streams approach.
    If you have the resource then this approach will have minimal impact on your Source instance, and the reporting instance will be mainly use for reporting purpose.

    Setup look like this:

    db1 (source) -----> db2 (downward instance, where Capture/Propogation live) ---> db3 (reporting instance, where Apply process live, and your reporting user access.)

    This setup first reduce the load on db1 (if report is lengthly),
    db2 instance function as a gate keeper, use very little resource..
    db3 mainly config to be use for reporting purpose...

    Hope this help...
  • 658997
    658997 Member Posts: 2
    Hiya. Thanks for that suggestion, it was something we considered, though some of the other aspects of the system (that I didn't mention) make it a rather challenging.

    The target database is not planned as an identical copy of the source. For some tables the target will keep some old data even when it is deleted from the source database. For other tables we will discard old data even when it is still present on the source database.

    We also need to target accept data from various secondary source databases (via Materialized Views, since they only have Standard Edition licenses) and merge them together. Yeah, none of this stops us having an intermediate logical/physical database copy and spooling to a third database - however given that the supplemental logging impact seems very low on the source DB, I'm hoping that we can live without the spooling database.

    And that's the question here. My testing was to examine the impact of the supplemental logging. And the impact seems to be negative. I.e. it seems to improve the performance of the source database! I really want to understand and validate this before proceeding.
This discussion has been closed.