On Thu, Feb 22, 2024 at 2:23 PM Siddharth Jain <siddh...@gmail.com> wrote: > I understand the storage layer in databases goes to great lengths to ensure: > - a row does not cross a block boundary > - read/writes/allocation happen in units of blocks > etc. The motivation is that at the OS level, it reads and writes pages > (blocks), not individual bytes. I am only concerned about SSDs but I think > the principle applies to HDD as well. > > but how can we do all this when we are not even guaranteed that the beginning > of a file will be aligned with a block boundary? refer this.
Interesting question. I was aware of FFS/UFS fragments 'cause I've poked at that code a bit (one of my learn-to-hack-kernels side projects was to add O_DSYNC support), but not some of those others mentioned. I don't think they are a problem for anything PostgreSQL does. Even with O_DIRECT, FFS (at least in its FreeBSD descendant) just quietly skips its raw fast path if the I/O is not *sector* aligned, but otherwise quietly falls back to the usual buffered path; actual interesting sized tables wouldn't use that feature anyway, so really it's just an internal edge case space optimisation that we don't have to worry about. Generally, that family of systems interprets O_DIRECT to mean "'*try* to bypass buffers" anyway, so there is no way for it to bite us. On the other hand, it does perform pretty badly if you use logical blocks that are too large: that's why in src/tools/ci/gcp_freebsd_repartition.sh I set up a new partition with 8KB blocks for CI testing (I originally did that in a branch doing a lot more with O_DIRECT, where the slow down due to block mismatch is worse). I think we just have to call that an administrator's problem to worry about. I have idly wondered before about some way to set a preferred 'grain' size on a tablespace so that, for example, a parallel seq scan over a very large read-only compressed ZFS/BTRFS/whatever file with 128KB blocks could try to align the allocation of block ranges with those boundaries in mind, just as a sort of performance hint. Or perhaps read it directly from st_blksize[1]. Combined with the I/O merging thing I'm working on right now which can build up single 128KB preadv() calls, that'd allow parallel workers to line those up neatly, and avoid accessing the same underlying block from two processes, which might have advantages in terms of ZFS's decompression threads and other kinds of contentions. I haven't looked into the potential benefits of that though, that is just speculation... For direct I/O, which we're still working on bringing to PostgreSQL in useful form, we had to do this: commit faeedbcefd40bfdf314e048c425b6d9208896d90 Author: Thomas Munro <tmu...@postgresql.org> Date: Sat Apr 8 10:38:09 2023 +1200 Introduce PG_IO_ALIGN_SIZE and align all I/O buffers. ... to avoid EINVAL errors, falling back to buffered mode or pathologically bad performance (depending on the system). It probably also helps buffered I/O performance for non-huge-pages a tiny bit (by touching the minimum number of memory pages). There is no industry standard to tell us what exactly is required for O_DIRECT, but we make sure addresses, offsets and lengths are all aligned to our macro PG_IO_ALIGN_SIZE, which is 4KB, because this is enough to satisfy all the systems we are aware of today (on some systems the true offset/length requirement is based on real or historical sectors, either 4KB or 512 bytes, which may be smaller than the filesystem block size, while on others it may be the filesystem block size which is usually some power-of-two multiple of those numbers). Since you can theoretically set PostgreSQL's data or WAL block size to less than that at compile time, we just don't let you turn direct I/O on in that case (perhaps we should, in case it does work, and just let it error out if it's going to? It's early days...). If the file system's block size is larger than that, you may have some issues, though, depending on the file system. Nobody standardised this stuff, and we certainly can't make any guarantees that anything we come up with will work on future systems, eg if someone invents 8KB sectors maybe it will need some work. That's OK, because direct I/O will likely always be a non-default optional mode, and many systems don't support it at all today. > Further, I don't see any APIs exposing I/O operations in terms of blocks. All > File I/O APIs I see expose a file as a randomly accessible contiguous byte > buffer. Would it not have been easier if there were APIs that exposed I/O > operations in terms of blocks? There are/were operating systems with block or record oriented I/O (eg IBM ones). Unix was about minimalism and simplicity and explicitly rejected that idea (first bullet point in [2]). Buffered, byte-oriented, synchronous I/O suited most applications. But yeah, not so much databases. Dr Stonebraker, who started this project, wrote a bunch of papers and articles about the mismatch between Unix file systems and database needs (back in those days, there wasn't even an fsync(), and the system buffer cache was basically the enemy). [1] https://pubs.opengroup.org/onlinepubs/009696699/basedefs/sys/stat.h.html [2] https://www.bell-labs.com/usr/dmr/www/retro.pdf