Datawarehousing benchmark

Recently some of my colleagues and myself worked on a data warehousing benchmark. This comprised of a 1TB data set consisting of a 14 billion row fact table with 5 dimensions of a much smaller size. Nothing outrageous a classical star schema... bread and butter to Oracle. We used the following hardware configuration

2 Intel based servers.
  • 4 CPUs with Dual Cores (hyper threading enabled).
  • 64 GB of memory in each
  • 2 dual ported 4Gb HBAs
  • 2 dual ported 1Gb Nics
The storage consisted of
  • 10 low cost storage arrays
  • 20 controllers
  • 20 trays of disk with 14 15k 36GB drives in each
The fabric consisted of
  • 2 mid range 16 port fibre switches
The network consisted of
  • 1 mid range 16 port 1Gb switch (I know we really should have had two of these)

The point here is that the hardware was low end but with the ability to deliver plenty of CPU and I/O at very attractive price point. For those that aren't that interested in any more of the details the headline figures where that we performed full table scans at over 1.6GB/sec but due to compression we achieved logically three times this figure.

We used a stock 2.6 64 bit Linux kernel we modified the following kernel settings
  • wem, rmem were to set to 512k to reflect the fact that we were going to use 32k block sizes
  • Huge pages were enabled... its a much more sensible way of managing the shared memory required for the SGA
  • We enabled jumbo frames in the switch and set the MTU on the interconnect to 9000bytes
  • We used the deadline scheduler to improve I/O prioritisation.
  • Multipath was used to provide DMP over the 4 paths to disk from each of the servers.

The Oracle database was largely unchanged from the defaults with the exception of the following settings
  • 16GB for SGA and PGA
  • The fact table was compressed
  • 32k block sized to improve compression ratios
  • Two parallel instance groups to ensure execution of certain queries on a given node.
  • Query rewrite was enabled.
  • ocfs2 was used for the quorum and ocr files
  • ASM was used to provide storage for the datafiles etc. Each physical tray of disk was presented to ASM as logical disk of roughly 500Gb.
  • ASMlib was used to persist disk identity and simplify configuration.

The tests consisted of a series of SQL statements that were run serial, concurrently and as a series of streams. I cant give much in the way of specifics for obvious reasons but the machine ran at about 80% utilization during the test most of the queries returned sub minute when run against the full data and many sub second when materialized views were exploited.

However the real benefit came from the fact that low the cost commodity hardware made the system relatively cheap and easy to put together. Like any system there were things we would have liked to have done differently if we'd had the chance... The benchmark was done in 10 days (OS Install, disk, layout, Oracle install, data load etc.) and so things were done in a rush and with a little more thought could have been done much more efficiently... We are fairly certain we could have improved the I/O performance but we didn't have the luxury of experimentation and so we made a call and lived with the consequences... I strongly recommend that any one building one of these systems spends some time with Oracle Orion disk benchmarking system to determine an optimal layout. A recent customer evaluating several possible disk layout configurations showed over 100% difference between two subtly different versions.
blog comments powered by Disqus