5 ms·

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 h

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

jasode 10y ago
>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-resource.html https://www.postgresql.org/docs/9.1/static/runtime-config-re...
- valarauca1 10y ago
  >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 Stack Overflow https://stackoverflow.com/questions/5055859/how-are-the-o-sync-and-o-direct-flags-in-open2-different-alike https://stackoverflow.com/questions/5055859/how-are-the-o-sy... Related LWN article https://lwn.net/Articles/457667/ https://lwn.net/Articles/457667/
Thaxll 10y ago
Pretty much every modern DB bypass the OS for file caching, why would Postgres on FreeBSD behaves differently?
- stonemetal 10y ago
  He is claiming FreeBSD provides a better environment than Linux not that PostgreSQL behaves differently.
FooBarWidget 10y ago
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.
- Mister_Snuggles 10y ago
  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.
morgo 10y ago
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?
paulftw 10y ago
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-architecture-2016-edition/ http://nickcraver.com/blog/2016/02/17/stack-overflow-the-arc...
jandrewrogers 10y ago
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.
KMag 10y ago
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.

jasode 10y ago

>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-resource.html https://www.postgresql.org/docs/9.1/static/runtime-config-re...

valarauca1 10y ago
>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 Stack Overflow https://stackoverflow.com/questions/5055859/how-are-the-o-sync-and-o-direct-flags-in-open2-different-alike https://stackoverflow.com/questions/5055859/how-are-the-o-sy... Related LWN article https://lwn.net/Articles/457667/ https://lwn.net/Articles/457667/

Thaxll 10y ago

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

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

FooBarWidget 10y ago

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.

Mister_Snuggles 10y ago
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.

morgo 10y ago

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?

paulftw 10y ago

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-architecture-2016-edition/ http://nickcraver.com/blog/2016/02/17/stack-overflow-the-arc...

jandrewrogers 10y ago

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.

KMag 10y ago

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.