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

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 Data Life Cycle Management activities  Techniques used  Sharing our experience with examples g p p Data Life Cycle Management @ CERN, Luca Canali 2

Balloon ( 3 0 Km ) LHC data CD stack w ith CD stack w ith 1 year LHC data! ( ~ 2 0 Km ) LHC d t LHC data correspond to about d t b t 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 ) Data Life Cycle Management @ CERN, Luca Canali 3

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 • File catalogues, file transfers and storage management, … Fil t l fil t f d t t  Metadata and transaction processing for custom tape storage system of physics data g y p y  Accelerator logging and monitoring systems Data Life Cycle Management @ CERN, Luca Canali 4

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’ Data Life Cycle Management @ CERN, Luca Canali 5

Data Life Cycle Management @ CERN, Luca Canali 6

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

Data Lifecycle Management  Motivated by large data volumes produced by LHC experiments C  Large amounts of data will be collected and stored for several years several years  Different requirements on performance and SLA can often be found for ‘current’ and ‘old’ data sets  Proactively attack ‘issues’ of databases that grow ‘too large’  Administration  Performance  Cost C Data Life Cycle Management @ CERN, Luca Canali 8

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.. Data Life Cycle Management @ CERN, Luca Canali 9

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)  Dependencies between applications D d i b t li ti  Task  Identify how to get advantages of consolidation Identif ho to get ad antages of consolidation coexist with the idea of having a ‘lean’ DBs for manageability and performance Data Life Cycle Management @ CERN, Luca Canali 10

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’ Data Life Cycle Management @ CERN, Luca Canali 11

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 Data Life Cycle Management @ CERN, Luca Canali 12

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 _ RECO_DG1 RECO_DG1 Failgroup1 Failgroup1 Failgroup1 Failgroup1 Failgroup2 Failgroup2 Failgroup2 Failgroup2 Failgroup3 Failgroup3 Failgroup3 Failgroup3 Failgroup4 Failgroup4 Failgroup4 Failgroup4 Data Life Cycle Management @ CERN, Luca Canali 13

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 Metadata 1GbE 1GbE Backup data FC FC FC FC Media Manager Server Server Database Tape drives Data Life Cycle Management @ CERN, Luca Canali 14

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 allow archiving ll hi i  Define data availability agreements for online data and archive  Joint sessions to identify how to leverage Oracle features Data Life Cycle Management @ CERN, Luca Canali 15

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

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  Natural optimization: having large amounts of N t l ti i ti h i l t f 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 Data Life Cycle Management @ CERN, Luca Canali 17

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 similar tables in different schemas i il t bl i diff t h  Advantages  Partitions can be treated as separate tables for P titi b t t d t t bl f bulk operations  Full scan operation, if they happen, do not span u sca ope at o , t ey appe , do ot spa all tables Data Life Cycle Management @ CERN, Luca Canali 18

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 Data Life Cycle Management @ CERN, Luca Canali 19

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  Sometimes global partitioning better for S i l b l i i i b f 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 Data Life Cycle Management @ CERN, Luca Canali 20