Optimizing IBM DB2 for Solaris 10 1/06 OS on Sun Fire T2000 Server

Print-friendly Version

This article is for users planning to deploy DB2 on the Solaris 10 platform using a Sun Fire T2000 server.

The information here is based on experiments carried out on DB2 with the Solaris 10 OS on the Sun Fire T2000 server in Sun's Market Development Engineering (MDE) and Strategic Applications Engineering (SAE) Labs in Burlington, Massachusetts. These tests used various workloads to identify configurations that allow the right mix of performance and ease-of-use. Results may vary for other users and environments.

Tips are provided here to help system administrators and DBAs optimize DB2 deployed with the Solaris 10 OS and the Sun Fire T2000 architecture.

A detailed list of the article's contents follows:

• Introduction
• Background
• UltraSPARC T1 Processor
• Sun Fire T2000 Server
• Solaris 10 OS
• DB2 Universal Database
• DB2 With Solaris 10 OS on Sun Fire T2000 Server Containing UltraSPARC T1 Processor
• Solaris 10 1/06 OS and Patches
• Tuning /etc/system
• Tuning Network Parameters
• Tuning Project Resource Parameters
• Creating a Resource Pool for the Project
• Tuning DB2 Environment Values (db2set)
• Tuning DB2 Table Spaces
• Tuning DB2 DBM and DB Configuration Parameters
• OLTP Workload-Specific Tuning Tips
• Tuning DBM Parameters for OLTP Applications
• Tuning DB Parameters for OLTP
• DSS Workload-Specific Tuning Tips
• Tuning DB2 Table Spaces for DSS
• Tuning DB2 INTRA_PARALLEL Option
• Using DB2 Database Partitioning Feature (DPF) Option
• Using DB2 DPF With Resource Pools
• Summary
• References

The UltraSPARC T1 processor with CoolThreads technology has up to 8 cores with 4 hardware threads each, allowing it to process up to 32 threads simultaneously. While each core can execute one hardware thread per clock cycle, each thread has its own set of registers. The optimizations allow each core to scale from 1X to 4X, depending upon the nature of application workload running on the processor, with the four hardware threads each with their own registers. The T1 architecture has been optimized to take advantage of cycles wasted due to memory access. A hardware thread is parked by the hardware if it is stalled for such an instruction. The UltraSPARC T1 processor can use those critical cycles to service other hardware threads on the core. The mileage varies depending on the nature of the application. Applications utilizing large datasets with excessive pointer chasing, such as modern commercial applications, are good candidates. Highly network-dependent datasets, with uneven flow processed by multiple processes or threads, also scale well on the UltraSPARC T1 processor.

The Sun Fire T2000 server sets a new industry standard for throughput performance, energy efficiency, and compute density. The Sun Fire T2000 server breaks through constraints for data center space, power, and cooling by providing an optimal platform for online transactions and web services. Using Sun's chip multithreading technology (CMT), the Sun Fire T2000 server can offer high levels of compute density and price performance. For more information, see sun.com.

The Sun Fire T2000 server has one UltraSPARC T1 CPU with 4, 6, or 8 core options (16, 24, or 32 threads), and supports up to 32 Gbytes of memory. (Note: There are plans to support up to 64 Gbytes.) Since applications with high network traffic and/or large datasets are the target audience of the UltraSPARC T1 processor, the Sun Fire T2000 server supports the processor with four 10/100/1000-Mbit/sec Ethernet adapters, three PCI-E slots, and two PCI-X slots. The combination of these features allows this server to support high network traffic for OnLine Transaction Processing (OLTP) or also high storage demands for decision support system (DSS) workloads.

In a storage-centric configuration, the Sun Fire T2000 server can support three optional Dual Channel 4-Gbit FC PCI-E Host Adapters, along with two optional Dual Channel 4-Gbit FC PCI-X Host Bus Adapters, offering a capacity of approximately 20 Gbit/sec of I/O bandwidth. Typically a well-tuned I/O-intensive application system combination can achieve about 80 percent of the theoretical I/O bandwidth, which in the case of a Sun Fire T2000 server is about 1.6 Gbytes/sec of usable I/O bandwidth. Internal tests have achieved up to 1.8 Gbytes/sec of I/O bandwidth. Similarly, using the 2-Gbit/sec HBA, the Sun Fire T2000 server achieved about 800 Mbytes/sec of I/O bandwidth.

For network-centric applications, four Gbit Ethernet adapters are available on all configurations of the Sun Fire T2000 server. Typically using a combination of the four adapters is sufficient to load the system without requiring any additional network adapters for the system. However, an optional Sun 10-Gbit Ethernet 133-MHz PCI-X Card is also available for the Sun Fire T2000 server.

The Solaris 10 OS is proven to support 144+ processing units and has features such as Dynamic Tracing (DTrace), Solaris Zones, and Resource Pools that allow administrators to quickly configure the Solaris platform for the application workload to run well on the underlying server system. The Solaris 10 OS utilizes scheduler optimizations that allow it to schedule threads on virtual CPUs based on the cores so that it does not stress one core out while other cores are still being underutilized. Using Resource Pools, the Solaris 10 OS allows administrators to control the usage of an application and provides them with dedicated resources and also further optimizes the system by isolating applications from impacting other applications. Zones allow further abstraction with multiple isolated, virtualized operating environments with their own root password with a common kernel of the Solaris OS. This lets different administrators control their own applications without fear of being impacted by other applications. Together, Resource Pools and Solaris Zones provide secured operating environments known as Solaris Containers. For performance or debugging issues, DTrace helps users to quickly figure out root causes even on production systems.

DB2 Universal Database (UDB) is a database management system that delivers a flexible and cost-effective database platform to build robust, on-demand business applications. DB2 UDB can leverage resources with broad support for open standards and popular development platforms such as the Java 2 Platform, Enterprise Edition (J2EE), and Microsoft .NET. The DB2 UDB family also includes solutions tailored for specific needs such as business intelligence and advanced tooling. For more information on DB2 UDB, see the IBM web site.

DB2 UDB is scalable with SMP types of machines in multiple ways, and the CMT-based UltraSPARC T1 chip qualifies as SMP. Every DB2 connection is scalable through a new agent process that is spawned to service the DB2 connection. For DB2 connections that require complex computations, DB2 can scale by way of the INTRA_PARALLEL option, which spawns multiple agent processes depending on the degree of parallelism (DFT_DEGREE), and divides the work among those agents. An optional dynamic partitioning feature is also available that allows DB2 to divide a database into two or more virtual separate datasets based on partitioning keys or partitioning groups of tables, and scale as the number of processing units available in the system.

Thus, the combination of these various technologies allows DB2 to utilize all the resources available in a system.

This section looks at deployment optimizations for DB2 on the Solaris 10 OS with the T1 processor.

Starting in June 2006, all Sun Fire T2000 servers are shipped with the Solaris 10 1/06 OS. This release of the Solaris OS has the 118822-25 revision of the kernel patch. To get the best performance with DB2, it is recommended that you use the latest kernel patch 118822-30 and patch 118833-03, which provides the fix for large, anonymous page problems. Also, upgrade the ipge driver to the latest version by installing the patch 120849-04, which upgrades the ipge driver to v1.32. (It might be worthwhile to upgrade to the Solaris 10 6/06 OS because that release contains all the above-mentioned patches and also provides new features such as ZFS.)

# modinfo |grep ipge
103 7ba6c000  43650  56   1  ipge (IPGE Ethernet Driver v1.32)

Following is a sample of a good /etc/system tunable to be used for DB2 with the Solaris 10 OS on the Sun Fire T2000 server.

The tuning is primarily for the network adapter and the high-water shared memory and semaphore settings required for DB2 with a high number of connections. (Hint: Use the db2osconf utility provided along with DB2.)

* Recommended Network settings for DB2/SunFire T2000
set sq_max_size=100
set ip:ip_squeue_bind=0
set ip:ip_squeue_fanout=1
set ipge:ipge_tx_syncq=0
set ipge:ipge_taskq_disable=0
set ipge:ipge_bcopy_thresh=512
set ipge:ipge_dvma_thresh=1
* For systems shipped before June 2006 enable
* set pcie:pcie_aer_ce_mask=0x1

* From db2osconf
set msgsys:msginfo_msgmni = 10240
set semsys:seminfo_semmni = 12288
set shmsys:shminfo_shmmax = 30813703372
set shmsys:shminfo_shmmni = 12288

Although the IPC resource settings output from db2osconf, when included in /etc/system, are still read and interpreted by the kernel during boot, it is recommended that these IPC resources are set using prctl(1M) or through projects(4). (Check projects(4) and the next section, "Tuning Project Resource Parameters.")

The network parameters are tuned by adding the following to a startup file /etc/rc3.d/S99setndd, which will always set the following settings during startup. (Do not forget to change the mode so that you have an executable file.)

#!/bin/sh
ndd -set /dev/tcp tcp_smallest_anon_port 2048
ndd -set /dev/tcp tcp_time_wait_interval 60000
ndd -set /dev/tcp tcp_conn_req_max_q 81920
ndd -set /dev/tcp tcp_conn_req_max_q0 81920
ndd -set /dev/tcp tcp_xmit_hiwat 400000
ndd -set /dev/tcp tcp_recv_hiwat 400000
ndd -set /dev/tcp tcp_max_buf 4194304
ndd -set /dev/tcp tcp_cwnd_max 2097152

Here are suggestions:

# projadd -U db2inst1 user.db2inst1
# projmod -a -K "project.max-shm-ids=(priv,1288,deny)" 
  user.db2inst1
# projmod -a -K "project.max-sem-ids=(priv,1288,deny)" 
  user.db2inst1
# projmod -a -K "project.max-shm-memory 
  =(priv,30813703372,deny)" user.db2inst1
# projmod -a -K "project.max-msg-ids 
  =(priv,10240,deny)" user.db2inst1

The recommended way of setting IPC resource control limits is now through projects(4) instead of defining them in /etc/system, which forces a reboot to enable the parameters. The command projadd(4M) is used to add these types of tuning to /etc/project for the DB2 instance owner ID (for example, db2inst1).

With the aforementioned basic setup configurations, the Solaris 10 OS can be configured to allow DB2 to scale in terms of resource usage to more than 5000 connections in practice.

Various other types of fine-tuning can be done that are more workload specific than generic, and hence, not mentioned in this section.

Set up the resource pool especially for the DB2 project with the FX scheduler. This also helps to improve performance by separating interrupts from the strands running DB2.

This can be achieved by creating a poolcfg(1M) command file, pool.host, as follows.

modify pset pset_default (uint pset.min=2; uint pset.max=2)
create pset db2pset  (uint pset.min=30; uint pset.max=30)
create pool db2pool (string pool.schedule="FX")
associate pool db2pool 

Then apply the commands to the resource manager as follows.

# pooladm -e
# pooladm -s
# poolcfg -f pool.host
# pooladm -c
# pooladm

The last command shows which CPU IDs are assigned to the db2pool set. Then using psradm -i, the interrupts can be diverted from the CPU ID in the db2pool to the default pool.

If the CPU ID of the default pool is 29,30,31, then interrupts can be turned off using psradm for the rest of the virtual CPUs as follows.

# psradm -i 0-28

Then assign user.db2inst1 (the instance owner of DB2) to use the newly created db2pool.

# projmod -a -K "project.pool=db2pool" user.db2inst1

The /etc/project will look similar to the following.

system:0::::
user.root:1::::
noproject:2::::
default:3::::
group.staff:10::::
user.db2inst1:100::db2inst1::project.max-msg-ids= 		
    (priv,10240,deny);project.max-sem-ids=
    (priv,1288,deny);project.max-shm-ids= 	
    (priv,1288,deny);project.max-shm-memory= 
    (priv,30813703372,deny);project.pool=db2pool

With the aforementioned configuration, a db2start within the instance owner db2inst1 will start the database (which is not partitioned) in the db2pool resource pool. (For a partitioned database, refer to the DB2 Information Center on how to set DB2 to use Resource Pools.)

DB2 environment values can help you get the best out of DB2 on a Sun Fire T2000 server. The most common values typically set for DB2 on the Solaris platform are as follows.

db2set DB2COMM=tcpip
db2set DB2_PARALLEL_IO=*

If the DB2 table spaces are created on UFS file systems, the following tips can help improve the system's performance.

Use either the AUTOMATIC STORAGE option for table spaces (which requires the DB2 database to be created with the AUTOMATIC STORAGE option turned ON) or use the DATABASE MANAGED TABLESPACES option, with table spaces allocated sequentially to avoid fragmentation.

NO FILE SYSTEM CACHING helps if you are using table spaces with the option MANAGED BY DATABASE or the AUTOMATIC STORAGE option. However, if you are using the MANAGED BY SYSTEM option for table spaces, FILE SYSTEM CACHING is recommended. The NO FILE SYSTEM CACHING clause on DB2 is equivalent to the Solaris option of mounting the file system with the forcedirectio option, which bypasses the file system cache buffer and writes to the underlying device directly.

Using the forcedirectio option on the file system, the Solaris OS writes to the underlying device and waits for an acknowledgment from the device, therefore it helps if the devices have battery backed-up write cache in order to improve write performance.

The DB2 and DB configuration parameters need to be tuned according to the application profiles. Recommended settings depending on the type of workload are covered in the following sections of the paper.

OLTP types of workloads are more transactional in nature -- for example, order-processing systems, shopping cart applications, and so on. OLTP workloads typically are more flow oriented than complex processing-oriented. The most common metrics for these types of workloads are response time, transactions per second, business-metric per minute, and so on. They are characterized as the quick in-and-out type of SQL queries, which typically return a single row or relatively low numbers of rows from the perspective of the database. The settings for tuning typically are applied in the following areas of the system:

• Ability to efficiently process high network connections and packet transfers by both the OS and the DBMS
• Ability of the DBMS, with the help of the OS, to efficiently process the relatively light and medium SQL queries requested
• Improvement in the access time of all data in question to achieve a faster response time
• Fine-tuning the resources used by the system in order to handle more and more simultaneous access of similar types of queries

In the case of DB2 with the Solaris 10 OS on the Sun Fire T2000 server, we have already applied the basic network tuning and other types of tuning for scalability that are required to handle the high load.

Now let's look at how to make DB2 more efficient with the connections on this system. The DB2 tunables that help in making it more efficient for OLTP workloads are as follows.

MAXAGENTS: Increase this with a rise in the number of client applications connecting to the database.

NUM_POOLAGENT: The default, which is half of MAXAGENTS, is good for most cases, except when the private heap of agents seems to be increasing and there are memory restrictions. In such cases NUM_POOLAGENT should be decreased with the trade-off in increase in opening a new connection, which would require an agent process to be spawned.

NUM_INITAGENT: Typically this is set to 0 on the Solaris OS or a low number to match the minimum number of database connections.

Following are example DB parameters that may need to be tweaked from default values.

db2 update dbm cfg using SHEAPTHRES         20000
db2 update dbm cfg using MAXAGENTS          5000
db2 update dbm cfg using DFT_MON_LOCK       OFF
db2 update dbm cfg using DFT_MON_UOW        OFF
db2 update dbm cfg using rqrioblk           16384
db2 update dbm cfg using JAVA_HEAP_SZ       128
db2 update dbm cfg using ASLHEAPSZ          256
db2 update dbm cfg using QUERY_HEAP_SZ      2048

MAXAPPLS: Typically set <= MAXAGENTS. Otherwise, DB2 will use Connection Concentrators.

Following are example DB parameters that may need to be tweaked from default values. (Note: Exact values needed will depend on the type of application running.)

db2 update db cfg for dbname using MAXAPPLS 5000
db2 update db cfg for dbname using locklist 65535
db2 update db cfg for dbname using logbufsz 512
db2 update db cfg for dbname using seqdetect NO
db2 update db cfg for dbname using logfilsiz 65535
db2 update db cfg for dbname using logprimary 3
db2 update db cfg for dbname using num_ioservers 2
db2 update db cfg for dbname using num_iocleaners 2
db2 update db cfg for dbname using MAXLOCKS 100

Other DBM and DB parameters can be tuned to make each agent process more memory efficient to allow high connections to occur. Hence, parameter settings are very dependent on the high water specifications planned for the database along with the type of SQL statements planned for the database. (OLTP workloads typically have a known set of SQL statements that are expected from the application running on top of the database.)

In case a physical RAM shortage is created due to a high number of database connections, the following parameters can be tweaked to a lower value so that each agent requires less memory, freeing up RAM to allow more agents to be created.

db2 update db cfg for dbname using sortheap 64
db2 update db cfg for dbname using STMTHEAP 256
db2 update db cfg for dbname using APPLHEAPSZ 128
db2 update db cfg for dbname using APPGROUP_MEM_SZ 128
db2 update db cfg for dbname using SHEAPTHRES_SHR 250
db2 update db cfg for dbname using UTIL_HEAP_SZ 64
db2 update db cfg for dbname using STAT_HEAP_SZ 1096

DSS workloads typically have more complex and/or computational queries running on the database. These workloads are characterized by long-running queries. The typical metrics used for measuring DSS types of workloads are in terms of throughput per hour, queries per hour, data processed per hour, and so on. Typically the goal in cases of DSS queries is to process a single query faster rather than tuning the database to handle hundreds of client applications. Queries in DSS typically return multiple rows back rather than a single row or a few rows as in the case of OLTP queries. Hence, tuning for DSS is different than OLTP tuning.

The Solaris OS can be tweaked a bit more to get more out of DSS-type queries.

For example, /etc/system is tweaked to allow bigger I/O operation size to increase the bandwidth of the I/O done between the system and the storage subsystem.

set maxphys=1048576
set md:md_maxphys=1048576

The second option is used by Solaris Volume Manager; this option is not needed if you are not using volumes defined by metainit(1M).

If you use FILE SYSTEM CACHING-based DB2 table spaces, then the following options can also help your performance.

set ufs:ufs_WRITES=0
set ufs:freebehind=0
set segmap_percent=40

To indicate that UFS should use the bigger I/O size, the file system also needs to be tuned as follows.

tunefs -a 128 /data/db2

To use the bigger I/O size, which is typically helpful in DSS-type workloads, you can do either of the following:

• Start with a bigger pagesize for the table space (the default is 4K on DB2 and can be increased to either 8K or 32K).
• Increase the extentsize value of the table space such that pagesize * extents is about equal to the maxphys value as set in /etc/system.

The DB2 INTRA_PARALLEL option is a quick way of improving DB2 performance for DSS-type queries. Use the prstat tool of the Solaris OS with the -am option to see if turning on the INTRA_PARALLEL option will help or not. If the prstat output with the -am option shows one or more db2sysc processes using a total of User + System close to 100 percent while the average system CPU (as reported by iostat or vmstat) is low, then turning on the DB2 INTRA_PARALLEL option can help to improve DB2 performance on the Sun Fire T2000 server.

Use the default degree of parallelism (DFT_DEGREE), which is set by default to ANY. However, typically this is set to a conservative value and should be tweaked to use a higher value. It is recommended to start with a value such that (DB2 Partitions x Degree of Parallelism) does not exceed the number of CPU threads for single DB2 user performance. However, for a single-partitioned DB2, then according to the above equation, DFT_DEGREE will be 32. But typically in this scenario, somewhere between DFT_DEGREE 3 and 8, the disks subsystem can become the bottleneck depending on the I/O access pattern of the table spaces, and it does not help if you increase the DFT_DEGREE beyond that.

If there is a greater number of heavy DSS users, then higher values of DFT_DEGREE will cause more load on the system. Hence, a good rule of thumb will be to set DFT_DEGREE to 4 and tweak the value higher or lower depending on the CPU utilization and performance metrics of the application workload.

db2 update dbm cfg using INTRA_PARALLEL     ON
db2 update db cfg for dbname using DFT_DEGREE 4

Another way to improve the performance of DSS-type queries with DB2 on the Sun Fire T2000 server is to partition the database using the optional DB2 DPF feature that uses the scale-out philosophy of scaling. For a dedicated DB2 database on the Sun Fire T2000 server, the recommended number of partitions to be used is "number of cores". Use the INTRA_PARALLEL option with DFT_DEGREE of 4 to get extra performance out of the system.

DB2 partitions are project aware. The resources for each DB2 partition can be controlled by assigning projects in db2nodes.cfg. Projects can be assigned Resource Pools, hence isolating each partition with its own pool of resources for efficient performance and minimum migrations.

An example of these settings is shown in the DB2 Information Center.

The configuration and tuning tips mentioned here can help system administrators and DB2 DBAs quickly configure IBM DB2 to get optimum performance with the Solaris 10 OS on a Sun Fire T2000 server.

• Developing and Tuning Applications on UltraSPARC T1 Chip Multithreading Systems (pdf)
• Sun Fire T2000 Server - Sun Systems Handbook
• Sun Update Connection - Patches and Updates
• IBM's DB2 Information Center
• The DB2 UDB Memory Model
• Everything You Wanted to Know About DB2 Universal Database Processes

The author would like to thank Dennis Sheahan and Chien Yen (from Sun Microsystems) and Gennady Yakub and Berni Schiefer (from IBM) for their help reviewing this document.

Jignesh Shah works in Sun Market Development Engineering, helping ISV partners on the technical side to integrate products into specific solutions involving Sun. You can see his weblog on blogs.sun.com.