Online backup performance

Database
Enthusiast

Online backup performance

Generally speaking, can anyone tell me if online backups perform better than regular backups?  In other words, if I am backing up a 500gb table, which method would back up quicker, online or regular?

Thanks,

Joe

8 REPLIES
Teradata Employee

Re: Online backup performance

Online backups are slower than regular backups. During online backup DB stays available and in regular/offline there is a DB outage. Hence it depends on your needs and environment if you can afford DB outage or not.

HTH!

Enthusiast

Re: Online backup performance

Think about the resources :) and priorities during online backups and regular. For regular one priority is given. In almost all DBs that I know Online backups are slower than regular .

Cheers,

Enthusiast

Re: Online backup performance

Hi Everybody,

I have few questions around online backups.

Sol& Tools: Currently we are planning to use Teradata DSA(TDSA) solution with NetBackup  tool. The amount of data to be backed up is currently around say 10TB. Backing to disks not the tapes

** Abbreviations:

(TDSA)- Teradata Data stream architecture solution.

TDATA-Teradata

I knew some clients run online backups as they run ETL most of the time, What said is the explicit feature of the online backup. That being said, I am not clear of 2-3% change guideline (if I am not wrong where it explicitly says while running online backups there should not be more than 2-3% change at the whole database level)** Correct me if I am wrong/not exact**

DID ANYBODY HAD SEEN BACKUP FAILED OR ETL FAILED BCZ OF SPOOL SPACE EXCEEDED.” considering only 20-30% of total space is dbc spool space

As I understood it, while running online backup TDSA read the whole and create the check point and start writing to the disk solution, while at the same time users can do read and write (R&W) operations on the objects.

                The question here, 1. is  DSA copying all the objects and placing them in the spool?(I hope this is not what goes there, but if you say yes, what for 10TB backup assuming it is a full backup, how much space it take up form DBC spool?), OR

2. is Teradata keep the updates of any kind in a temporary table(which of course should go to spool)  which one happens and how 2-3% change thumb rule effects the spool? Or what is this guideline warning us towards? If you say yes for this 2nd after running any ETL during backup and if we query the object will TDATA gives us new updated values?

                Scenario: If the table name is “SMILING” before backup run and while running backup table name is updated to “TERRIFIED”. Will querying the table with TERRIFIED return the values? If you say we don’t rename tables so often, if the update is at row level will we get the updated values while backup is still is in running status.

3. AS TERADATA already had the data compressed while storing on it’s disk, is it true that TDSA still compresses the data while backing it to disk backups.(if it recompresses what is the expected ratio?)

**I appreciate if you could attach any standard documents as a reference for any comments**

Hoping for a good advice, Please do ask me if I forgot to mention any details.

Thanks in advance. 

Teradata Employee

Re: Online backup performance

1. DSA (online or not) does not copy objects to spool.

2. Online backup journals changes to a PermSpace subtable for each table being backed up. Search the documentation for "online archive logging". This journal is appended to the backup of the table, and used to automatically "roll back" to the initial consistency point (start of the backup) during a restore. Online backup holds an ACCESS lock on the object and RENAME requires EXCLUSIVE lock, so a RENAME would be blocked until the backup had completed. Queries are able to access updated values during online backup.

3. With DSA, any compressed data blocks are sent to the storage device as-is (contrasted with ARC which decompresses the data blocks before sending to the client). The storage subsystem may perform some additional compression or de-duplication.

kvz
Enthusiast

Re: Online backup performance

We are getting blockings even in online backup. Blocking gets clear once the backup of that DB get completed.

Locks will be applied on DB level or Table level during online backups?

what is the solution to avoid this situation?

Thanks,

KVZ

Enthusiast

Re: Online backup performance

DSA offers no advantage in terminating the online logging and when the backup fails the database perm space will fillup and subsequent load jobs or updates may fail.

Enthusiast

Re: Online backup performance

Hi Fred,

I have two questions here :

  • when you say exclusive locks are not permitted on a table being online backed up, i am sure if we try to drop and recreate a table while online backup is going on, it will also not be allowed ?
  • Below script is from the manual. Does it mean that if Fload/Mload is currently active on a table and online archive is requested, we will get this error ? If the loading is over, the backup can continue ?

    When Online Archive is requested on a table that is involved in a load operation (by Fastload

    or Multiload), Teradata ARC displays the following DBS warning message:

    *** Warning 9136: Online archive logging cannot be enabled on one or more

    tables.

Teradata Employee

Re: Online backup performance

Correct. DROP also requires an EXCLUSIVE lock, so would wait for the backup to release the ACCESS lock.

 

If the load job is actually running, it will hold a table WRITE lock, which will block the online archive from obtaining the READ lock for the consistency point. The 9136 warning message indicates that a load started for the table but did not complete successfully (and implicitly, is not currently running either).

 

Error code 9136 is returned by the DBS for one of the following conditions:

The table is a MultiLoad or FastLoad target table that was aborted in the apply phase

The table is an aborted multiload work table

When Teradata ARC receives the 9136 error code, Online Logging is not enabled on the

table and when Teradata ARC attempts to archive the table, the table will be skipped.

 

Although the table is skipped, the table header will still be archived but no data is archived.

When the table is restored from the archive file, only the table header is restored, resulting

in an empty table after the restore or copy operation.