4 ms·

One thing I've always wondered about: It seems that Postgres is designed to use the operating system's capabilities in terms of disk caching, I/O scheduling, Mu

One thing I've always wondered about: It seems that Postgres is designed to use the operating system's capabilities in terms of disk caching, I/O scheduling, Multitasking etc as much as possible, whereas most other databases take the opposite approach and try to bypass the operating system as much as possible, sometimes even to the point of bypassing the file system and doing raw disk I/O. AFAIK something like Oracle is almost an operating system by itself. What lead to that design decision and what are the tradeoffs from it? Intuitively it seems like it'd be better to bypass the OS, but on the other hand Postgres does work incredibly well.

mangeletti 10y ago
That's all new to me, and I don't know the answer, but it seems like maybe some significant gains in developer (Postgres developers) productivity (not having to deal with such low level issues) could help to offset some of the lower level performance losses by giving developers more time to focus on higher order performance gains.
- dragonwriter 10y ago
  It also means that you get to free-ride on OS-level improvements. If you are bypassing the OS and doing all the low-level stuff yourself, you only get the improvements you invest directly in. OS vendors have to adapt their systems to changing hardware environments: if you rely on the OS, you get the benefit of that effort for free. If you bypass the OS, you've got to do it yourself.
raverbashing 10y ago
Yes, there was once a joke saying O_DIRECT would be renamed O_RACLE
jandrewrogers 10y 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 10y ago
  Thanks 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?
Spooky23 10y ago
It's a topic of DBA religious discussion. In the past, it was more clearly a benefit to use the direct IO method, as the DBMS could do stuff like be aware of disk geometry and optimize layout for IO and logging, and avoid the various gotchas of the file systems the DB ran on. Way back around 2000 I did a performance bake off of Informix with direct disk io vs the Solaris 2.7 (maybe 2.6) file system. For our workloads, performance was within 5% give or take. We opted to switch to file based storage as it saved us a bunch of money. There's no one answer -- and some technical explanations are mumbo jumbo concealing the real ask. Some DBAs will demand direct IO simply because of office politics. File systems "belong" to the storage team, but LUNs with Oracle "belong" to the DBAs.
deleted 10y ago
[deleted]

mangeletti 10y ago

That's all new to me, and I don't know the answer, but it seems like maybe some significant gains in developer (Postgres developers) productivity (not having to deal with such low level issues) could help to offset some of the lower level performance losses by giving developers more time to focus on higher order performance gains.

dragonwriter 10y ago
It also means that you get to free-ride on OS-level improvements. If you are bypassing the OS and doing all the low-level stuff yourself, you only get the improvements you invest directly in. OS vendors have to adapt their systems to changing hardware environments: if you rely on the OS, you get the benefit of that effort for free. If you bypass the OS, you've got to do it yourself.

raverbashing 10y ago

Yes, there was once a joke saying O_DIRECT would be renamed O_RACLE

jandrewrogers 10y 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 10y ago
Thanks 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?

Spooky23 10y ago

It's a topic of DBA religious discussion. In the past, it was more clearly a benefit to use the direct IO method, as the DBMS could do stuff like be aware of disk geometry and optimize layout for IO and logging, and avoid the various gotchas of the file systems the DB ran on. Way back around 2000 I did a performance bake off of Informix with direct disk io vs the Solaris 2.7 (maybe 2.6) file system. For our workloads, performance was within 5% give or take. We opted to switch to file based storage as it saved us a bunch of money. There's no one answer -- and some technical explanations are mumbo jumbo concealing the real ask. Some DBAs will demand direct IO simply because of office politics. File systems "belong" to the storage team, but LUNs with Oracle "belong" to the DBAs.

deleted 10y ago

[deleted]