atabase performance is one of those things that goes unnoticed, until it starts flagging. There are three broad areas relevant to database performance:
- System resources (essentially, the hardware)
- Data structures and the way they are allocated to system resources
- Data operations requested by the applications and they way they are allocated to system resources
This article presents an overview of key factors in how DB2 uses disk and memory to place and retrieve data. The information is pertinent to DB2 v5 or later.
An underperforming database can be tricky to troubleshoot, particularly for inexperienced DBAs. You want to optimize the performance of your DB2 database, but aren’t sure how to begin.
Learn how and where your data is stored and how to manage it for better performance.Instances and Databases
Instances
At the top of the DB2 hierarchy, is the instance or database manager. An instance is a complete environment for hosting databases, having a range of memory and CPU management policies and network port numbers for communication.
What you get by default:
DB2 Universal Database provides a default instance named ‘DB2.’
How to create a new instance:
DB2 provides a utility called db2icrt. The only mandatory parameter is a name for the new instance.
Why have more than one instance? A system can run multiple instances and each will have its own data, processes, I/O ports, and memory allocation, completely independent of any other instance. An instance can be started, stopped, and reconfigured, without directly affecting any other instance. Most situations will only require one instance.
Databases
A database is a collection of relational data in tables, along with associated indices, triggers, stored procedures etc. The tables can be related and queried together. A database can be backed up and restored as a whole.
What you get by default:
The “DB2 First Steps” tool offers to create a database called SAMPLE, which contains tables used by sample programs in the application development client.
How to create a new database:
Use the DB2 command: CREATE DATABASE or use the Control Center.
Why have more than one database? A single database may serve the purposes of a number of independent applications, or each application may use a separate database for isolation and independent backup and restore. Extra databases can also be created for development and troubleshooting, as an entire set of data can be reproduced numerous times without interfering with the main (or live) data.Disk Storage and Storage Management
Data is located logically in named areas called tablespaces, which can be System Managed Space (SMS) or Database Managed Space (DMS). In either case, the space comprises one or more containers.
In the case of a System Managed Space, a container is a file system directory. Tables and indices are created as numerous files within the directory, and, as the files grow, allocation of disk space is entrusted to the host file system. This means the tablespace is subject to file system fragmentation and overhead during the opening and closing of multiple files.
Database Managed Space requires an up-front allocation for storage, and the database administrator must perform additional allocation when required. A DMS container can be a single file in a host file system or can be created directly on a disk, bypassing the filesystem altogether. Generally DMS gives much better performance. (To find out how DB2 arranges data within the tablespace, regardless of type, see Sidebar, “Does the Data Get Fragmented?”)
Tablespaces can be nominated as:
- Regular, for normal persistent data
- Temporary, for data sets built during processing of queries
- Long, specifically for storing long data types such as graphics. Long data may be stored in regular tablespaces, though handling and tuning long data separately can result in significant performance gains.
How to create a new tablespace:
You can specify tablespace options in the CREATE DATABASE command, and add more with CREATE TABLESPACE or by using the Control Center. Then when you create tables, you can specify which tablespace to use for the table and/or indices.
Why have more than one tablespace?
In DB2, storage is handled in pages. Depending on the tablespace page size and the size of rows in your tables, a row may occupy many pages, or a page may contain many rows. Storing a row, or many rows, in a single page, allows the entire row or rows to be retrieved as a single unit from disk. (The same economy applies to buffer pools in memory?see next page).
Large page sizes are good for storing tables with very long rows or tables used regularly for sequential data access. Conversely, storing small, randomly accessed rows in large pages makes for inefficient use of the buffer pools. To allow different tables to be stored with different page sizes and extent sizes, and to allow separate buffering, it’s best to use multiple user tablespaces as you will see on the next page
Every database requires:
- A system catalog tablespace named SYSCATSPACE to store a description of the database and its structure and contents. By default this is created as SMS.
- A system temporary tablespace, for holding data while sorting or collating results. By default, an SMS tablespace named TEMPSPACE1 is created for you under the instance’s default database path.
- One or more regular tablespaces for user data. By default, an SMS tablespace named USERSPACE1 is created for you.
Unless you specify otherwise, SMS tablespaces are created under the instance’s default database path (parameter DFTDBPATH).
How to create additional containers:
Use the
Why have more than one container in a tablespace? DB2 will spread data across containers so you can store data on multiple disks for greater speed and storage capacity (see the Sidebar “What Else Goes on Your Disks?”).Memory for Data
Buffer Pools
A buffer pool is a disk cache specifically designed for tables and indices, allowing data requests to be filled from memory rather than disk. Buffer pools use fixed page sizes, which must be the same as the page size of any tablespaces they serve.
What you get by default:
DB2 provides a buffer pool called IBMDFTBP, which is very small (1 MB, or 250 pages, on Windows NT).
How to create a new buffer pool:
To create a new buffer pool, use the command CREATE BUFFERPOOL or use the DB2 Control Center.
How to I allocate a tablespace to a bufferpool?
Specify a BUFFERPOOL clause with CREATE TABLESPACE or ALTER TABLESPACE.
Why create a new buffer pool? There are four basic reasons. First, the default buffer pool is very small. You can change the size of the default buffer pool but that’s quite dangerous. When a database is initialized, DB2 attempts to allocate memory for all buffer pools in that database. If there is insufficient memory to do so, it loads only IBMDFTBP (the default buffer pool) plus one small buffer pool for each other page size in use, which allows you connect to the database anyway. If the default buffer pool is also too large, you will be unable to connect to the database even to correct it.
Second, if you have tablespaces with differing page sizes, you will need to create a buffer pool for each page size. Third, you might have a large table that is frequently scanned completely, which would flush out the buffer pool every time. You could use a separate tablespace and assign it to a small buffer pool. Finally, if you have a smaller table you want to keep entirely in memory at all times for fast and frequent access, you can use a separate tablespace with a dedicated buffer pool.
Most often, though, you would start with just one additional buffer pool. A quick rule of thumb for systems dedicated to DB2 is to allocate 75 percent of physical memory to the buffer pools. It will need to be much less when coexisting with other applications.
Five Tips to Remember
I’ve barely covered the basics of data storage in DB2. But here are my top five recommendations if you’re getting started:
- Be generous with memory and hard disk space
- Use DMS table spaces
- Create larger buffer pools
- Try the Control Center’s Performance Tuning wizard and read up on any parameter it nominates for change
- Download and read the DB2 v7 Tuning Redbook (see Related Resources)
