Configuring and Tuning Databases on the Solaris Platform
暫譯: 在Solaris平台上配置與調整資料庫

Allan N. Packer, Sun Microsystems Press

Configuring and Tuning Databases on the Solaris Platform
  • 出版商: Prentice Hall
  • 出版日期: 2001-12-06
  • 售價: $2,080
  • 貴賓價: 9.5 折 $1,976
  • 語言: 英文
  • 頁數: 552
  • 裝訂: Paperback
  • ISBN: 0130834173
  • ISBN-13: 9780130834171
  • 相關分類: 資料庫

    • 已絕版

買這商品的人也買了...

相關主題

商品描述

  • Turbocharge your Oracle, DB2, Sybase, or Informix database environment!
  • Covers every aspect of database and Solaris Operating Environment tuning
  • Optimize data layout, CPU and memory configuration, and system sizing
  • Identify and resolve bottlenecks, step-by-step
  • Understand industry-leading benchmarks

Maximize the performance of any enterprise database running on the Solaris Operating Environment!

Using this book, database professionals can optimize the performance and cost-effectiveness of virtually any database application running on the Solaris platform. Database specialist Allan Packer presents start-to-finish guidance for optimizing all four leading Solaris platform databases: Oracle (including Oracle9i), DB2, Sybase, and Informix XPS. Drawing on years of expertise as an engineer at Sun, Packer brings together best-practice guidelines for every aspect of Sun database server tuning.

  • Optimizing data layout, CPU and memory configuration, and system sizing
  • Identifying and resolving bottlenecks: a systematic, drill-down monitoring approach
  • Understanding database optimizers, database buffer cache, and other key subsystems
  • Demystifying the industry's leading benchmarks-and recognizing their limitations
  • Understanding the impact of Java technology on database application design
  • Contains a quick primer on database architecture and concepts

Table of Contents

I. DATABASES ON SUN SERVERS.

1. Sun's Relationship with Database Vendors .
The Nature of the Relationship. Engineering. Marketing. Joint Sales Activities. The Major Database Suppliers. Sun and DB2 for Solaris. Sun and Oracle. Sun and Sybase. Sun and Informix. Other Databases on Sun.

2. The Role of Database Engineering at Sun.
Sun's Database Engineering Group. Related Efforts Within Sun. The Ambassador Program.

3. Solaris Optimizations for Databases.
Scheduling Optimizations. Preemption Control. Dispatch Table Modifications. I/O Optimizations. The pread and pwrite System Calls. Kernel Asynchronous I/O. Unix File System Enhancements. Other I/O Optimizations. Other Optimizations. Compiler Optimizations. Intimate Shared Memory. Dynamic Reconfiguration. Dynamic Intimate Shared Memory. Reconfiguration Coordination Manager.

4. Hardware Architecture and Databases.
Hardware Architectures. Symmetric Multiprocessor (SMP) Systems. Nonuniform Memory Architecture (NUMA) Systems. Massively Parallel Processor (MPP) Systems. Chip-Level Multiprocessing (CLMP) Systems. Databases on SMP and NUMA Systems. Shared-Disk Databases on SMPs. Shared-Nothing Databases on SMPs. Should You Split Up? To Partition or Not to Partition?

II. Database Architecture.

5. Introduction to Database Architecture.
Architecture of Relational Databases. Data Store. Database Engine. Query Optimizer. Database Shared Memory. Logger. Pagecleaners. Database Recovery Process. Locking Subsystem. System Monitor. Command Interface. APIs. Database Monitoring. Process Models. 2n Architectures. Multithreaded Architectures. Parallel Processing. Distributed Databases. Replicated Databases. Transaction Monitors. Transactions and Ad Hoc Queries. User Multiplexing. Transaction Routing.

6. Database Workloads.
Online Transaction Processing (OLTP). Decision Support Systems (DSS). Data Warehouses and Data Marts. Operational Data Stores. Data Mining. Batch Workloads. Online Analytical Processing (OLAP).

7. The Role of the Buffer Cache.
Overview of the Buffer Cache. Monitoring the Buffer Cache. An Acceptable Cache Hit Rate. The Cache Hit Rate Confusion. Cache Hit Rate Guidelines. A Worked Example. Sizing the Buffer Cache. Influence of Buffer Cache Size on Throughput. Influence of Buffer Cache Size on Data Cache Hit Rate. Influence of Page Size on Buffer Cache Effectiveness.

8. The Role of the Database Optimizer.
Query Optimizers. Query Compilation. Query Optimization. Factors Affecting Query Optimization. Optimization Methods. Table Access. Table Join Order. Join Methods. Executing the Query Plan. Reducing the Workload. Data Partitioning. Denormalization. Concatenated Indexes. Exotic Optimizations. Expert Intervention.

9. Oracle Architecture.
Process Model. Memory Management. System Global Area (SGA). Program Global Area (PGA). Physical Data Storage. Tablespaces. Redo Log Files. Control Files. Logical Data Storage. Segments. Extents. Blocks. Partitions. System Tablespace. Parallel Processing. The Parallel Query Option (PQO). Other Parallel Capabilities.

10. Sybase Architecture.
Process Model. Memory Management. Data Cache. Procedure Cache. Physical Data Storage. Logical Data Storage. Tables and Indexes. Table Partitioning. System Databases. Parallel Processing. Adaptive Server IQ with Multiplex. Column Storage. Indexing. I/O and Caches. Parallelism. IQ Multiplex. Interfaces.

11 Informix XPS Architecture.
Process Model. Coservers. Virtual Processors. Memory Management. Buffer Pool. DS Memory. Dynamic Memory Allocation. Page Cleaning. Physical Data Storage. Logical Data Storage. Pages. Extents. Dbspaces. Dbslices. Tables and Indexes. Logical and Physical Logs. Fragmentation. Tblspaces. System Database. Parallel Processing. Query Parallelism. Colocated Joins.

12. DB2 for Solar is Architecture.
Process Model. Memory Management. Bufferpools. Bufferpool Management. Physical Data Storage. Tablespaces. Log Files. Logical Data Storage. System Tablespaces. Parallel Processing. DB2 Universal Database Enterprise Edition (EE). DB2 Universal Database Enterprise-Extended Edition (EEE). Other DB2-Related Products.

III. SIZING AND CONFIGURING SUN DBMS SERVERS.

13. Sizing Systems for Databases.
Basis of a Sizing Estimate. Minimum Requirements. Limitations of Estimates. The Right Questions. What type of workload is it? What is the status of the application? How many users? Do processing requirements vary with work shifts? How are users connected to the database server? What are the response time expectations? What I/O capacity and throughput are required? How much memory is required? What is the expected system growth rate? Using Published TPC Results for Sizing. Using TPC-C to Size Real-World OLTP Servers. Using TPC-D or TPC-R to Size Real-World DSS Servers. Using TPC-H to Size Real-World DSS Servers. Using Remote Terminal Emulators. Summary of Rules of Thumb. A General-Purpose OLTP Sizing Tool. Background. Establishing Metrics. The Search for Simplifying Assumptions. Gathering the Raw Data. Asking the Right Questions. Building a Model. Validating the Model. The Resulting Tool. Conclusions. Bibliography.

14. Configuring Systems.
Solaris Configuration. Memory Interleaving. Benefits of Memory Interleaving. Evaluation of Interleaving.

15. Configuring CPU.
Managing Workloads. Domains. Processor Sets. Resource Management. CPU Performance. Process Binding. Solaris Scheduling Classes.

16. Configuring The Network.
Performance Considerations. Availability Considerations.

17. Data Layout.
Storage Subsystems. Individually Addressable Disks. RAID-Based Arrays. Storage Area Networks (SANs). Network Attached Storage (NAS). Volume Managers. Veritas Volume Manager (Veritas). Solaris Volume Manager (SVM). RAID Manager (RM6). Component Manager. GUI Administration. Relative Performance. Data Layout Technologies. Introduction to RAID. Concatenation. RAID 0: Striping. RAID 1: Mirroring. RAID 0+1: Striping Plus Mirroring. RAID 1+0: Mirroring and Striping. RAID 3: Striping with Dedicated Parity Disk. RAID 5: Striping with Distributed Parity. Summary of RAID Technology. Database Files on UFS. Database Files on Raw Devices. Write Caches. Data Layout Strategies. Efficient Data Layout. Raw Devices vs. UFS. The Right and Wrong Way to Stripe. Placing Data and Indexes. Laying Out Data on a Single Disk. Write Caches for Improved Performance. High Availability. Disk Failure Planning. Performance Implications of Mirroring. Elimination of Single Points of Failure. Other Data Layout Issues. Database File Naming Using Symlinks. Migration Between Raw Devices and File Systems. RAID 5 Performance with Databases. The Implications of RAID for Database Optimizers. Volume Manager Pros and Cons. Storage Cocktails. Data Layout Recommendations. A Worked Example.

IV. PERFORMANCE MONITORING AND TUNING.

18. Troubleshooting Methods.
Problem-Solving Strategy Development. The Nature of the Problem. Possible Contributors to the Problem. Drilling Down to Find the Root Cause.

19. Major Contributors to Poor Performance.
Performance Problem Identification. Poorly Designed Applications. Poor Database Design and Implementation. Problems with the User Environment. Poor Data Layout. The Next Step.

20. System Performance Monitoring Tools.
Basic Solaris Tools. Monitoring Intervals. Other Monitoring Tools.

21. Drill-Down Monitoring.
STEP 1. Monitoring Memory. STEP 2. Monitoring Disks. STEP 3. Monitoring Networks. STEP 4. Monitoring CPUs. STEP 5. Monitoring and Tuning a Database. EXTRA STEP: Checking for ISM.

22. Monitoring and Tuning Oracle.
Managing Oracle Behavior. Running Administrative Commands. Viewing Current Oracle Tunable Parameters. Changing Tunable Parameters for Oracle. Making Dynamic Parameter Changes Persistent. Viewing and Changing Hidden Parameters. Monitoring Error Messages. Using Oracle Enterprise Manager. Monitoring Oracle System Tables. Generating Explain Plans. Calculating the Buffer Cache Hit Rate. Monitoring Oracle with utlbstat/utlestat. The Library Cache. User Connections. Systemwide Wait Events. Latch Wait Events. Buffer Busy Wait Statistics. Rollback Segments. Modified init.ora Parameters. Dictionary Cache Statistics. Tablespace and Database File I/O Activity. Date, Time, and Version Details. Monitoring the Shared Pool. Tuning Oracle. Tuning init.ora. Setting Tunable Parameters for OLTP Workloads. Setting Tunable Parameters for DSS Workloads. Applying Other Tuning Tips. Using Oracle with File Systems. Optimizing Oracle Load Performance. Planning for Indexes. Using an SGA Larger Than 2 Gbytes. Reconfiguring Oracle9i Dynamically. Oracle9i Dynamic System Global Area. How Oracle Chooses Between ISM and DISM. The Benefits of Using Dynamic SGA. Recovering Oracle. The Influence of Checkpoints on Recovery Time. The Influence of Checkpoints on Performance. The v$instance_recovery view. Other Parameters Influencing Recovery.

23. Monitoring and Tuning Sybase.
Sybase ASE Monitoring. Sybase Central Monitor Output. Sybase sp_sysmon Stored Procedure Output. Configuring Sybase ASE. Calculating the Buffer Cache Hit Rate. Monitoring Error Messages. Generating Query Plans. Tuning Sybase ASE. Tuning Memory. Tuning I/O. Tuning the CPU. Tuning Parallel Features. Tuning Other Aspects

24. Monitoring and Tuning Informix XPS.
Informix XPS Monitoring. Examining Informix Utilities. Changing Informix XPS Tunable Parameters. Monitoring Error Messages. Generating Query Plans. Monitoring Buffer Pool Behavior. Monitoring Scan Type. Monitoring Queries. Tuning Informix XPS. Tuning Parallel Features. Tuning Memory. Tuning I/O. Tuning CPU. Tuning Log Buffers. Optimizing Load Performance. Tuning Inter-Coserver Communication.

25. Monitoring and Tuning DB2 for Solaris.
Monitoring DB2. Changing DB2 Tunable Parameters. Monitoring Error Messages. Generating an Explain Plan. Monitoring the Cost of SQL Statements. Monitoring DB2 Processes. Managing Bufferpools. Calculating the Cache Hit Rate. Tuning DB2 for Solaris for DSS Workloads. Choosing a Partitioning Method. Choosing a Page Size. Tuning I/O. Tuning Bufferpools. Tuning Parameters That Influence the Optimizer. Tuning Other Important Parameters. Tuning DB2 for Solaris for OLTP Workloads. Choosing a Page Size. Tuning I/O. Tuning Bufferpools. General Tuning Tips. Balancing Tablespace Activity. Controlling Lock Activity. Configuring Agent Processes. Limiting Open Files.

26. Metrics: How to Measure and What to Report.
Common Performance Metrics. System Metrics. Application Metrics. Application Instrumentation. System and Application Metrics Combined. Choosing Statistics. Scalability Demystified. Lies, Damn Lies, and Statistics. When the Same Thing Isn't. Comparison of Methods. Interval Discrepancies. Data Collection and Presentation Discrepancies. Conclusion and Recommendations.

V. OTHER TOPICS.

27. Benchmarking.
Industry-Standard Database Benchmarks. Usefulness of Database Benchmarks. Introduction to TPC-C. Benefits of TPC-C. Limitations of TPC-C. Introduction to TPC-D, TPC-R, and TPC-H. TPC-D. TPC-R. TPC-H. Introduction to TPC-W. TPC Results in a Competitive Environment. Running Your Own Benchmark. Reasons for Running a Benchmark. Factors That Make a Benchmark Meaningful. Parameters to be Measured. Benchmark Requirements. Running the Benchmark. Competitive Benchmarks. What Often Goes Wrong with Benchmarks? Conclusion

28. Java Interfaces, Middleware, and Databases.
Java in the Database. Java Interfaces to Databases. JDBC. SQLJ. J2EE Middleware. J2EE Middleware Suppliers. The J2EE Framework. JavaServer Pages. The Java Servlet API. XML. Enterprise JavaBeans. Importance of J2EE to End Users. J2EE Availability. Other Related Technologies. In Conclusion.

Appendix A: References.

商品描述(中文翻譯)

- 加速您的 Oracle、DB2、Sybase 或 Informix 資料庫環境！
- 涵蓋資料庫及 Solaris 作業環境調整的各個方面
- 優化資料佈局、CPU 和記憶體配置，以及系統大小
- 逐步識別和解決瓶頸
- 理解業界領先的基準測試

最大化在 Solaris 作業環境上運行的任何企業資料庫的性能！

使用本書，資料庫專業人員可以優化在 Solaris 平台上運行的幾乎所有資料庫應用程式的性能和成本效益。資料庫專家 Allan Packer 提供了從頭到尾的指導，幫助優化四大 Solaris 平台資料庫：Oracle（包括 Oracle9i）、DB2、Sybase 和 Informix XPS。Packer 憑藉在 Sun 擔任工程師多年的專業知識，匯集了 Sun 資料庫伺服器調整的最佳實踐指導。

- 優化資料佈局、CPU 和記憶體配置，以及系統大小
- 識別和解決瓶頸：系統化的深入監控方法
- 理解資料庫優化器、資料庫緩衝區快取及其他關鍵子系統
- 解密業界領先的基準測試並認識其限制
- 理解 Java 技術對資料庫應用程式設計的影響
- 包含資料庫架構和概念的快速入門

**目錄**

I. 在 Sun 伺服器上的資料庫。
1. Sun 與資料庫供應商的關係。
- 關係的本質。工程。行銷。聯合銷售活動。主要資料庫供應商。Sun 與 DB2 for Solaris。Sun 與 Oracle。Sun 與 Sybase。Sun 與 Informix。其他在 Sun 上的資料庫。

2. Sun 的資料庫工程角色。
- Sun 的資料庫工程小組。Sun 內部的相關努力。大使計畫。

3. 資料庫的 Solaris 優化。
- 調度優化。搶佔控制。調度表修改。I/O 優化。pread 和 pwrite 系統呼叫。核心非同步 I/O。Unix 檔案系統增強。其他 I/O 優化。其他優化。編譯器優化。親密共享記憶體。動態重新配置。動態親密共享記憶體。重新配置協調管理器。

4. 硬體架構與資料庫。
- 硬體架構。對稱多處理器 (SMP) 系統。非均勻記憶體架構 (NUMA) 系統。大規模平行處理器 (MPP) 系統。晶片級多處理 (CLMP) 系統。SMP 和 NUMA 系統上的資料庫。SMP 上的共享磁碟資料庫。SMP 上的共享無資料庫。您應該分割嗎？分割還是不分割？

II. 資料庫架構。
5. 資料庫架構簡介。
- 關聯資料庫的架構。資料儲存。資料庫引擎。查詢優化器。資料庫共享記憶體。日誌。頁面清理器。資料庫恢復過程。鎖定子系統。系統監控。命令介面。API。資料庫監控。過程模型。2n 架構。多執行緒架構。平行處理。分散式資料庫。複製資料庫。交易監控。交易和即席查詢。用戶多路復用。交易路由。

6. 資料庫工作負載。
- 在線交易處理 (OLTP)。決策支持系統 (DSS)。資料倉儲和資料市集。操作資料儲存。資料挖掘。批次工作負載。在線分析處理 (OLAP)。

7. 緩衝區快取的角色。
- 緩衝區快取概述。監控緩衝區快取。可接受的快取命中率。快取命中率混淆。快取命中率指導方針。實例分析。緩衝區快取的大小。緩衝區快取大小對吞吐量的影響。緩衝區快取大小對資料快取命中率的影響。頁面大小對緩衝區快取有效性的影響。

8. 資料庫優化器的角色。
- 查詢優化器。查詢編譯。查詢優化。影響查詢優化的因素。優化方法。表存取。表連接順序。連接方法。執行查詢計畫。減少工作負載。資料分區。去正規化。串接索引。特殊優化。專家介入。

9. Oracle 架構。
- 過程模型。記憶體管理。系統全域區域 (SGA)。程式全域區域 (PGA)。物理資料儲存。表空間。重做日誌檔案。控制檔案。邏輯資料儲存。段。範圍。區塊。分區。系統表空間。平行處理。平行查詢選項 (PQO)。其他平行能力。

10. Sybase 架構。
- 過程模型。記憶體管理。資料快取。程序快取。物理資料儲存。邏輯資料儲存。表和索引。表分區。系統資料庫。平行處理。具有多路復用的 Adaptive Server IQ。列儲存。索引。I/O 和快取。平行性。IQ 多路復用。介面。

11. Informix XPS 架構。
- 過程模型。共伺服器。虛擬處理器。記憶體管理。緩衝池。DS 記憶體。動態記憶體分配。頁面清理。物理資料儲存。邏輯資料儲存。頁面。範圍。Dbspaces。Dbslices。表和索引。邏輯和物理日誌。碎片。Tblspaces。系統資料庫。平行處理。查詢平行性。共置連接。

12. DB2 for Solaris 架構。
- 過程模型。記憶體管理。緩衝池。緩衝池管理。物理資料儲存。表空間。日誌檔案。邏輯資料儲存。系統表空間。平行處理。DB2 通用資料庫企業版 (EE)。DB2 通用資料庫企業擴展版 (EEE)。其他與 DB2 相關的產品。

III. 調整和配置 Sun DBMS 伺服器。
13. 為資料庫調整系統大小。
- 調整估算的基礎。最低要求。估算的限制。正確的問題。這是什麼類型的工作負載？應用程式的狀態如何？有多少用戶？處理要求是否隨工作班次變化？用戶如何連接到資料庫伺服器？對響應時間的期望是什麼？需要多少 I/O 容量和吞吐量？需要多少記憶體？預期的系統增長率是多少？使用已發佈的 TPC 結果進行調整。使用 TPC-C 調整實際 OLTP 伺服器。使用 TPC-D 或 TPC-R 調整實際 DSS 伺服器。使用 TPC-H 調整實際 DSS 伺服器。使用遠端終端模擬器。經驗法則摘要。通用 OLTP 調整工具。背景。建立指標。尋找簡化假設。收集原始數據。提出正確的問題。建立模型。驗證模型。最終工具。結論。參考文獻。

14. 配置系統。
- Solaris 配置。記憶體交錯。記憶體交錯的好處。交錯的評估。

15. 配置 CPU。
- 管理工作負載。域。處理器集。資源管理。CPU 性能。過程綁定。Solaris 調度類別。

16. 配置網路。
- 性能考量。可用性考量。

17. 資料佈局。
- 儲存子系統。可單獨尋址的磁碟。基於 RAID 的陣列。儲存區網路 (SAN)。網路附加儲存 (NAS)。卷管理器。Veritas 卷管理器 (Veritas)。Solaris 卷管理器 (SVM)。RAID 管理器 (RM6)。元件管理器。GUI 管理。相對性能。資料佈局技術。RAID 簡介。串接。RAID 0：條帶化。RAID 1：鏡像。RAID 0+1：條帶化加鏡像。RAID 1+0：鏡像和條帶化。RAID 3：帶有專用奇偶校驗磁碟的條帶化。RAID 5：帶有分散奇偶校驗的條帶化。RAID 技術摘要。UFS 上的資料庫檔案。原始設備上的資料庫檔案。寫入快取。資料佈局策略。有效的資料佈局。原始設備與 UFS。條帶化的正確與錯誤方式。放置資料和索引。在單一磁碟上佈局資料。為提高性能而設置的寫入快取。高可用性。磁碟故障計畫。鏡像的性能影響。消除單點故障。其他資料佈局問題。使用符號連結的資料庫檔案命名。原始設備與檔案系統之間的遷移。RAID 5 在資料庫中的性能。RAID 對資料庫優化器的影響。卷管理器的優缺點。儲存雞尾酒。資料佈局建議。實例分析。

IV. 性能監控與調整。
18. 故障排除方法。
- 問題解決策略的發展。問題的本質。可能導致問題的因素。深入挖掘以找出根本原因。

19. 造成性能不佳的主要因素。
- 性能問題識別。設計不良的應用程式。資料庫設計和實施不良。用戶環境的問題。資料佈局不良。下一步。

20. 系統性能監控工具。
- 基本 Solaris 工具。監控間隔。其他監控工具。

21. 深入監控。
- 第一步：監控記憶體。第二步：監控磁碟。第三步：監控網路。第四步：監控 CPU。第五步：監控和調整資料庫。額外步驟：檢查 ISM。

22. 監控和調整 Oracle。
- 管理 Oracle 行為。執行管理命令。查看當前 Oracle 可調參數。更改 Oracle 的可調參數。使動態參數變更持久。查看和更改隱藏參數。監控錯誤訊息。使用 Oracle Enterprise Manager。監控 Oracle 系統表。生成解釋計畫。計算緩衝區快取命中率。使用 utlbstat/utlestat 監控 Oracle。庫快取。用戶連接。系統範圍的等待事件。鎖定等待事件。緩衝區繁忙等待統計。回滾段。修改的 init.ora 參數。字典快取統計。表空間和資料庫檔案 I/O 活動。日期、時間和版本詳細資訊。監控共享池。調整 Oracle。調整 init.ora。為 OLTP 工作負載設置可調參數。為 DSS 工作負載設置可調參數。應用其他調整建議。使用 Oracle 與檔案系統。優化 Oracle 載入性能。規劃索引。使用大於 2 Gbytes 的 SGA。動態重新配置 Oracle9i。Oracle9i 動態系統全域區域。Oracle 如何在 ISM 和 DISM 之間選擇。使用動態 SGA 的好處。恢復 Oracle。檢查點對恢復時間的影響。檢查點對性能的影響。

類似商品