Configuring database parameters for performane using a wizard: Control Center help"in the Help: Control Center Improving performance for a database with different workloads using a wizard: Control Center help"in the Help: Control Center Related reference: Configuration parameters summary"on page 376 Chapter 1 Introduction to performance 9
v “Configuring database parameters for performance using a wizard: Control Center help” in the Help: Control Center v “Improving performance for a database with different workloads using a wizard: Control Center help” in the Help: Control Center Related reference: v “Configuration parameters summary” on page 376 Chapter 1. Introduction to performance 9
Chapter 2 Architecture and processes This chapter provides general information about the DB2 architecture and process schem DB2 architecture and process overview General information about DB2 architecture and processes can help you understand detailed information provided for specific topics. The following figure shows a general overview of the architecture and processes for DB2 UDB right IBM Corp. 1993-2002
Chapter 2. Architecture and processes This chapter provides general information about the DB2 architecture and process schema. DB2 architecture and process overview General information about DB2® architecture and processes can help you understand detailed information provided for specific topics. The following figure shows a general overview of the architecture and processes for DB2 UDB. © Copyright IBM Corp. 1993 - 2002 11
Clients UDB client library TCPIP pipes, NetBIOS UDB server Log buffer Coordinator ordinator Subagents Subagents Common prefetch Prefetch Scatter/Gather Parallel, big-block Hard drive disks Figure 1. Architecture and Processes Overview On the client side, either local or remote applications, or both, are linked with the dB2 Universal Database client library. Local clients communicate using shared memory and semaphores; remote clients use a protocol such as Named Pipes(NPIPE), TCP/IP, NetBIOS, or SNA On the server side, activity is controlled by engine dispatchable units(EDUs In all figures in this section, EDUs are shown as circles or groups of circles 12 Administration Guide: Performance
On the client side, either local or remote applications, or both, are linked with the DB2 Universal Database™ client library. Local clients communicate using shared memory and semaphores; remote clients use a protocol such as Named Pipes (NPIPE), TCP/IP, NetBIOS, or SNA. On the server side, activity is controlled by engine dispatchable units (EDUs). In all figures in this section, EDUs are shown as circles or groups of circles. Clients Page cleaners Shared memory and semaphores, TCPIP, Named pipes, NetBIOS, SNA, IPX/SPX Log buffer UDB server Buffer Pool(s) Hard disks Hard disks Hard disks Hard drive Log Scatter/Gather I/Os Write log requests Async I/O prefetch requests Common prefetch request queue Parallel, page write requests Prefetchers Parallel, big-block, read requests Deadlock detector Victim notifications Coordinator agent Coordinator agent Subagents Subagents UDB client library Client application Client application Loggers Figure 1. Architecture and Processes Overview 12 Administration Guide: Performance
EDUs are implemented as threads in a single process on Windows-based platforms and as processes on UNIX. DB2 agents are the most common type of EDUs. These agents perform most of the SQl processing on behalf of applications. Prefetchers and page cleaners are other common EDUs a set of subagents might be assigned to process the client application requests. Multiple subagents can be assigned if the machine where the server resides has multiple processors or is part of a partitioned database. For example, in a symmetric multiprocessing(SMP)environment, multiple SMP subagents can exploit the many processors All agents and subagents are managed using a pooling algorithm that minimizes the creation and destruction of edus Buffer pools are areas of database server memory where database pages of user table data, index data, and catalog data are temporarily moved and can be modified. Buffer pools are a key determinant of database performance because data can be accessed much faster from memory than from disk. If more of the data needed by applications is present in a buffer pool, less time is required to access the data than to find it on disk. The configuration of the buffer pools, as well as prefetcher and page cleaner EDUs, controls how quickly data can be accessed and how readily available it is to applications. Prefetchers retrieve data from disk and move it into the buffer pool before applications need the data. For example, applications needing to scan through large volumes of data would have to wait for data to be moved from disk into the buffer pool if there were no data prefetchers. Agents of the application send asynchronous read-ahead requests to a common prefetch queue. As prefetchers become available, they implement those requests by using big-block or scatter-read input operations to bring the requested pages from disk to the buffer pool. If you have multiple disks for storage of the database data, the data can be striped across the disks Striping data lets the prefetchers use multiple disks at th he same time to retrieve data Page cleaners move data from the buffer pool back out to disk Page cleaners are background EDUs that are independent of the application agents. They look for pages from the buffer pool that are no longer needed and write the pages to disk. Page cleaners ensure that there is room in the buffer pool for the pages being retrieved by the prefetchers Without the independent prefetchers and the page cleaner EDUs, the application agents would have to do all of the reading and writing of data between the buffer pool and disk storage Chapter 2 Architecture and processes 13
EDUs are implemented as threads in a single process on Windows-based platforms and as processes on UNIX. DB2 agents are the most common type of EDUs. These agents perform most of the SQL processing on behalf of applications. Prefetchers and page cleaners are other common EDUs. A set of subagents might be assigned to process the client application requests. Multiple subagents can be assigned if the machine where the server resides has multiple processors or is part of a partitioned database. For example, in a symmetric multiprocessing (SMP) environment, multiple SMP subagents can exploit the many processors. All agents and subagents are managed using a pooling algorithm that minimizes the creation and destruction of EDUs. Buffer pools are areas of database server memory where database pages of user table data, index data, and catalog data are temporarily moved and can be modified. Buffer pools are a key determinant of database performance because data can be accessed much faster from memory than from disk. If more of the data needed by applications is present in a buffer pool, less time is required to access the data than to find it on disk. The configuration of the buffer pools, as well as prefetcher and page cleaner EDUs, controls how quickly data can be accessed and how readily available it is to applications. v Prefetchers retrieve data from disk and move it into the buffer pool before applications need the data. For example, applications needing to scan through large volumes of data would have to wait for data to be moved from disk into the buffer pool if there were no data prefetchers. Agents of the application send asynchronous read-ahead requests to a common prefetch queue. As prefetchers become available, they implement those requests by using big-block or scatter-read input operations to bring the requested pages from disk to the buffer pool. If you have multiple disks for storage of the database data, the data can be striped across the disks. Striping data lets the prefetchers use multiple disks at the same time to retrieve data. v Page cleaners move data from the buffer pool back out to disk. Page cleaners are background EDUs that are independent of the application agents. They look for pages from the buffer pool that are no longer needed and write the pages to disk. Page cleaners ensure that there is room in the buffer pool for the pages being retrieved by the prefetchers. Without the independent prefetchers and the page cleaner EDUs, the application agents would have to do all of the reading and writing of data between the buffer pool and disk storage. Chapter 2. Architecture and processes 13
Related concepts Prefetching data into the buffer pool"on page 274 Deadlocks between applications"on page 14 Database directories and files"on page 16 Logging process"on page 33 ·“ Update process" on page35 Client-server processing model"on page 36 n page 44 Connection-concentrator improvements for client connections"on page 312 Related reference: Maximum Number of Coordinating Agents configuration parameter max_coordagents 446 Maximum Number of Client Connections configuration parameter max_connections"on page 448 Deadlocks between applications With multiple applications working with data from the database there are opportunities for a deadlock to occur between two or more applications. A deadlock is created when one application is waiting for another application to release a lock on data. Each of the waiting applications is locking data needed by another application. Mutual waiting for the other application to release a lock on held data leads to a deadlock. The applications can wait forever until one application releases the lock on the held data a deadlock is illustrated in the following figure 14 Administration Guide: Performance
Related concepts: v “Prefetching data into the buffer pool” on page 274 v “Deadlocks between applications” on page 14 v “Database directories and files” on page 16 v “Logging process” on page 33 v “Update process” on page 35 v “Client-server processing model” on page 36 v “Memory management” on page 44 v “Connection-concentrator improvements for client connections” on page 312 Related reference: v “Maximum Number of Coordinating Agents configuration parameter - max_coordagents” on page 446 v “Maximum Number of Client Connections configuration parameter - max_connections” on page 448 Deadlocks between applications With multiple applications working with data from the database there are opportunities for a deadlock to occur between two or more applications. A deadlock is created when one application is waiting for another application to release a lock on data. Each of the waiting applications is locking data needed by another application. Mutual waiting for the other application to release a lock on held data leads to a deadlock. The applications can wait forever until one application releases the lock on the held data. A deadlock is illustrated in the following figure. 14 Administration Guide: Performance