pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tadipathri Raghu <traghu.dba@gmail.com>
To: Pierre C <lists@peufeu.com>
Cc: Jaime Casanova <jcasanov@systemguards.com.ec>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why Wal_buffer is 64KB
Date: Fri, 26 Mar 2010 08:49:21 +0530
Message-ID: <645d9d71003252019u6f01a513x7876c1a97b448dea@mail.gmail.com> (raw)
In-Reply-To: <op.u94yaoixeorkce@localhost>
References: <645d9d71003250801o246439a0j93c7b568786e98de@mail.gmail.com>
	<3073cc9b1003251015x590e962ata59dadbb58b42ce5@mail.gmail.com>
	<op.u94yaoixeorkce@localhost>

Hi Pierre,

First of all , I Thank all for sharing the information on this Issue.

On Thu, Mar 25, 2010 at 11:44 PM, Pierre C <lists@peufeu.com> wrote:
>

> If you do large transactions, which emits large quantities of xlog, be
> aware that while the previous xlog segment is being fsynced, no new writes
> happen to the next segment. If you use large wal_buffers (more than 16 MB)
> these buffers can absorb xlog data while the previous segment is being
> fsynced, which allows a higher throughput. However, large wal_buffers also
> mean the COMMIT of small transactions might find lots of data in the buffers
> that noone has written/synced yet, which isn't good. If you use dedicated
> spindle(s) for the xlog, you can set the walwriter to be extremely
> aggressive (write every 5 ms for instance) and use fdatasync. This way, at
> almost every rotation of the disk, xlog gets written. I've found this
> configuration gives increased throughput, while not compromising latency,
> but you need to test it for yourself, it depends on your whole system.


Small testing is done from my end. I have created a "test" table with one
row and done insertion into it(10,00,000- rows). I have turned off fsync and
syncronous_commit. I saw there is fast insert if i do so, but if i turn it
on then there is latency.

Before fsync / syncronous_commit on
============================
postgres=# explain analyze insert into test
values(generate_series(1,1000000));
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.015..6293.674
rows=1000000 loops=1)
 Total runtime: *37406.012 ms*
(2 rows)


After fsync/syncronous_commit off
=========================
postgres=# explain analyze insert into test
values(generate_series(1,1000000));
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.154..5801.584
rows=1000000 loops=1)
 Total runtime: *29378.626 ms
*(2 rows)

I request to know here is,  what would be xlog files with wal_buffer. Does
xlog will recycle or grow in creating one more for this particular
transaction. Could you explain here, when wal_buffer is 64kb which is very
small, and everything is in xlog files written, so wt happens if we increase
the wal_buffer here?

Regards
Raghav

view thread (11+ messages)  latest in thread

Message-ID: <645d9d71003252019u6f01a513x7876c1a97b448dea@mail.gmail.com>
Permalink:  ../645d9d71003252019u6f01a513x7876c1a97b448dea@mail.gmail.com/
Also on:    postgresql.org/message-id/645d9d71003252019u6f01a513x7876c1a97b448dea@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: traghu.dba@gmail.com, lists@peufeu.com, jcasanov@systemguards.com.ec
  Subject: Re: Why Wal_buffer is 64KB
  In-Reply-To: <645d9d71003252019u6f01a513x7876c1a97b448dea@mail.gmail.com>
  References: <645d9d71003250801o246439a0j93c7b568786e98de@mail.gmail.com> <3073cc9b1003251015x590e962ata59dadbb58b42ce5@mail.gmail.com> <op.u94yaoixeorkce@localhost> <645d9d71003252019u6f01a513x7876c1a97b448dea@mail.gmail.com>

* 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