PS1 PSPS Object Data Manager Design

1. PS1 PSPSObject Data Manager Design PSPS Critical Design Review November 5-6, 2007 IfA

2. Outline • ODM Overview • Critical Requirements Driving Design • Work Completed • Detailed Design • Spatial Querying [AS] • ODM Prototype [MN] • Hardware/Scalability [JV] • How Design Meets Requirements • WBS and Schedule • Issues/Risks [AS] = Alex, [MN] = Maria, [JV] = Jan

3. ODM Overview The Object Data Manager will: • Provide a scalable data archive for the Pan-STARRS data products • Provide query access to the data for Pan-STARRS users • Provide detailed usage tracking and logging

4. ODM Driving Requirements • Total size 100 TB, • 1.5 x 1011 P2 detections • 8.3x1010 P2 cumulative-sky (stack) detections • 5.5x109 celestial objects • Nominal daily rate (divide by 3.5x365) • P2 detections: 120 Million/day • Stack detections: 65 Million/day • Objects: 4.3 Million/day • Cross-Match requirement: 120 Million / 12 hrs ~ 2800 / s • DB size requirement: • 25 TB / yr • ~100 TB by of PS1 (3.5 yrs)

5. Work completed so far • Built a prototype • Scoped and built prototype hardware • Generated simulated data • 300M SDSS DR5 objects, 1.5B Galactic plane objects • Initial Load done – Created 15 TB DB of simulated data • Largest astronomical DB in existence today • Partitioned the data correctly using Zones algorithm • Able to run simple queries on distributed DB • Demonstrated critical steps of incremental loading • It is fast enough • Cross-match > 60k detections/sec • Required rate is ~3k/sec

6. Detailed Design • Reuse SDSS software as much as possible • Data Transformation Layer (DX) – Interface to IPP • Data Loading Pipeline (DLP) • Data Storage (DS) • Schema and Test Queries • Database Management System • Scalable Data Architecture • Hardware • Query Manager (QM: CasJobs for prototype)

7. High-Level Organization

8. Detailed Design • Reuse SDSS software as much as possible • Data Transformation Layer (DX) – Interface to IPP • Data Loading Pipeline (DLP) • Data Storage (DS) • Schema and Test Queries • Database Management System • Scalable Data Architecture • Hardware • Query Manager (QM: CasJobs for prototype)

9. Data Transformation Layer (DX) • Based on SDSS sqlFits2CSV package • LINUX/C++ application • FITS reader driven off header files • Convert IPP FITS files to • ASCII CSV format for ingest (initially) • SQL Server native binary later (3x faster) • Follow the batch and ingest verification procedure described in ICD • 4-step batch verification • Notification and handling of broken publication cycle • Deposit CSV or Binary input files in directory structure • Create “ready” file in each batch directory • Stage input data on LINUX side as it comes in from IPP

10. DX Subtasks DX Initialization Job FITS schema FITS reader CSV Converter CSV Writer Batch Ingest Interface with IPP Naming convention Uncompress batch Read batch Verify Batch Batch Conversion CSV Converter Binary Converter “batch_ready” Interface with DLP Batch Verification Verify Manifest Verify FITS Integrity Verify FITS Content Verify FITS Data Handle Broken Cycle

11. DX-DLP Interface • Directory structure on staging FS (LINUX): • Separate directory for each JobID_BatchID • Contains a “batch_ready” manifest file • Name, #rows and destination table of each file • Contains one file per destination table in ODM • Objects, Detections, other tables • Creation of “batch_ready” file is signal to loader to ingest the batch • Batch size and frequency of ingest cycle TBD

12. Detailed Design • Reuse SDSS software as much as possible • Data Transformation Layer (DX) – Interface to IPP • Data Loading Pipeline (DLP) • Data Storage (DS) • Schema and Test Queries • Database Management System • Scalable Data Architecture • Hardware • Query Manager (QM: CasJobs for prototype)

13. Data Loading Pipeline (DLP) • sqlLoader – SDSS data loading pipeline • Pseudo-automated workflow system • Loads, validates and publishes data • From CSV to SQL tables • Maintains a log of every step of loading • Managed from Load Monitor Web interface • Has been used to load every SDSS data release • EDR, DR1-6, ~ 15 TB of data altogether • Most of it (since DR2) loaded incrementally • Kept many data errors from getting into database • Duplicate ObjIDs (symptom of other problems) • Data corruption (CSV format invaluable in catching this)

14. sqlLoader Design • Existing functionality • Shown for SDSS version • Workflow, distributed loading, Load Monitor • New functionality • Schema changes • Workflow changes • Incremental loading • Cross-match and partitioning

15. Distributed design achieved with linked servers and SQL Server Agent LOAD stage can be done in parallel by loading into temporary task databases PUBLISH stage writes from task DBs to final DB FINISH stage creates indices and auxiliary (derived) tables Loading pipeline is a system of VB and SQL scripts, stored procedures and functions sqlLoader Workflow

16. Load Monitor Tasks Page

17. Load Monitor Active Tasks

18. Load Monitor Statistics Page

19. Load Monitor – New Task(s)

20. Data Validation Test Uniqueness Of Primary Keys Test the unique Key in each table Test Foreign Keys Test for consistency of keys that link tables Test Cardinalities Test consistency of numbers of various quantities Test HTM IDs Test the Hierarchical Triamgular Mesh IDs used for spatial indexing Test Link Table Consistency Ensure that links are consistent • Tests for data integrity and consistency • Scrubs data and finds problems in upstream pipelines • Most of the validation can be performed within the individual task DB (in parallel)

21. Distributed Loading Master Schema View of Master Schema View of Master Schema Publish Schema Publish Data Task Data Task Data Task Data Samba-mounted CSV/Binary Files Load Monitor Master LoadAdmin Slave Slave LoadSupport LoadSupport LoadSupport View of Master Schema Task DB Task DB Task DB Publish Finish

22. Schema Changes • Schema in task and publish DBs is driven off a list of schema DDL files to execute (xschema.txt) • Requires replacing DDL files in schema/sql directory and updating xschema.txtwith their names • PS1 schema DDL files have already been built • Index definitions have also been created • Metadata tables will be automatically generated using metadata scripts already in the loader

23. Workflow Changes LOAD • Cross-Match and Partition steps will be added to the workflow • Cross-match will match detections to objects • Partition will horizontally partition data, move it to slice servers, and build DPVs on main Export Check CSVs Create Task DBs Build SQL Schema Validate XMatch PUBLISH Partition

24. Matching Detections with Objects • Algorithm described fully in prototype section • Stored procedures to cross-match detections will be part of the LOAD stage in loader pipeline • Vertical partition of Objects table kept on load server for matching with detections • Zones cross-match algorithm used to do 1” and 2” matches • Detections with no matches saved in Orphans table

25. XMatch and Partition Data Flow Detections Loadsupport Load Detections ObjZoneIndx Detections_In XMatch Orphans LinkToObj_In Pm Detections_m Detections_chunk Merge Partitions Update Objects Pull Chunk LinkToObj_chunk LinkToObj_m PS1 Objects Objects_m Pull Partition Switch Partition Objects_m LinkToObj_m LinkToObj

26. Detailed Design • Reuse SDSS software as much as possible • Data Transformation Layer (DX) – Interface to IPP • Data Loading Pipeline (DLP) • Data Storage (DS) • Schema and Test Queries • Database Management System • Scalable Data Architecture • Hardware • Query Manager (QM: CasJobs for prototype)

27. Data Storage – Schema

28. PS1 Table Sizes Spreadsheet

29. PS1 Table Sizes - All Servers Sizes are in TB

30. Data Storage – Test Queries • Drawn from several sources • Initial set of SDSS 20 queries • SDSS SkyServer Sample Queries • Queries from PS scientists (Monet, Howell, Kaiser, Heasley) • Two objectives • Find potential holes/issues in schema • Serve as test queries • Test DBMS iintegrity • Test DBMS performance • Loaded into CasJobs (Query Manager) as sample queries for prototype

31. Data Storage – DBMS • Microsoft SQL Server 2005 • Relational DBMS with excellent query optimizer • Plus • Spherical/HTM (C# library + SQL glue) • Spatial index (Hierarchical Triangular Mesh) • Zones (SQL library) • Alternate spatial decomposition with dec zones • Many stored procedures and functions • From coordinate conversions to neighbor search functions • Self-extracting documentation (metadata) and diagnostics

32. Documentation and Diagnostics

33. Data Storage – Scalable Architecture • Monolithic database design (a la SDSS) will not do it • SQL Server does not have cluster implementation • Do it by hand • Partitions vs Slices • Partitions are file-groups on the same server • Parallelize disk accesses on the same machine • Slices are data partitions on separate servers • We use both! • Additional slices can be added for scale-out • For PS1, use SQL Server Distributed Partition Views (DPVs)

34. Distributed Partitioned Views • Difference between DPVs and file-group partitioning • FG on same database • DPVs on separate DBs • FGs are for scale-up • DPVs are for scale-out • Main server has a view of a partitioned table that includes remote partitions (we call them slices to distinguish them from FG partitions) • Accomplished with SQL Server’s linked server technology • NOT truly parallel, though

35. Scalable Data Architecture S1 Detections_S1 Objects_S1 Head Objects Objects_S1 Objects_S2 S2 Detections_S2 Objects_S3 Objects_S2 Detections DPV Detections_S1 Detections_S2 Detections_S3 S3 Detections_S3 Objects_S3 • Shared-nothing architecture • Detections split across cluster • Objects replicated on Head and Slice DBs • DPVs of Detections tables on the Headnode DB • Queries on Objects stay on head node • Queries on detections use only local data on slices

36. Hardware - Prototype Storage: S3 PS04 4 10A = 10 x [13 x 750 GB] 3B = 3 x [12 x 500 GB] 2A Server Naming Convention: Function: S2 PS03 4 LX = Linux L = Load server S/Head = DB server M = MyDB server W = Web server PS0x = 4-core PS1x = 8-core 2A S1 PS12 8 L2/M PS05 4 A 2A W PS02 4 Head PS11 8 L1 PS13 LX PS01 4 8 B 2B 2A A Web Staging MyDB Function DB Loading 39 TB 0 TB 9 TB 10 TB Total space RAID10 RAID10 RAID10 RAID5 RAID config 12D/4W 14D/3.5W Disk/rack config

37. Hardware – PS1 Ingest Queries Queries Queries Queries Queries Live (Copy 1) Offline (Copy 2) Spare (Copy 3) Live (Copy 1) Offline (Copy 2) Spare (Copy 3) Replicate Live (Copy 2) Offline (Copy 1) Spare (Copy 3) Replicate Live (Copy 1) Live (Copy 2) Spare (Copy 3) • Ping-pong configuration to maintain high availability and query performance • 2 copies of each slice and of main (head) node database on fast hardware (hot spares) • 3rd spare copy on slow hardware (can be just disk) • Updates/ingest on offline copy then switch copies when ingest and replication finished • Synchronize second copy while first copy is online • Both copies live when no ingest • 3x basic config. for PS1

38. Detailed Design • Reuse SDSS software as much as possible • Data Transformation Layer (DX) – Interface to IPP • Data Loading Pipeline (DLP) • Data Storage (DS) • Schema and Test Queries • Database Management System • Scalable Data Architecture • Hardware • Query Manager (QM: CasJobs for prototype)

39. Query Manager • Based on SDSS CasJobs • Configure to work with distributed database, DPVs • Direct links (contexts) to slices can be added later if necessary • Segregates quick queries from long ones • Saves query results server-side in MyDB • Gives users a powerful query workbench • Can be scaled out to meet any query load • PS1 Sample Queries available to users • PS1 Prototype QM demo

40. ODM Prototype Components • Data Loading Pipeline • Data Storage • CasJobs • Query Manager (QM) • Web Based Interface (WBI) • Testing

41. Spatial Queries (Alex)

42. Prototype (Maria)

43. Hardware/Scalability (Jan)

44. How Design Meets Requirements • Cross-matching detections with objects • Zone cross-match part of loading pipeline • Already exceeded requirement with prototype • Query performance • Ping-pong configuration for query during ingest • Spatial indexing and distributed queries • Query manager can be scaled out as necessary • Scalability • Shared-nothing architecture • Scale out as needed • Beyond PS1 we will need truly parallel query plans

45. WBS/Development Tasks 2 PM 3 PM 1 PM 3 PM 3 PM 1 PM 2 PM 2 PM 2 PM 2 PM 4 PM 4 PM Refine Prototype/Schema Staging/Transformation Initial Load 4 PM Load/Resolve Detections Workflow Systems Logging Data Scrubbing SSIS (?) + C# Resolve/Synchronize Objects Create Snapshot Replication Module Query Processing 2 PM Hardware QM/Logging Redistribute Data Total Effort: 35 PM Delivery: 9/2008 Documentation Testing

46. Personnel Available • 2 new hires (SW Engineers) 100% • Maria 80% • Ani 20% • Jan 10% • Alainna 15% • Nolan Li 25% • Sam Carliles 25% • George Fekete 5% • Laszlo Dobos 50% (for 6 months)

47. Issues/Risks • Versioning • Do we need to preserve snapshots of monthly versions? • How will users reproduce queries on subsequent versions? • Is it ok that a new version of the sky replaces the previous one every month? • Backup/recovery • Will we need 3 local copies rather than 2 for safety • Is restoring from offsite copy feasible? • Handoff to IfA beyond scope of WBS shown • This will involve several PMs

48. Mahalo!

49. Query Manager MyDB table that query results go into Check query syntax Name that this query job is given Context that query is executed in Get graphical query plan Query buffer Run query in quick (1 minute) mode Load one of the sample queries into query buffer Submit query to long (8-hour) queue

50. Query Manager Stored procedure arguments SQL code for stored procedure