Fuzzy.cz

If you compare the SSD with a traditional drive in a TPC-H benchmark, you'll notice that the performance increases even less than in case of the read-write pgbench workload. For example when comparing m-time (i.e. time spent evaluating queries with identical query plans, more detailed definition is available in the article about TPC-H benchmark with a traditional drive), SSD with an XFS file system performs like this

while the traditional drive performs like this

i.e. the SSD drive is about 1.5x faster  than the traditional drive (which is significantly worse than with the read-only/read-write pgbench, where the SSD was about 25x/13x faster). But even for a SSD drive it's true that the best performance is achieved for large database blocks and the size of file system blocks does not matter.

So let's see the next part of SSD results - read-write pgbench. Just like in case of a read-only benchmark, the performance of all file systems is almost exactly the same (with the exception of nilfs2, as mentioned later). The average performance looks like this

BTW I forgot to mention one important thing in the previous post - if you're interested in the data collected during the benchmark, I'm ready to provide them. There's one slight inconvenience, though - the HDD results occupy 3.4GB (1GB gzipped), SSD results occupy 38GB (10GB compressed). That's too much to place it to this blog. But if you're going to pgconf.eu in Amsterdam, just ask me ...

In the last few posts I've discussed benchmark results with a traditional SATA drive, now it's time to discuss results of the same tests with a SSD drive (Intel 320). This post is about results of the read-only pgbench test. As expected, the SSD drive performs better than a traditional drive in all three tests, but it's interesting to see how the performance boost varies for various tests and how perfectly are eliminated differences between the file systems.

The average results for all file systems looks like this

The SSD results are available here, comparison of read-only results is here.

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.

Recently I've been asked how is the database performance affected by the block size (for filesystem and database), especially with respect to SSD drives that have 512kB internal blocks while traditional spinning HDD use 512B or 4096B sectors.

When someone asks about this on pgsql-performance list, the usual answer is "Use 8kB, Luke!" because 8kB is a reasonable compromise and using a different block size probably won't give you much. But is that really true? Is that true for all file systems? And does that hold for all workloads? And what about the SSD drives? And what about the filesystem block size?

Interesting questions, but it's impossible to answer them without hard data ... time for a benchmark!