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

51–60 of 60 posts

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

#51

Post by stock_toaster »

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.

You might also try setting primarycache=metadata, to avoid double caching the data. From what I have been told, this can help reduce memory pressure of the ARC competing with postgres' own caching.

Are you also setting a max limit for the ARC? You don't want postgres and the zfs ARC to compete for memory. I wonder if this impacts FreeBSD's poor performance in the read intensive tests.

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

#52

Post by gigatexal »

olavgg wrote: post #24
gigatexal wrote: post #7

I don’t think it’s linux raid software overhead but on the fly compression decompression of ZFS which could mean the BSDs are doing less IO than their Linux counterparts. I don’t know for sure it’s just a hunch.

I've done plenty of benchmarks with LZ4 and GZIP compression. It really helps a lot, especially LZ4 should always be enabled as it is always faster. GZIP is fantastic for some datasets where you do sequential scans of many tables around 10MB - 100MB'ish sizes, but should be used with care. The great thing about PostgreSQL is using tablespaces, so you can put tables and indexes on different ZFS filesystems, with diffe…

Interesting. I didn’t know about table spaces.

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

#53

Post by justinsaccount »

acd wrote: post #48

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.

In the linked article, FreeBSD+zfs was half as fast as linux on the read only tests.

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

#54

Post by justinsaccount »

alsadi wrote: post #47

The fs of choice for a database workload in centos 7 should be xfs because it's one of the defaults (specially in public clouds) and because it can be grown online. I suggest you add it.

> it's one of the defaults

so is ext4..

> and because it can be grown online

How this is different from how resize2fs can resize a mounted ext4 filesystem?

xfs may still be better than ext4 for some workloads, but "it's there" is not a reason why someone should use it.

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

#56

Post by RantyDave »

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.

Ah. OK, sorry.

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

#57

Post by kovacik »

stock_toaster wrote: post #51
kovacik wrote: post #49

Earlier quoted context omitted.

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

You might also try setting primarycache=metadata, to avoid double caching the data. From what I have been told, this can help reduce memory pressure of the ARC competing with postgres' own caching. Are you also setting a max limit for the ARC? You don't want postgres and the zfs ARC to compete for memory. I wonder if this impacts FreeBSD's poor performance in the read intensive tests.

I actually did try this and the results were worse. I did not investigate it further and went with primarycache=all

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

#58

Post by mobilemidget »

srevenant wrote: post #22

Sorry, but your testing methodology needs some help. Were you testing OSes? Postgres? Storage configuration? I suggest investigating FIO first, use it to isolate the best performing disk configuration (Storage+Kernel+Filesystem+whatever), then do some pgbench tests with different postgres tuned parameters, to show the best way to tune postgres. A few thoughts: * You weren't testing OSes which the subject implied, you…

I kind of miss network traffic diagrams; just mentioning its connecting over Gbit isn't enough for me. Is there 200 mbit sql traffic going back/forth, or just 8 mbit?

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

#59

Post by bradknowles »

kovacik wrote: post #35
srevenant wrote: post #22

Sorry, but your testing methodology needs some help. Were you testing OSes? Postgres? Storage configuration? I suggest investigating FIO first, use it to isolate the best performing disk configuration (Storage+Kernel+Filesystem+whatever), then do some pgbench tests with different postgres tuned parameters, to show the best way to tune postgres. A few thoughts: * You weren't testing OSes which the subject implied, you…

Hello, OP here. I'm certain that you can fine tune every OS for specific use case. I may indeed do that in a future blogpost. The question is what to compare ? Should I compare Linux kernel versions, PostgreSQL versions, filesystems (and features like compression, block size, ...) ? As you can see the permutations are endless and thats why I compared stock OSes with their default filesystems of choice. I don't think…

With respect, I believe it should be the TPC-B benchmark, not TCP-B.

It is from the “Transaction Processing Performance Council”, correct? At least, that’s what they call themselves at tpc.org.

Otherwise, interesting results that I think need further examination.

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

#60

Post by rsa4096 »

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.

I should have been more specific. I find it hard to believe many would use Linux software raid (i.e. md) for Postgres in production.
Post reply on HN