Skip to main content
Solved

DDB access - performance transaction logs - SQL - Oracle

Interestingly enough there are 2 conflicting theories regarding SQL and ORA transaction log deduplication and pruning question. 

There is one from CV support engineer: 

 

“We discussed high Q&I times and any way to reduce the load on the DDB.

We both acknowledged that SQL Transaction Logs are not deduplicated, however you were curious if the T-Log backups communicated in anyway to the DDB which may impose any, albeit minimal, load or processing on the DDB.

I was able to confirm that SQL Transaction Log backups do not communicate with the Deduplication process and therefore will not have any effect on Q&I times:

 

https://documentation.commvault.com/11.24/expert/12434_deduplication_support.html””

…………………………...

But but but, other CV engineer says if the storage policy used for trans logs is deduplicated, CV is not smart enough and will try to deduplicate anyway. It will also involve pruning at the end of the cycle. And when I look at the jobs from deduplication engine level, I see my transaction log job there. If it is there, obviously it would require pruning, will it not? And another interesting indicator, while running the trans log job, average throughput line shows DDB look up eats up 95%+ of time. Given these few issues, I am thinking second engineer is more likely right.

 

Which engineer is right? I have V11.20

I have terrible DDB performance due to slow SSD drives, and I am hunting for any even minimal DDB performance improvements venues, without sacrificing too much storage.

Do we have someone very familiar with DDB details?

 

The theory is that later, up to date  Commvault versions can handle transaction logs backups in the deduplicated storage policy without ANY sacrifice of precious DDB resources. Thus putting trans logs into dedicated non deduplicated storage policy is a waste of administrative time if retention is the same. Which theory is correct?

and after looking in more detail, differential and full backups show that DDB access value, trans logs does not seem to show it.

Yes we do enable them from time to time. Definitely have an impact.

Have you looked into setting a Data Pruning Blackout window on the Media Agent associated with the Storage Library:

 

Can you define “do not run” time intervals daily, to prevent pruning from running during this time. So you could set this during the backup period to ensure pruning doesn’t occur during this time. 

 

See the documentation here - https://documentation.commvault.com/11.25/expert/6614_setting_operation_rules_in_blackout_window.html

Lately like today I do not see on transaction logs showing that DDB figures, maybe I was not picky enough before, but again, looks like you are correct, dead end here for me on DDB gains due to trans logs.

Oh I had meant which agent type, SQL Oracle, etc - but you mentioned SQL.

 

So it’s definitely very odd that these jobs run slowly and show 99% DDB lookup - do you see that on all tlog jobs or just occurring intermittently or maybe only occurring on a specific subclient?

When the jobs complete, what does the size of application/data written look like compared to other tlog and full jobs in the subclient history? Does it look like a tlog-sized backup or is it look like it’s backing up more? 

I 100% agree with compression statement. As far as DDB, Can you clarify what are you asking here “What kind of iDA did you see the load showing DDB lookup?””

I am only looking at the job and I do not account for trans log job making a conversion to full backup, likely that could be my issue, but one way or the other my sql backup trans logs and regular backups were on the different storage policies(deduped and trans logs not deduped), once we merged into one SP, problems started when pruning got involved, right after the retention end, 30 days in my case. Maybe pruning is so resource intensive that more than likely it can cause trouble by itself on a regular backups alone. Yes when I stop pruning, by extending retention to 31-32 days, environment at least partially stabilizes. Thus I wanted to make sure SQL trans logs are not the contributing factor. One way or the other, when I look at the normal long running trans log, it shows in my insanely slow DDB environment, as 99% DDB utilization as a culprit. Why would I see that.

We 100% know we need new SSD drives, but we need to survive next 30 days or so.

 

Hi @Vitas,

I can confirm 100% that a tlog job will not engage the DDB for any lookup or dedup. In the vast majority of cases we very good space savings with Compression alone for transaction logs. 

It’s ok if you see the job listed under the View → Jobs at the DDB level, all jobs associated with that Storage Policy will have affinity to that DDB, we just will not leverage the DDB when backing up.

The software definitely is smart enough to detect a tlog job and it will not attempt to dedup them. 

I ran some test MSSQL tlogs backups in my lab environment and I am not seeing any of the load statistics on the job after or while it was running - just the gb/hr speed. What kind of iDA did you see the load showing DDB lookup?

 

 

 

That is my understanding too, but I get pushback from Commvault and my superiors that this is old practice. Thus I have opened this thread, to get someone really who knows ins and outs of DDB issues to comment.

Have you attempted running transaction logs and database backups to two separate policies which is best practice for databases with transaction logs to be protected?  You would create a non deduplicated policy to send the T-Logs to.  

Thanks for the reply, but how these 2 issues can be explained:

 

And when I look at the jobs from deduplication engine level, I see my transaction log job there. If it is there, obviously it would require pruning, will it not? And another interesting indicator, while running the trans log job, average throughput line shows DDB look up eats up 95%+ of time.

 

Commvault deduplication will not deduplicate transaction logs even when they are being backed up to a deduplicated storage policy.

Terms & ConditionsCookie settings