Siebel 6 is a Customer Relationship Management application. It uses a two tier architecture that includes "fat" PC clients that run the application and a backend database server. The database server workload is mostly "read" queries.

The benchmark measured the database performance in terms of throughput and query response time. The load was generated by up to 800 simulated users running queries. The queries ranged in complexity from simple lookups to long running reports requiring non-indexed table scans and up to 16 way table joins.

The server ran AIX 4.3.3 ML7 and Oracle 8.1 (64 bit). The Oracle tables resided on a JFS filesystem. The client connections were JDBC (Java 1.1.8). The server was a p680 with 24 CPU's, 64 GB memory, 100 Mbit Ethernet, 4 Fibre adapters connected to Shark "Raid 5" disk. The Shark was configured as 8 "6+P" Raid 5 LUN's, with 32 GB Cache..

The AIX tuning for this benchmark focused on I/O settings for multiuser database access. We were concerned with two types of database workloads: simple lookup queries and long running reporting queries.

The following tables list the AIX settings used in the benchmark. The tables are organized by CPU, memory, I/O and network settings. In a few cases, the setting affects two categories (ie memory and I/O), In these cases, my assignment to a category was arbitrary.

The benchmark system had sufficient memory to avoid paging. If paging had occurred, we would have tuned memory to favor paging out permanent storage (executables, filesystem metadata) over paging out working storage (Oracle's SGA). To do so, see the "vmtune" command and the "minperm" and "maxperm" settings.

I/O Settings

Randomize data layout: reduce possible I/O contention by maximizing data spread across disks (mklv -e x). In addition, each LV should be laid out in unique sequence across the disks. This minimizes I/O bottlenecks that can occur when multiple serial reads (table scans, data loads) "tailgate" each other.

To specify a layout sequence, first create the LV specifying the layout sequence. Second create a filesystem on the "previously defined" LV. Here's an example of how to create 2 LV's across the same disks, but with different sequencing.

Improve sequential read by increasing "read ahead: When AIX senses a sequential read, it attempts to improve I/O performance by increasing the number of blocks it reads on the next I/O. The vmtune "maxpgahead" flag determines the number of blocks AIX can read ahead. The maxpgahead parameter must be a power of 2 (2, 4, 8, 16, …).

In our benchmark, we sized "maxpgahead" so that the maximum read ahead would roughly match the size of the RAID 5 stripe across the disks (6+P). We used the following formula to calculate "maxpgahead", then rounded up to the nearest multiple of 2:

JFSLOG is a logical volume that is used by AIX to journal file changes. There's at least one JFSLOG in each Volume Group containing a filesystem. The JFSLOG helps maintain data integrity, but can become a bottleneck during heavy write traffic.

The JFSLOG should be tuned for filesystem size and I/O rate. The default JFSLOG is undersized for large filesystems. The sizing rule of thumb is 2 MB JFSLOG per GB of filesystem. To support 150GB of filesystems, we needed a 300 MB JFSLOG. To prevent the JFSLOG from becoming an I/O bottleneck, we "Raid 0" striped it across multiple disks.

The procedure for recreating a striped JFSLOG is below. In this example, the volume group name is "datavg", the JFSLOG is named jfslog00, and we want to "Raid 0" stripe it with 128k blocks over hdisks 6, 7, 8 and 9.

1. Unmount all filesystems in datavg
2. Remove the JFSLOG: rmlv jfslog00
3. Recreate JFSLOG: mklv -y jfslog00 datavg -S128k hdisk6 hdisk7 hdisk8 hdisk9
4. Format the JFSLOG: logform /dev/jfslog00
5. Remount filesystems in datavg

Asynchronous I/O improves write performance by grouping acknowledgments. Applications must be written to take advantage of AIO, as is the case with Oracle. AIO tuning involves specifying the number of AIO servers, and enabling them (smit aio). We set minimum AIX at 150, based on a SupportLine's recommendation. The maximum setting of 300 is double the minimum setting.

During the benchmark, we noted the minimum setting of 150 was probably too high. The number of active AIO servers should be between the min/max setting (pstat -a | grep -c aios). Being overconfigured doesn't hurt, so we didn't change the setting.

All of the networks setting were changed before the start of the benchmark. The changes were based on prior experience, not bottlenecks.

System performance: the CPU averaged less than 30% utilization. Wait I/O averaged below 15%. Disk activity was equally distributed across all disks. No system bottlenecks were observed during the benchmark.

Read-only databases can have significant write content. This benchmark exhibited periods of high "write" activity, even though the queries were read-only. In order of activity, the sources of write I/O included:

1. The JFSLOG had the highest level of write activity. The JFSLOG was 100% utilized at the end of the benchmark when Oracle closed inactive connections. (We suspect this is a "known" Oracle bug associated with excessive fsync() calls. I understand a patch is available.) Because it occurred after the benchmark, it didn't affect the results.
2. Table joins were the second highest source of write activity. Write I/O to Oracle's temp disk peaked over 40MB/sec. However these I/O's tended to short bursts lasting 10-20 seconds.
3. Oracle log files accounted for up to 5% of the Wait I/O. This level of activity would not be considered a system bottleneck, but might affect Oracle performance.

Memory requirements: The primary users of memory were Oracle's SGA and the JDBC connections. The Oracle SGA used 4-5 GB. (It could have been larger, but was constrained to match the size competitive server.) Each JDBC connection between the client and the database required roughly 3 MB of memory on the p680. The p680 ran out of memory at about 20,000 connections, the excess connections being paged to disk. The large number of connections did not significantly affect the database performance (after the excess connections were paged to disk).

Data Caching: AIX and Oracle did a good job of caching data in memory. There was almost no disk I/O with 400 active users running simple queries. Most I/O was associated mainly with writing temp files.

Because of the low disk activity, I would recommend the default 8 GB Shark cache over the 32 GB cache used in the benchmark.

Mixing simple and complex queries required more resources than if run separately. There appeared to be an interaction between simple lookup and long running queries that reduced throughput. The effect is difficult to quantify, but I'd estimate it to be on the order of 10-15% added overhead.