Subtly Bad Things Linux May Be Doing To PostgreSQL

#1

Post by r4um » Thu, Apr 03, 2014, 4:46 AM UTC

Subtly Bad Things Linux May Be Doing To PostgreSQL
rhaas.blogspot.in

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#2

Post by forkandwait » Thu, Apr 03, 2014, 6:48 AM UTC

Anybody know of a FreeBSD comparison?

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#3

Post by moe » Thu, Apr 03, 2014, 7:09 AM UTC

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.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#4

Post by bifrost » Thu, Apr 03, 2014, 7:22 AM UTC

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.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#5

Post by davidpardo » Thu, Apr 03, 2014, 7:39 AM UTC

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?

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#6

Post by kev009 » Thu, Apr 03, 2014, 7:53 AM UTC

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.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#7

Post by treenyc » Thu, Apr 03, 2014, 7:57 AM UTC

forkandwait wrote: post #2

Anybody know of a FreeBSD comparison?

FreeBSD has something like

SHMMAXPGS, SHMMAX in postgresql.conf

in the

sysctl.conf kern.ipc.shmall kern.ipc.shmall

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#8

Post by treenyc » Thu, Apr 03, 2014, 7:58 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?

same here. Was a FreeBSD user, but find hard to find VM a few years ago that support FreeBSD, may switch back, if this issue is NOT addressed.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#9

Post by kev009 » Thu, Apr 03, 2014, 8:08 AM UTC

treenyc wrote: post #7

forkandwait wrote: post #2

Anybody know of a FreeBSD comparison?

FreeBSD has something like SHMMAXPGS, SHMMAX in postgresql.conf in the sysctl.conf kern.ipc.shmall kern.ipc.shmall

I don't think this has any direct influence on performance.

It was necessary to increase the default limits for larger shared_buffers, etc on pgsql prior to 9.3 where SysV shared memory was used, but this was common to many *nix operating systems.

Re: Subtly Bad Things Linux May Be Doing To PostgreSQL

#10

Post by contingencies » Thu, Apr 03, 2014, 8:12 AM UTC

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 supported and full-featured. However, in this day and age using them just feels a little ... lazy ... for most workloads.