13 ms·
Subtly Bad Things Linux May Be Doing To PostgreSQL
- forkandwait 13y agoAnybody know of a FreeBSD comparison?
- treenyc 13y agoFreeBSD has something like SHMMAXPGS, SHMMAX in postgresql.conf in the sysctl.conf kern.ipc.shmall kern.ipc.shmall
- kev009 13y agoI 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.
- treenyc 13y agothanks a lot really. I have being use Linux for a while now. Thanks for the update.
- DrJokepu 13y agoNot relevant since PostgreSQL 9.3 as PostgreSQL now uses Posix shared memory / mmap rather that System V.
- bifrost 13y agoAside from the NUMA stuff, the disk buffer issue probably depends on if you're using ZFS or not.
- tankenmate 13y agoAnd 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.
- kev009 13y ago+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 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 http://citusdata.com/blog/64-zfs-compression I run pgsql on FreeBSD/ZFS and have no complaints but am not taxing the system.
- ksec 13y agoI love ZFS, but Oracle is holding it up which is very annoying.
- kev009 13y agoHow 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.
- bcantrill 13y agoYes, 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 http://www.youtube.com/watch?v=-zRN7XLCRhc
- kev009 13y agoCheers Bryan, that's a great one of your talks and thanks for the interesting licensing comedy.
- ams6110 13y agoDragonfly is probably the best BSD to run for database performance. http://www.dragonflybsd.org/performance/ http://www.dragonflybsd.org/performance/
- profquail 13y agoThat chart is 18 months old. FreeBSD 10 got a number of SMP-related improvements (and some for ZFS as well, if I'm not mistaken). It'd be interesting to see an up-to-date comparison with FreeBSD 10 (and maybe 11-CURRENT), DragonFlyBSD 3.6, and NetBSD 6.1 (and maybe a couple of Linux distros for good measure).
- moe 13y agoI 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_Server https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serv...). The only place where it will ultimately be seen by a worthwhile audience.
- vog 13y agoIndeed, 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.html http://www.postgresql.org/docs/9.3/static/runtime-config.htm...
- dgarrett 13y ago> 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"
- tadfisher 13y agoThis isn't helping.
- viraptor 13y ago> infinitely nerdy stuff Since when is "in depth" and "technical" equivalent to "infinitely nerdy"? We're professionals using that kind of information for work, not nerds doing infinitely nerdy stuff.
- davidpardo 13y agoBeing 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?
- treenyc 13y agosame 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.
- pjc50 13y agoIf you're running in a VM, the VM may impose its own constraints on the disk write and virtual memory issues.
- dfox 13y agoFirst issue is relevant only on systems that have more than one NUMA node, which is probably every meaningful physical server and essentially no VM (at least on Xen, multiprocessor VMs are single NUMA node), as it does not make much sense to advertise NUMA topology to guest VMs. Second issue is relevant for postgresql mostly only if you use very large shared_buffers which anyway is not recommended for general workloads. Writing page that exists on disk and was not read short time before is not especially common thing to do.
- rodgerd 13y agoNUMA can absolutely ping you in virtual servers, but without access to the hypervisor you'll never know why it's happening (JVMs straddling NUMA regions have caused me pain in the past, when the guest was split across memory regions).
- dfox 13y agoThe point is that kernel inside VM guest knows nothing about NUMA, so it cannot do any kind of NUMA optimizations hence such optimizations cannot hurt performance as they do not happen at all.
- contingencies 13y agoNot 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.
- kev009 13y agoThat'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 noteworthy or a source of problems to even bring up considering something else. YMMV but it's a good idea to use logic and reason to dictate architecture instead of following the shiny thing or hubris.
- davidw 13y agoWell, 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 a dog. MongoDB will run circles around Postgresql because MongoDB is web scale.
- rimantas 13y agoCheck this "web scale" out: http://smalldatum.blogspot.com http://smalldatum.blogspot.com (via http://dom.as/2014/03/31/mongo-io/ http://dom.as/2014/03/31/mongo-io/ )
- atmosx 13y agoIf we had some metrics in a graph form, backing up the post would be great for us mere mortals to understand which way to go in order to achieve maximal performance.
- lallysingh 13y agoFor (1), you can use numactl to change the default behavior. For #s (2) and (3), I'm pretty sure this is FS dependent?
- rivert 13y agorelated post on lwn: https://lwn.net/Articles/591723/ https://lwn.net/Articles/591723/
- MrBuddyCasino 13y agoThis is slightly OT, but I always wondered: what is the overhead of fetching cached stuff from the file system vs. having a built-in cache? Is there a way to circumvent going through the kernel and avoid a context switch? In other words: could a database have only minimal built-in caching and instead rely on the OS cache?
- Negitivefrags 13y agoPostgres does rely on the OS cache. The "effective_cache_size" parameter is there for you to tell postgres how big you are expecting your OS cache to be and it is supposedly used when planning queries, presumably so it can rely on a plan having cached reads.
- jdubs 13y agoThe database has a better understanding of the workload and therefore can make more intelligent caching decisions. The file system caching system does a good job however, it's not as effective in predicting what should be cached.
- raverbashing 13y agoFor 1: I don't remember exactly which machines use NUMA, but I thought it was limited to 1st Opterons (and the behaviour makes sense) 2: Not sure, this may be specific to FS, or something that has to do with the behaviour of MMAPed files however I don't know how do you guarantee that what you're writing corresponds to a single block in the FS (unless you're writing directly to /dev/sda and even then)
- kev009 13y ago1 affects any multi-socket Intel system after Nehalem, and any multi-socket Opteron+ AMD system. So basically most physical servers.
- spaznode 13y agoI was under impression from reading another article that setting the correct kernel io scheduler to use helps with a related concept, maybe possibly: http://www.cybertec.at/postgresql-linux-kernel-io-tuning/ http://www.cybertec.at/postgresql-linux-kernel-io-tuning/
- jandrewrogers 13y agoIssues like the ones raised are why all sufficiently advanced database engine designs tend to evolve toward a kernel bypass architecture. From the perspective of a database engine, operating systems do a lot of "dumb" things with resource management and scheduling in ways that are essentially impossible to avoid that the database engine has enough context to do intelligently on its own. OS bypass in a database can have substantial performance benefits and add robustness for many edge cases. The obvious downside is that you basically end up rewriting the operating system in userspace, minus the device drivers, which is an enormous amount of work. It is not an incremental kind of design approach and it makes portability difficult. PostgreSQL actually does a really good job trying to be fast and robust without going to bypass internals. I've always asserted that the reason we've never seen a proper kernel bypass database engine in open source is that the Minimum Viable Product is too complex. A bare, stripped-down, low-level database engine that does full bypass of the operating system is usually at least 100kLoC of low-level C/C++, and that is before you add all the features a database user will actually want. That is a big initial investment by some people with fairly rare software design skills.
- dfox 13y agoThese two issues are actually perfect counterpoint to bypassing kernel caching and scheduling. There is no way to overcome first issue in userspace, second is trigered by what amounts to too agressive caching in userspace, and third is something that you should not be doing anyway (I'm not exactly sure if there is something better that kernel can do in that case, except doing write-thru caching on writes which has it's own performance implications for other users of O_DIRECT).
- jandrewrogers 13y agoI think you aren't understanding what a bypass kernel does. It literally takes control of the physical resources to the extent kernel interfaces exist that allow it to do so and, at least in the case of Linux, the level of control possible is quite high. That means taking control of the CPU, physical memory, disk I/O, network I/O, etc so that they can be scheduled and managed from userspace. All at the same time starting with bypass kernel initialization. For obvious reasons, it is usually a bad idea to run other non-trivial processes on the same machine because they will tend to be resource starved. Once you have these resources, you can organize them and use them as you see fit. Because it is not going to the kernel for any resources or buffering or scheduling or memory etc, there is little opportunity for the OS to do the wrong thing with resources that already are tightly controlled by the runtime. However, this is also why it is an "all or nothing" kind of situation.
- caf 13y agoPoint 2 is not actually the case, as long as your write does not partially fill a pagecache page (pagecache pages are the same size as the architecture's native page size - 4K on x86, x86-64 and arm). You can demonstrate this with a program like the following: int main(int argc, char *argv[]) { int i; char pattern[512*1024]; int fd; for (i = 0; i < sizeof pattern; i++) pattern[i] = 'X'; fd = open("testfile", O_RDWR | O_CREAT | O_EXCL, 0666); if (fd < 0) { perror("open"); return 1; } while (1) { pwrite(fd, pattern, sizeof pattern, 0); fdatasync(fd); posix_fadvise(fd, 0, sizeof pattern, POSIX_FADV_DONTNEED); } return 0; } ...then watch vmstat or iostat while it's running. Plenty of writes, no reads. On the other hand, if you subtract one from the size of 'pattern', you'll see that you also get reads (as partially writing the last page requires a read-modify-write cycle).