Database performance tuning on AIX / [Budi Darmawan ... et al.].

Format:

Book

Contributor:

Darmawan, Budi.

International Business Machines Corporation. International Technical Support Organization.

Series:

IBM redbooks.

IBM redbooks

Language:

English

Subjects (All):

Database management.

AIX (Computer file).

Physical Description:

xxvi, 516 p. : ill.

Edition:

2nd ed.

Place of Publication:

[United States] : IBM International Technical Support Organization, 2003.

Language Note:

English

Contents:

Front cover

Contents

Figures

Tables

Notices

Trademarks

Preface

The team that wrote this redbook

Become a published author

Comments welcome

Summary of changes

January 2003, Second Edition

Chapter 1. Introduction to this redbook

1.1 Relational database management systems

1.2 Project environment

1.2.1 Hardware configuration

1.2.2 Operating systems level

1.2.3 Database server

1.3 Document organization

Part 1 RDBMS concepts

Chapter 2. Introduction to relational database system concepts

2.1 RDBMS defined

2.2 RDBMS characteristics

2.2.1 Database logging

2.3 RDBMS components

2.3.1 RDBMS data

2.3.2 RDBMS cache

2.3.3 RDBMS processes

2.3.4 RDBMS tools

2.4 Defining the RDBMS terms and ideas

2.4.1 RDBMS terms

2.4.2 Structured Query Language

2.5 Ensuring data availability

2.5.1 Data loss cause and options

2.5.2 Backup and performance

2.6 Parallel databases

2.6.1 Parallel concepts in database environments

2.6.2 Advantages and disadvantages of parallel databases

Chapter 3. Types of workload

3.1 Transactional workload

3.1.1 Online Transaction Processing (OLTP)

3.1.2 Enterprise Resource Planning (ERP)

3.1.3 e-business

3.2 Decision Support Systems (DSS)

3.2.1 Data warehouses

3.2.2 Data marts

3.2.3 Online Analytical Processing (OLAP)

3.2.4 Data mining

3.2.5 Reporting

Chapter 4. DB2 Universal Database

4.1 New features and enhancements

4.2 DB2 UDB database architecture

4.2.1 Memory structures

4.2.2 Logical storage structures

4.2.3 Physical storage structures

4.2.4 Processes

4.2.5 SQL extensions: Stored procedures

4.2.6 DB2 tools

4.3 DB2 UDB parallel database considerations

4.3.1 Concepts and functionality

4.3.2 Optimizer

4.3.3 Inter-partition and intra-partition parallelism.

4.3.4 Hardware implementation

Chapter 5. Oracle databases

5.1 Oracle database architecture

5.1.1 Memory structures

5.1.2 Logical storage structures

5.1.3 Physical storage structures

5.1.4 Processes

5.1.5 SQL extensions: Stored procedures

5.1.6 Administration tools

5.2 Oracle Real Application Cluster

5.2.1 Oracle RAC architecture

5.2.2 Virtual Shared Disk (VSD)

5.2.3 Global Cache Service

5.2.4 Global Enqueue Service

5.2.5 Distributed Lock Manager (DLM)

Chapter 6. IBM Informix Dynamic Server

6.1 Informix DS architecture

6.1.1 Memory structures

6.1.2 Storage structures

6.1.3 Physical storage structures

6.1.4 Processes

6.1.5 SQL extensions: User defined routines

6.1.6 Administration tools

6.2 Informix Extended Parallel Server

6.2.1 Concepts and functionality

6.2.2 Fragmentation of data

6.2.3 Hardware implementation

Part 2 System design and sizing

Chapter 7. Sizing a database system

7.1 Introduction to sizing

7.1.1 Sizing concepts

7.1.2 Sizing constraints

7.2 Sizing techniques

7.2.1 Sizing from the data size

7.2.2 Sizing from transaction rates

7.2.3 Sizing from user numbers

7.2.4 Sizing for a particular application

7.3 CPU goals and sizing

7.3.1 Uniprocessor (UP) Systems

7.3.2 Symmetric Multiprocessor (SMP) Systems

7.3.3 CPU utilization

7.4 Memory goals and sizing

7.4.1 AIX operating system

7.4.2 AIX file system cache (AIX buffer cache)

7.4.3 RDBMS cache and structures

7.4.4 User applications and database connections

7.5 Disk goals and sizing

7.5.1 General database sizing: High-level

7.5.2 Specific table by table sizing: Detailed level

7.5.3 Which disk size to choose

7.5.4 Disk protection

7.6 Balancing a system using the component costs

Chapter 8. Designing RDBMS servers.

8.1 Production, development, and testing

8.1.1 Production

8.1.2 Development

8.1.3 Testing

8.1.4 Hybrid machines

8.2 Working space

8.2.1 Basic and future AIX resources

8.2.2 Basic and future application resources

8.2.3 Basic RDBMS resources

8.2.4 Future RDBMS resources

8.3 Sizing consideration

8.3.1 Workload and network considerations

8.3.2 System resource considerations

8.3.3 Additional considerations

8.4 Database back up and restore strategy

8.4.1 DB2 UDB backup restore scenario

8.4.2 Oracle backup restore scenario

8.4.3 Informix backup restore scenario

8.4.4 General backup considerations

8.5 Coping with growth

8.5.1 DB2 UDB reorganization method

8.5.2 Oracle reorganization method

8.5.3 Informix reorganization method

8.5.4 When and how to avoid database reorganization

8.5.5 Coping with large, unexpected growth

8.5.6 Expected growth areas

8.5.7 Loading large amounts of data

8.6 Performance versus availability

8.7 AIX and RDBMS upgrades

Chapter 9. Designing a disk subsystem

9.1 Disk subsystem design approach

9.1.1 Bandwidth related performance considerations

9.1.2 Physical database layout considerations

9.2 Logical Volume Manager (LVM) concepts

9.2.1 Physical partition striping versus LVM fine striping

9.2.2 Use of LVM policies

9.2.3 Recommendations for performance optimization

9.3 RAID levels overview and considerations

9.3.1 RAID 0

9.3.2 RAID 1

9.3.3 RAID 2 and 3

9.3.4 RAID 4

9.3.5 RAID 5

9.3.6 RAID 0+1 or RAID 10

9.3.7 Comparison of RAID Levels

9.3.8 RAID 5 versus AIX LVM mirroring

9.4 AIX disk performance topics

9.4.1 Raw logical volumes versus Journaled File System

9.4.2 Synchronous and asynchronous I/O

9.4.3 Use of Mirror Write Consistency

9.5 Direct access storage.

9.5.1 IBM 7133 Serial Disk System

9.5.2 IBM 2104 Expandable Storage Plus

9.6 Integrated storage subsystems

9.6.1 IBM TotalStorage Enterprise Storage Server

9.6.2 IBM FAStT Storage Servers

9.7 Network storage

9.7.1 Storage Area Network

9.7.2 Internet SCSI

9.7.3 Network Attached Storage

Part 3 System optimization

Chapter 10. Implementing your database

10.1 RDBMS installation process

10.2 Before RDBMS installation

10.2.1 Hardware and AIX ready check list

10.2.2 Pre-starting check list

10.2.3 Database data

10.2.4 Hardware testing

10.3 Installing the RDBMS code

10.3.1 Physical layout of the database

10.3.2 Scripting the build

10.3.3 Build a small cut down system

10.4 After installation

10.4.1 Documentation and log book

10.4.2 Backup and recovery test

Chapter 11. Monitoring an RDBMS system for performance

11.1 Performance monitoring issues

11.1.1 Monitoring responsibility

11.1.2 Documenting performance problems

11.2 Monitoring methods usage

11.2.1 Regular monitoring method

11.2.2 Ad hoc monitoring method

11.2.3 Alert monitoring method

11.3 RDBMS tools

11.3.1 DB2 UDB monitoring tools

11.3.2 Oracle monitoring tools

11.3.3 Informix monitoring tools

11.4 IBM Tivoli Monitoring for Databases

Chapter 12. Tuning an RDBMS system

12.1 Performance tuning basics

12.1.1 Tuning philosophy

12.1.2 Tuning skills

12.1.3 Reference manuals and books

12.1.4 About RDBMS tuning

12.1.5 Performance improvement process

12.2 Tuning strategies

12.2.1 Formal fine tuning method

12.2.2 Change all at once method

12.3 Bottlenecks, utilization, and resources

12.3.1 Insufficient CPU and latent demand

12.3.2 Insufficient memory

12.3.3 Insufficient disk I/O

12.3.4 Insufficient network resources

12.3.5 Insufficient logical resource access.

12.4 Additional tuning considerations

12.4.1 What can we tune

12.4.2 Tuning window

12.4.3 Classic mistake list

Chapter 13. AIX and hardware tuning considerations

13.1 Tuning categories in this chapter

13.2 Common AIX issues

13.3 AIX tuning for RDBMS hints

13.3.1 AIX asynchronous I/O

13.3.2 AIX Logical Volume Manager or database files

13.3.3 Create logical volumes at a standardized size

13.3.4 AIX JFS or raw devices

13.3.5 AIX disk geometry considerations

13.3.6 AIX sequential read ahead

13.3.7 AIX paging space

13.3.8 AIX paging rate

13.3.9 Hot disk removal

13.3.10 Disk sets for hot disk avoidance

13.3.11 SMP balanced CPU utilization

13.4 Advanced AIX tuning hints

13.4.1 AIX logical track group size

13.4.2 AIX write behind

13.4.3 AIX disk I/O pacing

13.4.4 AIX processor binding on SMP

13.4.5 AIX process time slice

13.4.6 AIX free memory

13.4.7 AIX buffer cache size

Chapter 14. DB2 UDB tuning

14.1 Introduction to DB2 UDB tuning

14.1.1 Quick-start tips for tuning

14.1.2 General tuning elements

14.1.3 Tablespace page size

14.1.4 Reorganizing tables

14.2 Areas of interest

14.2.1 Database manager configuration parameters

14.2.2 Database parameters

14.2.3 DB2 UDB registry variables

14.3 Which options will make a large difference

14.3.1 Buffer pool size

14.3.2 Number of I/O servers (num_ioservers)

14.3.3 Number of asynchronous page cleaners (num_iocleaners)

14.3.4 Changed pages threshold (chngpgs_thresh)

14.3.5 Sort heap size (sortheap)

14.3.6 Sort heap threshold (sheapthres)

14.3.7 Statement heap size (stmtheap)

14.3.8 Package cache size (pckcachesz)

14.3.9 Database heap size (dbheap)

14.3.10 Catalog cache size (catalogcache_sz)

14.3.11 Log buffer size (logbufsz).

14.3.12 Maximum number of active applications (maxappls).

Notes:

"SG24-5511-01."

Includes bibliographical references (p. 493-497) and index.

OCLC:

939263490