Data Life Cycle Management for Oracle @ CERN with partitioning - - PowerPoint PPT Presentation

▶
data life cycle management for oracle cern with
SMART_READER_LITE
LIVE PREVIEW

Data Life Cycle Management for Oracle @ CERN with partitioning - - PowerPoint PPT Presentation

Feb 04, 2024 109 likes •661 views

Data Life Cycle Management for Oracle @ CERN with partitioning Oracle @ CERN with partitioning, compression, and archive. Luca Canali, CERN Orcan Conference, Stockholm, May 2010 y Outline Physics DB Services at CERN y Motivations for

slide-1
SLIDE 1

Data Life Cycle Management for Oracle @ CERN with partitioning Oracle @ CERN with partitioning, compression, and archive.

Luca Canali, CERN

Orcan Conference, Stockholm, May 2010 y

slide-2
SLIDE 2

Outline

  • Physics DB Services at CERN

y

  • Motivations for Data Life Cycle Management

activities

  • Techniques used
  • Sharing our experience with examples

g p p

2 Data Life Cycle Management @ CERN, Luca Canali

slide-3
SLIDE 3

LHC data

Balloon ( 3 0 Km ) CD stack w ith

LHC d t d t b t

CD stack w ith 1 year LHC data! ( ~ 2 0 Km )

LHC data correspond to about 20 million CDs each year!

Concorde ( 1 5 Km )

RDBMS play a key role for p y y the analysis of LHC data

  • Mt. Blanc

( 4 .8 Km ) 3 Data Life Cycle Management @ CERN, Luca Canali

slide-4
SLIDE 4

Databases and LHC

  • Relational DBs play today a key role in the LHC

production chains production chains

  • online acquisition, offline production, data

(re)processing, data distribution, analysis

  • SCADA, conditions, geometry, alignment, calibration, file

bookkeeping, file transfers, etc..

  • Grid Infrastructure and Operation services

p

  • Monitoring, Dashboards, User-role management, ..
  • Data Management Services

Fil t l fil t f d t t

  • File catalogues, file transfers and storage management, …
  • Metadata and transaction processing for custom tape

storage system of physics data g y p y

  • Accelerator logging and monitoring systems

4 Data Life Cycle Management @ CERN, Luca Canali

slide-5
SLIDE 5

The RDBMS Workload

  • Most applications are of OLTP type
  • Oracle used mainly for its transactional engine
  • Oracle used mainly for its transactional engine
  • Concurrency and multi-user environment
  • Index based access paths and NL joins very

Index based access paths and NL joins very important

  • Sequential workload / DW type queries are not

Sequential workload / DW type queries are not the main use case

  • Another way of looking at it:

y g

  • RDBMS stores ‘metadata’

5 Data Life Cycle Management @ CERN, Luca Canali

slide-6
SLIDE 6

Data Life Cycle Management @ CERN, Luca Canali 6

slide-7
SLIDE 7

CERN Databases in Numbers

  • CERN databases services – global numbers
  • Global users community of several thousand users
  • ~ 100 Oracle RAC database clusters (2 – 6 nodes)

Currently over 3300 disk spindles providing more than

  • Currently over 3300 disk spindles providing more than

1PB raw disk space (NAS and SAN)

  • Some notable DBs at CERN

Some notable DBs at CERN

  • Experiment databases – 13 production databases
  • Currently between 1 and 9 TB in size
  • Expected growth between 1 and 19 TB / year
  • LHC accelerator logging database (ACCLOG) – ~30 TB
  • Expected growth up to 30 TB / year
  • Expected growth up to 30 TB / year
  • ... Several more DBs on the range 1-2 TB

Data Life Cycle Management @ CERN, Luca Canali 7

slide-8
SLIDE 8

Data Lifecycle Management

  • Motivated by large data volumes produced by

C LHC experiments

  • Large amounts of data will be collected and stored for

several years several years

  • Different requirements on performance and SLA can
  • ften be found for ‘current’ and ‘old’ data sets
  • Proactively attack ‘issues’ of databases that

grow ‘too large’

  • Administration
  • Performance

C

  • Cost

8 Data Life Cycle Management @ CERN, Luca Canali

slide-9
SLIDE 9

Digression

  • What is a VLDB?

“… VLDB means bigger than you are comfortable managing” (Cary Millsap)

  • There is a part of it that has to do with the share DB size

The threshold seems to be moving with time and technology

  • The threshold seems to be moving with time and technology

progress

  • Not too long ago 1TB Oracle DBs were classes as high end..

9 Data Life Cycle Management @ CERN, Luca Canali

slide-10
SLIDE 10

Administration of VLDB

  • VLDB and consolidation advantages:
  • Data consolidation, application consolidation

pp

  • Some data sets are very large by nature
  • VLDB and consolidation disadvantages
  • DB-wide operations can become slow (backup,

stats gathering, full scan of largest tables) D d i b t li ti

  • Dependencies between applications
  • Task

Identif ho to get ad antages of consolidation

  • Identify how to get advantages of consolidation

coexist with the idea of having a ‘lean’ DBs for manageability and performance

10 Data Life Cycle Management @ CERN, Luca Canali

slide-11
SLIDE 11

Attack problem from multiple sides

  • No out of the box solutions available
  • Attack the problem where possible
  • Attack the problem where possible
  • Applications
  • Oracle and DB features
  • Oracle and DB features
  • HW architecture
  • Application layer:

Application layer:

  • focus on discussing with developers
  • build life cycle concepts in the applications

y p pp

  • Oracle layer
  • Leverage partitioning and compression
  • Movement of data to an external ‘archival DB’

11 Data Life Cycle Management @ CERN, Luca Canali

slide-12
SLIDE 12

Commodity HW

  • Dual-CPU quad-core 2950 DELL servers 16GB memory
  • Dual-CPU quad-core 2950 DELL servers, 16GB memory,

Intel 5400-series “Harpertown”; 2.33GHz clock

  • Dual power supplies, mirrored local disks, 4 NIC (2 private/

2 public), dual HBAs, “RAID 1+0 like” with ASM

12 Data Life Cycle Management @ CERN, Luca Canali

slide-13
SLIDE 13

High capacity storage, resiliency and low cost y

  • Low cost HA storage with ASM
  • Latest HW acquisition:

Latest HW acquisition:

  • 492 disks of 2TB each -> almost 1 PB of raw storage
  • SATA disk for price/perf and high capacity

p p g p y

  • ASM can take care mirroring
  • Destroking can be used (external part for data)

Destroking can be used (external part for data)

DATA DG1 DATA DG1

Failgroup4 Failgroup4 Failgroup2 Failgroup2 Failgroup3 Failgroup3 Failgroup1 Failgroup1

_ RECO_DG1 RECO_DG1

Failgroup4 Failgroup4 Failgroup2 Failgroup2 Failgroup3 Failgroup3 Failgroup1 Failgroup1

13 Data Life Cycle Management @ CERN, Luca Canali

slide-14
SLIDE 14

Backup challenges

  • Backup/recovery over LAN becoming problem with

databases exceeding tens of TB databases exceeding tens of TB

  • Days required to complete backup or recovery
  • Some storage managers support so-called LAN-free backup
  • Backup data flows to tape drives directly over SAN
  • Backup data flows to tape drives directly over SAN
  • Media management server used only to register backups
  • Very good performance observed during tests (FC saturation, e.g. 400MB/s)
  • Alternative – using 10Gb Ethernet
  • Alternative – using 10Gb Ethernet

Backup data

Metadata

1GbE 1GbE Media Manager Server

FC FC FC FC

Data Life Cycle Management @ CERN, Luca Canali 14 Database Server Tape drives

slide-15
SLIDE 15

Application Layer

  • Data Life Cycle policies cannot be easily

implemented from the DBA side only

  • We make sure to discuss with application

developers and application owners

  • To reduced amount of data produced
  • To allow for DB structure that can more easily

ll hi i allow archiving

  • Define data availability agreements for online

data and archive

  • Joint sessions to identify how to leverage Oracle

features

15 Data Life Cycle Management @ CERN, Luca Canali

slide-16
SLIDE 16

Use Case: Transactional application with historical data application with historical data

  • Data has an active part (high DML activity)
  • Older data is made read-only (or read-mostly)
  • As data ages, becomes less and less used

Data Life Cycle Management @ CERN, Luca Canali 16

slide-17
SLIDE 17

Active Dataset

  • Many Physics applications are structured as

write-once read-many y

  • At a given time typically only a subset of data is

actively used N t l ti i ti h i l t f

  • Natural optimization: having large amounts of

data that are set read only

  • Can be used to simplify administration

Can be used to simplify administration

  • Replication and backup can profit too
  • Problem
  • Not all app are ready for this type of optimization

17 Data Life Cycle Management @ CERN, Luca Canali

slide-18
SLIDE 18

Time-Organized data

  • Several key database tables are naturally

time organized g

  • this leads to range-based partitioning
  • Other solution is ‘manual split’ i.e. multiple

i il t bl i diff t h similar tables in different schemas

  • Advantages

P titi b t t d t t bl f

  • Partitions can be treated as separate tables for

bulk operations

  • Full scan operation, if they happen, do not span

u sca

  • pe at o ,

t ey appe , do

  • t spa

all tables

18 Data Life Cycle Management @ CERN, Luca Canali

slide-19
SLIDE 19

Techniques: Oracle Partitioning

  • Range partitioning on timestamp attributes
  • Range partitioning on timestamp attributes
  • Note: unique indexes and local partitioning

Partitioning key must be part of index

  • Partitioning key must be part of index
  • Partitions for ‘future time ranges’
  • Currently pre allocated
  • Currently pre-allocated
  • 11g interval partitions will come handy
  • 11g reference partitioning

11g reference partitioning

  • Not used yet although interesting, will be tested

19 Data Life Cycle Management @ CERN, Luca Canali

slide-20
SLIDE 20

Partitioning Issues

  • Index strategy
  • Indexes need to be local partitioned in the ideal

p case to fully make use of ‘partition isolation’

  • Not always possible, depends on application

S i l b l i i i b f

  • Sometimes global partitioning better for

performance

  • Data movement issues
  • Data movement issues
  • Using ‘Transportable tablespace’ for single

partitions is not straightforward g

  • Query tuning
  • App owners and DBAs need to make sure there are

no ‘stray queries’ that run over multiple partitions by mistake

20 Data Life Cycle Management @ CERN, Luca Canali

slide-21
SLIDE 21

‘Manual’ Partitioning

  • Range partitioning obtained by creating

multiple schemas and sets of tables

  • Flexible, does not require partitioning option
  • And is not subject to partitioning limitations

M k i t th li ti l

  • More work goes into the application layer
  • Application needs to keep track of ‘catalog’ of partitions
  • CERN Production examples
  • PVSS (commercial SCADA system)

SS (co e c a SC syste )

  • COMPASS (custom development at CERN)

21 Data Life Cycle Management @ CERN, Luca Canali

slide-22
SLIDE 22

Details of PVSS

  • Main ‘event tables’ are monitored to stay

within a configurable maximum size g

  • A new table is created after the size threshold is

reached PVSS t d t k t k f t d

  • PVSS metadata keep track of current and

historical tables

  • Additional partitioning by list on sys id for insert

Additional partitioning by list on sys_id for insert performance

  • Historical data can be post-processed with

compression

  • Current size (Atlas offline): 4.6 TB

22 Data Life Cycle Management @ CERN, Luca Canali

slide-23
SLIDE 23

Details of COMPASS

  • Each week of data is a separate table

I t h t

  • In a separate schema too
  • DST and RAW also separated
  • IOT table used

IOT table used

  • Up to 4.6 billion rows per table
  • Key compression used for IOT
  • Current size: ~10 TB

23 Data Life Cycle Management @ CERN, Luca Canali

slide-24
SLIDE 24

Details for PANDA Archive migration to Oracle migration to Oracle

  • ‘Jobsarchived’ table consolidates many

smaller tables previously in MySQL smaller tables previously in MySQL

  • historical data coming from production
  • Range partitioning by time
  • Range partitioning by time
  • One partition per month
  • One tablespace per year

p p y

  • Performance
  • Application modified to add time range in all

g queries -> to use partition pruning

  • All indexes are local

C i h i i d

  • Compromise change-> unique index on

panda_id changed to be non-unique

24 Data Life Cycle Management @ CERN, Luca Canali

slide-25
SLIDE 25

Details of MDT DCS

  • DATA tables
  • Contain live and historical data
  • Range partitioned
  • 1 partition per month
  • Additional table compression for historical data
  • Indexes
  • primary key is local partitioned (prefixed with

time column)

  • Other indexes are local too
  • Other indexes are local too
  • Key compression on indexes
  • Current size: ~300GB

Current size: 300GB

25 Data Life Cycle Management @ CERN, Luca Canali

slide-26
SLIDE 26

Details of LCG SAME and GRIDVIEW

  • Critical tables are partitioned
  • Range partitioned using timestamp

g p g p

  • 1 partition per month
  • Contain live and historical data
  • All indexes are local
  • LCG_SAME makes use of partitioned LOBs

C t i

  • Current size
  • 1.7 TB for LCG_SAME and 1.2 TB for

GRIDVIEW GRIDVIEW

26 Data Life Cycle Management @ CERN, Luca Canali

slide-27
SLIDE 27

Techniques: Schema Reorganization Reorganization

  • When downtime of part of the application

can be afforded can be afforded

  • Alter table move (or CTAS)
  • Alter index rebuild
  • Alter index rebuild
  • More sophisticated
  • DBMS REDEFINITION

DBMS_REDEFINITION

  • Allows to reorganization of tables online (add

partitioning for example)

  • Users experience, works well but it has let us

down a couple of times in presence of high transaction rates transaction rates

  • hard to debug and test ahead

27 Data Life Cycle Management @ CERN, Luca Canali

slide-28
SLIDE 28

Use Case: Archive DB

  • Move data from production to a

separate archive DB separate archive DB

  • Cost reduction: archive DB is sized

for capacity instead of IOPS for capacity instead of IOPS

  • Maintenance: reduces impact of production DB

growth

  • Operations: archive DB is less critical for HA

than production

Data Life Cycle Management @ CERN, Luca Canali 28

slide-29
SLIDE 29

Archive DB service

  • Proposal of archive DB
  • First presented Q4 2008, production Q4 2009

p p

  • It’s an additional DB service to archive pieces of

applications D i hi DB i l f d l kl d

  • Data in archive DB mainly for read-only workload

(with a lower performance)

  • How to move data to archive and possible
  • How to move data to archive and possible

back in case of restore?

  • Most details depend on application owner
  • st deta s depe d o

app cat o

  • e
  • It’s complicated by referential constraints
  • Area of custom work, still in progress

29 Data Life Cycle Management @ CERN, Luca Canali

slide-30
SLIDE 30

Archive DB in Practice

  • Detach ‘old’ partitions form prod and load them on the

archive DB

  • Can use partition exchange to table

Can use partition exchange to table

  • Also transportable tablespace is a tool that can help
  • Archive DB post-move jobs can implement compression for

archive (exadata 11gR2) archive (exadata 11gR2)

  • Post-move jobs may be implemented to drop indexes
  • Difficult point:
  • One needs to move a consistent set of data
  • Applications need to be developed to support this move
  • Access to data of archive need to be validated with application

Access to data of archive need to be validated with application

  • wners/developers
  • New releases of software need to be able to read archived data

Data Life Cycle Management @ CERN, Luca Canali 30

slide-31
SLIDE 31

Example of Data Movement

31 Data Life Cycle Management @ CERN, Luca Canali

slide-32
SLIDE 32

Techniques: Data Movement

  • impdp/expdp
  • Very useful although performance issues found

y g p

  • Impdp over DB link in particular
  • Partitioning and data movement
  • Exchange partition with table
  • Transportable tablespaces
  • Very fast Oracle-oracle data movement
  • Requires TBS to be set read only

C b bl i d ti

  • Can be a problem in production
  • Workarounds:
  • Use of standby or a restored backup to move data from

y p

32 Data Life Cycle Management @ CERN, Luca Canali

slide-33
SLIDE 33

Compression

  • The ‘high level’ view:
  • Databases are growing fast, beyond TB scale
  • CPUs are becoming faster
  • There is a opportunity to reduce storage cost by using

compression techniques

  • Gaining in performance while doing that too
  • Gaining in performance while doing that too
  • Oracle provides compression in the RDBMS

33 Data Life Cycle Management @ CERN, Luca Canali

slide-34
SLIDE 34

Making it Work in Real World

  • Evaluate gains case by case
  • Not all applications can profit
  • Not all data models can allow for it
  • Compression can give significant

gains for some applications gains for some applications

  • In some other cases applications can be

modified to take advantage of compression g p

  • Comment:
  • Our experience of deploying partitioning goes on

the same track

  • Implementation involves developers and DBAs

Data Life Cycle Management @ CERN, Luca Canali 34

slide-35
SLIDE 35

Evaluating Compression Benefits

  • Compressing segments in Oracle
  • Save disk space

Save disk space

  • Can save cost in HW
  • Beware that capacity in often not as important as

number of disks which determine max IOPS number of disks, which determine max IOPS

  • Compressed segments need less blocks so
  • Less physical IO required for full scan
  • Less logical IO / space occupied in buffer cache
  • Beware compressed segments will make you consume

more CPU

Data Life Cycle Management @ CERN, Luca Canali 35

slide-36
SLIDE 36

Compression and Expectations

  • A 10TB DB can be shrunk to 1TB of storage

with a 10x compression?

  • Not really unless one can get rid of indexes (!)
  • Data warehouse-like with only FULL SCAN
  • perations
  • perations
  • Data very rarely read (data on demand, almost

taken offline)

  • Licensing costs
  • Advanced compression option required for

hi b b i i anything but basic compression

  • Exadata storage required for hybrid columnar

compression

Data Life Cycle Management @ CERN, Luca Canali

compression

36

slide-37
SLIDE 37

Archive DB and Compression

  • Non-active data can be compressed
  • To save storage space
  • In some cases speed up queries for full scans
  • Compression can be applied as post-processing
  • On read-mostly data partitions
  • On read-mostly data partitions

– (Ex: Atlas’ PVSS, Atlas MDT DCS, LCG’s SAME)

  • With alter table move or online redefinition
  • Active data
  • Non compressed or compressed for OLTP (11g)

Data Life Cycle Management @ CERN, Luca Canali 37

slide-38
SLIDE 38

Data Archive and Indexes

  • Indexes do not compress well
  • Drop indexes in archive when possible
  • Risk archive compression factor dominated by

index segments

  • Important details when using partitioning
  • Important details when using partitioning
  • Local partitioned indexes preferred
  • for ease of maintenance and performance

p

  • Note limitation: columns in unique indexes need be

superset of partitioning key

  • May require some index change for the archive

May require some index change for the archive

– Disable PKs in the archive table (Ex: Atlas PANDA) – Or change PK to add partitioning key

Data Life Cycle Management @ CERN, Luca Canali 38

slide-39
SLIDE 39

Oracle Segment Compression - What is Available

  • Heap table compression:
  • Basic (from 9i)
  • For OLTP (from 11gR1)
  • 11gR2 hybrid columnar (11gR2 exadata)

Oth i t h l i

  • Other compression technologies
  • Index compression
  • Key factoring

Key factoring

  • Applies also to IOTs
  • Secure files (LOB) compression
  • 11g compression and deduplication
  • Compressed external tables (11gR2)

Details not covered here

Data Life Cycle Management @ CERN, Luca Canali

  • Details not covered here

39

slide-40
SLIDE 40

Table Compression In Practice

  • Measured compression factors for tables:
  • About 3x for BASIC and OLTP
  • About 3x for BASIC and OLTP
  • In prod at CERN, example: PVSS, LCG SAME, Atlas

TAGs, Atlas MDT DCS

  • 10-20x for hybrid columnar (archive)
  • more details in the following

C i b f h l f th

  • Compression can be of help for the use

cases described above

  • Worth investigating more the technology
  • Worth investigating more the technology
  • Compression for archive is very promising

Data Life Cycle Management @ CERN, Luca Canali 40

slide-41
SLIDE 41

Compression for Archive – Some Tests and Results

  • Tests of hybrid columnar

compression

  • Exadata V1, ½ rack
  • Oracle 11gR2
  • Courtesy of Oracle

y

  • Remote access to a test machine

in Reading (Q3 2009)

Data Life Cycle Management @ CERN, Luca Canali 41

slide-42
SLIDE 42

Advanced Compression Tests

  • Representative subsets of data from production exported

to Exadata V1 Machine: to Exadata V1 Machine:

  • Applications: PVSS (slow control system for the detector and

accelerator) GRID it i li ti

  • GRID monitoring applications
  • File transfer applications (PANDA)
  • Log application for ATLAS
  • Exadata machine accessed remotely to Reading, UK for a 2-week

test

  • Tests focused on :

Tests focused on :

  • OLTP and Hybrid columnar compression factors
  • Query speedup

42 Data Life Cycle Management @ CERN, Luca Canali

slide-43
SLIDE 43

Hybrid Columnar Compression on Oracle 11gR2 and Exadata

Measured Compression factor for selected Physics Apps.

50 60 70

factor

30 40 50

  • mpression

10 20 Columnar for Query Low Columnar for Query High Columnar for Archive Low Columnar for Archive High

Co

No compression OLTP compression PVSS (261M rows, 18GB) LCG GRID Monitoring (275M rows, 7GB)

Data from Svetozar Kapusta – Openlab (CERN).

LCG TESTDATA 2007 (103M rows, 75GB) ATLAS PANDA FILESTABLE (381M rows, 120GB) ATLAS LOG MESSAGES (323M rows, 66GB)

43 Data Life Cycle Management @ CERN, Luca Canali

slide-44
SLIDE 44

Full Scan Speedup – a Basic Test a Basic Test

Full table scan speedup of compressed tables for count(*) operation (speed=1 for ‘no compression’)

3 3.5

can

1.5 2 2.5

up of full sc

0.5 1 BASIC QUERY LOW QUERY HIGH ARCHIVE LOW ARCHIVE HIGH

Speed

NO COMPRESSION OLTP PVSS (261M rows, 18GB) LCG GRID Monitoring (275M rows, 7GB) ATLAS PANDA FILESTABLE (381M, 120GB) ATLAS LOG MESSAGES (323M rows, 78GB) ( , ) ( , ) LCG TESTDATA (103M rows, 75GB)

Data from Svetozar Kapusta – Openlab (CERN).

44 Data Life Cycle Management @ CERN, Luca Canali

slide-45
SLIDE 45

IO Reduction for Full Scan Operations

  • n Compressed Tables
  • n Compressed Tables

Hypothetical full scan speed up for count(*) operations Obtained by disabling cell offloading in exadata.

25 30

can

15 20 25

up of full sc

5 10 BASIC QUERY LOW QUERY HIGH ARCHIVE LOW ARCHIVE HIGH

Speed

NO COMPRESSION OLTP PVSS (261M rows, 18GB) LCG GRID Monitoring (275M rows, 7GB) ATLAS PANDA FILESTABLE (381M, 120GB) LCG TESTDATA (103M rows, 75GB) ATLAS PANDA FILESTABLE (381M, 120GB) LCG TESTDATA (103M rows, 75GB) ATLAS LOG MESSAGES (323M rows, 78GB) Data from Svetozar Kapusta – Openlab (CERN).

45 Data Life Cycle Management @ CERN, Luca Canali

slide-46
SLIDE 46

Time to Create Compressed Tables Compressed Tables

Table creation time for various compression types of various physics applications. T = 1 for ‘no compression’.

40 45

ess

20 25 30 35

  • r to compre

5 10 15 OLTP BASIC QUERY LOW QUERY HIGH ARCHIVE LOW ARCHIVE HIGH

Time facto

NO COMPRESSION OLTP PVSS (261M rows, 18GB) LCG GRID Monitoring (275M rows, 7GB) LCG TESTDATA (103M rows, 75GB) ATLAS PANDA FILESTABLE (381M, 120GB) ATLAS LOG MESSAGES (323M rows, 78GB)

Data from Svetozar Kapusta – Openlab (CERN).

46 Data Life Cycle Management @ CERN, Luca Canali

slide-47
SLIDE 47

A Closer Look at Hybrid Columnar Compression p

  • Data is stored in compression units (CUs), a collection of

blocks (around 32K) E h i it t d t i t ll ‘b l ’

  • Each compression unit stores data internally ‘by column’:
  • This enhances compression

CU HEADER BLOCK HEADER BLOCK HEADER BLOCK HEADER BLOCK HEADER

C3 C7

Logical Compression Unit

CU HEADER

C3 C4 C1 C2 C7 C5 C6 C8 C8

Data Life Cycle Management @ CERN, Luca Canali 47

Picture and info from: B. Hodak, Oracle (OOW09 presentation on OTN).

slide-48
SLIDE 48

Compression Factors

  • An artificial tests to put compression

algorithms into work

  • Two different types of test table
  • Constant: each row contains a random string

R d h t i titi f

  • Random: each row contains a repetition of a

given string

  • 100M rows of about 200 bytes each

100M rows of about 200 bytes each

  • Details of the test in the notes for this slide

Data Life Cycle Management @ CERN, Luca Canali 48

slide-49
SLIDE 49

Compression Factors

Compression Table Type Compression Type Blocks Used Comp Factor Constant Table no compression 2637824 1 Constant Table comp basic 172032 15 3 Constant Table comp basic 172032 15.3 Constant Table comp for oltp 172032 15.3 Constant Table comp for archive high 3200 824.3 Constant Table high 3200 824.3 Constant Table comp for query high 3200 824.3

  • -------------------- -------------------- -------------------- --------------------

Random Table no compression 2711552 1 Random Table comp basic 2708352 1.0 Random Table comp for oltp 2708352 1.0 p p Random Table comp for archive high 1277952 2.1 comp for query

Data Life Cycle Management @ CERN, Luca Canali 49

Random Table high 1449984 1.9

slide-50
SLIDE 50

Hybrid Columnar and gzip

  • Compression for archive reaches high

compression

  • How does it compare with gzip?
  • A simple test to give a ‘rough idea’

Test: used a table populated with dba objects

  • Test: used a table populated with dba_objects
  • Results ~20x compression in both cases

Method Uncompressed Compressed Ratio gzip -9 13763946 bytes 622559 bytes 22 compress for archive high 896 blocks 48 blocks 19

Data Life Cycle Management @ CERN, Luca Canali 50

slide-51
SLIDE 51

Compression and DML

What happens when running row updates on compressed tables? What about locks?

  • BASIC and OLTP:
  • BASIC and OLTP:
  • the updated row stays in the compressed block
  • ‘usual’ Oracle’s row-level locks
  • usual Oracle s row-level locks
  • Hybrid columnar:
  • Updated row is moved as in a delete + insert

Updated row is moved, as in a delete + insert

  • How to see that? With dbms_rowid package
  • New row is OLTP compressed
  • Lock affects the entire CU that contains the row

Data Life Cycle Management @ CERN, Luca Canali 51

slide-52
SLIDE 52

Compression and Single-row Index Range Scan access de a ge Sca access

Consistent gets Consistent gets Table Compr Type (select *) (select owner) no compression 4 4 basic compression 4 4 comp for oltp 4 4 comp for query high 9 5 comp for query low 9 5

  • Test SQL: select from a copy of dba objects with a

p q y comp for archive low 10 5 comp for archive high 24 5

  • Test SQL: select from a copy of dba_objects with a

index on object_id

  • Predicate: ‘where object_id=100

Data Life Cycle Management @ CERN, Luca Canali

j _

  • Note: ‘owner’ is the first column of the test table

52

slide-53
SLIDE 53

Conclusions

  • Data Life Cycle Management experience at CERN

y g p

  • Proactively address issues of growing DBs
  • manageability
  • performance
  • cost
  • Involvement of application owners is fundamental
  • Techniques within Oracle that can help
  • Partitioning
  • Archival DB service

C i

  • Compression

53 Data Life Cycle Management @ CERN, Luca Canali

slide-54
SLIDE 54

Acknowledgments

  • CERN-IT DB group and in particular:
  • Jacek Wojcieszuk, Dawid Wojcik, Maria Girone
  • Oracle, for the opportunity of testing Oracle Exadata
  • Monica Marinucci, Bill Hodak, Kevin Jernigan

More info

  • More info:

http://cern.ch/it-dep/db htt // h/ li http://cern.ch/canali

Data Life Cycle Management @ CERN, Luca Canali 54