# Presentation: 005492

## LCG Database Workshop Summaryand Proposal for the First Distributed Production Phase

- Dirk Duellmann, CERN IT
- (For the LCG 3D Project : http://lcg3d.cern.ch)

## Why a LCG Database Deployment Project? 

- LCG today provides an infrastructure for distributed access to file based data and file replication
- Physics applications (and grid services) require a similar services for data stored in relational databases
- Several applications and services already use RDBMS
- Several sites have already experience in providing RDBMS services
- Goals for common project as part of LCG
- increase the availability and scalability of LCG and experiment components
- allow applications to access data in a consistent, location independent way
- allow to connect existing db services via data replication mechanisms
- simplify a shared deployment and administration of this infrastructure during 24 x 7 operation

- Dirk Duellmann

- LCG 3D Production Phase

## LCG 3D Service Architecture

- O

- O

- O

- M

- T1- db back bone
- - all data replicated
- - reliable service

- T2 - local db cache
- -subset data
- -only local service

- T3/4

- T0
- - autonomous reliable service

- Dirk Duellmann

- LCG 3D Production Phase

## 3rd LCG Database Workshop

- Three days workshop Oct 17-19 with LHC experiments and
- Sites: ASCC, CERN, CNAF, BNL, FNAL, GridKA, IN2P3, RAL
- Contact with PIC and NIKHEF/SARA
- Details: _[http://agenda.cern.ch/fullAgenda.php?ida=a055549](http://agenda.cern.ch/fullAgenda.php?ida=a055549)_
- Focus on preparation for large scale deployment
- Capture current deployment plans
- Discuss proposed services and impact on applications
- Continue service validation with real apps/workload
- Define and schedule production setup at LCG Tier sites

- Dirk Duellmann

- LCG 3D Production Phase

## ATLAS Database / Data Management Project

- Responsible for DB/DM activities across ATLAS:
- DBs for detector production & installation, survey, detector geometry
- Online configuration, bookkeeping, run conditions
- Online and offline calibrations & alignments
- Event data and metadata
- Offline processing configuration and bookkeeping
- Distributed data management (file-based data)
- Distributed database infrastructure and services
- All of these rely on DB services at CERN and at external institutes, and a distributed DB infrastructure knitting these together
**Consequently we strongly support and rely on the 3D project!**

| Subprojects |
| --- |
|  |
| Production DBs |
| Installation DBs |
| Geometry DB |
| Online DBs |
| Calib/Align |
| Conditions DB |
| Event Data |
| Distributed DM |
| Offl Processing |
| DB Services |
| SW Support |

- Torre Wenaus, Stefan Stonjek

- LCG 3D Production Phase

## Online/T0 DB System Architecture

- Torre Wenaus, Stefan Stonjek

- LCG 3D Production Phase

## Experiment Deployment Plans

- LHCb
- Databases online / Tier 0 / Tier 1
- Oracle streams for conditions replication (COOL)
- ATLAS
- Databases online / Tier 0 / Tier 1 / T2 (mysql)
- Oracle streams for conditions replication (COOL)
- Both interested in FroNtier cache for conditions data in COOL

- Dirk Duellmann

- LCG 3D Production Phase

## Offline conditions DB relations 

- Latchezar Betev

- LCG 3D Production Phase

## CMS OnlineOffline DB 

- POOL-ORA
- format

- Sub-Detector designed format

- Lee Lueking

- LCG 3D Production Phase

## Rough Estimates of CMS DB ResourcesMarch through September 2006

| Area
(CMS contact) | Disk 
Maximum (GB) | Concurrent Users 
Peak usage | Transactions
Peak usage (Hz) |
| --- | --- | --- | --- |
| Online P5
(F. Glege) | 500 | 20 | 10 |
| Offline Conditions
Tier-0
(L. Lueking/O.Buchmuller) | 500 (DB)
100 (per Squid)
2-3 Squids | 10 (DB)*
10 (Squid)
* Incl. On2off Xfer | 10 (DB)*
10 (Squid)
* Incl. On2off Xfer |
| Offline Conditions Tier-1 (each site)
(L.Lueking/O. Buchmuller) | 100 (per Squid)
2-3 Squids/site | 10(per Squid)
>100  all sites | 10 per (Squid)
>100  all sites |
| Offline DMS Tier-0
(P. Elmer/L. Turra) | 20 | 10 | 10 |

- Lee Lueking

- LCG 3D Production Phase

## Offline FroNTier Resources/Deployment

- Tier-0: 2-3 Redundant FroNTier servers.
- Tier-1: 2-3 Redundant Squid servers.
- Tier-N: 1-2 Squid Servers.
- Typical Squid server requirements:
- CPU/MEM/DISK/NIC=1GHz/1 GB/100GB/Gbit
- Network: visible to Worker LAN (private network) and WAN (internet)
- Firewall: Two Ports open for URI (FroNTier Launchpad)  access and SNMP monitoring (typically 8000 and 3401 respectively)
- Squid non-requirements
- Special hardware (although high-throughput Disk I/O is good)
- Cache backup (if disk dies or is corrupted, start from scratch and reload automatically)
- Squid is easy to install and requires little on-going administration.

- http

- JDBC

- Lee Lueking

- LCG 3D Production Phase

## Experiment Deployment Plans

- ALICE
- Databases online / Tier 0 - files Tier 1 and higher
- Alice s/w for online to offline copy/transformation
- Files for conditions data distribution
- No 3D service request outside T0
- CMS
- Databases online / Tier 0 - DB cache Tier 1 and higher
- Oracle streams for online to offline replication
- FroNtier/POOL for conditions data distribution (cache at T1/T2)
- Oracle streams as fallback

- Dirk Duellmann

- LCG 3D Production Phase

## Databases in SC3 Middleware Deployment

- Takes place already for services used in SC3
- Existing setups at the sites
- Existing experience with SC workloads -> extrapolate to real production
- LFC, FTS - Tier 0 and above
- Low volume but high availability requirements
- CERN: Run on 2-node Oracle cluster; outside Oracle or MySQL
- CASTOR 2  - CERN and some T1 sites
- Scalability requirements may need effort on DB side
- CERN: Currently run on 3 Oracle servers
- Currently not driving the requirements for the database service
- Consolidation of databases h/w and procedures may reduce effort/diversity at Tier 1 sites

- Dirk Duellmann

- LCG 3D Production Phase

## DB Requirements Status

- Experiment data at Tier 1 and higher read-only
- Few well defined accounts write online/Tier 0
- Volume, CPU and transaction estimates still evolving
- Need detector s/w integration for conditions data
- Need production service in place for SC4
- based on today’s estimates
- Review access pattern after eg first 3 month of production
- Iterate via  application validation tests
- Significant testing effort and flexibility from all participants
- Experiments, db service and s/w development teams

- Dirk Duellmann

- LCG 3D Production Phase

## Service Architecture @ CERN  

- Service Architecture @ CERN

- The Physics Database services at CERN moved to database clusters

- Maria Girone

- LCG 3D Production Phase

## CERN Database h/w Evolution 

| Current State |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| ALICE | ATLAS  | CMS  | LHCb | Grid  | 3D | Non-LHC | Validation   |
| - | 2-node offline | 2-node | 2-node | 2-node | - | - | 2x2-node |
|  | 2-node online test |  |  |  |  |  |  |
| Proposed structure in 2006  |  |  |  |  |  |  |  |
| 2-node | 4-nodes |  4-nodes | 4-nodes | 4-nodes | 2-node | 2-node (PDB replacement) |  |
|  | 2-node valid/test | 2-node valid/test | 2-node valid/test | 2-node pilot |  | Non-LHC |  |

- Ramp up of hardware resources during 2006-2008

- Maria Girone

- LCG 3D Production Phase

## Database Clusters

- Several sites are testing/deploying Oracle clusters
- CERN, CNAF, BNL, FNAL, GridKA, IN2P3, RAL
- Several experiments foresee Oracle clusters for online systems
- Focus on db clusters as main building block also for Tier 1
- 3D will organize detailed DBA level discussions on database cluster setup
- Tier 1 DB teams and Online DB teams
- Share test plans and expertise among LCG sites  and experiments
- Cluster setup and existing test results
- Storage configuration and performance tests

- Dirk Duellmann

- LCG 3D Production Phase

## Proposed Tier 1 Service Setup

- Propose to setup for first 6 month
- 2/3  dual-cpu database nodes with 2GB or more
- Setup as RAC cluster (preferably) per experiment
- ATLAS: 3 nodes with  300GB storage (after mirroring)
- LHCb: 2 nodes with 100GB storage (after mirroring)
- Shared storage (eg FibreChannel) proposed to allow for clustering
- 2-3  dual-cpu Squid nodes with 1GB or more
- Squid s/w packaged by CMS will be provided by 3D
- 100GB storage per node
- Need to clarify service responsibility (DB or admin team?)
- Target s/w release: Oracle 10gR2
- RedHat Enterprise Server to insure Oracle support

- Dirk Duellmann

- LCG 3D Production Phase

## Proposed 2006 Setup Schedule

- November: h/w setup  defined and plan to PEB/GDB
- January: h/w acceptance tests, RAC setup
- Begin February: Tier 1 DB readiness workshop
- February: Apps and streams setup at Tier 0
- March: Tier 1 service starts
- End May: Service review -> h/w defined for full production
- September: Full LCG database service in place

- Dirk Duellmann

- LCG 3D Production Phase

## Summary

- Database applications s/w and distribution models firming up - driving application “Conditions database”
- ATLAS, CMS and LHCb require access to database data also outside CERN, ALICE  only at CERN
- Two target distribution technologies (Streams and FroNtier) and complementary deployment plans for initial production phase
- Online -> offline replication based on streams for all three experiments
- Propose to setup  pre-production services for March 06 and full LHC setup after 6 month deployment experience
- Definition of concrete conditions data models in experiment s/w should be aligned

- Dirk Duellmann

- LCG 3D Production Phase

## Questions?

## Additional Slides

## Tier 2 Setup

- Only little effort from 3D so far
- Will change with full slice test for COOL now
- BNL / ATLAS have most deployment experience
- Propose to define and document standard s/w installation
- Need more participation of prototype Tier 2 sites

- Dirk Duellmann

- LCG 3D Production Phase

## Project Non-Goals

- Store all database data
- Experiments are free to deploy databases and distribute data under their responsibility
- Setup a single monolithic distributed database system
- Given constraints like WAN connections one can not assume that a single synchronously updated database work and provide sufficient availability.
- Setup a single vendor system
- Technology independence and a multi-vendor implementation will be required to minimize the long term risks and to adapt to the different requirements/constraints on different tiers.
- Impose a CERN centric infrastructure to participating sites
- CERN is one equal partner of other LCG sites on each tier
- Decide on an architecture, implementation, new services, policies
- Produce a technical proposal for all of those to LCG PEB/GDB

- Dirk Duellmann

- LCG 3D Production Phase

## LCG Certificate Support

- Easier for 3-tier apps
- Still an issue for client-server db applications
- X509 authentication in Oracle and MySQL
- Proxy certs can be made work with MySQL but fail so far with Oracle
- Authorization (mapping of VOMS to DB roles) still missing
- Stop-gap solution (read-only access outside T0) acceptable for security and experiments for ~6month
- Monitoring will important and needs real user id (DN)
- Little manpower left in the project
- ASCC contributing in the area of Oracle security infrastructure setup
- Need a test system and review manpower @ CERN

- Dirk Duellmann

- LCG 3D Production Phase

## Physics DB Services @ CERN

- Database clusters (RAC) are in production now !!
- Big effort of Maria’s team in parallel to ongoing services and significant support load
- Hope to re-focus resources to consultancy and service improvement
- Standardized account setup for smother transition between service levels and more stable client side connection information
- Proposed new storage organization provides improved performance  and uses available disk volume to decrease recovery latency
- Backup policy review and read-only data are important to insure that tape backup volume stays scalable as volume grows
- DB Server monitoring for developers/deployment integrated into LEMON
- Unavailability caused by security patches and overload are most significant - applications and services need to retry/failover

- Dirk Duellmann

- LCG 3D Production Phase

## Database Service Levels  

- Development Service (run by IT-DES)
- Code development, no large data volumes, limited number of concurrent connections
- Once stable, the application code and schema  move to validation
- Validation Service (for key apps)
- Sufficient resources for larger tests and optimisation
- Allocated together with DBA resources consultancy
- Needs to be planned in advance
- Limited time slots of about  3 weeks
- Production Service
- Full production quality service, including backup, monitoring services, on call intervention procedures
- Monitoring to detect new resource consuming applications or changes in access patterns
- OS level support provided by IT-FIO

- Maria Girone

- LCG 3D Production Phase

## Streams

- Tested successfully for simple workloads (file catalogs)
- LFC replication test requested by LHCb scheduled
- Several validation test ongoing for conditions data
- Online->offline for ATLAS and CMS (CC->CC, CMS P5->CC)
- Offline->T1 for ATLAS and LHCb (CERN->RAL->Oxford)
- Several tests scheduled requiring service and consultancy
- 0.5 FTE DBA support the CERN side likely to become a bottleneck

- Dirk Duellmann

- LCG 3D Production Phase

## FroNtier

- CMS Baseline for conditions data
- No database service outside T0 required
- Simpler squid service on T1 and T2
- Integrated with LCG persistency framework via transparent plug-in
- Early testing phase in CMS
- Interest from other experiments (ATLAS/LHCb)
- FroNtier test setup available in 3D test bed

- Dirk Duellmann

- LCG 3D Production Phase

## CMS Non-event Data Model

- Conditions data: (Calibration, Alignment, Geometry, Configuration, Monitoring)
- Online DB: Schemas designed specific to sub-system; Oracle DB server at P5
- Offline DB: POOL-ORA repositories
- HLT Farm: Oracle DB server at P5
- Tier-0:Oracle DB server at IT
- Online to offline: Transform online format to POOL-ORA payloads. Transfer POOL-ORA payloads to offline via Oracle Streams.
- Offline (Tier-0) to Tier-N:
- Plan A: CMS Client POOL-RAL  SQUID proxy/caching servers  FroNTier “pass through” server   POOL-ORA repository.
- Plan B: Oracle replication to Tier-1 sites, if Plan A is insufficient or fails.
- Event Data Management System (DMS)
- Tier-0: Dataset Bookkeeping Service (DBS), Dataset Location Service (DLS)
- Tier-1: Local DBS and DLS for internal bookkeeping. Possible use of Oracle replication w/ Tier-0 if needed for performance/availability.
- Tier-2: Local DBS and DLS for internal bookkeeping (not Oracle).

- Lee Lueking

- LCG 3D Production Phase

## ATLAS applications and plans

**LCG Database Deployment and Persistency Workshop**
- 17-Oct-2005,CERN
- Stefan Stonjek (Oxford), Torre Wenaus (BNL)

## Online Databases

- ATLAS Online uses standardized DB tools from the DB/DM project (and mostly from LCG AA)
- RAL used as standard DB interface, either directly or indirectly (COOL)
- Direct usages: L1 trigger configuration, TDAQ OKS configuration system
- COOL conditions DB is successor to Lisbon DB for time dependent conditions, configuration data
- Links between COOL and online (PVSS, information system) in place
- PVSS data sent to PVSS-Oracle and then to COOL (CERN Oracle)
- Strategy of DB access via ‘offline’ tools (COOL/POOL) *and* via direct access to the back end DB popular in online
**Joint IT/ATLAS project to test online Oracle DB strategy being established**
- Online Oracle DB physically resident at IT, on ATLAS-online secure subnet
- Data exported from there to offline central Oracle DB, also IT resident

- Torre Wenaus, Stefan Stonjek

- LCG 3D Production Phase

## Conditions DB – COOL (1)

- COOL used for conditions data across ATLAS subdetectors and online
- ATLAS is the main experiment contributor to its development
- Initiative started to use COOL to manage time-dependent aspects of subsystem configurations
- COOL now fully integrated with ATLAS offline framework Athena
- Deployment plan written and well received within ATLAS and by IT
- Usage model: write centrally (CERN Oracle), read mainly via caches/replicas
**Scalability and performance testing/debugging of COOL and underlying DB services being tested with realistic ATLAS online workloads**

- Torre Wenaus, Stefan Stonjek

- LCG 3D Production Phase

## Some ATLAS DB Concerns (1)

- Scalable distributed access to conditions data
- COOL copy/extraction tools in development, and in good hands; will come but aren’t there yet
- FroNTier approach of great interest but untouched in ATLAS for lack of manpower
- FroNTier/RAL integration is welcome, we need to look at it!
- DDM already deployed in a limited way for calibration data file management, but needs to be scaled up and the divide between file- and DB-based conditions data better understood
- Role of object relational POOL and implications for distributed access still to be understood

- Torre Wenaus, Stefan Stonjek

- LCG 3D Production Phase

## Conclusion (1)

- ATLAS DB/DM is well aligned with, draws heavily from, and contributes to LCG 3D and LCG AA/persistency; we depend on them being strongly supported and will continue to support them as best we can
- The ‘easier’ applications from the DB services point of view are well established in production, reasonably well understood, and relatively light in their service/distribution requirements
- TC databases (except survey), geometry database

- Torre Wenaus, Stefan Stonjek

- LCG 3D Production Phase

## Conclusion (2)

- For the most critical of the rest, the ‘final’ applications now exist in various states of maturity, but more scale/usage information and operational experience is needed before we can be reliably concrete
- Conditions DB, event tags?, production DB, DDM
- For the remainder, applications and even strategies are still immature to non-existent (largely because they relate to the still-evolving analysis model)
- Event processing metadata, event tags?, physics dataset selection, provenance metadata

- Torre Wenaus, Stefan Stonjek

- LCG 3D Production Phase

##  Databases in ALICE

- L.Betev
**LCG Database Deployment and Persistency Workshop**** **
- Geneva, October 17, 2005

## Databases relations and connectivity

- Examples from previous slides:
- DCDB – “quasi” distributed system, central repository is read-only, updates are infrequent
- DAQ – completely closed central system
***The biggest challenges remain the Grid file catalogue and offline conditions databases:***
- Used in a heterogeneous and often ‘hostile’ computing environment (the Grid).
- Contains data from wide variety of sources

- Latchezar Betev

- LCG 3D Production Phase

## Considerations for Conditions DB

- Sources for conditions DB and relations to other DBs are already quite complicated:
- All databases potentially containing conditions information are “closed”, i.e. only accessible at CERN
- It would be difficult to provide access methods to all DBs from the production and analysis code
- ALICE uses ROOT as offline framework base:
- Naturally defines the technology choice for object store – all conditions data are stored as ***root files***
- Additional condition - these files are read-only

- Latchezar Betev

- LCG 3D Production Phase

## Considerations for Conditions DB (2)

- Conditions data should be accessible in a Grid distributed production and analysis environment
- The root files are registered in the ***Grid Distributed File Catalogue***:
***No need*** for distributed DBMS in a traditional sense and with all accompanying problems (access, replication, authentication) – a very big plus
- Assures worldwide access to all files and associated tags
- Drawbacks:
- Replication of information
- Depends almost entirely on the Grid file catalogue functionality

- Latchezar Betev

- LCG 3D Production Phase

## Medium term plans 

- Grid file catalogue is used routinely in ALICE since several years in the scope of the physics data challenges (PDC04, PDC05, SC3)
- ROOT Grid access classes are now mature
- AliRoot Conditions DB access framework is complete
- Beginning of 2006 – Test of ALICE TPC with operational DCS and DAQ:
- Test of both DBs and in addition the offline Conditions DB
- Beginning of 2006 – Physics Data Challenge ’06:
- Final test of the entire ALICE offline framework on the Grid, including test of Conditions framework

- Latchezar Betev

- LCG 3D Production Phase

## Conclusions 

- ALICE has a rich field of databases used in the various groups for wide variety of tasks
- The development of the majority of DBs is well advanced:
- Some of them are already in production since several years
- The biggest challenge remaining is to gather the information from all sources and make it available for Grid data reconstruction and analysis:
- In this context, ALICE has decided to re-use the already existing Grid file catalogue technology
- And enrich the AliRoot framework with tools allowing connectivity to all other DBs currently in existence

- Latchezar Betev

- LCG 3D Production Phase