5 ms·

Not a Postgres expert, but a MS SQL one: performance of the storage in well configured servers, after the warm up, is the least concern for perf tuning. In quer

Not a Postgres expert, but a MS SQL one: performance of the storage in well configured servers, after the warm up, is the least concern for perf tuning. In query tuning physical reads are a flag for bad configuration, logical reads are the first and foremost indicator and a 20% increase in storage performance brings less than 1% in total server performance. This is because if your query is well written and covered with indexes, reads are performed in memory and large writes are very rare. Nonetheless, the article is interesting as a way to understand how Postgres and ZFS go together.

hardwaresofton 5y ago
Completely agreed -- the reason that ZFS actually warrants tuning here is because ZFS has the ARC -- basically reads/writes are going to go through the linux page cache AND this additional ARC component, and there's a bit more leverage there than a more traditional LVM-on-xfs/ext4 stack. Writes always have to touch disk of course to be durable so the underlying does matter. I'm not super familiar with MSSQL but does it have Direct IO? I have to admit I know almost nothing about the internals of MSSQL or the characteristics of filesystems on Windows. There's a wealth of complexity and tunables in just how ZFS will write to disk along with how it will use and optimize ARC. Basically there's a second WAL at the disk level (the ZIL/SLOG) to contend with so there are lots of ways to do things and properties of the storage system can change.
- AdrianB1 5y ago
  MS SQL IO is tightly integrated with Windows, including modes (like instant file initialization) that are not accessible to user mode apps. That makes it 'kind of Direct IO' on Windows, I am not familiar with the Linux port but I don't have any expectations there. MS SQL configuration and tuning is less and less about storage and more and more about server parameters (most defaults are coming from the SQL version that introduced it, ex. SQL 2000 almost 22 years ago), query and index tuning. The last 2 can make huge differences, many orders of magnitude, so this is where most time is spent optimizing. The server configuration part is usually a list of 10-20 parameters every DBA is changing from start almost never touch later. This makes it really easy on that part.

hardwaresofton 5y ago

Completely agreed -- the reason that ZFS actually warrants tuning here is because ZFS has the ARC -- basically reads/writes are going to go through the linux page cache AND this additional ARC component, and there's a bit more leverage there than a more traditional LVM-on-xfs/ext4 stack. Writes always have to touch disk of course to be durable so the underlying does matter. I'm not super familiar with MSSQL but does it have Direct IO? I have to admit I know almost nothing about the internals of MSSQL or the characteristics of filesystems on Windows. There's a wealth of complexity and tunables in just how ZFS will write to disk along with how it will use and optimize ARC. Basically there's a second WAL at the disk level (the ZIL/SLOG) to contend with so there are lots of ways to do things and properties of the storage system can change.

AdrianB1 5y ago
MS SQL IO is tightly integrated with Windows, including modes (like instant file initialization) that are not accessible to user mode apps. That makes it 'kind of Direct IO' on Windows, I am not familiar with the Linux port but I don't have any expectations there. MS SQL configuration and tuning is less and less about storage and more and more about server parameters (most defaults are coming from the SQL version that introduced it, ex. SQL 2000 almost 22 years ago), query and index tuning. The last 2 can make huge differences, many orders of magnitude, so this is where most time is spent optimizing. The server configuration part is usually a list of 10-20 parameters every DBA is changing from start almost never touch later. This makes it really easy on that part.