3 ms·

>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 b

by 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/

by jasode 10y ago

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/