SPIN-2 Tom Barclay Jim Gray, Don Slutz, Greg Smith, many others Microsoft Research
Scaleup - Big Database F F Build a 1 TB SQL Server database Data must be – – F 1 TB Unencumbered Interesting to everyone everywhere And not offensive to anyone anywhere Loaded – 1. 1 M place names from Encarta World Atlas – 1 M Sq Km from USGS (1 meter resolution) – 2 M Sq Km from Russian Space agency (2 m) F F Will be on web (world’s largest atlas) Sell images with commerce server.
What’s a Terabyte? 1 Terabyte 1, 000, 000 business letters 100, 000 book pages 50, 000 FAX images 10, 000 TV pictures (mpeg) 4, 000 Land. Sat images 150 miles of book shelf 15 miles of book shelf 7 miles of book shelf 10 days of video 16 earth images (100 m) Library of Congress (in ASCII) is 25 TB 1980: 200 M$ of disc 5 M$ of tape silo 1998: 100 k$ of magnetic disc 50 K$ nearline tape Terror Byte !! 10, 000 discs 10, 000 tapes 60 discs 30 tapes 3
Some Other Terror-Byte Databases F Terra. Server F Sloan Digital Sky Survey: – 40 TB raw, 2 TB cooked – EOS/DIS (picture of planet each week) – 15 PB by 2007 F Federal Mega Giga Reserve Clearing house: images of checks Tera – 15 PB by 2006 (7 year history) F Nuclear Kilo Stockpile Stewardship Program – 10 Exabytes (? ? ? !!) Peta Exa Zetta Yotta
Terra. Server is: “A shameless advertisement of WNT and SQL Server Scalability” ÊAn on-line demo and sales tool directed at IT customers and ISVs ËA test of the Sphinx VLDB features: – Load performance – Online Backup/Restore – Query Performance ÌA “cool 90 s app” – Image and Text data – Web-lication – Electronic Commerce
Application Requirements F BIG — 1 TB of data. F PUBLIC — available on the world wide web. F INTERESTING — to a wide audience F ACCESSIBLE — using standard browsers (IE, Netscape) F REAL — a real application (users can buy imagery) F FREE —cannot require NDA or money to access F FAST — impress customers for Back. Office, Storage. Works F EASY — Inexpensive to develop, deploy, and maintain
Project Partners Motivation Demo scope & quality of Spin-2 imagery Open new markets SPIN-2 for imagery sales Demo DEC Alpha & Storage. Works™ Scalability Recognized as superior h/w vendor Distribute DOQs to a wider audience Lower cost of distribution Demo Scalability of NT & SQL Server
Database & App UI F F Coverage: Range from 70ºN to 70ºS 35% U. S. , 1% outside U. S. Source Imagery: – 3. 5 TB 1 sq meter/pixel Aerial (USGS 60, 000 46 Mb B&W- 151 Mb Color IR files) – 700 GB 1. 56 meter/pixel. Satellite (Spin-2 2400 300 Mb B&W) F F Display Imagery: 80 m 225 x 150 pixel images, 1. 6 m x 3 sub-sampled views Nav Tools: – 1. 5 m place names – “Click-on” Coverage map – Expedia & Virtual Globe map F Concept: User navigates an ‘almost seamless’ image of earth 225 x 150 m tile 1. 8 x 1. 2 km 8 m browse 1. 8 x 1. 2 km 16 m thumbnail 1. 8 x 1. 2 km 32 m “city view”
Terra. Server Demo F Intranet Beta Sites: – http: //terraweb 1 – http: //terraweb 2 F Internal Beta Schedule – Mon April 27 - June 23
What Microsoft & DEC Contribute F Microsoft’s contribution: – Build an “internet UI” – Design the app and the database – Slice & Dice & Load the data. – Build “electronic stores” for USGS’ for Aerial Images to operate to sell & distribute images – Run a “robust”web site 18 months F Digital contribution: – Provide high-performance processors – provide high capacity, reliable storage. – Provide technical advice
F World’s Largest PC! – 324 disks (2. 4 TB) – 8 x 440 mhz Alpha CPU – 10 GB RAM
Site Configuration Storage. Tek Enterprise Storage Array Alpha 8400 9 HSZ 70 Ultra-SCSI Dual redundant Controllers (8 x 440) 10 GB Ram 324 9. 1 Seagate Disks 6 DLT 7000 Quantum Drives FWD SCSI Compaq 5500 4 x 200 mhz Web Servers To the Web
Software Image Server Active Server Pages Web Client Internet Information Server 4. 0 Java Viewer broswer MTS Terra-Server Stored Procedures HTML The Internet Info Server 4. 0 Sphinx (SQL Server) Microsoft Automap Active. X Server Terra-Server DB Automap Server Terra-Server Web Site Internet Information Server 4. 0 Microsoft Site Server EE Image Delivery SQL Server Application 7 Image Provider Site(s)
F “Chopped” How We Did It big images into small “tiles” – Sub-sampled tiles to create zoom levels – Tile sizes map to Lat/Lon system – Unique ID assigned to each Tile location u (Z-transform of lat/long or UTM) – Unique ID clusters adjacent tiles onto the same database & index pages F Wrote Load Management program – Runs image cutting job – Loads meta and image data into SQL – Multiple Loaders can run in parallel – Web Active Server Page controls load process
USGS Editing Process 1 Quadrangle (7. 5’ x 7. 5’) 1 “QUAD” DOQ Photo (3. 75’ x 3. 75’) 1 9 1 2 5 8 6 9 10 13 16 11 12 14 15 17 18 1 Degree Latitude Quad Cut 3 x 6 Jump, Thumb-nails & Browse Images DOQQ Origin Point 1 Degree Longitude 64 3 4 7 8 DOQ Tiles
Spin-2 Image Editing Process 48 x 96 cells per sq degree Image aligned to left corner of grid system Non-image squares (all white) are discarded Cut Images are extracted Sub. Sample Jump 32 m Thumb 16 m 8 m Tiles are cut 5 x 5, scrambled output Jpeg Browse
Spin-2 Meta Data Semi-colon delimited fields, ASCII encoding 1 records per line · · · · · 1 Field File name (of image) City 1 State 1 Country Number of Rows Number of Columns Shooting Height of Sun Date of survey (mm/dd/yyyy) · Time of survey (GMT) (hr: mn: ss) · Upper Left Latitude · Upper Left Longitude · Lower Right Latitude · Lower Right Longitude · Camera System 1 · Pixel size 1 · Copyright 1 is not required, if not present, then a blank field is present
Database Design and Load F F Build a 1 TB (2**40 B) SQL Server Database includes – Gazetteer data for searching – Image data pyramid and metadata F Load the Database – – – F Chop the big images into tiles BCP data and metadata in Allow for restart and undo of loads Create indexes Check consistency of the data Keep it Simple, no Tricks, Test the Scaling
The Image Pyramid F Zooming in on the Washington Monument 1: 1 64: 1 Jump image 1 pixel = 32 x 32 m 2 Dithered Browse image 1 pixel = 16 x 16 m 2 Dithered Thumb image 1 pixel = 8 x 8 m 2 USGS Tile image DOQ of Washington Monument 1 pixel = 1 sq meter
‘Logical’ Schema Country Place. Type State Place Image Data & Meta Data Lat/Long (U/ZGrid. Id) Theme Meta Information Tile. Log Img. Meta Tile. Meta Feature. Type Gazetteer Star schema Index on • image, place, type • image, state, country, type • image, place, state, type • image, place, country, type all lookups are fast Jump Img Browse. Img Thumb Img Tile. Img Lookup by UGrid or ZGrid ID plus resolution Lookups are fast. Indices are in DRAM (auto-magically by SQL) SQL manages all the tiles and indices Images are brought in on demand
Gazetteer Design F Classic Snowflake Schema F Top 10 Hint to RE for Cursor Select
Image Data Design F Image pyramid stored in DBMS (250 M recs)
Terra. Server File Group Design F Make 28 RAID 5 sets from 324 disks Each raid set has 11 disks (16 spare drives) F Make 4 595 GB NT volumes Each striped over 7 Raid sets on 7 controllers F Create 26 20, 000 MB files on F: , 27 on G: F DB is File Group of 53 files (1. 011 TB) F: G: H: I:
Physical Database F 53 Files. 20, 000 MB each F 16, 960, 000 extents F 135, 680, 000 pages F Separate tables for DOQ, Spin ‘Themes’ F Each image stored in column of type ‘image’ F All tile images in one (big) table F A number of indexes too
Terra. Server Tables F USGS DOQ Data – 48, 000 DOQQ images (45 -55 mb / image) – Creates 864, 000 Jump, Thumb, & Browse images (3. 5 m rows) – Creates 55. 3 m Tile images (110. 6 m rows) F SPIN-2 Data – 3200 278 MB images (approximate size) – Creates 620, 800 Jump, Thumb, & Browse images (2. 5 m rows) – Creates 15. 5 m Tile images (31 m rows) F Gazetteer Data – 1. 1 m named places (Encarta World Atlas) – 45 m cell names F Total Rows = 193. 7 M
The Loading Process F Includes Cutting Images, building BCP files, BCP meta data, BCP image data F First Load 1/97 -5/97 for Scalability Day – 190 GB actual image data, 800 GB duplicates – Pre-beta Sphinx F Second Load 12/97 -4/98 for Web Server – 750 GB actual image data, all images recut
Image Preperation and Load DLT Tape “tar” NT Do. Job Drop’N’ Load. Mgr DB Wait 4 Load Backup Load. Mgr ESA Alpha Server 4100 100 mbit Ether. Switch 60 4. 3 GB Drives Alpha Server 4100 Img. Cutter Drop’N’ Images Enterprise Storage Array STC DLT Tape Library 108 9. 1 GB Drives Alpha Server 8400 10: Img. Cutter 20: Partition 30: Thumb. Img 40: Browse. Img 45: Jump. Img 50: Tile. Img 55: Meta Data 60: Tile Meta 70: Img Meta 80: Update Place . . .
*. IMD & *. JPG Pre-Process Data NT Backup Read *. IMD files Generate Ids Generate ZLat. Long Sort by ZLat. Long Image Meta Tile Meta Load Thumb Img Load Browse Img Load Tile Img Read Image Meta Read Image Data BCP into Img. Tbl Read Tile Meta Read Tile Data BCP into Tile. Tbl “SRC”Thumb. Img. Id int Img. Meta. Id int ZLat. Long int Src. Id int Img. Type. Id int Pix. Width int Pix. Height int Img. Data Blob “SRC”Browse. Img. Id int Img. Meta. Id int ZLat. Long int Src. Id int Img. Type. Id int Pix. Width int Pix. Height int Img. Data Blob Meta & Image Load Process “SRC”Tile. Img. Id Tile. Meta. Id ZLat. Long Src. Id Img. Type. Id Pix. Width Pix. Height Img. Data int int Blob Load Tile Meta Load Img Meta Read Image Meta BCP into Tile. Meta. Id Img. Meta. Id Orig. Meta. Id Src. Id Img. Type. Id XGrid. Id YGrid. Id Hemisphere Continent xx. Lat xx. Long ZLat. Long int int smallint Img. Meta. Id int Orig. Meta. Id int Src. Id int Img. Type. Id int XGrid. Id int YGrid. Id int Img. Date Hemisphere smallint Continent smallint xx. Lat smallint xx. Long smallint ZLat. Long int Meta. Str vchar(255)
The Load Manager FA Workflow System. Manages Job ‘Steps’. F Built as an SQL Database App. Collects Stats. F Would use Data Transformation Services today
Load Statistics F 601 DOQ Jobs, 818 Spin Jobs – Each job does 3 meta BCP, 4 Image BCP steps F 5676 Image BCP Steps – 106 million total images loaded – 546 GB total. 5. 4 KB avg image size F For Tile Images (96% of the database) – avg 68, 000 images/step. max 757, 000 – avg 33 minutes/step. max 596 – total time 796 hours (33 days)
System Maintenance: Backup & Recovery F Industrial Strength – High Performance – Online Backups – Simple, Error Free Media Handling – Minimal Recovery Time
Project Phases & Characteristics F Load Phase – Ongoing Massive Data Loads – Updates to Fix Errors in Meta-Data – Backups at Key Milestones F Deployed – 7 x 24 – Some Updates to Existing Data – Small Loads as More Data Arrives – Infrequent Large Loads
SQL Server 7. 0 Backup/Restore F Features Fast F Online Backup Under Load – Minimal Impact F Just the Data F Backup Part of the Database F Minimize Recovery Time – Differential Backups, Log Backups – Restore Only Damaged Files
Backup ISVs Address Limitations F Legato Net. Worker™ F Computer Associates Arc. Serve™ F Seagate Backup Exec™ F Others… These Products support SQL Server 6. 5 None Support SQL Server 7. 0 yet.
Deployed 6/98. . . F ISV Supports SQL Server 7. 0 High Performance Backup API F ISV Supports Full Range of SQL Server 7. 0 Backup/Restore Features Tape Library SQL Server Backup API Backup Software
Backup API Performance
Verifying Backup/Restore F Minimal Risk Restore to a Separate System at DECWest – Early Problems with Unreadable Tapes Terra. Server Test System Another Terabyte of Disk!
Terra. Server Backup/Restore Factoids F Backup/Restore Rate 200 GB/Hr (57 MB/sec) F Time Required for Full Database Backup: 5 Hours F Number 36 of DLT Tape Cartridges:
Other Details F Active Server pages – faster and easier than DB stored procedures. F Commerce Server is interesting – Images the Inventory u no SKU, u millions of them – USGS built their own u they are very smart, but it is easy u masquerade as a credit-card reader. The earth is a geoid, and F Every Geographer has a coordinate system (or two). F Tapes are still a nightmare. F Everyone is a UI expert. F
Thank You! SPIN-2 Microsoft Back. Office
Скачать презентацию SPIN-2 Tom Barclay Jim Gray Don Slutz Greg
ad251bb81e3fa50ca891a5609403e26f.ppt