Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

From: Michael Clemmons <glassresistor(at)gmail(dot)com>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: pgsql-hackers(at)postgresql(dot)org, Greg Smith <greg(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org, Hannu Krosing <hannu(at)2ndquadrant(dot)com>, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Subject: Re: [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)
Date: 2009-12-29 02:53:12
Message-ID: 4427a97a0912281853g36e7132al67a73927ae4068c0@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
Andres,
Great job.  Looking through the emails and thinking about why this works I
think this patch should significantly speedup 8.4 on most any file
system(obviously some more than others) unless the system has significantly
reduced memory or a slow single core. On a Celeron with 256 memory I suspect
it'll crash out or just hit the swap  and be a worse bottleneck.  Anyone
have something like this to test on?
-Michael

On Mon, Dec 28, 2009 at 9:05 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:

> 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
>

In response to

Responses

pgsql-performance by date

Next:From: Andres FreundDate: 2009-12-29 02:55:37
Subject: Re: [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)
Previous:From: Andres FreundDate: 2009-12-29 02:05:39
Subject: Re: [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

pgsql-hackers by date

Next:From: Andres FreundDate: 2009-12-29 02:55:37
Subject: Re: [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)
Previous:From: Tatsuo IshiiDate: 2009-12-29 02:35:52
Subject: Re: exec_execute_message crush

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group