Introduction
This is the start of an ongoing investigation into system performance of an oracle 10.2 data warehouse being loaded . The database server has 2 real storage volumes (called dw-clear and dw-encrypt) and 1 virtual one (dw-encrypt-u) used to decrypt data on the fly. Most of the data and the i/o are on the dw-clear volume.
System-data performance have been collected via
sadc -d to capture per-device statistics. The data are then extracted using
sadf -d filename -- -d -b -d. The summary is available
here as a csv. It's a large table of block i/o stats, cpu stats and per-device i/o stats, suitable to be imported into R.
The system characteristics are as follows.
- Sun x4150 64GB RAM, 2x4 x5450, 1 4Gb/s QL2462 HBA with 2 ports.
- 3 device-mapper devices, 2 using a round-robin multipath (v1, v2), 1 using an on-the-fly cipher to decode encrypted data (v3).
- 3PAR S400 with 10k drives and 4Gb/s HBAs.
- Out of the 64GB, 8GB are set aside as HugePages to serve as memory pages for the SGA.
The goal of this investigation is to understand what the bottleneck is in the processing and what can be done to remove it.
Let's start with cpu utilization.
[caption id="attachment_172" align="aligncenter" width="510" caption="Distribution of CPU time spent in userland when not idle"]
[/caption]
Not terribly loaded (I'm filtering out the long idle portions with
user > 5. How about I/O?
[caption id="attachment_177" align="aligncenter" width="510" caption="% of CPU spent waiting on IO"]
[/caption]
Interesting,
iowait is not negligible. Is it correlated to anything in particular? First of all, let's see how
iowait varies with device utilization of v1.
v1 is slowly but surely bringing
iowait higher, to the point than more than one processor ends up waiting on I/O.
To be continued...
