Re: [PATCHES] O_DIRECT for WAL writes

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Unfortunately, I cannot believe these numbers --- the near equality of
> fsync off and fsync on means there is something very wrong with the
> measurements. What I suspect is that your ATA drives are doing write
> caching and thus the "fsyncs" are not really waiting for I/O at all.

I think direct io and writeback-cache should be considered separate issues.
I guess that direct-io can make OSes not to cache WAL files and they will
use more memory to cache data files.

In my previous test, I had enabled writeback-cache of my drives
because of performance. But I understand that the cache should be
disabled for reliable writes from the discussion.
Also my checkpoint_segments setting might be too large against
the default. So I'll post the new results:

checkpoint_ | writeback |
segments | cache | open_sync | fsync=false | O_DIRECT only | fsync_direct | open_direct
------------+-----------+-----------+---------------+---------------+---------------+--------------
[1] 48 | on | 109.3 tps | 125.1(+ 11.4%)| 157.3(+44.0%) | 160.4(+46.8%) | 161.1(+47.5%)
[2] 3 | on | 102.5 tps | 136.3(+ 33.0%)| 117.6(+14.7%) | |
[3] 3 | off | 38.2 tps | 138.8(+263.5%)| 38.6(+ 1.2%) | 38.5(+ 0.9%) | 38.5(+ 0.9%)

- 30runs * pgbench -s 100 -c 10 -t 1000
- using 2 ATA disks:
- hda(reiserfs) includes system and wal. writeback-cache is on at [1][2] and off at [3].
- hdc(jfs) includes database files. writeback-cache is always on.

---
ITAGAKI Takahiro
NTT Cyber Space Laboratories