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: <something to be filled> [optional thing(s)] Metric. Classes. Def Metric. Name <metric name> [Metric. Description <metric description string>] [Metric. URL <metric URL string>] Metric. Id <metric. Id integer> [Parameters] [<parameter name> <parameter value>] [more parameters] Metric. Fields Field. Name <field. Name string> [Field. Description <metric description string>] Field. Format <C style format string> [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 <all fields> FROM <a table name> WHERE <TIMESTAMP_constraint (specifies time range)> AND <NODEID_constraint (specifies a list of nodes)> • Prototype SQL query for metric tables: SELECT [UNIQUE] <any sub set of fields> FROM <a table name> WHERE <any logical constraint/s on any field/s> December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 15

MR_Getsamples and non- MR_Getsamples SQL example queries • MR_Getsamples example query: SELECT * FROM MR_ system_Disk. IO WHERE TIMESTAMP > 1038903460 AND TIMESTAMP <= 1038903470 AND (NODEID = 1 OR NODEID = 5) • Non- MR_Getsamples example query Look for nodes with almost full partitions: SELECT UNIQUE node. Id, Partition. Name FROM MR_ system_Disk. IO WHERE TIMESTAMP> 1038903460 AND TIMESTAMP <= 1038903470 AND Partition. Used. Perc > 30 December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 16

Another query: Latest values December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 17

Charts • Following are examples how to use tools to create charts • The idea is to get the taste what can be done (anything. . ). A (future) GUI application will enhance this and hide details • Tools: – www. benthicsoftware. com + Excel Recommended by Oracle support, Easy to use Only work on Windows – Oracle ‘Graphics Builder’ Both Windows and Linux are supported • Guideline: Use complex SQL queries to create simple data for charts December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 18

Manipulate Oracle data with www. benthicsoftware. com tools December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 19

Easy chart creation December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 20

Easy chart creation December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 21

More examples: CPU UTIL December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 22

More examples: CPU UTIL December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 23

Smart SQL queries allow Simple chart creation December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 24

Smart SQL queries allow Simple chart creation December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 25

Smart SQL queries allow Simple chart creation December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 26

Query with restricted time December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 27

Query with restricted time December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 28

Exercise: what does this query mean? December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 29

Is the chart title correct? Suggest a better way to present this information! December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 30

Better? December 02 MR RDB API - Relational DB for WP 4 fabric monitoring repository 31