From: Andres Freund <andres@anarazel.de>
To: pgsql-hackers@postgresql.org
Cc: Greg Smith <greg@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org, Michael Clemmons <glassresistor@gmail.com>
Cc: Hannu Krosing <hannu@2ndquadrant.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Subject: Re: [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)
Date: Tue, 29 Dec 2009 03:05:39 +0100
Message-ID: <200912290305.39932.andres@anarazel.de> (raw)
In-Reply-To: <4B39515D.6050309@2ndquadrant.com>
References: <4427a97a0912101235x3ade510blcc00d5ce9883c6b6@mail.gmail.com>
<200912290020.35843.andres@anarazel.de>
<4B39515D.6050309@2ndquadrant.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: andres@anarazel.de, greg@2ndquadrant.com, tgl@sss.pgh.pa.us, glassresistor@gmail.com, hannu@2ndquadrant.com, scott.marlowe@gmail.com
Subject: Re: [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)
In-Reply-To: <200912290305.39932.andres@anarazel.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox