Database BI workload

Workload analysis

Figure 1 shows the relative throughput of the Database BI workload when processed as part of the combined workload set within a variety of differently constrained z/VM configurations.

General description of relative throughput diagrams

Figure 1 (and similar figures in subsequent test results) depict the relative throughput of the workload with respect to the reference result.

The left side of Figure 1 emphasizes the dependency of the workload relative throughput on the z/VM real memory configuration. It depicts the average relative throughput for this workload over the amount of real memory configured for z/VM, parametrized by the amount of real CPUs configured for z/VM.

The right side of Figure 1 emphasizes the dependency of the workload's relative throughput on the z/VM real CPU configuration. It shows the same set of values as the left side, but this time grouped by the amount of real CPUs configured for z/VM, and within these grouped by the amount of real memory configured for z/VM.

In both cases, the dark dotted line indicates the 100 % throughput achieved during the reference execution.

Observations:

The right side of Figure 1 shows that a corresponding reduction of the throughput results, when less real CPUs were configured for z/VM. Depending on the number of real CPUs configured for z/VM, this general behavior is more or less interfered by the amount of real memory configured for z/VM.

From the left side of Figure 1, it appears that with the z/VM configurations using 20 or 5 real CPUs, the workload throughput was comparatively low when z/VM was configured with 512 GiB real memory, but then gradually increased as z/VM was configured with less real memory. Only after the amount of z/VM real memory went below a certain limit, the relative throughput decreased again.

The throughput that came most closely to that achieved during the reference execution was 70 %, using a z/VM configuration with 96 GiB real memory and 20 real CPUs.

With the z/VM configurations using 15 real CPUs, the throughput gradually decreased as less z/VM real memory was configured. It is noteworthy that when 512 GiB real memory were configured for z/VM, the throughput was 60 % – slightly higher than with 20 real CPUs.

The z/VM configurations using 10 real CPUs produced throughput results that fluctuated between 30 % and 44 %.

Conclusions:

The Database BI workload seems to be more impacted by z/VM real CPU constraints as opposed to z/VM real memory constraints. Furthermore, the influence of the amount of real memory configured for z/VM varies depending on how many real CPUs are configured.

When either 20 or 5 real CPUs were configured for z/VM, the Database BI workload was able to reclaim throughput in spite of the fact that the amount of real memory configured for z/VM was decreased. Apparently in these cases the other workloads suffered more than the Database BI workload from the z/VM real memory constraints, preventing them to make full use of other resources such as CPU and I/O bandwidth. The Database BI workload in turn could apparently claim these resources, resulting in a relative throughput increase.

This is a very important aspect to be always considered when evaluating the results from this study. It shows not only the effects resulting from real resource shortage, but also shows that the results were additionally influenced by the behavior of the other workloads.

The behavior of the Database BI workload, when processed in real memory constrained z/VM configurations, exhibited an increasing throughput when z/VM real memory was reduced. This is very untypical when compared the throughput of the most other workloads, which stay constant and then decrease. One probable reason for this behavior might be that databases are very sophisticated systems that might be able to compensate the impacts resulting from memory access latencies by efficiently caching data in buffer pools, and by asynchronously scheduling operations.

CPU usage analysis shows that indeed in the 20 real CPU case, the Database BI workload for most z/VM real memory configurations was able to consume more CPU than during the reference execution.

CPU usage analysis

Figure 2 shows the average CPU load for the Database BI workload when processed as part of the combined workload set within a variety of differently constrained z/VM configurations.

A general description of the elements shown in Figure 2 is given in General description of relative throughput diagrams, except that in Figure 2 CPU load is presented instead of throughput.

Observations:

When 20 real CPUs were available to z/VM, the Database BI workload for all except the 512 GiB z/VM memory setting, generated a higher average CPU load at a lower throughput than during the reference execution (see Figure 1).

When 15 real CPUs were available to z/VM, the average CPU load decreased slowly when z/VM was configured with 112 GiB real memory. The decrease was more pronounced when 96 GiB or less of real memory were configured for z/VM.

When 10 or less real CPUs were configured for z/VM, the dependency of the average CPU load on the amount of real memory became less significant, and there was almost not dependency when only 5 real CPUs were configured for z/VM.

Conclusions:

The CPU load behavior of the Database BI workload reacts more strongly on CPU constraints, as opposed to constraints on the amount of real memory. For example, halving the amount of real memory configured for z/VM from 128 GiB to 64 GiB when z/VM was configured with 10 or 5 CPUs, caused only slight reductions of the average CPU load. By contrast, when halving the amount of real CPUs from 20 to 10 with all but the lowest z/VM real memory setting, the average CPU load was almost halved as well.

When 20 real CPUs were configured for z/VM, the average CPU load generated by the Database BI workload was even higher than that generated during the reference execution. In other words, when running in a memory constrained z/VM environment, but with sufficient processing power available to z/VM, the Database BI workload reclaimed processing power, apparently at the expense of other workloads. This was already suspected when analyzing the workload performance, see the workload analysis. However, this gain of CPU power did not result in an absolute increase of workload throughput. Apparently, in these cases, the Database BI workload lacked other resources required for its workload processing.

Memory usage and paging workload analysis for CID 64/20

A detailed memory usage and workload analysis is only presented for the z/VM configuration with 64 GiB real memory and 20 real CPUs (CID 64/20). We consider this configuration as most suited to outline the effects resulting from z/VM real memory and real CPU constraints.

Figure 3 shows the average page distribution and the average page read rates for the Database BI workload when processed as part of the combined workload set when z/VM is running with the CID 64/20 configuration.

General description of page distribution and page read rate diagrams

In Figure 3 (and similar figures in subsequent test results), the blue columns on the left side depict the page distribution for the virtual system running the workload or workload component.

• The lower part (bright blue) represents the virtual system's resident memory.
• The upper part (dark blue) represents the stored memory.
• The bright yellow line marks the virtual system's instantiated memory. For comparison, the bright red line shows the virtual system's instantiated memory during the reference execution.

The magenta column on the right side depicts the page read rate for the virtual systems running the workload. If the workload is comprised of more than one component, the page read rate of each component is shown in a different shade of magenta. For comparison, the page read rate for the whole z/VM system is also depicted (hollow magenta).

Observations:

On average, the Database BI workload used about 62 % (39936 MiB of 64783 MiB) of the z/VM DPA. The average instantiated memory of 80896 MiB was about 3.7 % less than that of the reference result (83968 MiB). About half of the instantiated memory was kept resident by z/VM, while the other half was stored on paging devices.

Note that the sum of resident memory and stored memory is larger than instantiated memory, indicating that a certain amount of virtual memory is backed on both locations: in z/VM real memory and on z/VM paging devices.

Also note that the instantiated memory within CID 64/20 is less than that observed during the reference execution.

On average, the Database BI workload accounted for about 22 % (20 MiB/s from 88 MiB/s) of the z/VM page read operations. About 0.05 % (19.414 MiB/s / 39936 MiB) of the virtual system's resident memory were in the process of being paged in per second. At the same time – not shown in Figure 3 – about another 0.05 % (19.628 MiB/s / 39936 MiB) were in the process of being paged out per second.

Conclusions:

The Database BI workload is by far the largest consumer of virtual memory from the combined workload set – resulting in the use of almost two thirds of the z/VM DPA size. With that large amount of virtual memory kept resident, the Database BI workload consumed only about a quarter of the bandwidth of the z/VM paging subsystem.

For the Database BI workload, the dominant parameters determining the virtual memory consumption (beyond the memory usage imposed by the Linux system itself) are the Oracle system global area (SGA, configured 200 GiB) and the program global area (PGA, configured 45 GiB) sizes.

A rather low number of workload users (16) was used to drive the Database BI workload in order to keep the workload CPU usage within practical limits. As a consequence,

• the amount of allocated shared memory was 67 GiB (as reported by Linux™ sar report). The shared memory usage is a metric for the actually used fraction of the SGA. In this case, it is still larger than the average amount of resident memory. In other words, at least a fraction of the shared virtual memory is backed as stored memory, that is, it is stored on z/VM paging devices.
• the amount of memory really used for the PGA is about 100 MiB (according to the Oracle AWR report).
• the memory requirement for Linux page tables was small (about 60 MiB), even though no large pages were used for the Oracle memory pools.

Apparently, the most important pages for the database could be kept in real memory. This seems to make the Database BI workload well suitable for memory over-committed environments. This assessment is even strengthened by the fact that the instantiated memory for the database virtual system is about five times larger than for the other largest guest (configured with 16 GiB virtual memory), and about twenty times larger than that of a typical 4 GiB guest.

Recall that if upon virtual memory access a page needs to be read from the z/VM paging devices, a significant performance impact results, because of the much lower disk access speed compared to the real memory access speed. Thus, a high page read rate is a first indicator of a potential performance impact. Considering the large guest size used for the database (300 GiB), the observed page read rate of 20 MiB/s still seems moderate. Apparently, the database is able to hide the impact on total throughput, for example by asynchronously scheduling operations.