Database LPAR analysis

Edit online

This study measures the effect on the DB2® relational database of scaling the workload and the CPUs on the WebSphere® system.

Even though the transactional workload stresses the DB2 backend database system only slightly, this stress shows the effects resulting from scaling the workload and the CPUs on the WebSphere system.

Methodology

The DB2 snapshot tool is used to collect database snapshot data before and after the steady state portion of the workload. The Dynamic SQL statements attempted during the 10 minute steady state were added to the static SQL statements, and a DB2 workload throughput rate is established. CPU utilization data was also obtained. The CPU cost per unit is calculated as sql/sec/CPU * 100, meaning that the number of transactions driven with 100% CPU (== 1 Integrated Facility for Linux® (IFL)). This is the value shown in the sql/sec/CPU * 100 column. It is a measurement of the throughput that takes into account the CPU cost.

Table 1 summarizes these results in tabular format. Figure 1, Figure 2, and Figure 3 are graphical representations of the results.

Table 1. Database LPAR analysis: DB2 UDB CPU utilization and throughput
Workload throughput	31-bit or 64-bit WebSphere	CPU utilization	SQL statements per second	sql/sec/CPU * 100
101	31-bit	23%	4193	18326
100	64-bit	25%	4282	17350
174	31-bit	38%	7263	19013
174	64-bit	39%	7233	18700
322	31-bit	65%	13500	20692
321	64-bit	65%	13476	20732
551	31-bit	115%	21853	19057
550	64-bit	120%	22973	19176

Graph of transaction rates for database LPAR — Figure 1. Database LPAR analysis: Database transaction rate for scaling WebSphere Application Server CPUs and workload submission rate.

Graph of CPU utilization for Database LPAR — Figure 2. Database LPAR analysis: CPU utilization on Database LPAR

Graph of workload for SQL transactions — Figure 3. Database LPAR analysis: Normalized internal SQL transaction rate

Observations

The number of SQL statements per second for the 64-bit WebSphere Application Server is very linear, while the 31-bit WebSphere Application Server causes fewer statements per second at a submission rate of 600. The CPU utilization scales linearly up to a submission rate of 350, with a higher slope to the submission rate of 600, where it increases faster with a 64-bit WebSphere Application Server.

Looking at the CPU cost in terms of how many statements are driven with one CPU, it is shown that the cost is independent of the addressability from the application server, except for the submission rate of 110. It is also shown that submission rate 350 achieves the largest number of SQL statement processed with the database CPUs.

Conclusions

The CPU load of the DB2 LPAR is generally quite light for the transaction workload. A full CPU is rarely used unless there are eight CPUs used on the WebSphere system. The workload generally uses short data records and does not use complex SQL statements, so the amount of backend database load is expected to be light.

It should be remembered that in an end-to-end transaction, the performance of the middleware will affect the DB2 CPU utilization and workload throughput. The DB2 load originating from 31-bit or 64-bit WebSphere was nearly identical. The higher cost on the 64-bit version at a submission rate of 110 strongly suggests that the WebSphere CPU load is too high for the HiperSockets connection, because missing CPU resources for the HiperSockets on the middleware will cause overhead on the database for re-sending packets.