An evaluation of Data Storage and Analysis With Oracle DBMS

1
An evaluation of Data Storage and Analysis With
Oracle DBMS

• Eric Grancher, CERN IT/DB
• Database Workshop, CERN
• July 11th-13th 2001

2
Outline

• Initial study, using Oracle 8i
• RAW data tests
• Various methods
• Bandwidth
• Overhead
• TAG data analysis
• Type of queries
• Bitmap indexes
• Binning and function based indexes

3
RAW Data Tests

• Purpose
• Verify on a reduced scale that oracle is not too
  consuming when stressed to acquire a large data
  volume
• First design options
• Ideas about the overhead

4
Model

• Typical model (NA 45) and random generator thanks
  to Dirk Düllmann
• Run, burst, event and label types
• Many ways to map it in an ORDBMS

Vectorltlonggt
5
Storage

• We implemented
• Run, Burst and Event relational tables
• Label one Binary Large Object in order to store
  the doubles
• There is more than one way to do it !

6
Tests

• We used a workgroup server, sun E420R with a
  D1000 (ultra SCSI) disk subsystem
• Veritas volume manager (manageability and tuning)
• RAW devices tests show that disk subsystem
  saturates with 4 disks stripe (38MBytes/s
  compared to the 40MBytes/s specification)

7
Host Language

• There are many ways to connect to Oracle DBMS,
  supported ones
• C/C and Oracle8i OCI, ProC and ODBC
• ProC pre-compiler (embedded SQL)
• ODBC generic interface

8
Choice OCI

• The low level C native interface, all facilities
• Encapsulated into C
• Typically quite complex (callback and C) and
  verbose

9
Setup
Sun E420R D1000
Oracle
Linux (lxplus)
100Mbit/s
Random
10
Optimisation

• We use NO CACHE tables, NOLOGGING hint
• Using VM, we could see that control files were
  the hot spot -gt we moved them to free disks,
  factor of 5 gain in speed
• Networking subsystem is the bottleneck (100
  Mbit/s), several Linux clients

11
Control Files
Data files and redo log filesLog historyCurrent
log sequenceCheckpoint information
Instance
SGA
Shared pool
Serverprocess
CKPT
DBWR
LGWR
Userprocess
Modified slide from Bjørn Engsig / Oracle
Database
12
Overhead

• Overhead 4.1 over the C data
• NUMBER type in Oracle
• LOB are multiple of the block size (8kB), size of
  the Label was often around 20k long(ie 80kB on
  Linux/i386)

13
Results and perspectives

• Sustained 10.88 MB/s during 2000s, 8 Linux
  client machines
• The server networking sub-system is the
  bottleneck
• Study during this summer with Gbit/s Ethernet and
  large Linux PC
• Larger machines later (how does Oracle Real
  Application Cluster scales for such an
  application?)

14
Conclusion

• At this scale, it works
• LOB -gt byte ordering dependency
• Other tests with non-LOB types
• more overhead
• better facilities to analyse the data
• Overhead has to be further studied
• Other bindings (OCCI and refs)