# Presentation: 506263

## BTeV Database Workshop

- Summary

- D. Menasce - I.N.F.N. Milano

- Day 1
- 1.  Database design
-             database design (constraints)
-             application considerations
-             architectures (3 tier vs 2 tier)
-             when to use what database
-             middleware - and interfaces
-             process of the DB design - what to start from
-             DB designer - how to become a specialist
- 2.  Comparison of databases and tools
-             possible contenders and their estimated longevity
-             associated design tools

- _Arguments of discussion_

- BTeV Database Workshop Summary

## Day 2 

- 1.Run II experiences
-             What worked and didn't.
-             What should/should not be in databases.

- Day 3
- 1.DB applications and requirements
-             What kind of DB applications we can foresee?
-             What can be a list of an application requirements?
-                 purpose of the database
-                 source of the data -- where is the information that
-                 populates the database generated?
-                 quantity of the data
-                 anticipated use of the data/access patterns

- BTeV Database Workshop Summary

## BTeV will achieve its physics goals 

-   Important concern is **evolution schema** in the design
-   On-line and off-line DB have different requirements

-   Keep in mind **Joel’s Prime Directive**

**BTeV will achieve its physics goals**

- architectures

- BTeV Database Workshop Summary

## architectures

-   CDF experience shows **Three Tiers of Management** is a good approach

-   Always remember **sociology** is an important constrain:

- DBA:         they speak SQL
- Oracles:     they speak both C++ and SQL
- AP:            they speak C++ and even order breakfast in SQL
- Physicists:  Speak E=mc2 and make dog’s dinner of C++

-   Provide excellent public interfaces and documentation

- (St.Denis, CDF)

-   Design should conceal horrendous details (Corba backends and such) from
-    final user

- BTeV Database Workshop Summary

## when to use what database

-   Continental divide is difficult to define, but criteria are:

-   Time critical (real time?)
-   Size
-   Data integrity
-   Back ups
-   Very advanced features

-   ORACLE for heavy-duty (proven efficiency) but expensive. Requires big
-     effort in terms of number of experts.

-   MySQL and/or Postgres good candidates for middle size DB

- Comparison of databases and tools

- BTeV Database Workshop Summary

## MySQL vs PostrgreSQLperformance

- MySQL vs PostrgreSQLperformance

- Concurrency test results

- (Semenov)

- BTeV Database Workshop Summary

## MySQL vs PostrgeSQL

- (Semenov)

- BTeV Database Workshop Summary

## DB Roll-Out Procedure

- process of the DB design - what to start from

**Rough draft. ****Plan with rough idea**** of what the functionality, data and relationships among the tables might be. **
**Use ****Oracle Designer**** to create the schema design, and designer repository. **
**Employ the DDL which is created from Designer to create the tables, keys, triggers, etc. **
**All development is performed using a "development" instance of the database. ****DDL saved in cvs****. **
**Must provide rate and sizing information for DBA's. **
**An INT instance is created with controlled DDL and thoroughly tested. **
**When the application is ready for production use, DDL has version control imposed and the application is transitioned to PRD database instance. The methodology for version cutting and evolution is written up. **

-   Design by experts with physicists sitting on their shoulders

-   Many iterations to converge to desired functionality (evolution schema again)

- (Lueking, D0)

-   Provide testing tools at early stage (both HW platforms and SF components)

- BTeV Database Workshop Summary

## Having so many database developers is complex.

- DB designer - how to become a specialist

-   Tier schema helps: no need to become a real expert (maybe)

- The middle tier architecture added significantly to the development time. This was exacerbated (to say the least) by the middle tier tools not being ready in time. Probably worth it, for most applications.
- Having the databases administered centrally by professionals is a real good idea.

- (Lueking, D0)

-   Central problem in overall BTeV software

- Should we address problem of transition to modern technologies
- organizing “schools” with experts? (C++, OO issues, DB issues, ...)

- BTeV Database Workshop Summary

## Online Calibration Coordinator

- 1.Run II experiences
-             What worked and didn't.
-             What should/should not be in databases.

- Sets policy for calibration writers
- Liaison for support software
- First line debug
- Provides a team that can carry a pager
- CDF Does not Have one!

-   No major failure/problem reported by D0 and CDF
-   Big effort by D0 to provide fail-over service: no need at the end since system
-    (SUNs) proved to be very reliable (can we afford same approach?)

- (St.Denis, CDF)

- BTeV Database Workshop Summary

## DB applications and requirements 

- 1) Detector construction tracking databases:
- 2) Calibration databases:
- 3) Configuration databases:
- 4) Monitoring databases:
- 5) Event-related databases:
- 6) Analysis and Simulation support databases:
- 7) Documentation and general information databases:

-   For each envisaged category, a WEB interface is considered essential
-   APIs of database to scripting languages is important to this extent
-   Design of WEB server is crucial for efficiency (application server better that CGI)

-  On-line and off-line applications have crossed access paths to almost all these
-    DB categories. Again, good design in the de-coupling of the core services and
-    the application front-end is crucial.

-  For certainty some category has escaped this zero-th order list: good insulation
-    layer could provide room for later addition with minimal impact (hopefully).

-  Longevity of the DB is an overall requirement: how do we access data after 10
-   years? What are the tools of the trade to accomplish this? Answers were not
-   given in this workshop.

- BTeV Database Workshop Summary

## Where do we go from here? 

- DB requirements need to be refined
- Use patterns (how will people use the db in the end)
- How does it connect with the rest of the software
- Where can Physists experiment with the kind of data that needs to be saved?
- D0 – code generator answered this requirement. It gives you a script. This will be in the private DB instance.
- What if you want to add a column or add a row? Do you have to go through 3 people to do that? Does it actually modify the production db?
- No, you play in your own db
- If you want to change the production db, you need to go through the db person – one person who is in charge of the schema. You can version your table and data, when you decide to make it official then you can go through the procedure. Most will not use this, they will want a tool.
- CDF has “books” which are copies of the db that are used as playgrounds by groups.

- BTeV Database Workshop Summary

## Where do we go now (cont.)

- Question (Harry) – There is an Oracle vapor ware rumor
- depends which features. The newest features (OO) are probably not working correctly yet. They are known to do this. The relational db features are working well.
- Is this subset (relational) just as good in MySQL ?See Pavel’s talk
- Assessment of tools
- What are people using now?
- Gather information on what is available
- Thanks to all

- BTeV Database Workshop Summary