11 ms·
PostgreSQL Benchmark on FreeBSD, CentOS, Ubuntu Debian and OpenSUSE
- Tostino 9y agoOh man, that is some serious SW raid overhead. Does anyone who knows FreeBSD and Postgres have any idea why it was so much slower for the read only tests?
- GalacticDomin8r 9y agoIt may be related to the multi-threaded page daemon: https://www.kib.kiev.ua/kib/pgsql_perf_v2.0.pdf https://www.kib.kiev.ua/kib/pgsql_perf_v2.0.pdf
- brightball 9y agoWow! The difference in RAID between FreeBSD and the GNU Linux flavors is more than a little bit shocking on the read/write tests. I would not have expected it to be that significant. I wonder what it will take to fix that in Linux?
- saosebastiao 9y agoI wasn’t expecting such a drastic difference between FreeBSD and the linux distros. I wonder if this is a general BSD thing, or if it was FreeBSD specific (or filesystem or drivers).
- Fiahil 9y ago> 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 ?
- belthesar 9y agoI wondered the same thing. Best explanation had to do with the 32 GB dataset being on the system with 32 GB of RAM vs the 200 GB dataset on the system with 256 GB of RAM. Makes sense, but isn’t clear from the article’s perspective.
- elygre 9y agoProbably different postgresql.conf-settings. For example, 32GiB instance has "effective_cache_size = 24GB", while 200GiB instance has "effective_cache_size = 144GB"
- lazyjones 9y agoThey used 2 different servers. One with 32GB RAM, the other with 256GB.
- ianai 9y agoThat might explain the first results. ZFS eats ram (to good use).
- kovacik 9y agoThe 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.
- gigatexal 9y agoI 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.
- Nicholas7900 9y agoCOMPOSITE HACKS If Truly you Are In Need Of A PROFESSIONAL LEGIT HACKER Who Will Get Your Job Done Efficiently With Swift Response, Congratulations, You Have Met the Right HACKERS. WHO ARE COMPOSITE HACKS??? • We are a Team Of Professional HACKERS , a product of the coming together of Legit Hackers from the Dark-Web, (pentaguard,CyberBerkut, RedHack , Black Hat, White Hack ) we have been existing for over 12years, our system is a veryStrong and decentralized command structure that operates on ideas and directives. JOB GUARANTEE: • Frankly speaking, I always give a 100% guarantee on any job we are been asked to do, because we have always been successful in Almost all our jobs for over 12years and our clients can testify to that .To hack any thing needs time though, but we can provide a swift response to your job depending on how fast and urgent you need it.Time also depends on what exactly you want to hack and how serious you are.Enough time with social engineering is required for hacking.So if you want to bind us in a short time, then just don't contact us because We can't hack within 30minutes,sorry.Basically, time depends on your luck.If its good luck, then it is possible to hack within 30minutes but, if it is in the other way round, it would take few hours.I have seen FAKE HACKERS claiming they can hack in 30min, 20min , but there is no REAL HACKER who can say this (AVOID THEM). Please Note : we have only one contact email : compositehacks@gmail.com We will be happy to have you join over 2000 satisfied clients around the world to use our services. OUR HACKING CAPABILITIES: There are so many Reasons why people need to hire a hacker, It might be to Hack a Websites to deface , retrieve information, edit information or give you admin access Some people might need us for Hacking any smart phone giving you access to all activities onthe phone like , text messages , call logs , Social media Apps and other informations.Some might need to Hack a Facebook , gmail, yahoomail, Instagram , twitter and every other social network Accounts, Some might need to Hack into Court's Database to Clear criminal records.However we can also Hack into school's websites (server) to change grades without any trace, Also Some Individuals might want to Track someone else's Location probably for investigation cases. All these Are what we can get Done withing few hours. SOME OTHER SPECIAL SERVICES WE OFFER: Hacking and sales of Programmed credit cards & CREDIT SCORE TOP-UP Sales of untraceable phones (even the pentagon can not track our phones) Sales of Tutorial packs for Beginner Ethical Hackers. You can also contact us for other Cyber Attacks And Hijackings, we do almost All. Contact Us for Your Desired Service Via: compositehacks@gmail.com We Treat Every Request With Utmost Confidentiality
- kchoudhu 9y agoWow, this is pretty eye-opening for anyone looking to use FreeBSD in production as a database server. Anyone from FreeBSD devs have any thoughts on why we're so far behind?
- GalacticDomin8r 9y agoI think you meant so far ahead.
- howard941 9y agoThe article's confusing and surprising. He tried postgresql on FreeBSD UFS where it sucked but on ZFS, it rocked.
- GalacticDomin8r 9y agoYeah it's not a super great article, one thing I prefer to see is ufs compared against ext and zfs under both OS's. Plus detailed info on the setup.
- kakarot 9y agoI can't see the results, even with uBlock and uMatrix turned off. Using Firefox.
- Macha 9y agouBlock and HTTPS Everywhere both enabled but no uMatrix - works for me on Firefox.
- anttisalmela 9y agoAt least Debian and Ubuntu has cfq as the default IO scheduler which probably is not the best choice here.
- ketsa 9y agoLooks like the underlying filesystem is the key, not the OS.
- srevenant 9y agoSorry, 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 were testing Linux kernel variants and their stock OS configurations/kernel scheduler setups, and FreeBSD was tossed into the mix. Whether you are running Ubuntu, CentOS, Debian or whatever you should have the Linux kernel tuned to perform well, so adding the distribution as a variable is just a red herring. I'd be more interested in removing that variable and comparing different storage configurations (such as XFS, and LVM). * Clients connecting over the network adds a huge variable at play (the network) -- ideally you would want to remove this. * I may have missed it, but it wasn't clear if you had a warmup period to your benchmarks. Especially with a system like ZFS which has COW, you need to do a few benchmarks on the same blocks first, to break past the cache.
- kev009 9y agoHe did adequately disclose that the OS was swapped out while pgsql and settings were held steady. Adding in more arbitrary benchmarks like FIO wont really clarify pgsql performance if that is the desired investigation. Instead, active bench marking where you identify the bottlenecks in each tested platform would be a better use of time, and you could hypothesize how to improve each platform if you were to pick it. As a counter-example, I could easily cherry pick versions, tunables and patch sets to make the numbers go whichever way I want so these types of comparisons aren't that useful unless someone is dropping a big delta on the floor with out of the box settings vs another. As a FreeBSD developer, I will actually tell you that Linux could be selected to graph massive wins by cherry picking hardware with very high core count and several NUMA domains. But even then, by selecting kernel features (which could be innocuously hidden in a version number/vendor patch set) you can cherry pick large swings. That said, FreeBSD+ZFS+PGSQL (https://www.slideshare.net/SeanChittenden/postgresql-zfs-best-practices https://www.slideshare.net/SeanChittenden/postgresql-zfs-bes...) is a joy to administer, and is unlikely to be the weak link in a production setup if you stick to a two socket system. There is a lot of work going on in HEAD that is relevant to this workload in the memory management subsystem, including NUMA support. And some TCP accept locking changes that'd be relevant for TCP connection turnover.
- rsa4096 9y agoI 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)
- chasil 9y agoZFS 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.
- X86BSD 9y agoYou 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.
- rsa4096 9y agoI should have been more specific. I find it hard to believe many would use Linux software raid (i.e. md) for Postgres in production.
- xi- 9y agoYou benchmarked a compressed filesystem against uncompressed filesystems and are wondering why the compressed filesystem is slower?
- acdha 9y agoThat'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.
- b2ttb 9y agoThe benchmark is a bit arbitrary, there are just too many variables. Filesystems (ZFS/ext4), RAID (ZFS Raid vs Linux Software Raid), compression (ZFS lz4 vs raw), IO-Scheduler (CFS, deadline, ULE). Also it is not clear if the Postgres dataset includes the data and transaction logs (WAL).
- RantyDave 9y agoThe 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.
- ianai 9y agoI 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.
- kovacik 9y agoI did set the recommended 8k block size for PostgreSQL dataset as described in the blogpost. Also note the logbias=throughput option.
- ianai 9y agoI stand corrected. Thank you.
- stock_toaster 9y agoYou 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.
- kovacik 9y agoI actually did try this and the results were worse. I did not investigate it further and went with primarycache=all
- RantyDave 9y ago
- more-entropy 9y agoI suppose, it's worth nothing to read an article, which compares benchmarks of Ubuntu and CentOS. It's too bullshit. Be honest, compare comparable things.
- jedisct1 9y agoDragonflyBSD should have been included.
- alsadi 9y agoThe 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.
- justinsaccount 9y ago> 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.
- acd 9y agoNote 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.
- justinsaccount 9y agoIn the linked article, FreeBSD+zfs was half as fast as linux on the read only tests.
- songco 9y agoGreat test, thanks.