1 / 82

If You Want to Have your Database Sing on Key, Then You Need to Get It Tuned

If You Want to Have your Database Sing on Key, Then You Need to Get It Tuned. Bryan Dickerson, Woolpert Jim Moening, ESRI. Agenda. How Does ArcSDE Work? Database Tuning (Oracle and SQL) Best Practices for Geodatabase Design ArcSDE Management Roles & Responsibilities. How Does ArcSDE Work?.

jerica
Télécharger la présentation

If You Want to Have your Database Sing on Key, Then You Need to Get It Tuned

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. If You Want to Have your Database Sing on Key, Then You Need to Get It Tuned Bryan Dickerson, Woolpert Jim Moening, ESRI

  2. Agenda • How Does ArcSDE Work? • Database Tuning (Oracle and SQL) • Best Practices for Geodatabase Design • ArcSDE Management Roles & Responsibilities Database Tuning Water/Wastewater DB Design Conference February 2005

  3. How Does ArcSDE Work? Database Tuning Water/Wastewater DB Design Conference February 2005

  4. ArcSDE Data Storage • A feature class (layer) is comprised of 3 tables in an ArcSDE database • Business table • Feature table • Spatial index table Database Tuning Water/Wastewater DB Design Conference February 2005

  5. Business Table • Contains attributes and a spatial column • Spatial column is a key used to relate to the feature and spatial index tables Business table (Roads) Feature table (F1) Spatial Indextable(S1) Database Tuning Water/Wastewater DB Design Conference February 2005

  6. Feature Table • Stores geometry including annotation and CAD features • Also stores attributes describing the geometry such as Area and Length Business table (Roads) Feature table (F1) Spatial Indextable(S1) Database Tuning Water/Wastewater DB Design Conference February 2005

  7. Spatial Index Table • Defines the grid range for each feature • Defines the shape envelope for each feature Business table (Roads) Feature table (F1) Spatial Indextable(S1) Database Tuning Water/Wastewater DB Design Conference February 2005

  8. Spatial column CENSUS_TRACTS SHAPE 1 2 2 3 1 Business table 1 2 ArcSDE Storage Model Coordinate Information F34 2 FID POINTS 3 xy,xy,xy 1 . . . . . S34 1 2 . . . . . SP_FID xy,xy,xy GX GY 1 1 1 . . . . . xy,xy,xy 3 2 1 1 3 1 1 • Feature table stores geometry • Spatial index table stores grid tiles and envelopes • Tables join on integer Feature ID column 2 1 2 3 1 2 1 2 1 2 2 1 Database Tuning Water/Wastewater DB Design Conference February 2005

  9. Spatial index • Spatial index is used to improve performance of data retrieval • IBM DB2, Oracle, and SQL Server use grid tiles • Goal • Optimize the size of the grid tiles • Balance • Number of features referenced by each tile (features per tiles) • Number of tiles referencing each feature (tiles per feature) • Informix and Oracle Spatial use R-tree indexing • R-tree index constructs the index using the feature envelope • Do not need to add any parameters for building R-tree (built automatically) Database Tuning Water/Wastewater DB Design Conference February 2005

  10. Reducing Work With a Spatial Index • Clients use a spatial filter to reduce query results and speed up data retrieval • Example: Only fetch visible features for display • ArcSDE uses spatial index to reduce I/O • Eliminates need for full table scan • Same principle as RDBMS column index Spatial filter Database Tuning Water/Wastewater DB Design Conference February 2005

  11. Spatial Index • Spatial index uses the envelope (bounding box) of the features for fast searching 38,68 53,28 49.5,53.6 32,64 49,25 Envelopes for area and line features use min x,y and max x,y coordinates. The envelope for a point is the actual x,y coordinate. Database Tuning Water/Wastewater DB Design Conference February 2005

  12. Grid Tile Spatial Index Components 49 ParcelsExample 48 73 74 75 76 77 • Grid tiles • Two numbers in indexed columns • Shape envelope • Two coordinates (four numbers) in indexed columns • Shape • Many coordinates in binary column A Database Tuning Water/Wastewater DB Design Conference February 2005

  13. RDBMS ArcSDE server or client How the Grid Tile Spatial Index Works Goal: Avoid reading shapes for entire layer (full table scan) Four stages of elimination FILTER (SELECTION SHAPE) LAYER tile tile 1. envelope envelope 2. shape envelope 3. shape shape 4. Database Tuning Water/Wastewater DB Design Conference February 2005

  14. Selecting a Spatial Index Size • Size tiles based on average feature envelope • Grid tile size too small • S-table is too big • Many tiles referencing one feature • Grid 3x feature envelope • S-table smaller • Fewer tiles referencing one feature • Many rows take longer to search • Many tiles referencing one feature inefficient Balance tiles per feature against feature per tile Database Tuning Water/Wastewater DB Design Conference February 2005

  15. ArcSDE Command Line Tools Are Your Friend sdelayer -o si_stats -l Parcels,shape -i esri_sde -s jayhawk -D demo ArcSDE 8.2 Build 967 Thu Feb 28 22:31:11 PST 2002 Layer Administration Utility ----------------------------------------------------- Layer 6 Spatial Index Statistics: Level 1, Grid Size 1326.14 |-------------------------------------------------------------------| | Grid Records: 6438 | | Feature Records: 5099 | | Grids/Feature Ratio: 1.26 | | Avg. Features per Grid: 55.03 | | Max. Features per Grid: 181 | | % of Features Wholly Inside 1 Grid: 81.64 | |-------------------------------------------------------------------| | Spatial Index Record Count By Group | | Grids: <=4 >4 >10 >25 >50 >100 >250 >500 | |---------- ------ ------ ------ ------ ------ ------ ------ ------ | | Features: 5073 26 8 0 0 0 0 0 | | % Total: 99% 1% 0% 0% 0% 0% 0% 0%| |-------------------------------------------------------------------| Database Tuning Water/Wastewater DB Design Conference February 2005

  16. Spatial Index Tile Size Considerations • Optimal size varies by average feature envelope • Recalculate index if feature class changes • Most feature envelopes should not exceed grid size • S-table becomes too large • Features are referenced by many tiles which is inefficient • Use multiple resolutions (multiple grids) for feature classes with highly-variable feature sizes Database Tuning Water/Wastewater DB Design Conference February 2005

  17. Making Data Editable - Register As Versioned • Performed by data owner in ArcCatalog • Two tables are added to feature class • Additions table • Deletions table Database Tuning Water/Wastewater DB Design Conference February 2005

  18. Inserting Adds table Deletes table ID Name SHAPE ID Citrus 3 Adds table Deletes table Deleting ID Name SHAPE ID Citrus 2 3 Adds table Deletes table Updating ID Name SHAPE ID Citrus 2 3 1 1 Roma How Tables React to Edits Business table ID Name SHAPE 1 Roma 2 Palm Database Tuning Water/Wastewater DB Design Conference February 2005

  19. Raster Data Storage • Raster data is stored in a similar fashion to vector data • 4 tables are used to reference a raster feature class • Business table • Raster bands table (stores bands for each image) • Raster auxiliary table (stores metadata for each raster band) • Raster blocks table (stores the actual raster in a tiled format) • Blocks table is most important as it is by far the largest Database Tuning Water/Wastewater DB Design Conference February 2005

  20. Pyramids • Pyramids can be built for a raster layer when loaded into ArcSDE • Multiple resolutions of the raster layer are stored in the database • At a zoomed out scale the lower resolution is displayed • As the user zooms in to the highest level of detail, the raster as it was originally loaded (its highest resolution) is shown • This significantly helps to speed display of raster layers in ArcGIS and ArcIMS Database Tuning Water/Wastewater DB Design Conference February 2005

  21. Raster Blocks Table (SDE_BLK_1) • Raster is divided into tiles containing many pixels and each tile is a row in the table • Each tile at each pyramid level has a row in the table • This table WILL get very large! Database Tuning Water/Wastewater DB Design Conference February 2005

  22. Database Tuning for Oracle and SQL Database Tuning Water/Wastewater DB Design Conference February 2005

  23. Query Response Time • When the client submits a query, the server must: • Analyze query statement • Fetch data • Transfer results to client over network • 2 biggest performance factors are network and disk I/O • Network I/O is database independent • Scanning large tables increases disk I/O • Fetching and transmitting large results increases disk and network I/O Database Tuning Water/Wastewater DB Design Conference February 2005

  24. Network I/O • T1 line connecting a remote office to an ArcSDE database in a headquarters office can be tied up with one ArcGIS desktop performing normal operations • Solution to this is terminal emulation software such as Citrix or Terminal Server Database Tuning Water/Wastewater DB Design Conference February 2005

  25. SQL Server Basics • Terminology is different than that for Oracle • A SQL Server instance is made up of many databases • SQL Server databases are a logical container for objects such as: • Users • Roles • Tables • Etc… Database Tuning Water/Wastewater DB Design Conference February 2005

  26. SQL Server Database • A SQL Server database is comprised of data files and log files • A database has, at minimum, one data file and one log file Database Logfile Datafile Database Tuning Water/Wastewater DB Design Conference February 2005

  27. SQL Server File Groups • There can be multiple data files and log files and these can be stored in different places • A group of data files and/or log files is called a file group Database File group Logfile Logfile Datafile Datafile Database Tuning Water/Wastewater DB Design Conference February 2005

  28. Oracle Basics • Terminology is different than that for SQL Server • An Oracle database is made up of many table spaces • A table space can be made up of many data files. Also need to be aware of redo log files and control files • Table spaces are logical containers for objects such as: • Tables • Stored procedures • Roles and users are stored at the database level and not the table space level Database Tuning Water/Wastewater DB Design Conference February 2005

  29. DBTune File/Table • Actually exists as a table in the SDE database or table space • Contains storage parameters for tables and feature classes • Whenever a new feature class is created and data is loaded, the ArcSDE server process reads the DBTune table for information on how to store these new objects • There can be different parameters for different layers or data types and these are specified via keywords Database Tuning Water/Wastewater DB Design Conference February 2005

  30. Storing Layers in the SDE Database (SQL) or Table Space (Oracle) • Do not store layers in the SDE database or table space • Leave the SDE database or table space for ArcSDE application use (managing what layers are where, versioning, etc.) • Otherwise this can significantly slow down data retrieval as this database or table space will become large Database Tuning Water/Wastewater DB Design Conference February 2005

  31. Multiple Database Logins • Do not have all your editors in your organization use the same login • Each database user has a log table in the SDE database or table space that tracks a user’s actions • If all users use one login, all their actions will write to one table and this table will grow very large very fast • Doing this also prohibits from restricting user access on a layer by layer basis Database Tuning Water/Wastewater DB Design Conference February 2005

  32. Disk I/O Contention • This is the number one way to slow down an ArcSDE database • As users view or query data remember that, at minimum, ArcSDE is working through the business tables, feature tables, and spatial index tables for each layer in the map • If the layers are versioned, ArcSDE is also working through the adds and deletes tables Database Tuning Water/Wastewater DB Design Conference February 2005

  33. Preventing Disk I/O Contention • Use many disks and spread the tables and logs across those disks • In SQL Server, make use of file groups and spread the data and log files across different physical disks • In Oracle, make use of different table spaces and store the different tables (business, feature, and index) in the different table spaces on the different disks Database Tuning Water/Wastewater DB Design Conference February 2005

  34. Example SQL Server Configuration Operating System Pagefile ArcSDE Software SQL Server Software System database SDE database Business table file group Disk 1 Disk 3 Feature table file group SDE Transaction log Raster table file group Spatial index file group Disk 4 Disk 2 Database Tuning Water/Wastewater DB Design Conference February 2005

  35. Example Oracle Configuration Operating System Pagefile/Swap space ArcSDE Software Oracle Software Business table space Primary control files Disk 1 Disk 3 Feature table space Redo log Raster table space Spatial index table space Secondary control files Disk 4 Disk 2 Database Tuning Water/Wastewater DB Design Conference February 2005

  36. Disk I/O - RAID • RAID = Redundant Array of Independent (or Inexpensive) Disks • Different levels of RAID • RAID 0 = Striping. Data is striped across drives. Offers good performance but no fault tolerance. • RAID 1 = Mirroring. Data is written or mirrored to a second disk. Simple fault tolerance and performs better than a single disk. • RAID 5 = Striping w/ parity. Data is striped and parity calculations are distributed among disks. Provides good READ performance and excellent fault tolerance. • RAID 0 + 1 = Mirroring and striping. Excellent read/write performance and good fault tolerance. Database Tuning Water/Wastewater DB Design Conference February 2005

  37. Other Things You Can Do… • Reduce amount of data processed for faster queries • Provide a filter to limit number of rows • Index tables and layers to reduce searching • If you know users will search or query on particular fields, index those fields ahead of time. • ArcSDE is fastest for small, indexed queries!!! Database Tuning Water/Wastewater DB Design Conference February 2005

  38. ArcSDE Performance Tuning • Optimize Workflow • Data Loading • Data Maintenance • Reconcile/Post/Compress • Database Configuration • Keep it simple • Increase default cache sizes • Editing Operations • Use of edit cache is CRITICAL • Index Management • Regularly rebuild indexes • Collect Performance Stats • Establish baseline • Use for performance validation • Versioning Methodology • Reconcile performance costs • Using versioning to model history or alternatives • Reconcile/post/compress procedures • Database Design • Cost of complex data models • While Editing • Recursive Relationships • Feature linked annotation • Types of labels • Monitor Table Statistics • Adds and Deletes tables • SDE.state_lineages, states A good DBA is ESSENTIAL! Database Tuning Water/Wastewater DB Design Conference February 2005

  39. Tools for Managing ArcSDE Databases • Database vendor provided tools • SQL Server Enterprise Manager • SQL Server Query Analyzer • Oracle DBA Studio • ESRI provided tools • ArcCatalog • ArcSDE command line tools • Other tools • StorageVisitron (available from ArcScripts) • Version Manager ArcObjects sample (Developer Kit) Database Tuning Water/Wastewater DB Design Conference February 2005

  40. Excellent Instructor-led Classes • ArcSDE Administration for SQL Server • ArcSDE Administration for Oracle • System Architecture Design for GIS Database Tuning Water/Wastewater DB Design Conference February 2005

  41. Seamless Data Structure Topology Custom features Integrity: Domains, Subtypes, Relationship classes Geodatabase Disconnected Editing* Versioning* Multi-User Editing* Scalability* *ArcSDE Geodatabase (Enterprise) Best Practices for Geodatabase Design Database Tuning Water/Wastewater DB Design Conference February 2005

  42. support.esri.com – Project Center Database Tuning Water/Wastewater DB Design Conference February 2005

  43. support.esri.com – Project Center (cont.) Database Tuning Water/Wastewater DB Design Conference February 2005

  44. Database Design Database Tuning Water/Wastewater DB Design Conference February 2005

  45. Data Design Planning Database Tuning Water/Wastewater DB Design Conference February 2005

  46. Geodatabase Database Tuning Water/Wastewater DB Design Conference February 2005

  47. Data Models Tips and Tricks Database Tuning Water/Wastewater DB Design Conference February 2005

  48. Steps to designing geodatabases • Conceptual Design • Identity the information products to be produced with your GIS. • Identify the key thematic layers based on your information requirements. • Specify the scale ranges and spatial representations for each thematic layer. • Group representations into datasets. Database Tuning Water/Wastewater DB Design Conference February 2005

  49. Steps to designing geodatabases • Logical Design • Define the tabular database structure and behavior for descriptive attributes. • Define the spatial properties of your datasets. • Propose a geodatabase design. Database Tuning Water/Wastewater DB Design Conference February 2005

  50. Steps to designing geodatabases • Physical Design • Implement, prototype, review, and refine your design. • Design work flows for building and maintaining each layer. • Document your design using appropriate methods. Database Tuning Water/Wastewater DB Design Conference February 2005

More Related