Fuzzy.cz

Last week I've posted a bief analysis of the read-only and read-write OLTP parts of the benchmark, now it's time to look into the part dealing with DWH/DSS workload, based originally on the TPC-H benchmark. In brief, the average results for various block sizes are these

A more detailed description and definition of m-score will be presented later, for now it should suffice that higher values mean better performance. It's clear that larger blocks (file system and database) yield better performance when querying the data.

But does this hold for other tasks common in DWH applications, i.e. loading the data, creating indexes, foreign keys, collecting stats etc. And how do the various file systems perform?

I've already briefly analyzed results of the read-only pgbench runs, now it's time to discuss the read-write workload. The average tps for all file systems with write barriers enabled looks like this

That clearly shows that, just like in case of a read-only workload, better performance is achieved with smaller database blocks. The difference between 1kB and 32kB blocks is about 30%, depending on the file system block size.

In the previous post, I've briefly described benchmark results, especially how to interpret the images. Now it's time to analyze the first part of the results, namely the read-only portion of the OLTP workload (i.e. the output of the read-only pgbench runs).

TPS over all tested file systems (detailed comparison is available here), with XFS excluded (it's the only system with 512B blocks and makes the image a bit less readable) looks like this

The image clearly shows that the best performance is achieved for small database blocks and large file system blocks. The dependency on file system block size is not equally strong for all the file systems - for some file systems it's quite strong, for other file systems there's almost no dependecy.