Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id EC0006327A2 for ; Fri, 26 Mar 2010 00:28:08 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 33341-01-5 for ; Fri, 26 Mar 2010 03:27:55 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com [209.85.212.46]) by mail.postgresql.org (Postfix) with ESMTP id CA4F2633AF8 for ; Fri, 26 Mar 2010 00:19:21 -0300 (ADT) Received: by vws8 with SMTP id 8so1235264vws.19 for ; Thu, 25 Mar 2010 20:19:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:received:message-id:subject:from:to:cc:content-type; bh=QS+959fOjOkQK+vtqFCOWkIVZ3SvS9R/chXkuWP35WA=; b=Tcf9n8P3QFDA2AW/BIcLw3xyzAhi8vN4bsFXKGGVNuevCQAK3+7h54QVcYN59jUxyx 6YWGe5QibSW+iY2BHe3UyoUTOfbvySh1ddgwA8FB1FeHknrUS/sLPAFlfu7bFESZ036q IssAokcFGPQv/vtDlDqP5udR6MjEkIrMbBMSo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=cGKVprrKbOt+88qSCzP/cCe06G6wiM0Y/Q+SdIogTKQyZX/zfpYBFfFnvR5ZATpaRD VU0WmOshBDzpNLwEVRDIN08SJ+JB5ob6UVe8W2A4SnEHiBniR9WjkftNUKjN3gnQ7hQv 7lvH2HvL3X6t7C9MI+rXZAVlYcLo8gvBGcFUE= MIME-Version: 1.0 Received: by 10.220.99.197 with HTTP; Thu, 25 Mar 2010 20:19:21 -0700 (PDT) In-Reply-To: References: <645d9d71003250801o246439a0j93c7b568786e98de@mail.gmail.com> <3073cc9b1003251015x590e962ata59dadbb58b42ce5@mail.gmail.com> Date: Fri, 26 Mar 2010 08:49:21 +0530 Received: by 10.220.58.69 with SMTP id f5mr177671vch.181.1269573561400; Thu, 25 Mar 2010 20:19:21 -0700 (PDT) Message-ID: <645d9d71003252019u6f01a513x7876c1a97b448dea@mail.gmail.com> Subject: Re: Why Wal_buffer is 64KB From: Tadipathri Raghu To: Pierre C Cc: Jaime Casanova , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=00235445bad624bcb30482aba1f7 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.762 tagged_above=-10 required=5 tests=AWL=0.836, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/331 X-Sequence-Number: 38156 --00235445bad624bcb30482aba1f7 Content-Type: text/plain; charset=ISO-8859-1 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 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 --00235445bad624bcb30482aba1f7 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi Pierre,
=A0
First of all , I Thank all for sharing the information on this Issue.<= br>
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 xl= og segment is being fsynced, no new writes happen to the next segment. If y= ou use large wal_buffers (more than 16 MB) these buffers can absorb xlog da= ta while the previous segment is being fsynced, which allows a higher throu= ghput. However, large wal_buffers also mean the COMMIT of small transaction= s 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 ca= n set the walwriter to be extremely aggressive (write every 5 ms for instan= ce) 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.
=A0
Small testing is done from my end. I have created a "test" t= able with one row and done insertion into it(10,00,000- rows). I have turne= d off fsync and syncronous_commit. I saw there is=A0fast insert if i do so,= but if i turn it on then there is latency.
=A0
Before fsync / syncronous_commit on
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D
postgres=3D# explain analyze insert into test values(generate_series(1= ,1000000));
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN
= ---------------------------------------------------------------------------= ------------------
=A0Result=A0 (cost=3D0.00..0.01 rows=3D1 width=3D0) (actual time=3D0.015..6= 293.674 rows=3D1000000 loops=3D1)
=A0Total runtime: 37406.012 ms=
(2 rows)

=A0
After fsync/syncronous_commit off
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D
postgres=3D# explain analyze insert into test values(generate_series(1= ,1000000));
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN
= ---------------------------------------------------------------------------= ------------------
=A0Result=A0 (cost=3D0.00..0.01 rows=3D1 width=3D0) (actual time=3D0.154..5= 801.584 rows=3D1000000 loops=3D1)
=A0Total runtime: 29378.626 ms=
(2 rows)
=A0
I request to know here is,=A0 what would be xlog files with wal_buffer= . Does xlog will recycle or grow in creating one more for this particular t= ransaction. Could you explain here, when wal_buffer is 64kb which is very s= mall, and everything is in xlog files written, so wt happens if we increase= the wal_buffer here?
=A0
Regards
Raghav
--00235445bad624bcb30482aba1f7--