Re: [PATCHES] O_DIRECT for WAL writes

Tom Lane <tgl@sss.pgh.pa.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