Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3F42E63377A; Mon, 28 Dec 2009 22:06:05 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 04908-02-3; Tue, 29 Dec 2009 02:05:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.anarazel.de (mail.anarazel.de [217.115.131.40]) by mail.postgresql.org (Postfix) with ESMTP id CF5F8634175; Mon, 28 Dec 2009 22:05:42 -0400 (AST) Received: from alap2.localnet (unknown [81.163.62.229]) by mail.anarazel.de (Postfix) with ESMTPSA id 2DC11448003; Tue, 29 Dec 2009 03:05:40 +0100 (CET) From: Andres Freund To: pgsql-hackers@postgresql.org 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 User-Agent: KMail/1.13.0 (Linux/2.6.33-rc2-andres-00001-g6e0f169; KDE/4.3.85; x86_64; ; ) Cc: Greg Smith , Tom Lane , pgsql-performance@postgresql.org, Michael Clemmons , Hannu Krosing , Scott Marlowe References: <4427a97a0912101235x3ade510blcc00d5ce9883c6b6@mail.gmail.com> <200912290020.35843.andres@anarazel.de> <4B39515D.6050309@2ndquadrant.com> In-Reply-To: <4B39515D.6050309@2ndquadrant.com> MIME-Version: 1.0 Content-Type: Text/Plain; charset="iso-8859-15" Content-Transfer-Encoding: 7bit Message-Id: <200912290305.39932.andres@anarazel.de> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=AWL=0.000, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200912/257 X-Sequence-Number: 36871 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