Subtly Bad Things Linux May Be Doing To PostgreSQL • Hacker News Board

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#31

Post by fauigerzigerk » Thu, Apr 03, 2014, 9:22 AM UTC

davidpardo wrote: post #5

Being a user of both Linux and PostrgreSQL, I'm very interested in this issue, but I only understand some of the words... Could everybody wiser than me tell me if I should be concerned and the possible implications of these decisions? Should I invest in alternative platforms?

It is something that could be helpful to have at the back of your mind if you're tasked with optimizing postgres and OS settings for big workloads.

But it's one of a myriad of little things, not something that could inform a platform decision. It's much more interesting for kernel devs than it is for postgresql users.

I think what this shows is that issues related to interactions between RAM, caches and CPU cores are becoming a lot more complex on all platforms.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#32

Post by kev009 » Thu, Apr 03, 2014, 9:28 AM UTC

ksec wrote: post #22

kev009 wrote: post #6

Earlier quoted context omitted.

+1 I'd love to see a FreeBSD kernel hacker chime in here. ZFS enables some interesting things for pgsql: * http://open-zfs.org/wiki/Performance_tuning#PostgreSQL - it seems like the primarycache setting prevents the double buffering problem that Linux' page cache has * http://citusdata.com/blog/64-zfs-compression I run pgsql on FreeBSD/ZFS and have no complaints but am not taxing the system.

I love ZFS, but Oracle is holding it up which is very annoying.

How do you mean? I trust OpenZFS more and in fact would consider it negligent if Oracle wasn't cherry picking OpenZFS bug fixes. Several original Sun engineers including ZFS co-founder Matt Ahrens are involved with OpenZFS.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#33

Post by tadfisher » Thu, Apr 03, 2014, 9:29 AM UTC

dgarrett wrote: post #29

moe wrote: post #3

I love that infinitely nerdy stuff like this can still make it to the HN homepage (there's still hope!). Stuff like this really needs to make it into the PG tuning guide ( https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serv... ). The only place where it will ultimately be seen by a worthwhile audience.

> I love that infinitely nerdy stuff like this can still make it to the HN homepage (there's still hope!). I can't wait for "Subtly Bad Things Linux May Be Doing To 2048"

This isn't helping.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#34

Post by octotoad » Thu, Apr 03, 2014, 9:35 AM UTC

ksec wrote: post #22

kev009 wrote: post #6

Earlier quoted context omitted.

+1 I'd love to see a FreeBSD kernel hacker chime in here. ZFS enables some interesting things for pgsql: * http://open-zfs.org/wiki/Performance_tuning#PostgreSQL - it seems like the primarycache setting prevents the double buffering problem that Linux' page cache has * http://citusdata.com/blog/64-zfs-compression I run pgsql on FreeBSD/ZFS and have no complaints but am not taxing the system.

I love ZFS, but Oracle is holding it up which is very annoying.

Do you mean they're holding back progress? With OpenZFS on the scene, it's highly likely that the original Sun/Oracle implementation will become irrelevant to everyone apart from users of Oracle's Solaris releases.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#35

Post by Orva » Thu, Apr 03, 2014, 9:43 AM UTC

davidpardo wrote: post #5

Being a user of both Linux and PostrgreSQL, I'm very interested in this issue, but I only understand some of the words... Could everybody wiser than me tell me if I should be concerned and the possible implications of these decisions? Should I invest in alternative platforms?

In NUMA front Linux is, as far as I know, leaps and bounds ahead of FreeBSD, Solaris and Windows. All four platforms offer ways to tune process and memory allocations by hand, but Linux is only platform that puts lot of work to make automatic NUMA scheduling actually work. It is actually pretty difficult problem. Something to read if you are interested:

http://queue.acm.org/detail.cfm?id=2513149 http://lwn.net/Articles/591995/ http://lwn.net/Articles/568870/

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#36

Post by lallysingh » Thu, Apr 03, 2014, 10:03 AM UTC

For (1), you can use numactl to change the default behavior. For #s (2) and (3), I'm pretty sure this is FS dependent?

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#37

Post by bcantrill » Thu, Apr 03, 2014, 10:06 AM UTC

kev009 wrote: post #32

ksec wrote: post #22

Earlier quoted context omitted.

I love ZFS, but Oracle is holding it up which is very annoying.

How do you mean? I trust OpenZFS more and in fact would consider it negligent if Oracle wasn't cherry picking OpenZFS bug fixes. Several original Sun engineers including ZFS co-founder Matt Ahrens are involved with OpenZFS.

Yes, as I have elaborated upon at length[1], the ZFS engineers have long-since left Oracle -- and the open community's ZFS (that is, OpenZFS) has become the ZFS of record.

One clarification: Oracle can't actually cherry-pick back OpenZFS bug fixes because they are (ironically) violating the CDDL by not making available source code. This isn't an issue for the code for which they hold copyright -- but that doesn't include any of the bug fixes and features that we've seen in OpenZFS since 2010. And yes, it is absolutely negligent, but of a different sort than you intended...

[1] http://www.youtube.com/watch?v=-zRN7XLCRhc

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#38

Post by rivert » Thu, Apr 03, 2014, 10:10 AM UTC

related post on lwn: https://lwn.net/Articles/591723/

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#39

Post by ro_sharp » Thu, Apr 03, 2014, 10:10 AM UTC

davidw wrote: post #13

kev009 wrote: post #11

Earlier quoted context omitted.

That's a very obtuse point of view. I'm curious sociologically: what field do you work in and what your exposure to data is? Consider an inventory system for a big box retailer. I can't think of anything better than a fat-ass RDBMS as the primary data store. Sharding sounds like a horrific idea. There are myriad workloads like this. Personally, I've seen pgsql handle terabytes of data just fine and it wasn't really n…

Well, yes and no. Everybody knows that relational databases don't scale because they use JOINs and write to disk. Also, relational databases weren't built for web scale. MongoDB handles web scale. You turn it on and it scales right up. And before you knock shards, shards are the secret ingredient in the web scale sauce. They just work. Furthermore, relational databases have impetus mismatch, and Postgresql is slow as…

Are you being intentionally sarcastic? Because this reads a lot like http://www.mongodb-is-web-scale.com/

Edit: Whoops, just read your reply :)

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#40

Post by kev009 » Thu, Apr 03, 2014, 10:22 AM UTC

bcantrill wrote: post #37

kev009 wrote: post #32

Earlier quoted context omitted.

How do you mean? I trust OpenZFS more and in fact would consider it negligent if Oracle wasn't cherry picking OpenZFS bug fixes. Several original Sun engineers including ZFS co-founder Matt Ahrens are involved with OpenZFS.

Yes, as I have elaborated upon at length[1], the ZFS engineers have long-since left Oracle -- and the open community's ZFS (that is, OpenZFS) has become the ZFS of record. One clarification: Oracle can't actually cherry-pick back OpenZFS bug fixes because they are (ironically) violating the CDDL by not making available source code. This isn't an issue for the code for which they hold copyright -- but that doesn't inc…

Cheers Bryan, that's a great one of your talks and thanks for the interesting licensing comedy.