2 ms·
Generally speaking, bypassing the operating system I/O scheduler and cache will substantially increase the effective throughput of the database kernel. The reas
by jandrewrogers 11y ago
Generally speaking, bypassing the operating system I/O scheduler and cache will substantially increase the effective throughput of the database kernel. The reasons why are somewhat complex and technical (e.g. it is prerequisite to implementing a good approximation of Bélády's optimal algorithm) but this fact is not controversial. The reason for doing your own task scheduling has similar theoretical foundations in the abstract though it manifests differently.
The tradeoff is that it greatly increases the complexity of maintaining portability and consistency because every operating system and file system behaves differently and offers different guarantees at this level of abstraction. PostgreSQL makes a priority of being highly portable, and has therefore chosen to avoid this particular code swamp and testing nightmare altogether thus far even though it comes at a significant cost to peak performance potential.
PostgreSQL is quite efficient and well designed for the design constraints they currently operate within but their primary value and philosophy is not peak performance per se.
- jordanthoms 11y agoThanks for that - the tradeoff between performance and complexity in this case is clearer now. It's interesting how many different approaches there are when stepping back and using some of the OS - for example where does MySQL/InnoDB fit in this? It seems they are intending you to use their buffer pool rather than the OS's disk cache (as they advise to make the buffer pool take large percentage of RAM), as opposed to Postgres where they recommend having shared_buffers as ~20% of RAM and leaving the rest for the OS to use. Is InnoDB doing something different here or is it just because they have a better cache implementation?