COOL Status and Plans Whats new in COOL

COOL – Status and Plans (What’s new in COOL 2. 0. 0) Andrea Valassi (CERN IT-PSS-DP) On behalf of the COOL team LCG Application Area Meeting, 21 st February 2007 CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it

Outline • Development overview • Deployment overview • Conclusions CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 2

Development overview • What’s new in COOL 2. 0. 0 – Major API changes – Major schema changes – Functionality enhancements – Extensive tests and bug fixes • Both internal and external (e. g. Frontier, SEAL…) – Configuration changes (CMT, Mac. OSX) • Plans after COOL 2. 0. 0 – Optimizations and new features with backwardcompatible C++ API and no schema evolution CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 3

COOL development team Active manpower • A. V. (CERN - IT/PSS) – 80% FTE since Oct. 2004 – Project coordination, core development and release mgmt • Marco Clemencic (CERN - LHCb) – 20% FTE since Nov. 2004 – Core development, release mgmt • Sven A. Schmidt (Mainz - ATLAS) – 20% FTE since Oct. 2006 – Core development – Previously 80% FTE (Oct. 2004 to June 2006) • With useful contributions from many other people – David Front (IT/LCG since April 2005) - stress tests – Richard Hawkings and other ATLAS users, testers and DBAs – The CORAL, SEAL/ROOT, SPI and 3 D teams Former collaborators • Uli Moosbrugger (Mainz - ATLAS) – 80% FTE (Sep. 2005 to March 2006) – performance optimization CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it • Kristoffer Dahl (CERN - IT/PSS summer student 2006) – 60% FTE (July-August 2006) - performance optimization A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 4

COOL 2. 0. 0: a major release COOL 2. 0. 0 (Jan 2007) COOL 1. 2. 3 (Aug 2005) COOL 1. 3. 0 (Apr 2006) COOL 1. 3. x (branch) + 2. 0. 0 (HEAD) • COOL 2. 0. 0 development started in May 2006 – Parallel development on HEAD and 1. 3. x CVS branches • A lot of changes especially since November 2006 CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it – Last branch releases 1. 3. 4 (Jan 07) and 1. 3. 3 c (Oct 06) only contain minor bug fixes and new external software A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 5

Functionality enhancements • Channel names – Uniquely mapped to channel IDs • Dynamic replication tools – Create a replica of the database at time t 1 – Dynamically update the replica at a later time t 2 • Add only the new IOVs inserted since t 1 • Scratch and copy all tag information – Not fully functional: bug fixes needed (2. 0. 1? ) CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 6

New Record. Specification API • Record. Specification and Record – Specification of persistent storage precision • Examples: “Int 64”, “String 255”, “String 16 M”, “Blob 64 k” • Record creation/update performs data validation – Replaces coral Attribute. List. Specification and Attribute. List (transient rather than persistent) – Abstract interfaces as well as concrete classes • Folder. Specification – Encapsulating Record. Specification and more – Recommended argument for create. Folder CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 7

User payload API changes • Added support for BLOB fields • New constraints on # payload fields – At most 900 fields (Oracle limit: 1000 columns) – At most 10 BLOB fields (My. SQL limit: 15) • New constraints on payload field names – Field names can only contain letters (uppercase and lowercase), numbers or the “_” character • Avoid bad SQL when using -. %$ and so on… • Empty string ‘’ is considered as NULL CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it – Oracle ‘feature’: adopt it also for My. SQL/SQLite A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 8

Other API changes • SEAL-free C++ API – No Time, Int. Bits, IMessage. Service in the API – Internal implementation still based on SEAL • Until component model is moved to CORAL • Changes in data retrieval methods – Removed defaults for IOV and channel selection – Renamed MC browse. Objects as find. Objects • Enhanced connection string syntax – Support for CORAL roles: “alias(role)/dbname” • New default mode for open. Database: R/O CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 9

New C++ API: example (1) // Open an existing database in R/W mode and create a new folder cool: : Application app; bool read. Only = false; // default is R/O (read. Only=true) cool: : IDatabase. Ptr db = app. database. Service(). open. Database( url, read. Only ); cool: : Record. Specification payload. Spec; payload. Spec. extend( "I 64", cool: : Storage. Type: : Int 64 ); // COOL type. ID //payload. Spec. extend( "I-64", cool: : Storage. Type: : Int 64 ); // exception (-) cool: : Folder. Specification folder. Spec ( cool: : Folder. Versioning: : SINGLE_VERSION, payload. Spec ); cool: : IFolder. Ptr folder = db->create. Folder( "/myfolder", folder. Spec ); cool: : Int 64 i 64 = // COOL typedef (do NOT use long – not portable) cool: : Int 64 Max; // COOL const cool: : Record payload( payload. Spec ); payload["I 64"]. set. Value( i 64 ); // performs data validation! folder->store. Object // channel# is now mandatory ( 0, 100, payload, 1 ); // t=[0, 100] in ch#1 CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 10

New C++ API: example (2) // Open an existing database in R/O mode and read from a folder cool: : Application app; cool: : IDatabase. Ptr db = app. database. Service(). open. Database( url ); // default is R/O cool: : IFolder. Ptr folder = db->get. Folder( "/myfolder" ); cool: : MSG: : Level old. Level = app. output. Level(); // SEAL-free API app. set. Output. Level( cool: : MSG: : VERBOSE ); // show SQL query! cool: : IObject. Ptr obj = folder->find. Object( 50, 1 ); // t=50 in ch#1 app. set. Output. Level( old. Level ); std: : cout << "Payload at t=50 in ch#1: " << obj->payload() << std: : endl; const cool: : IField& field = obj->payload()["I 64"]; // replaces coral: : Attribute (abstract interface) std: : cout << "Payload['I 64'] at t=50 in ch#1: " << field << std: : endl; cool: : Int 64 i 64 = field. data<cool: : Int 64>(); // const data accessor std: : cout << "I 64 at t=50 in ch#1: " << i 64 << std: : endl; CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 11

Relational schema changes (1) • Get ready for tag locking functionality – Add ‘tag lock status’ column • Implement protection for tags with lock status != 0 – Actual lock. Tag/unlock. Tag API planned for 2. 1. 0 • Can be added without requiring schema evolution • New channels table for channel metadata – Also needed for multi-channel bulk insertion – Now: channel name; later: user-defined? • Implement dynamic replication tool – Add several ‘last modification date’ columns • Use default SQL types from CORAL – Few minor changes only for My. SQL and SQLite CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 12

Relational schema changes (2) • Get ready for schema with fewer tables – Attach schema version to individual folders • COOL 2. 0. 0 cannot read/write folders > 2. 0. 0 – Actual new folder schema with fewer tables will be added in a later release (e. g. 2. 2. 0) • Can be added without requiring schema evolution • COOL 2. 0. 0 clients will still be able to read/write 2. 0. 0 folders, while 2. 2. 0 clients can also use 2. 2. 0 folders – In 2. 0. 0 schema, each folder has 5 tables • In 2. 2. 0 schema, folders with the same user payload specification will be able to share the same 5 tables • Some SQL query optimizations also postponed to 2. 2. 0 CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 13

Improved platform support • AMD 64 – Fully supported in COOL 2. 0. 0 • Thanks to new IRecord. Specification API (ambiguity for ‘long’ precision in COOL 1. 3. 0 on 32 bit/64 bit Linux) • Mac. OSX – Complete C++ private build – all tests succeed • Using private CORAL and Oracle installations • One minor issue with Oracle 10. 1 client (no 10. 2 yet) – Pending issues with Py. Cool – runtime errors • Incompatible ROOT/python installations CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 14

Improved test infrastructure • “Reference database” test package – Create a “reference database” covering several folder types, payload types and payload values – Test data retrieval and insertion of new data • Schema evolution tests – Using the reference database package • Create a database using COOL 1. 3. x • Evolve the database to COOL 2. 0. 0 • Test data retrieval and insertion of new data CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 15

Improved build infrastructure • CMT – Ready for nightly builds and releases (e. g. 1. 3. 4) – COOL 2. 0. 0 was released using SCRAM • SCRAM will be kept for internal use for some time • Nightly builds and QMTEST tests – Two slots (2. 0. 0 bug fixes and HEAD preview) – Oracle, My. SQL, SQLite, Frontier are all tested • An Oracle ‘integration’ RAC will be allocated by IT-PSS • Improvements still needed: known (expected) failures should trigger warnings (orange) instead of errors (red) CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 16

Goals for COOL 2. 1. 0 • Tag locking functionality • Performance optimizations – Multi-channel bulk insertion and a few others • Integration with LFCReplica. Service – With role-based authentication • Improved channel names API • Timescale: March 2007 – Atlas release 13 in March CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 17

Performance optimizations • Two improvements in CVS HEAD (2. 0. 1? ) – Added index for MV find. Object from user tags • Thanks to useful suggestions by Gancho and Florbela – Better SQL for SV single channel browse. Objects • Committed code developed by Kristoffer in 2006 CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 18

Goals for COOL 2. 2. 0 • New relational schema with fewer tables – And further query optimizations • CORAL component model (drop SEAL) • Tag cloning • Payload queries? – Based on the new IRecord API • Timescale: July 2007 – Atlas release 14 in August CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 19

Deployment overview • Similar Oracle setups in Atlas and LHCb – Two separate servers at CERN (online, offline) – Distributed replicas at the experiment Tier 1’s – Replication via Oracle Streams • Deployment status – Most Tier 1’s have joined • Gridka, RAL, IN 2 P 3, CNAF (both Atlas and LHCb) • BNL, Taiwan, Triumf (Atlas) – Work in progress in the remaining Tier 1’s • SARA, PIC (both); Nordugrid (Atlas) – Distributed tests underway in both Atlas and LHCb CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 20

Atlas deployment and tests COOL deployment setup in Atlas: final goal and present status (G. Dimitrov and F. Viegas, WLCG Workshop, Database BOF, Jan 07) Test data retrieval into CERN from Tier 1 replicas: effect of user tag index optimization (S. Stonjek, R. Hawkings, G. Dimitrov, F. Viegas et al. , Feb 07) CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 21

Conclusions • Many major improvements in COOL 2. 0. 0 – Major API and schema changes – Planning ahead for future enhancements • Development is far from finished – Performance issues are more and more urgent – Well defined goals for COOL 2. 1. 0 – Limited manpower (1. 2 FTE, over 60% from IT) • Major progress in distributed deployment – Services must be ready well before LHC startup CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 22

Reserve slides CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 23

COOL - deployment in LHCb • Computing model – Reconstruction at Tier 0/1 – Only MC prod at Tier 2 • COOL stores only conditions data for event reconstruction – Oracle at PIT, Tier 0, Tier 1 with replication via Streams – Geometry and conditions for MC sent to Tier 2 as SQLite file • Online db master at PIT (Marco Clemencic, COOL meeting 3 July 2006) – Replicated forward to Tier 0 and Tier 1 via Streams – Data from PVSS processes • Offline db master at Tier 0 – Replicated back to PIT and forward to Tier 1 via Streams – Data computed in offline calibration/alignment jobs CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February. COOL 2007 COOL Status And Plans - 24

COOL - deployment in Atlas ATLAS pit Computer centre ATLAS pit network (ATCN) Outside world Calibration updates CERN public network Tier-1 replica gateway Online / PVSS / HLT farm Offline master Cond. DB Online Oracle. DB Dedicated 10 Gbit link Tier-1 replica Streams replication Tier-0 recon replication Tier-0 farm (Sasha Vaniachine and Richard Hawkings, 3 D workshop 14 Sep 2006) • Replication to Tier 2’s – COOL ‘dynamic replication’, e. g. to My. SQL – as of COOL 2. 0. 0 – Evaluating COOL Frontier backend (performance, cache consistency…) CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 25

COOL - relational implementation • Modeling of condition 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 simple C++ types as CORAL “Attribute. List” CERN - IT Department CH-1211 Genève 23 Switzerland www. cern. ch/it A. Valassi – AAM 21 st February 2007 COOL Status And Plans - 26