COOL Conditions Database for the LHC Experiments p Development and - - PowerPoint PPT Presentation

▶
cool
SMART_READER_LITE
LIVE PREVIEW

COOL Conditions Database for the LHC Experiments p Development and - - PowerPoint PPT Presentation

Jan 23, 2023 464 likes •638 views

Data Management Group COOL Conditions Database for the LHC Experiments p Development and Deployment Status Andrea Valassi (CERN IT-DM) Andrea Valassi (CERN IT-DM) M. Clemencic (CERN - LHCb) S. A .Schmidt, M. Wache (Mainz - ATLAS) , ( )

slide-1
SLIDE 1

Data Management Group

COOL

Conditions Database for the LHC Experiments p

Development and Deployment Status

Andrea Valassi (CERN IT-DM) Andrea Valassi (CERN IT-DM)

  • M. Clemencic (CERN - LHCb)
  • S. A .Schmidt, M. Wache (Mainz - ATLAS)

, ( )

  • R. Basset, G. Pucciani (CERN IT-DM)

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008

IEEE-NSS 2008, 23rd October 2008

slide-2
SLIDE 2

Outline

  • Introduction
  • Development activities

– Maintenance and code consolidation Maintenance and code consolidation – Functionality enhancements – Performance tests and optimization p

  • Deployment-oriented activities

– Scalability tests with simulated data – Support of actual deployment with real data

  • Conclusions

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008 COOL Status - 2

slide-3
SLIDE 3

What is COOL

  • Software for LHC ‘conditions data’ access

Time variation (validity) and versioning (tags) – Time variation (validity) and versioning (tags) – Offline (calibration, alignment) and online (DCS)

  • Common project of Atlas, LHCb, CERN IT

– Atlas and LHCb store conditions data using COOL P i t F k f LCG A li ti A – Persistency Framework of LCG Application Area

  • Collaboration with other LCG AA projects

Collaboration with other LCG AA projects

– CORAL for C++ access to SQL on relational DBs – ROOT/Reflex for Python bindings (PyCool)

  • Support for several relational backends

Oracle MySQL SQLite Frontier (all via CORAL)

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008 COOL Status - 3

– Oracle, MySQL, SQLite, Frontier (all via CORAL)

slide-4
SLIDE 4

COOL development overview

  • Mature functionality and code base

– First release in April05, latest (2.5.0) in June08 First release in April05, latest (2.5.0) in June08 – Test-driven development, automatic nightly tests for all supported relational database backends

  • Maintenance and code consolidation

Internal refactoring of existing functionalities – Internal refactoring of existing functionalities – New platforms (osx/Intel, gcc43, VS9, SLC5…) – New versions of external software New versions of external software – Fix bugs/issues identified in real-life deployment

  • Not yet fully in maintenance mode

– Functionality enhancements P f ti i ti

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008

– Performance optimization

COOL Status - 4

slide-5
SLIDE 5

Functionality enhancements

(work in progress)

  • Tagging enhancements

“P ti l t l ki ” ( t t difi ti ) – “Partial tag locking” (prevent tag modifications)

D t t i l h t

  • Data retrieval enhancements

– Payload queries (fetch time for given calibration)

D f lt f t h lib ti t i lidit ti

  • Default use case: fetch calibration at given validity time

Database connection enhancements

  • Database connection enhancements

– User control over database transactions DB i h i b t COOL i – DB session sharing between COOL sessions

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008 COOL Status - 5

slide-6
SLIDE 6

Performance optimization

  • Main focus: performance for Oracle DBs

Master Tier0 database for both Atlas and LHCb – Master Tier0 database for both Atlas and LHCb

  • Proactive performance test on small tables

Proactive performance test on small tables

– Test main use cases for retrieval and insertion – Response times should not increase as tables p grow larger (indexes instead of full table scans)

O l f ti i ti t t

  • Oracle performance optimization strategy

– Basic SQL optimization (fix indexes and joins) U hi t t t bili ti l f i SQL – Use hints to stabilize execution plan for given SQL

  • Instability from unreliable statistics, bind variable peeking
  • Determine best hints from analysis of “10053 trace” files

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008

y

COOL Status - 6

slide-7
SLIDE 7

Performance optimization example

  • Systematic tests of known causes of instabilities

– 6 plots: bind var. peeking (2) x fresh/stale/no statistics (3) p p g ( ) ( )

– Such instabilities were actually observed in the Atlas 2007 tests

– Stable performance after adding Oracle hints

Bad SQL strategy (COOL230). Retrieval time for 10 IOVs is larger for IOVs at the end of the relational table (full table scan). Good SQL strategy (COOL231). Good Oracle statistics. Bad execution plan due to relational table (full table scan). “bind variable peeking” (no hints).

Good SQL strategy (COOL231)

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008

Good SQL strategy (COOL231). Stable execution plan thanks to the use of hints.

slide-8
SLIDE 8

Scalability tests

  • Proactive performance test on large tables

Stable insertion and retrieval rates (>1k rows/s) – Stable insertion and retrieval rates (>1k rows/s) – Simulate data sets for 10 year of LHC operation

Romain Basset (DCS data)

  • Test case: Atlas

– Largest data set: DCS Largest data set: DCS

  • 1.5 GB (2M IOVS) / day
  • From PVSS into COOL
  • Work in progress:

O l titi i Oracle partitioning

– For data management

f ?

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008

  • Performance impact?
slide-9
SLIDE 9

COOL deployment overview

  • Similar Oracle setups in Atlas and LHCb

– Two separate servers at CERN (online offline) Two separate servers at CERN (online, offline) – Distributed replicas at the experiment Tier1 sites – Replication via the Oracle Streams technology

Atlas (G. Dimitrov, F. Viegas) LHCb (M Cl

i )

LHCb (M. Clemencic)

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008 COOL Status - 9 COOL

slide-10
SLIDE 10

Deployment status

  • Setup is complete for both experiments

– T0 online/offline DBs, T1 sites (6 LHCb, 10 Atlas) T0 online/offline DBs, T1 sites (6 LHCb, 10 Atlas)

  • Distributed tests are very useful for COOL

– Several lessons from Atlas tests in 2007 already

  • Most T0 and T1 databases were up by Q4 2006 already

New issues identified and addressed in 2008 – New issues identified and addressed in 2008

  • e.g. user-level read access during Streams write activity

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008 COOL Status - 10 COOL Status - 10 Much larger data rates in ATLAS NSS 2008 – 23rd October 2008

slide-11
SLIDE 11

New deployment model?

COOL API User Code

DB access via CORAL server

Add th ti ti

User Code COOL API Connection Pool CORAL API

– Address secure authentication and connection multiplexing Development still in progress

User Code COOL API CORAL API Oracle Plugin Oracle OCI Connection Pool

– Development still in progress

  • See next talk by Zsolt Molnar
  • Only minimal changes in COOL

C l Pl i Connection Pool CORAL API

CORAL protocol

Oracle OCI

Only minimal changes in COOL

Coral Plugin CoralServer

Oracle OCI protocol (OPEN PORTS) p

O l Pl i Connection Pool CORAL API

Oracle OCI protocol (NO OPEN PORTS)

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008 Oracle DB Server Oracle Plug-in Oracle Client COOL Status - 11

slide-12
SLIDE 12

Conclusions

  • COOL: conditions DB for Atlas and LHCb

A j i t j t ith CERN IT d LCG AA – A joint project with CERN IT and LCG AA

  • Development is mature but not finished
  • Development is mature but not finished

– Performance optimization is the highest priority

  • Proactive tests and support for real deployment issues
  • Proactive tests and support for real deployment issues
  • Distributed deployment setup is ready

Distributed deployment setup is ready

– Waiting for more data from LHC!

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008 COOL Status - 12

slide-13
SLIDE 13

R lid Reserve slides

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008 COOL Status - 13

slide-14
SLIDE 14

COOL collaborators

Core development team

  • Andrea Valassi (CERN IT-DM)

80% FTE (core development project coordination release mgmt) – 80% FTE (core development, project coordination, release mgmt)

  • Marco Clemencic (CERN LHCb)

– 20% FTE (core development, release mgmt)

Sven A Schmidt (Mainz ATLAS)

  • Sven A. Schmidt (Mainz ATLAS)

– 20% FTE (core development)

  • Martin Wache (Mainz ATLAS)

80% FTE ( d l t) – 80% FTE (core development)

  • Romain Basset (CERN IT-DM)

– 50% FTE (performance optimization) + 50% FTE (scalability tests)

O d 2 FTE i t t l f d l t i 2004

  • On average, around 2 FTE in total for development since 2004

Collaboration with users and other projects

  • Richard Hawkings and other Atlas users and DBAs
  • Richard Hawkings and other Atlas users and DBAs
  • The CORAL, ROOT, SPI and 3D teams

Former collaborators

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008 COOL Status - 14

Former collaborators

  • G. Pucciani, D. Front, K. Dahl, U. Moosbrugger
slide-15
SLIDE 15

COOL data model

  • Modeling of conditions data objects

– System-managed common “metadata”

  • Data items: many tables, each with many channels
  • Interval of validity - “IOV” [since, until]
  • Versioning information - with handling of interval overlaps

– User-defined schema for “data payload”

  • Support for fields of simple C++ types
  • Main use case: event reconstruction

– Lookup data payload valid at a given event time

CERN - IT Department CH-1211 Genève 23 Switzerland

www.cern.ch/ it

NSS 2008 – 23rd October 2008 COOL Status - 15