1 / 46

Tom Barclay Jim Gray, Don Slutz, Greg Smith, many others Microsoft Research

SPIN-2. Tom Barclay Jim Gray, Don Slutz, Greg Smith, many others Microsoft Research. Scaleup - Big Database. Build a 1 TB SQL Server database Data must be 1 TB Unencumbered Interesting to everyone everywhere And not offensive to anyone anywhere Loaded

fay
Download Presentation

Tom Barclay Jim Gray, Don Slutz, Greg Smith, many others Microsoft Research

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SPIN-2 Tom Barclay Jim Gray, Don Slutz, Greg Smith, many others Microsoft Research

  2. Scaleup - Big Database • Build a 1 TB SQL Server database • Data must be • 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) • Will be on web (world’s largest atlas) • Sell images with commerce server.

  3. What’s a Terabyte? 1 Terabyte 1,000,000,000 business letters 150 miles of book shelf 100,000,000 book pages 15 miles of book shelf 50,000,000 FAX images 7 miles of book shelf 10,000,000 TV pictures (mpeg) 10 days of video 4,000 LandSat images 16 earth images (100m) Library of Congress (in ASCII) is 25 TB 1980: 200 M$ of disc 10,000 discs 5 M$ of tape silo 10,000 tapes 1998: 100 k$ of magnetic disc 60 discs 50 K$ nearline tape 30 tapes Terror Byte !!

  4. Some Other Terror-Byte Databases • TerraServer • Sloan Digital Sky Survey: • 40 TB raw, 2 TB cooked • EOS/DIS (picture of planet each week) • 15 PB by 2007 • Federal Reserve Clearing house: images of checks • 15 PB by 2006 (7 year history) • Nuclear Stockpile Stewardship Program • 10 Exabytes (???!!) Kilo Mega Giga Tera Peta Exa Zetta Yotta

  5. TerraServer 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 90s app” • Image and Text data • Web-lication • Electronic Commerce

  6. Application Requirements • BIG —1 TB of data. • PUBLIC — available on the world wide web. • INTERESTING — to a wide audience • ACCESSIBLE — using standard browsers (IE, Netscape) • REAL — a real application (users can buy imagery) • FREE —cannot require NDA or money to access • FAST — impress customers for BackOffice, StorageWorks • EASY — Inexpensive to develop, deploy, and maintain

  7. Demo scope & quality of Spin-2 imageryOpen new marketsfor imagery sales Distribute DOQs to a wider audienceLower cost of distribution Demo Scalabilityof NT & SQL Server Demo DEC Alpha & StorageWorks™ Scalability Recognized as superior h/w vendor SPIN-2 Project PartnersMotivation

  8. Coverage: Range from 70ºN to 70ºS35% U.S., 1% outside U.S. Source Imagery: 3.5 TB 1sq meter/pixel Aerial (USGS - 60,000 46Mb B&W- 151Mb Color IR files) 700 GB 1.56 meter/pixelSatellite (Spin-2 - 2400 300 Mb B&W) 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 Concept: User navigates an ‘almost seamless’ image of earth 225x150m tile 1.8x1.2km 8m browse 1.8x1.2km 16m thumbnail 1.8x1.2km 32m “city view” Database & App UI

  9. TerraServer Demo • Intranet Beta Sites: • http://terraweb1 • http://terraweb2 • Internal Beta • Mon April 27 - May 30 • Whitepaper: • terraserver.doc

  10. What Microsoft & DEC Contribute • 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 • Digital contribution: • Provide high-performance processors • provide high capacity, reliable storage. • Provide technical advice

  11. World’s Largest PC! 324 disks (2.4 TB) 8 x 440 mhz Alpha CPU 10 GB RAM

  12. Alpha 8400 (8x440) 10GB Ram Compaq 5500 4x200mhz Web Servers Compaq 5500 4x200mhz Web Servers Site Configuration StorageTek Enterprise Storage Array 9 HSZ70 Ultra-SCSI Dual redundant Controllers 324 9.1 Seagate Disks 6 DLT7000 Quantum Drives FWD SCSI To the Web

  13. Software Terra-Server Web Site Web Client ImageServer Active Server Pages Internet InformationServer 4.0 HTML JavaViewer The Internet broswer MTS Terra-ServerStored Procedures Internet InfoServer 4.0 Internet InformationServer 4.0 Sphinx (SQL Server) MicrosoftSite Server EE Microsoft AutomapActiveX Server Automap Server Image DeliveryApplication SQL Server7 Terra-Server DB Image Provider Site(s)

  14. USGS Store Built by USGS MSCS V2.0 Based Standard Shopping Basket approach Purchase Digital Ortho Quads used by MS to build TerraServer Pricing subject to quantity Image you were viewing given away for free (public domain data) Spin-2 Store Microsoft SP built MSCS V2.0 Based Moving to V3.0 for RTW Buy Small, Medium, Large Digital image Can get Photographic print thru SPIN-2 relationship with Kodak Digital images are “sized” to make photographic prints look good TerraServer E-Commerce Microsoft does not collect or share in the revenues generated by TerraServer image sales!

  15. TerraServer Schedule • Load Data 12/15/97 - 4/5/98 • Public beta with Aerial Images 1/3/98 - 6/24/98 • Data & App Fine Tuning 4/10/98 - 5/31/98 • Remove SPIN-2 Duplicates • Find all missing images • Implement on-line update program • Fix final app bugs • Move to the IDC: 6/1/98 - 6/10/98 • Launch at Federal Enterprise Day 6/24/98 • http://terraserver.microsoft.com

  16. How We Did It • “Chopped” 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 • (Z-transform of lat/long or UTM) • Unique ID clusters adjacent tiles onto the same database & index pages • 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

  17. 1 Degree Latitude 1 Degree Longitude USGS Editing Process 1 8 9 1 “QUAD” DOQ Photo (3.75’ x 3.75’) 1 Quadrangle (7.5’ x 7.5’) 64 1 2 3 4 5 6 DOQTiles 7 8 9 10 11 12 13 14 15 16 17 18 Quad Cut 3x6Jump, Thumb-nails & Browse Images DOQQ Origin Point

  18. 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 SubSample Jump 32m Thumb Tiles are cut 5x5, scrambled output Jpeg 16m 8m Browse

  19. Database Design and Load • Build a 1 TB (2**40B) SQL Server Database • Database includes • Gazetteer data for searching • Image data pyramid and metadata • Load the Database • 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

  20. 1:1 1:1 64:1 Jump image 1 pixel = 32x32 m2 Dithered Browse image 1 pixel = 16x16 m2 USGS Tile image DOQ of Washington Monument 1 pixel = 1 sq meter Dithered Thumb image 1 pixel = 8x8 m2 The Image Pyramid • Zooming in on the Washington Monument

  21. Country State Image Data & Meta Data Theme Meta Information TileLog Place PlaceType TileMeta ImgMeta FeatureType Gazetteer Star schema Index on • image, place, type • image, state, type • image, state, country, type • image, place, state, type • image, place, country, type all lookups are fast BrowseImg TileImg Jump Img Thumb 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 ‘Logical’ Schema Lat/Long(U/ZGridId)

  22. Gazetteer Design • Classic Snowflake Schema • Top 10 Hint to RE for Cursor Select

  23. Image Data Design • Image pyramid stored in DBMS (250 M recs)

  24. I: H: F: G: TerraServer File Group Design • Make 28 RAID5 sets from 324 disks Each raid set has 11 disks (16 spare drives) • Make 4 595GB NT volumesEach striped over 7 Raid sets on 7 controllers • Create 26 20,000MB files on F:, 27 on G: • DB is File Group of 53 files (1.011TB)

  25. Physical Database • 53 Files. 20,000MB each • 16,960,000 extents • 135,680,000 pages • Separate tables for DOQ, Spin ‘Themes’ • Each image stored in column of type ‘image’ • All tile images in one (big) table • A number of indexes too

  26. TerraServer Tables • USGS DOQ Data • 48,000 DOQQ images (45-55mb / image) • Creates 864,000 Jump, Thumb, & Browse images (3.5 m rows) • Creates 55.3 m Tile images (110.6 m rows) • 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) • Gazetteer Data • 1.1 m named places (Encarta World Atlas) • 45 m cell names • Total Rows = 193.7 M

  27. The Loading Process • Includes Cutting Images, building BCP files, BCP meta data, BCP image data • First Load 1/97-5/97 for Scalability Day • 190 GB actual image data, 800 GB duplicates • Pre-beta Sphinx • Second Load 12/97-4/98 for Web Server • 750 GB actual image data, all images recut

  28. ESA LoadMgr AlphaServer4100 AlphaServer4100 60 4.3 GB Drives Image Preperation and Load DLTTape “tar” \Drop’N’ LoadMgrDB DoJob Wait 4 Load DLTTape NTBackup ... Cutting Machines LoadMgr 10: ImgCutter 20: Partition 30: ThumbImg40: BrowseImg 45: JumpImg 50: TileImg 55: Meta Data 60: Tile Meta 70: Img Meta 80: Update Place ImgCutter 100mbitEtherSwitch \Drop’N’ \Images TerraServer Enterprise Storage Array STCDLTTape Library AlphaServer8400 108 9.1 GB Drives 108 9.1 GB Drives 108 9.1 GB Drives

  29. “SRC”ThumbImg “SRC”BrowseImg “SRC”TileImg TileMeta ImgMeta ThumbImgId int ImgMetaId intZLatLong intSrcId intImgTypeId intPixWidth int PixHeight intImgData Blob BrowseImgId intImgMetaId intZLatLong intSrcId intImgTypeId intPixWidth int PixHeight intImgData Blob TileImgId intTileMetaId intZLatLong intSrcId intImgTypeId intPixWidth int PixHeight intImgData Blob TileMetaId intImgMetaId intOrigMetaId intSrcId intImgTypeId intXGridId intYGridId intHemisphere smallintContinent smallintxxLat smallintxxLong smallintZLatLong int ImgMetaId intOrigMetaId intSrcId intImgTypeId intXGridId intYGridId intImgDate DateHemisphere smallintContinent smallintxxLat smallintxxLong smallintZLatLong intMetaStr vchar(255) *.IMD & *.JPG Pre-Process DataRead *.IMD filesGenerate IdsGenerate ZLatLongSort by ZLatLong NT Backup Meta & ImageLoad Process Image Meta Tile Meta Load Browse ImgRead Image MetaRead Image DataBCP into ImgTbl Load Thumb ImgRead Image MetaRead Image DataBCP into ImgTbl Load Tile ImgRead Tile MetaRead Tile DataBCP into TileTbl Load Tile MetaRead Image MetaBCP into TileMeta Load Img MetaRead Image MetaBCP into TileMeta

  30. The Load Manager • A Workflow System. Manages Job ‘Steps’. • Built as an SQL Database App. Collects Stats. • Would use Data Transformation Services today

  31. Load Statistics • 601 DOQ Jobs, 818 Spin Jobs • Each job does 3 meta BCP, 4 Image BCP steps • 5676 Image BCP Steps • 106 million total images loaded • 546 GB total. 5.4 KB avg image size • 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)

  32. Total mB Loaded Each Day • Bottleneck varied: image supply, preprocess,network traffic, BCP.

  33. Average BCP Rate Each Day • Rate measured just over BCP calls • Single BCP stream. Client sometimes remote

  34. BCP rates for Tile Images • BCP improvements with new builds • There were also higher rates (10-13 mB/sec)for local clients

  35. System Maintenance: Backup &Recovery • Industrial Strength • High Performance • Online Backups • Simple, Error Free Media Handling • Minimal Recovery Time

  36. Project Phases & Characteristics • Load Phase • Ongoing Massive Data Loads • Updates to Fix Errors in Meta-Data • Backups at Key Milestones • Deployed • 7 x 24 • Some Updates to Existing Data • Small Loads as More Data Arrives • Infrequent Large Loads

  37. SQL Server 7.0 Backup/RestoreFeatures • Fast • Online Backup Under Load • Minimal Impact • Just the Data • Backup Part of the Database • Minimize Recovery Time • Differential Backups, Log Backups • Restore Only Damaged Files

  38. SQL Server 7.0 Backup/RestoreLimitations • No Tape Robot Support • Limited Media Management • Doesn’t Back Up the Whole NT Platform

  39. Backup ISVs Address Limitations • Legato NetWorker™ • Computer Associates ArcServe™ • Seagate Backup Exec™ • Others… These Products support SQL Server 6.5 BUT... None Support SQL Server 7.0 yet.

  40. Load Phase3/97 - 6/98 • SQL Server Backup Not Used • Backup the Database as a Set of NT files • Shut Down SQL Server During Backups Tape Library Z Z Z Backup Software Z Z Z Z Z

  41. Deployed6/98... • ISV Supports SQL Server 7.0 High Performance Backup API • ISV Supports Full Range of SQL Server 7.0 Backup/Restore Features Tape Library SQL Server Backup Software Backup API

  42. Backup API Performance

  43. Verifying Backup/Restore • Minimal Risk Restore to a Separate System at DECWest • Early Problems with Unreadable Tapes Test System TerraServer Another Terabyte of Disk!

  44. TerraServer Backup/RestoreFactoids • Backup/Restore Rate • Time Required for Full Database Backup: • Number of DLT Tape Cartridges: 200 GB/Hr (57 MB/sec) 5 Hours 36

  45. Other Details • Active Server pages • faster and easier than DB stored procedures. • Commerce Server is interesting • Images the Inventory • no SKU, • millions of them • USGS built their own • they are very smart, but it is easy • masquerade as a credit-card reader. • The earth is a geoid, and • Every Geographer has a coordinate system (or two). • Tapes are still a nightmare. • Everyone is a UI expert.

  46. Microsoft BackOffice SPIN-2 Thank You!

More Related