Haisley - Backup and Recovery Optimization by rocker12

Backup and Recovery Optimization

Objectives •

Provide a short introduction to Recovery Manager (RMAN)

•

Explain and demonstrate factors that influence speed of: – Backups – Restorations – Recoveries

•

Give you some ideas at what to look at to make backup and recovery faster

What is RMAN? • •

Introduced in 8.0

•

Built into RDBMS kernel so can take advantage of features (e.g. block checking)

•

Can back up datafiles, controlfile, archivelogs and SPFILE

•

Offers image copy or backupset

Allows DBA to manage backup and recovery operations with ease

– Image copy: byte for byte copy – Backupset: multiplexed files together into proprietary format 3

Incremental Backups Day of the week Sun

Mon

Tues

Wed

Thr

Fri

Sat

Incremental backup level

Differential

Day of the week Sun

Mon

Tues

Wed

Thr

Fri

Sat

Incremental backup level

Cumulative

Differential vs Cumulative •

Backup speeds: Backup#

Type

Level

Base

Diff

#blocks

Time (secs)

CPU (secs)

778112

626

227.20

42375

312

82.93

Diff

42370

312

82.65

Diff

42369

312

82.45

Base

778112

628

226.09

Cumu

42371

314

80.61

Cumu

49605

315

83.70

Cumu

60176

321

85.33

Differential vs Cumulative •

•

Restore speeds: Type

#Backup Sets restored

Base level 0

626.67

210.85

Differential

98.67

23.00

Base level 0

629.33

209.21

Cumulative

43.00

11.05

Time (secs)

CPU (secs)

Backup and Restore Performance •

Backup & Restore times can be influenced by: – Channel configuration – Size of memory buffers (read & write) – Speed of backup devices – Amount of data being backed up – Amount of block checking features enabled – Use of compression

Channel Configuration •

Match up the number of channels to each backup device – Manually allocate channels – Use automatic channel parallelism

•

Avoid Media Management Layer (MML) multiplexing of backup sets – Increase restore times

•

Channel Configuration â&#x20AC;˘

Reducing filesperset can decrease speed of single file restores: Filesperset

BS Size (blks)

Restored file (blks)

Time (secs)

CPU (secs)

702320

97727

132

39.42

658221

97727

110

36.92

132773

97727

29.92

97730

97727

25.62

Read and Write Memory Buffers

Datafiles input Buffers (4 per datafile)

Output Buffers (4 per channel)

Backup Device

Size of Read Buffers •

Allocated according to MAXOPENFILES channel parameter: MAXOPENFILES

MAXOPENFILES ≤ 4 4 > MAXOPENFILES ≤ 8 MAXOPENFILES > 8

• 11

Buffer Size Each buffer = 1Mb, total buffer size for channel is up to 16Mb Each buffer = 512Kb, total buffer size for channel is up to 16Mb. Numbers of buffers per file depends on number of files Each buffer = 128Kb, 4 buffers per file, so each file will have 512Kb buffer

Size of Read Buffers •

Read buffer allocation for backups: MAXOPENFILES

•

Buffer Size (Kb)

#Buffers per file

Total Buffer size (Mb)

1024

512

128

Size of Write Buffers •

Allocates four buffers per channel – Disk = 1Mb per buffer – SBT = 256Kb per buffer

•

SBT is smaller due to slower speed of tape devices

•

Can see increased performance when increasing size of tape buffers… Total buffer size (Kb)

I/O Count

I/O Time (secs)

128

60564

617.4

1024 (default)

7571

595.9

2048

3786

505.3

Where is buffer memory allocated from? •

PGA if not using I/O slaves (use async I/O) – tape_asynch_io – disk_asynch_io

•

Shared Pool if using I/O slaves (use if OS does not support async I/O) – backup_tape_io_slaves – dbwr_io_slaves

• 14

Large Pool if size > 0 and using I/O slaves

Speed of Backup Devices •

Maximum speed of backup: min(disk read Mb/s, tape write Mb/s)

•

Monitor v$backup_async/sync_io for effective_bytes_per_second where input is output or input – If transfer rate slower than device is capable of, look at OS level data, CPU statistics, MML settings (compression?), device settings (block size)

•

Can slow down speed of backup to reduce loading on I/O system: RMAN> configure channel device type sbt rate=1M;

Amount of data being backed up •

Put static data into Read-Only tablespace and backup one time only – Make sure backup not purged from MML catalog

•

Use differential incrementals and monitor v$backup_datafile to identify files not changing frequently – Reduce their backup frequency

•

Avoid using datafiles with large amounts of freespace – The whole datafile is scanned for a backup

Block Change Tracking • •

Fast Incremental backups introduced in 10g

• •

Size of tracking file ~1/30,000 size of database

•

Performance gain for backups make this bearable:

Uses change tracking file to store bitmaps representing ranges of blocks in datafiles Overhead on database performance ~3% (in my TPCC tests)

Fast Incrementals?

#Blocks in DB

#Blocks read

#Blocks in backup

Time (secs)

404160

36567

156

Yes

404160

72832

37215

Amount of Block Checking Features Enabled •

Each type of block checking will increase time and CPU usage for backup and restoration: –

Head and Tail sanity check – Makes sure key structures in head match tail

–

Block Checksums – Calculated and compared with existing

checksum –

Logical structure checks – Checks various block structures for consistency

•

Tests showed time for database backup increased ~1% and CPU usage by ~8% –

Backup Compression • •

Backupset compression introduced in 10g Can reduce size of backupset by 80-90% – Saves space on backup media space – Reduces amount of network traffic if backup device not local

•

Increases CPU and time (as expected) for backup and restore

•

Do NOT use along with MML compression – Time both types of compression and use most suitable

Recovery Performance

•

Recovery times can be influenced by: – Number of archivelogs/incrementals being applied – Number of datafiles needing recovery – If archivelogs available on disk – If using parallel recovery – General database performance

Number of archivelogs/incrementals being applied •

RMAN will choose to use incrementals over archivelogs – My tests showed restoring the incremental was ~17 times quicker than applying 20 archivelogs – Mileage will vary depending on backup / restore speeds as previously discussed

•

Previous slide showed cumulative being faster than differentials

•

Number of datafiles needing recovery •

For each datablock that needs recovery, it first needs to be read into the buffer cache and then written back to disk by DBWR after redo is applied to it

•

By reducing the number of files that are recovered, reduce overall work in the database = speed up recovery – Only restore and recover the files that NEED recovering

• 22

Block Media Recovery (BMR) •

RMAN will restore and apply recovery to the specified blocks only, leaving rest of datafile in tact for normal use

•

Significant increase in recovery time over the whole datafile: Datafile recovery time (secs)

#Corrupt Blocks

• 23

BMR Time (secs)

941

145

925

155

991

937

219

5000

922

616

10000

938

1156

Archiveslogs available on disk? •

Avoid the RMAN restore times for archivelogs and keep n days worth on disk – Depends on incremental strategy – Depends on available disk space

•

Backup most recent archivelogs to disk and then to tape at a later time – Take a backup of a backup (from 9i onwards)

Parallel Recovery •

By default Oracle will use a single process to carry out recovery, unless using parallel_automatic_tuning – Oracle will decide if best to use parallel recovery and how many slave processes

• •

Single coordinator process reads the archivelogs

•

Will increase CPU usage and need for DBWR to perform well

•

Watch for waits on ‘PX Deq’ events

Reading of datablocks and applying redo is split up amongst slave processes, each working on a range of blocks

General Database Performance •

Recovery happens within the database, so a badly performing database will not help with recovery times

•

Areas to look for improvement: – I/O → read and write intensive – DBWR performance → look for ‘free buffer waits’ – use async. IO or DBWR slaves – CPU → make sure it doesn’t become starved during recovery – parallelism won’t help you!

Helpful views

•

v$session_longops → shows currently running backup, restore, recovery with RMAN

•

v$backup_async/sync_io → shows RMAN performance information

• •

v$session_wait → session wait information v$backup_set, v$backup_piece, v$backup_datafile etc. → shows sizing information for backups

Summary •

Explained factors that influence speed of: – Backups – Restorations – Recoveries

•

Gave you something to think about when looking at backup, restore and recovery time windows

•

Make sure you test any alterations with production volume FIRST!