Comment by chasil | Hacker News Reader

Comment by chasil | Hacker News Reader

Parent Full thread

If you look at database benchmarks on TPC.org, you will find that many of them are on XFS (as a consequence of using RHEL/CentOS), which was designed with database performance in mind.

It used to be necessary for Oracle to use raw partitions to get the very best database performance. I haven't heard of anyone doing that for many years.

"The default implementation in most kernels is simply a file-global lock placed at the in-memory inode, making sure there can be only one writer per inode. Implementers of databases hate that because it limits the write concurrency on any single file to One. This is, for example, why Oracle recommends that you make tablespaces from multiple files, each no larger than one GB.

"XFS, in O_DIRECT mode, removes this lock and allows atomic, concurrent writes, making database people very happy."

Developers at Oracle have posted a number of blog posts on their code changes for Linux XFS.

https://blogs.oracle.com/linux/post/introduction-to-xfs-tran...

https://blogs.oracle.com/linux/post/xfs-online-filesystem-ch...

https://blogs.oracle.com/linux/post/xfs-2019-development-ret...

https://blogs.oracle.com/linux/post/xfs-whats-new-and-whats-...

There can be a strong bias against Oracle at HN (and I understand why) but credit must be given where it's due.

Note that Oracle still uses "raw" block devices for high-performance configurations. It's called Oracle Automatic Storage Management (ASM), basically you give it a bunch of block devices without any partitions or file systems on them and ASM does its own "extent" placement, striping, mirroring, snapshots, etc. And Oracle's Exadata scale-out appliance also uses block devices without any filesystems directly, the DB nodes talk to any storage nodes over InfiniBand/RDMA (older hardware) or RDMA over Ethernet/RoCE.

ZFS is also from Oracle, right? Seems like a dominating force in the FS industry if one can call it a such.

I mean, in that it came out of Sun, kind of I guess.

Btrfs is the filesystem for which they are most responsible.

They explicitly do not support its use for their database.

"[Btrfs] was initially designed at Oracle Corporation in 2007 for use in Linux, and since November 2013, the file system's on-disk format has been declared stable in the Linux kernel."

https://en.m.wikipedia.org/wiki/Btrfs

They own the rights to ZFS by way of their purchase of Sun Microsystems.

Oracle is a power player in the realm of Linux filesystems, no doubt.

Linux uses OpenZFS, which is a fork of OpenSolaris' ZFS; they haven't shared any code since 2010 AFAIK.

ZFS is Sun.

BTTFS is Oracle.

And neither have anything to do with a database, IMHO.

Oracle DBAs used to recommend using raw block devices to back the dB, a long long time ago.

I never did get one to explain why. I suspect it was received wisdom depending on VFS models which cached disk io and an effect on the DB engine. Almost certainly overtaken by both disk controller logic, file systems cache, striping and RAID and increasing memory sizes.

There are good pages on the Web discussing optimising ZFS for postgres tuned to write disk block aligned data through the zfs L2ARC

> Almost certainly overtaken by both disk controller logic, file systems cache, striping and RAID and increasing memory sizes.

In practice this might be true if the defaults settings of each layer happen to align sympathetically and/or you know what you’re doing and how to tune them all, but from first principles it stands to reason that the extra abstraction layer of a file system isn’t providing the database storage engine with anything useful?

Same argument for unikernels, distroless containers, LiquidVM (https://docs.oracle.com/cd/E13223_01/wls-ve/docs92-v11/confi...) etc…

Yes. It's still a valid proposition that raw disk is fastest if you map cleanly to the block abstraction the integrated controller offers, if only because of avoidance of indirect call stacks to carry data into the device.

But that said: the DBA in question constructed a catenated drive, not a stripe, and were slightly dismayed when we found out it was working at the speed of one disk interface and using only the front pack, not all of them in some kind of parallel or interleaved manner. Not a very smart DBA it turned out.

The reason for using raw/block devices back then (or Veritas VxFS that created additional raw devices to access VxFS files in a manner that bypassed regular OS filesystem I/O codepath & restrictions for reads & writes) was concurrent I/O.

In a busy database there are lots of processes reading a datafile and multiple processes writing (like DBWR checkpointing dirty blocks from buffer cache, but also some others), so this caused unnecessary file level contention in the OS kernel. Note that Oracle has I/O concurrency control built in, you can't have two processes accidentally writing the same block to the storage at the same time or a reader reading a "torn block" due to an ongoing write (the serialization happens at buffer lock/pin level in Oracle code).

So you wanted to have concurrent I/O and you either had to use raw devices or Veritas VxFS back then. Later on concurrent I/O became available for some other filesystems too, like Solaris UFS having the "cio" mount option, etc.

Reply on news.ycombinator.com