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

Subtly Bad Things Linux May Be Doing To PostgreSQL

rhaas.blogspot.in

Post reply on HN
Print view View original

21–30 of 95 posts

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#21

Post by arethuza »

contingencies wrote: post #17
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…

Sure, if you are running stats across everything in a nontrivial and frequently changing way, then you have a great ally in an RDBMS. But I don't believe many people do that, because usually that sort of stuff is pretty damn predictable, executed offline, or can be consolidated from shards. However, if you have any of the following: (1) vastly different security requirements for different parts of your datastore (2)…

Apart from your point on "wish to scale by running on commodity hardware" I'd say that relational databases handle all of those other things pretty well - might cost you an arm and a leg for the licenses, hardware and network connections but those goals are achievable.

Anyway, in a lot of environments it's application's that drive choice of database engine - not the other way round.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#22

Post by ksec »

kev009 wrote: post #6
forkandwait wrote: post #2

Anybody know of a FreeBSD comparison?

+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.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#23

Post by [deleted] »

contingencies wrote: post #10

Not to detract from the very intelligent and reasoned posting, but what tiny percentage of people honestly still use fat-ass RDBMS as their primary datastore and would be better off performance tuning it at the kernel IO level than actually analyzing their load and subsequently sharding or migrating their data structures to less behemoth-like datastores? Yes , RDBMS are easy to hire developers and DBAs for, are well…

[deleted]

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#24

Post by kev009 »

contingencies wrote: post #17
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…

Sure, if you are running stats across everything in a nontrivial and frequently changing way, then you have a great ally in an RDBMS. But I don't believe many people do that, because usually that sort of stuff is pretty damn predictable, executed offline, or can be consolidated from shards. However, if you have any of the following: (1) vastly different security requirements for different parts of your datastore (2)…

I counter that many people have met each of your numbers for the past 20 years using commercial RDBMS.

I can't think of anything that is magnificently easier or better at solving your numbers, especially all together. #4 seems less relevant, is it really cheaper than operationalizing a distributed system? These days, likely for situations where consistency can be relaxed. Not so for many business workloads.

Can you enlighten us with some example products for your numbers?

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#25

Post by vog »

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.

Indeed, this should really go into the PostgreSQL docs, possibly even be raised on their mailing list to get it eventually included into the official docs, maybe somewhere around here:

http://www.postgresql.org/docs/9.3/static/runtime-config.htm...

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#26

Post by artumi-richard »

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?

You probably don't need to worry about it, it's just further proof that the postgresql devs do it right, and you're in safe hands.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#27

Post by tankenmate »

bifrost wrote: post #4
forkandwait wrote: post #2

Anybody know of a FreeBSD comparison?

Aside from the NUMA stuff, the disk buffer issue probably depends on if you're using ZFS or not.

And also whether the kernel is reporting the true block size of the device; xref advanced format drives (what the drive reports and what the drive does can be two different things), SSDs (minimum write size is typically much bigger than the reported block size), SANs. The true underlying block size can sometimes be determined by benchmarking the latency of different write sizes and also unaligned writes of different sizes.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#28

Post by davidw »

rimantas wrote: post #19
davidw wrote: post #13

Earlier quoted context omitted.

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…

Check this "web scale" out: http://smalldatum.blogspot.com (via http://dom.as/2014/03/31/mongo-io/ )

I was actually referring to this: http://www.mongodb-is-web-scale.com/ - which is what contingencies' comments reminded me of, but I guess people either didn't get it or thought it was a bit stale. C'est la vie.

I am a happy Postgres user and always default to it unless I am really sure a project calls for something else.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#29

Post by dgarrett »

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"

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#30

Post by sickpig »

kev009 wrote: post #6
forkandwait wrote: post #2

Anybody know of a FreeBSD comparison?

+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.

other useful resources on using PostgresSQL on a Linux server with a ZFS filesystem:

- http://adpgtech.blogspot.it/2013/04/linuxzfs-is-great.html

- http://wiki.postgresql.org/images/8/86/PostgreSQL_on_ZFS.pdf

- http://lanyrd.com/2013/linuxcon-north-america/scqmfb/

- http://www.postgresql.org/message-id/CAGa5y0NkVLTNGywmZ6YS8O...

Post reply on HN