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

Re: [HACKERS] Full page writes improvement, code update

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Koichi Suzuki <suzuki(dot)koichi(at)oss(dot)ntt(dot)co(dot)jp>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, josh(at)agliodbs(dot)com, pgsql-hackers(at)postgresql(dot)org, pgsql-patches(at)postgresql(dot)org
Subject: Re: [HACKERS] Full page writes improvement, code update
Date: 2007-04-10 17:28:51
Message-ID: 27693.1176226131@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Koichi Suzuki <suzuki(dot)koichi(at)oss(dot)ntt(dot)co(dot)jp> writes:
> My proposal is to remove unnecessary full page writes (they are needed 
> in crash recovery from inconsistent or partial writes) when we copy WAL 
> to archive log and rebuilt them as a dummy when we restore from archive 
> log.
> ...
> Benchmark: DBT-2
> Database size: 120WH (12.3GB)
> Total WAL size: 4.2GB (after 60min. run)
> Elapsed time:
>    cp:            120.6sec
>    gzip:          590.0sec
>    pg_compresslog: 79.4sec
> Resultant archive log size:
>    cp:             4.2GB
>    gzip:           2.2GB
>    pg_compresslog: 0.3GB
> Resource consumption:
>    cp:   user:   0.5sec system: 15.8sec idle:  16.9sec I/O wait: 87.7sec
>    gzip: user: 286.2sec system:  8.6sec idle: 260.5sec I/O wait: 36.0sec
>    pg_compresslog:
>          user:   7.9sec system:  5.5sec idle:  37.8sec I/O wait: 28.4sec

What checkpoint settings were used to make this comparison?  I'm
wondering whether much of the same benefit can't be bought at zero cost
by increasing the checkpoint interval, because that translates directly
to a reduction in the number of full-page images inserted into WAL.

Also, how much was the database run itself slowed down by the increased
volume of WAL (due to duplicated information)?  It seems rather
pointless to me to measure only the archiving effort without any
consideration for the impact on the database server proper.

			regards, tom lane

PS: there's something fishy about the gzip numbers ... why all the idle
time?

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-04-10 17:36:20
Subject: Re: [PATCHES] Fix mdsync never-ending loop problem
Previous:From: Tom LaneDate: 2007-04-10 17:07:29
Subject: Re: [DOCS] uuid type not documented

pgsql-patches by date

Next:From: Tom LaneDate: 2007-04-10 17:36:20
Subject: Re: [PATCHES] Fix mdsync never-ending loop problem
Previous:From: Peter EisentrautDate: 2007-04-10 15:19:39
Subject: Re: xpath_array with namespaces support

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