Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL Benchmark on FreeBSD, CentOS, Ubuntu Debian and OpenSUSE

redbyte.eu

Post reply on HN
Print view View original

41–50 of 60 posts

Re: PostgreSQL Benchmark on FreeBSD, CentOS, Ubuntu Debian and OpenSUSE

#41

Post by chasil »

rsa4096 wrote: post #23

I find it hard to believe that many would use SW RAID with Postgres in production, especially under load conditions that were tested for here. I have seen little hope for performant SW RAID on Linux in the past twenty years. It would be interesting to see the same tests run using stable HW RAID of some sort (preferably, with SSDs)

ZFS raidz is software raid.

If you want the ability to scrub (in such a way that detects errors over the whole storage stack), you must sacrifice hardware raid. You must also pay the fletcher/sha256 tax.

ZFS enthusiasts advocate that critical data absolutely requires those sacrifices. I'm inclined to agree.

Re: PostgreSQL Benchmark on FreeBSD, CentOS, Ubuntu Debian and OpenSUSE

#42

Post by kovacik »

Fiahil wrote: post #5

> 32GiB read only: read only test, the dataset does not fit into the PostgreSQL cache > 200GiB read only: read only test, the dataset fits into the PostgreSQL cache How come a larger dataset fit into the PostgreSQL cache and not the smaller one ?

The size of postgresql database was ~74GB for every test combination. The bare metal server had 256GB RAM for every test combination.

The only difference was the amount of RAM PostgreSQL could use - as specified in postgresql.conf.

So, 74GB database does not fit in PostgreSQL cache for 32GB instance and fits for 200GB instance.

Re: PostgreSQL Benchmark on FreeBSD, CentOS, Ubuntu Debian and OpenSUSE

#43

Post by chasil »

kovacik wrote: post #40
Fnoord wrote: post #30

Earlier quoted context omitted.

Yeah I was thinking about cache as well. It also seems unfair to compare Ext4fs on Linux with ZFS on FreeBSD. Ubuntu ships with ZFS included. There's also Btrfs. Also, its known that Netflix uses FreeBSD internally, and I'm curious why.

I think it's pretty fair to test each OS with its filesystem of choice. I'm aware that you can use ZFS on Linux, but I'm not (yet) brave enough to recommend ZFS+Linux. And yes there's btrfs but would you trust it with your data ? :)

ZFS is bundled into the Antergos Linux installer.

I think pacman can get you their PostgreSQL package easily enough.

I have screenshots at the end of this (unpublished) article:

http://syro.org/systemd/zfs.html

Re: PostgreSQL Benchmark on FreeBSD, CentOS, Ubuntu Debian and OpenSUSE

#44

Post by X86BSD »

chasil wrote: post #41
rsa4096 wrote: post #23

I find it hard to believe that many would use SW RAID with Postgres in production, especially under load conditions that were tested for here. I have seen little hope for performant SW RAID on Linux in the past twenty years. It would be interesting to see the same tests run using stable HW RAID of some sort (preferably, with SSDs)

ZFS raidz is software raid. If you want the ability to scrub (in such a way that detects errors over the whole storage stack), you must sacrifice hardware raid. You must also pay the fletcher/sha256 tax. ZFS enthusiasts advocate that critical data absolutely requires those sacrifices. I'm inclined to agree.

You don’t HAVE to checksum. You can set checksumming to off. It’s foolish and defeats the point of keeping your data safe but ZFS gives you the ability to commit suicide if you choose.

Re: PostgreSQL Benchmark on FreeBSD, CentOS, Ubuntu Debian and OpenSUSE

#45

Post by acdha »

xi- wrote: post #27

You benchmarked a compressed filesystem against uncompressed filesystems and are wondering why the compressed filesystem is slower?

That's specifically incorrect: the results showed that the compressed ZFS was slower on pgbench and faster on TPC-B.

It's also generically an unwise assumption because you have to know how fast the compression is compared to disk I/O. If, like almost all servers these days, you have more CPU than I/O capacity the compression overhead will often be buried in the I/O latency and if the data compresses well it it's easy to have it be faster because the I/O savings is greater than the CPU.

Since LZ4 was designed to be very fast, that seems like a reasonable bar to hit — I see single-core performance on old desktops in the 1.8+GB/s range.

Re: PostgreSQL Benchmark on FreeBSD, CentOS, Ubuntu Debian and OpenSUSE

#48

Post by acd »

Note that FreeBSD in the benchmark is running with LZ4 ZFS compression on which works like a speed miracle for read only database tests. ZFS with compression doubles the read bandwidth of the disks will close to double the database benchmark speed.

In Linux one would need to benchmark BTRFS with LZO compression to benchmark compete against FreeBSD ZFS with compression.

Re: PostgreSQL Benchmark on FreeBSD, CentOS, Ubuntu Debian and OpenSUSE

#49

Post by kovacik »

ianai wrote: post #31
RantyDave wrote: post #29

The glaring difference will probably be a ZFS thing. On some systems the default page size is 128k whereas databases usually use 8k. I didn't read closely enough to see what the story is with async writes, or the ZIL, or whatever. tl;dr - there are huge gains to be had with ZFS with a little tuning.

I think the BSDs have always left it to the administrator to optimize the system to their needs, and it didn’t look like OP did any optimization for the first set of results.

I did set the recommended 8k block size for PostgreSQL dataset as described in the blogpost. Also note the logbias=throughput option.

Re: PostgreSQL Benchmark on FreeBSD, CentOS, Ubuntu Debian and OpenSUSE

#50

Post by ianai »

kovacik wrote: post #49
ianai wrote: post #31

Earlier quoted context omitted.

I think the BSDs have always left it to the administrator to optimize the system to their needs, and it didn’t look like OP did any optimization for the first set of results.

I did set the recommended 8k block size for PostgreSQL dataset as described in the blogpost. Also note the logbias=throughput option.

I stand corrected. Thank you.
Post reply on HN