f MON MR RDB API Relational DB for

f. MON MR RDB API Relational DB for WP 4 fabric monitoring repository David Front Weizmann Institute, Israel December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 1

Contents 1. 2. 3. 4. 5. 6. 7. 8. Topic Motivation RDB SW components Main steps RDB requirements DBs evaluation DB design Two ways to query the DB Charts December 02 Page/s 3 4 5 6 -7 8 -10 11 -14 15 -17 18 -31 MR RDB API - Relational DB for WP 4 fabric monitoring repository 2

Supporting Relational DB in addition to flatfile DB: motivation • Slow retrieval from flatfile DB repository • Relational DB is the standard way to save data • Rich query possibilities via SQL • Enhanced techniques to organize data and access data: • Partitions • Warehouse (smart indexing) • Use existing tools to: • View data (GUI/Web) • Support redundancy and backup • Analyze DB performance (bottlenecks) • Scalability • Robustness December 02 Central repository Flatfile DB Add: relation DB MR RDB API - Relational DB for WP 4 fabric monitoring repository 3

RDB SW components SQL Monitored entitiy Customer DB client Monitored entitiy thread Soap / Administrative XML threads: External MR API Monitored entitiy entity External Internal MR API DB client DB server December 02 MR API DB server DB client Server Each thread May have connection to DB Monitoring server / Central repository MR API External MR API Legend: • Partitions • Warehouse • Backup Internal Various RDBs and flatfile DB share the same ‘remote MR API’ Various RDBs and flatfile DB implement ‘MR API’ differently Administrative Components specific for each relational DB. Flatfile has different components. MR RDB API - Relational DB for WP 4 fabric monitoring repository 4

Supporting Relational DB in addition to flatfile DB: main steps • Define repository (RDB) requirements • Evaluate DBs • Development: – Support MR API (like flatfile DB does): • Oracle, with PRO*C API Current step – TBD: SQL WEB GUI – TBD: RDB may not fit metrics that create text files • Open DB – Optimizations Enhance performance, no API behavior changes: • Insertion: Insertion speed (sql*loader) • Retrieval: Support partitions and warehouse – Backup December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 5

Repository (RDB) speed requirements Required data volume: Collect data up to a few (10) thousands of nodes Online data for the last X (12) months Fast enough insertion speed: Quantity estimations: – Each node sends up to 100 different monitoring variable kinds, divided to ‘metric’ groups. – Each metric sends data between once every 10 seconds to once a day. Fast enough retrieval speed: – Answer any query concerning latest 10 minutes, from a table(/partition) of up to 1 M records in less than 60 seconds – Query response time requirement for longer time scope: TBD December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 6

Requirements: total amount of data Amount of records nodes metric types polls/ hours/ records/ day second records/ 10 minutes records/ day prototype 500 10 120 24 ~0. 16 K 0. 1 M ~14, 000 maximum 10000 100 24 ~60 K 40 M ~6, 000, 000 Test bed 10000 1 -2 K 0. 5 -1 M Amount of data nodes metric types polls/ hours/ message size day [byte] total Size/ second [KB] total Size/ day [MB] prototype 500 10 120 24 10 ~1. 6 ~140 maximum 10000 100 24 100 ~6, 600 ~600, 000 December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 7

Testing DBs for fabric monitoring Insertion Required 1 -2 K rec/sec Mysql 3 Oracle Usual: 1. 5 Sqlloader: >10 Pg Select DB Postgresql 3 0. 5 Yes 2 1 No 2 0. 5 No My. Sql 0. 5 Oracle • All DBs successfully answer queries: – – • # Table size With tables [M rec] inserts ? At Join With no joins (like MR API MR_get. Samples()) In less than 60 sec, table size is 1. 0 M records, insertion rate 1 K rec/sec. Queries with joins cause performance problems December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 8

DB comparison, August 02 Topic Reliability importance: 3 max My. Sql Postgresql Oracle + + - 3 - + + Redundancy 2 Insertion (small table) 3 ++ - + (usual: +-, sqlloader: ++) Select – small table (correlation) with joins 3 + ++ - Select – large table (report) 3 ? ? ? Parallelism 2 - - + Partitions ? - - + Warehouse 2+ - - + Foreign keys Features 3 Robustness Performance Transactions 2+ - + + 3 + + + 6+ 4 - 8+ 1 - Support and maintenance Summary 4+ 6 - *+ * 5+ 5 - * Update: new version of My. Sql December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 9

Strong points of DBs • Mysql: Insertion + select ok TBD: features at new version • Oracle: Many features Very robust TBD: sqlloader: Very fast insertion • Postgresql: Select speed Features Robust December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 10

Per metric tables examples Table: MR_space. Used Field Name seqnum Field Format -- Example values node. Id Time stamp Recv Time %Ld %Ld Each metric has one table Space Used %ld 1 87838 103890 3470 103890 3472 Common fields DB is aware of field names (unlike flatfile system) 21 Per metric Fields, defined at Metric. Class. Def Table: MR_system_Disk. IO Field Name seqnum Field Format -- Example values 87839 December 02 node. Id Time stamp Recv Time Partition Name Partition Type Partiton Size Partition Used Perc Partiton Read Rate Partiton Write Rate Partiton Use Rate %Ld %Ld %4 s %8 s %ld %10. 1 f %ld 1038903 470 1038903 472 sda 1 ext 3 1 17225 26 0 MR RDB API - Relational DB for WP 4 fabric monitoring repository 3. 4 99 11

Simulating small and big tables with partitions or (sub)tables Big tables: 1 year Small tables: 10 minutes now past 1 ? Last 10 minutes 2 Table kinds December 02 future 5 minutes time Legend: A 5 minute partition (Oracle) or table (not Oracle) MR RDB API - Relational DB for WP 4 fabric monitoring repository 12

# # # # # #0 Configuration: Metric. Class. Def syntax Metric. Classes. Def syntax: -----------Notation: [optional thing(s)] Metric. Classes. Def Metric. Name [Metric. Description ] [Metric. URL ] Metric. Id [Parameters] [ ] [more parameters] Metric. Fields Field. Name [Field. Description ] Field. Format [more fields] [more metrics] Indentation levels: 1 2 3 December 02 4 5 MR RDB API - Relational DB for WP 4 fabric monitoring repository 13

Configuration: Metric. Class. Def example MR Metric. Classes. Def #… # 14 Metric. Name system. Disk. IO Metric. Description "name, type, size(MB), used(%)…. “ Metric. Id 9101 Metric. Fields Field. Name Partition. Name Field. Description “Partition name" Field. Format %4 s Field. Name Partition. Type Field. Format %8 s Field. Name Partiton. Size Field. Format %ld Field. Name Partition. Used. Perc Field. Format %ld Field. Name Partiton. Read. Rate Field. Format %10. 1 f Field. Name Partiton. Write. Rate Field. Format %10. 1 f Field. Name Partiton. Use. Rate Field. Format %ld December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 14

SQL’s wider query capabilities than MR API MR_Getsamples() • MR_Getsamples prototype query: SELECT FROM WHERE AND • Prototype SQL query for metric tables: SELECT [UNIQUE] FROM WHERE December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 15