oracle 11gr2数据压缩技术在cern中的实际应用.ppt
Compressing Very Large Data Sets in Oracle,Luca Canali, CERN UKOUG Conference Birmingham, December 1st, 2009,Outline,Physics databases at CERN and compression Use cases for compression and archive for Physics DBs @ CERN Current experience with10g Advanced compression tests with 11g Oracle and compression internals A closer look on how compression is implemented in Oracle BASIC, OLTP, Columnar compression Appendix: how to play with hybrid columnar without exadata,2,Compressing Very Large Data Sets in Oracle, Luca Canali,CERN and LHC,Compressing Very Large Data Sets in Oracle, Luca Canali,3,LHC data,LHC data correspond to about 20 million CDs each year!,Concorde (15 Km),,Balloon (30 Km),,CD stack with 1 year LHC data! (~ 20 Km),,Mt. Blanc (4.8 Km),,RDBMS play a key role for the analysis of LHC data,4,Compressing Very Large Data Sets in Oracle, Luca Canali,Why 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 perance while doing that too Oracle provides compression in the RDBMS,5,Compressing Very Large Data Sets in Oracle, Luca Canali,Compressing Very Large Data Sets in Oracle, Luca Canali,Making it Work in Real World,uate 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 In some other cases applications can be modified to take advantage of compression Comment: Our experience of deploying partitioning goes on the same track Implementation involves developers and DBAs,6,Compressing Very Large Data Sets in Oracle, Luca Canali,uating Compression Benefits,Compressing segments in Oracle Save disk space Can save cost in HW Beware that capacity in often not as important as 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,7,Compressing Very Large Data Sets in Oracle, Luca Canali,Compression and Expectations,A 10TB DB can be shrunk to 1TB of storage with a 10 x compression? Not really unless one can get rid of inds Data ware house like with only FULL SCAN operations Data very rarely read (data on demand, almost taken offline) Licensing costs Advanced compression option required for anything but basic compression Exadata storage required for hybrid columnar,8,Compressing Very Large Data Sets in Oracle, Luca Canali,Use Case 1: Data Life Cycle,Transactional 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,9,Compressing Very Large Data Sets in Oracle, Luca Canali,Example of Archiving and Partitioning for Physics Applications,Separation of active and the read-mostly part Data is inserted with a timestamp (log-like) Most queries will specify a timestamp value or a time interval Range/interval partitioning, by time comes natural Example: Atlas’ PANDA, DASHBOARD Other option: application takes care of using multiple tables Example: PVSS More flexible but need app to maintain metadata Alternative: archiving as post-processing This steps allow to add modifications Ex: changing index structure (Atlas’ PANDA),