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: Andres Freund <andres(at)anarazel(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Greg Smith <greg(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org, Michael Clemmons <glassresistor(at)gmail(dot)com>, 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:05:39
Message-ID: 200912290305.39932.andres@anarazel.de (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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: Michael ClemmonsDate: 2009-12-29 02:53:12
Subject: Re: [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)
Previous:From: davidDate: 2009-12-29 00:46:26
Subject: Re: Re: Faster CREATE DATABASE by delaying fsync (was 8.4.1 ubuntu karmic slow createdb)

pgsql-hackers by date

Next:From: Tom LaneDate: 2009-12-29 02:30:04
Subject: Re: exec_execute_message crush
Previous:From: Tatsuo IshiiDate: 2009-12-29 01:52:30
Subject: Re: exec_execute_message crush

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