Re: [PERFORM] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb) - Mailing list pgsql-hackers

On Tuesday 29 December 2009 01:46:21 Greg Smith wrote:
> Andres Freund wrote:
> > As I said the real benefit only occurred after adding posix_fadvise(..,
> > FADV_DONTNEED) which is somewhat plausible, because i.e. the directory
> > entries don't need to get scheduled for every file and because the kernel
> > can reorder a whole directory nearly sequentially. Without the advice it
> > the kernel doesn't know in time that it should write that data back and
> > it wont do it for 5 seconds by default on linux or such...
> It would be interesting to graph the "Dirty" and "Writeback" figures in
> /proc/meminfo over time with and without this patch in place.  That
> should make it obvious what the kernel is doing differently in the two
> cases.
I did some analysis using blktrace (usefull tool btw) and the results show that
the io pattern is *significantly* different.

For one with the direct fsyncing nearly no hardware queuing is used and for
another nearly no requests are merged on software side.

Short stats:

OLD:

Total (8,0):
 Reads Queued:           2,        8KiB     Writes Queued:        7854,    29672KiB
 Read Dispatches:        2,        8KiB     Write Dispatches:     1926,    29672KiB
 Reads Requeued:         0         Writes Requeued:         0
 Reads Completed:        2,        8KiB     Writes Completed:     2362,    29672KiB
 Read Merges:            0,        0KiB     Write Merges:         5492,    21968KiB
 PC Reads Queued:        0,        0KiB     PC Writes Queued:        0,        0KiB
 PC Read Disp.:        436,        0KiB     PC Write Disp.:          0,        0KiB
 PC Reads Req.:          0         PC Writes Req.:          0
 PC Reads Compl.:        0         PC Writes Compl.:     2362
 IO unplugs:          2395             Timer unplugs:         557


New:

Total (8,0):
 Reads Queued:           0,        0KiB     Writes Queued:        1716,     5960KiB
 Read Dispatches:        0,        0KiB     Write Dispatches:      324,     5960KiB
 Reads Requeued:         0         Writes Requeued:         0
 Reads Completed:        0,        0KiB     Writes Completed:      550,     5960KiB
 Read Merges:            0,        0KiB     Write Merges:         1166,     4664KiB
 PC Reads Queued:        0,        0KiB     PC Writes Queued:        0,        0KiB
 PC Read Disp.:        226,        0KiB     PC Write Disp.:          0,        0KiB
 PC Reads Req.:          0         PC Writes Req.:          0
 PC Reads Compl.:        0         PC Writes Compl.:      550
 IO unplugs:           503             Timer unplugs:          30


Andres