Hacker News Reader

Comment by gtrubetskoy | Hacker News Reader

The article talks about Postgres mostly delegating caching to the OS while dealing with it via system calls (which they describe as "expensive"), while InnoDB has its own implementation and is therefore faster.

I wonder if part of the problem is really Linux, not Postgres and that another way they could have addressed this is by trying FreeBSD (which is what a lot of people run Postgres on).

View on HN

>I wonder if part of the problem is really Linux, not Postgres

I'm not a PostgreSQL expert but it seems like it has a different philosophy with OS filesystem buffering.

Using Oracle and MS SQL Server as counterexamples... you can configure Oracle to bypass os filesystem buffers by using "raw" disk io. With MS SQL Server, it opens datafiles with CreateFile(,,,FILE_FLAG_NO_BUFFERING) to bypass the Windows NTFS caching. (Microsoft recently ported SQL Server to Linux so not sure what combination of techniques they are doing on there since CreateFile(FILE_FLAG_NO_BUFFERING) is a Win32 api and not relevant to Linux.)

The closest approximation PostgreSQL has is the "shared_buffers" parameter but from the documentation[1] I've read, the best practice for that setting is 25% to 40% of main memory. In contrast, Oracle and MS SQL Server db engines with their self-managed buffer pools are designed to use almost 100% of the main memory.

Conclusion: PostgreSQL relies on the os filesystem cache and its own db buffer does not supersede the os buffers. Other db engines' buffer pools are designed to bypass the os filesystem cache.

Installing PostgreSQL on FreeBSD instead of Linux wouldn't drastically change the recommended range for "shared_buffers".

[1]https://www.postgresql.org/docs/9.1/static/runtime-config-re...

>Microsoft recently ported SQL Server to Linux so not sure what combination of techniques they are doing on there

Open options:

        int f = open("your file", O_DIRECT|O_DSYNC|O_RDWR);

O_DIRECT signals that a file descriptor should by-pass kernel level caching and write directly to the device.

O_SYNC signals that a file descriptor calls should not return until all data+metadata has been synced with disk.

O_DSYNC does the same as O_SYNC but doesn't force meta-data synchronicity before the block ends.

Related LWN article https://lwn.net/Articles/457667/

Ironically, in another "universe", StackOverflow doesn't have either part of this problem - they use the DB recommended by Microsoft and the OS recommended by Microsoft :)

http://nickcraver.com/blog/2016/02/17/stack-overflow-the-arc...

A userspace I/O cache is always going to be faster than the Linux one but that is not a problem with Linux per se. The userspace implementation not only has far more context about the workload with which to make decisions but the design of the underlying I/O cache itself can be perfectly tuned for the expected and desired behaviors a priori. The Linux buffer cache can't reasonably be designed for a single application. This has large performance effects.

By analogy, it is similar to how a purpose-designed hash map will almost always significantly outperform a generic hash map. There are enough design knobs that perfect foreknowledge of the use case allows significant optimization of the implementation.

I would say this is not just a case of faster, but more aware.

Linux does not have the same meta-data knowledge that the native cache has. There is a layering problem which makes it hard to add this. For example: after some page splits and fragmentation pages are no longer stored in physical order. How can a logical read ahead work on the Linux level?

The article mentions Postgres using many lseek and read calls. Does anyone know why Postgres doesn't use pread to cut the number of context switches in half (if, as the author says, the number of context switches is such a bottleneck)? On systems where pread doesn't exist, presumably they could use an inline function that emulates pread with lseek and read.

Yes, mmap would presumably be preferable in most situations, but pread would seem to be universally preferable to an lseek and read. I assume the kernel keeps per-fd statistics on sequential reads anyway, so for long-lived fds, lseek presumably wouldn't provide any better read-ahead hinting than pread.

The problem is fundamental to anything related to system calls. The only way to solve it is to somehow make system calls less expensive than they are now.

I believe that's what the parent comment was implying - that FreeBSD system calls are faster than Linux system calls.

I have no idea if this is true or not though.

Pretty much every modern DB bypass the OS for file caching, why would Postgres on FreeBSD behaves differently?

He is claiming FreeBSD provides a better environment than Linux not that PostgreSQL behaves differently.

Reply on news.ycombinator.com