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, 2×4 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.
Distribution of CPU time spent in userland when not idle
Not terribly loaded (I’m filtering out the long idle portions with user > 5. How about I/O?
% of CPU spent waiting on IO
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…
