pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy Wal_buffer is 64KB
11+ messages / 6 participants
[nested] [flat]
* Why Wal_buffer is 64KB
@ 2010-03-25 15:01 Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-25 16:05 ` Re: Why Wal_buffer is 64KB Brad Nicholson <bnichols@ca.afilias.info>
2010-03-25 17:15 ` Re: Why Wal_buffer is 64KB Jaime Casanova <jcasanov@systemguards.com.ec>
0 siblings, 2 replies; 11+ messages in thread
From: Tadipathri Raghu @ 2010-03-25 15:01 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why Wal_buffer is 64KB
2010-03-25 15:01 Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
@ 2010-03-25 16:05 ` Brad Nicholson <bnichols@ca.afilias.info>
1 sibling, 0 replies; 11+ messages in thread
From: Brad Nicholson @ 2010-03-25 16:05 UTC (permalink / raw)
To: Tadipathri Raghu <traghu.dba@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why Wal_buffer is 64KB
2010-03-25 15:01 Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
@ 2010-03-25 17:15 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-03-25 18:14 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
1 sibling, 1 reply; 11+ messages in thread
From: Jaime Casanova @ 2010-03-25 17:15 UTC (permalink / raw)
To: Tadipathri Raghu <traghu.dba@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why Wal_buffer is 64KB
2010-03-25 15:01 Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-25 17:15 ` Re: Why Wal_buffer is 64KB Jaime Casanova <jcasanov@systemguards.com.ec>
@ 2010-03-25 18:14 ` Pierre C <lists@peufeu.com>
2010-03-26 03:19 ` Re: Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Pierre C @ 2010-03-25 18:14 UTC (permalink / raw)
To: Jaime Casanova <jcasanov@systemguards.com.ec>; Tadipathri Raghu <traghu.dba@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why Wal_buffer is 64KB
2010-03-25 15:01 Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-25 17:15 ` Re: Why Wal_buffer is 64KB Jaime Casanova <jcasanov@systemguards.com.ec>
2010-03-25 18:14 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
@ 2010-03-26 03:19 ` Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-26 13:43 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tadipathri Raghu @ 2010-03-26 03:19 UTC (permalink / raw)
To: Pierre C <lists@peufeu.com>; +Cc: Jaime Casanova <jcasanov@systemguards.com.ec>; pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why Wal_buffer is 64KB
2010-03-25 15:01 Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-25 17:15 ` Re: Why Wal_buffer is 64KB Jaime Casanova <jcasanov@systemguards.com.ec>
2010-03-25 18:14 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
2010-03-26 03:19 ` Re: Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
@ 2010-03-26 13:43 ` Pierre C <lists@peufeu.com>
2010-03-26 14:00 ` Re: Why Wal_buffer is 64KB Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Pierre C @ 2010-03-26 13:43 UTC (permalink / raw)
To: Tadipathri Raghu <traghu.dba@gmail.com>; +Cc: Jaime Casanova <jcasanov@systemguards.com.ec>; pgsql-performance
> 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).
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why Wal_buffer is 64KB
2010-03-25 15:01 Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-25 17:15 ` Re: Why Wal_buffer is 64KB Jaime Casanova <jcasanov@systemguards.com.ec>
2010-03-25 18:14 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
2010-03-26 03:19 ` Re: Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-26 13:43 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
@ 2010-03-26 14:00 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-03-29 06:00 ` Re: Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2010-03-26 14:00 UTC (permalink / raw)
To: Pierre C <lists@peufeu.com>; +Cc: Tadipathri Raghu <traghu.dba@gmail.com>; Jaime Casanova <jcasanov@systemguards.com.ec>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why Wal_buffer is 64KB
2010-03-25 15:01 Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-25 17:15 ` Re: Why Wal_buffer is 64KB Jaime Casanova <jcasanov@systemguards.com.ec>
2010-03-25 18:14 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
2010-03-26 03:19 ` Re: Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-26 13:43 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
2010-03-26 14:00 ` Re: Why Wal_buffer is 64KB Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-03-29 06:00 ` Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-29 06:45 ` Re: Why Wal_buffer is 64KB Scott Marlowe <scott.marlowe@gmail.com>
2010-03-30 14:43 ` Re: Why Wal_buffer is 64KB Robert Haas <robertmhaas@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Tadipathri Raghu @ 2010-03-29 06:00 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Pierre C <lists@peufeu.com>; Jaime Casanova <jcasanov@systemguards.com.ec>; pgsql-performance
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:
> >
> >> 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.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why Wal_buffer is 64KB
2010-03-25 15:01 Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-25 17:15 ` Re: Why Wal_buffer is 64KB Jaime Casanova <jcasanov@systemguards.com.ec>
2010-03-25 18:14 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
2010-03-26 03:19 ` Re: Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-26 13:43 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
2010-03-26 14:00 ` Re: Why Wal_buffer is 64KB Scott Marlowe <scott.marlowe@gmail.com>
2010-03-29 06:00 ` Re: Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
@ 2010-03-29 06:45 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-03-29 07:05 ` Re: Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2010-03-29 06:45 UTC (permalink / raw)
To: Tadipathri Raghu <traghu.dba@gmail.com>; +Cc: Pierre C <lists@peufeu.com>; Jaime Casanova <jcasanov@systemguards.com.ec>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why Wal_buffer is 64KB
2010-03-25 15:01 Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-25 17:15 ` Re: Why Wal_buffer is 64KB Jaime Casanova <jcasanov@systemguards.com.ec>
2010-03-25 18:14 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
2010-03-26 03:19 ` Re: Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-26 13:43 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
2010-03-26 14:00 ` Re: Why Wal_buffer is 64KB Scott Marlowe <scott.marlowe@gmail.com>
2010-03-29 06:00 ` Re: Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-29 06:45 ` Re: Why Wal_buffer is 64KB Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-03-29 07:05 ` Tadipathri Raghu <traghu.dba@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Tadipathri Raghu @ 2010-03-29 07:05 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Pierre C <lists@peufeu.com>; Jaime Casanova <jcasanov@systemguards.com.ec>; pgsql-performance
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:
> > 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.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why Wal_buffer is 64KB
2010-03-25 15:01 Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-25 17:15 ` Re: Why Wal_buffer is 64KB Jaime Casanova <jcasanov@systemguards.com.ec>
2010-03-25 18:14 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
2010-03-26 03:19 ` Re: Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-26 13:43 ` Re: Why Wal_buffer is 64KB Pierre C <lists@peufeu.com>
2010-03-26 14:00 ` Re: Why Wal_buffer is 64KB Scott Marlowe <scott.marlowe@gmail.com>
2010-03-29 06:00 ` Re: Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
@ 2010-03-30 14:43 ` Robert Haas <robertmhaas@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Robert Haas @ 2010-03-30 14:43 UTC (permalink / raw)
To: Tadipathri Raghu <traghu.dba@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Pierre C <lists@peufeu.com>; Jaime Casanova <jcasanov@systemguards.com.ec>; pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2010-03-30 14:43 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-25 15:01 Why Wal_buffer is 64KB Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-25 16:05 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-03-25 17:15 ` Jaime Casanova <jcasanov@systemguards.com.ec>
2010-03-25 18:14 ` Pierre C <lists@peufeu.com>
2010-03-26 03:19 ` Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-26 13:43 ` Pierre C <lists@peufeu.com>
2010-03-26 14:00 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-03-29 06:00 ` Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-29 06:45 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-03-29 07:05 ` Tadipathri Raghu <traghu.dba@gmail.com>
2010-03-30 14:43 ` Robert Haas <robertmhaas@gmail.com>
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