Thread: Why Wal_buffer is 64KB
Hi All,
Can anybody clarify on this, why wal_buffer is 64kb and what is advantages and disadvantages in increasing or decreasing the wal_buffer.
Regards
Raghav
On Thu, 2010-03-25 at 20:31 +0530, Tadipathri Raghu wrote: > Hi All, > > Can anybody clarify on this, why wal_buffer is 64kb and what is > advantages and disadvantages in increasing or decreasing the > wal_buffer. This is addressed in the documentation. http://www.postgresql.org/docs/8.4/interactive/wal-configuration.html -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.
On Thu, Mar 25, 2010 at 11:01 AM, Tadipathri Raghu <traghu.dba@gmail.com> wrote: > Hi All, > > Can anybody clarify on this, why wal_buffer is 64kb and what is advantages > and disadvantages in increasing or decreasing the wal_buffer. > is 64kb just because by default we have low values in almost everything :) and the advantages is that if your average transaction is more than 64kb large all wal data will be in memory until commit, actually i thing it should be large enough to accomodate more than one transaction but i'm not sure about that one... i usually use 1Mb for OLTP systems -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
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.
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)
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)
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
> After fsync/syncronous_commit off Do not use fsync off, it is not safe. Who cares about the performance of fsync=off, when in practice you'd never use it with real data. synchronnous_commit=off is fine for some applications, though. More info is needed about your configuration (hardware, drives, memory, etc).
On Fri, Mar 26, 2010 at 7:43 AM, Pierre C <lists@peufeu.com> wrote: > >> After fsync/syncronous_commit off > > Do not use fsync off, it is not safe. Who cares about the performance of > fsync=off, when in practice you'd never use it with real data. > synchronnous_commit=off is fine for some applications, though. There are situations where it's ok, when all the data are reproduceable from other sources, etc. for instance I have a reporting server that is a slony slave that runs with fsync off. If it does crash and I can recreate the node in an hour or so and be back online. With fsync off the machine is too slow to do its job, and it's not the primary repo of the real data, so it's ok there.
Hi All,
Thank you for all the support.
I have noticed one more thing here, that if you turn off the fsync and try to run the transaction than its breaking the currnet filenode and generating another filenode. Is it true that whenever you turn off or on the fsync the filenode will break and create one more on that table.
Regards
Raghavendra
On Fri, Mar 26, 2010 at 7:30 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Fri, Mar 26, 2010 at 7:43 AM, Pierre C <lists@peufeu.com> wrote:There are situations where it's ok, when all the data are
>
>> After fsync/syncronous_commit off
>
> Do not use fsync off, it is not safe. Who cares about the performance of
> fsync=off, when in practice you'd never use it with real data.
> synchronnous_commit=off is fine for some applications, though.
reproduceable from other sources, etc. for instance I have a
reporting server that is a slony slave that runs with fsync off. If
it does crash and I can recreate the node in an hour or so and be back
online. With fsync off the machine is too slow to do its job, and
it's not the primary repo of the real data, so it's ok there.
On Mon, Mar 29, 2010 at 12:00 AM, Tadipathri Raghu <traghu.dba@gmail.com> wrote: > Hi All, > > Thank you for all the support. > > I have noticed one more thing here, that if you turn off the fsync and try > to run the transaction than its breaking the currnet filenode and generating > another filenode. Is it true that whenever you turn off or on the fsync the > filenode will break and create one more on that table. From what I understand, with fsync on or off the same stuff gets written. It's just not guaranteed to go out in the right order or right now, but eventually.
Hi Scott,
Yes, May i know any particular reason for behaving this. Are its looking for any consistency. I havnt got any clear picture here.
Could you Please explain this..
Thanks & Regards
Raghavendra
On Mon, Mar 29, 2010 at 12:15 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Mon, Mar 29, 2010 at 12:00 AM, Tadipathri Raghu <traghu.dba@gmail.com> wrote:From what I understand, with fsync on or off the same stuff gets
> Hi All,
>
> Thank you for all the support.
>
> I have noticed one more thing here, that if you turn off the fsync and try
> to run the transaction than its breaking the currnet filenode and generating
> another filenode. Is it true that whenever you turn off or on the fsync the
> filenode will break and create one more on that table.
written. It's just not guaranteed to go out in the right order or
right now, but eventually.
On Mon, Mar 29, 2010 at 2:00 AM, Tadipathri Raghu <traghu.dba@gmail.com> wrote: > I have noticed one more thing here, that if you turn off the fsync and try > to run the transaction than its breaking the currnet filenode and generating > another filenode. Is it true that whenever you turn off or on the fsync the > filenode will break and create one more on that table. I don't know what you mean by a filenode. Changing the fsync parameter doesn't cause any additional files to be created or written. ...Robert