pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwal_sync_methods for AIX
7+ messages / 5 participants
[nested] [flat]
* wal_sync_methods for AIX
@ 2008-02-15 21:55 Dan Langille <dan@langille.org>
0 siblings, 2 replies; 7+ messages in thread
From: Dan Langille @ 2008-02-15 21:55 UTC (permalink / raw)
To: pgsql-performance
We're using PostgreSQL 8.1.11 on AIX 5.3 and we've been doing some
playing around
with various settings. So far, we've (I say we, but it's another guy
doing the work) found
that open_datasync seems better than fsync. By how much, we have not
yet determined,
but initial observations are promising.
Our tests have been on a p550 connected to DS6800 array using pgbench.
One nasty behaviour we have seen is long running commits. Initial
thoughts connected
them with checkpoints, but the long running commits do not correlate
with checkpoints being
written. Have you seen this behaviour?
FYI, 8.3.0 is not an option for us in the short term.
What have you been using on AIX and why?
thanks
--
Dan Langille -- http://www.langille.org/
dan@langille.org
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: wal_sync_methods for AIX
@ 2008-02-15 22:04 Joshua D. Drake <jd@commandprompt.com>
parent: Dan Langille <dan@langille.org>
1 sibling, 1 reply; 7+ messages in thread
From: Joshua D. Drake @ 2008-02-15 22:04 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Fri, 15 Feb 2008 16:55:45 -0500
Dan Langille <dan@langille.org> wrote:
> Our tests have been on a p550 connected to DS6800 array using pgbench.
>
> One nasty behaviour we have seen is long running commits. Initial
> thoughts connected
> them with checkpoints, but the long running commits do not correlate
> with checkpoints being
> written. Have you seen this behaviour?
Are you sure? What makes you think this? Do you have a high level of
shared buffers? What are your bgwriter settings?
Joshua D. Drake
- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHtgyFATb/zqfZUUQRAv2LAJ41l25YG7PwfgpZtuPD/1aL5I4ZTwCfRGii
LkFFefSDT72qGzY8PxOMXKE=
=0iC3
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: wal_sync_methods for AIX
@ 2008-02-15 22:20 Erik Jones <erik@myemma.com>
parent: Dan Langille <dan@langille.org>
1 sibling, 1 reply; 7+ messages in thread
From: Erik Jones @ 2008-02-15 22:20 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-performance
On Feb 15, 2008, at 3:55 PM, Dan Langille wrote:
> We're using PostgreSQL 8.1.11 on AIX 5.3 and we've been doing some
> playing around
> with various settings. So far, we've (I say we, but it's another
> guy doing the work) found
> that open_datasync seems better than fsync. By how much, we have
> not yet determined,
> but initial observations are promising.
Here's a good explanation (by the Greg Smith) on the different sync
methods. It basically says that if you have open_datasync available,
it'll probably beat everything else.
> Our tests have been on a p550 connected to DS6800 array using pgbench.
>
> One nasty behaviour we have seen is long running commits. Initial
> thoughts connected
> them with checkpoints, but the long running commits do not
> correlate with checkpoints being
> written. Have you seen this behaviour?
>
> FYI, 8.3.0 is not an option for us in the short term.
>
> What have you been using on AIX and why?
I really don't know anything about AIX, but are you sure that these
long running commits are directly correlated with using open_datasync?
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fwd: wal_sync_methods for AIX
@ 2008-02-19 20:21 JP Fletcher <jpfletch@ca.afilias.info>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 7+ messages in thread
From: JP Fletcher @ 2008-02-19 20:21 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-performance
Dan Langille wrote:
>
>
> Begin forwarded message:
>
>> From: "Joshua D. Drake" <jd@commandprompt.com>
>> Date: February 15, 2008 5:04:53 PM EST
>> To: Dan Langille <dan@langille.org>
>> Cc: pgsql-performance@postgresql.org
>> Subject: Re: [PERFORM] wal_sync_methods for AIX
>>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> On Fri, 15 Feb 2008 16:55:45 -0500
>> Dan Langille <dan@langille.org> wrote:
>>
>>> Our tests have been on a p550 connected to DS6800 array using pgbench.
>>>
>>> One nasty behaviour we have seen is long running commits. Initial
>>> thoughts connected
>>> them with checkpoints, but the long running commits do not correlate
>>> with checkpoints being
>>> written. Have you seen this behaviour?
>>
>> Are you sure? What makes you think this? Do you have a high level of
>> shared buffers? What are your bgwriter settings?
I've set checkpoint_warning to 300, knowing that my testcase will
definitely cause checkpoints inside this window, and generate log
messages. I see long running INSERT and END transactions sprinkled
evenly throughout the duration of the test, not specifically around the
time of the checkpoint messages.
Shared buffers are set to 50000, bgwriter settings are as follows:
# - Background writer -
bgwriter_delay = 50 # 10-10000 milliseconds between
rounds
bgwriter_lru_percent = 20.0 # 0-100% of LRU buffers
scanned/round
bgwriter_lru_maxpages = 300 # 0-1000 buffers max written/round
bgwriter_all_percent = 5 # 0-100% of all buffers
scanned/round
bgwriter_all_maxpages = 600 # 0-1000 buffers max written/round
The testcase is a simple pgbench test, with 100 clients, 10000
transactions. I modified the pgbench db to increase the number of
writes by adding 3 history_archive tables, populated by rules. I am not
assuming that changing the wal_sync_method will eliminate the long
running transactions but repeating the testcase with open_datasync (vs
fsync) resulted in fewer long running END transactions (by a large margin)
>>
>> Joshua D. Drake
>>
>>
>>
>>
>> - --
>> The PostgreSQL Company since 1997: http://www.commandprompt.com/
>> PostgreSQL Community Conference: http://www.postgresqlconference.org/
>> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
>> PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit
>>
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.4.6 (GNU/Linux)
>>
>> iD8DBQFHtgyFATb/zqfZUUQRAv2LAJ41l25YG7PwfgpZtuPD/1aL5I4ZTwCfRGii
>> LkFFefSDT72qGzY8PxOMXKE=
>> =0iC3
>> -----END PGP SIGNATURE-----
>
>
--
JP Fletcher
Database Administrator
Afilias Canada
voice: 416.646.3304 ext. 4123
fax: 416.646.3305
mobile: 416.561.4763
jpfletch@ca.afilias.info
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: wal_sync_methods for AIX
@ 2008-02-19 21:58 Dan Langille <dan@langille.org>
parent: Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dan Langille @ 2008-02-19 21:58 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-performance
Erik Jones wrote:
>
> On Feb 15, 2008, at 3:55 PM, Dan Langille wrote:
>
>> We're using PostgreSQL 8.1.11 on AIX 5.3 and we've been doing some
>> playing around
>> with various settings. So far, we've (I say we, but it's another guy
>> doing the work) found
>> that open_datasync seems better than fsync. By how much, we have not
>> yet determined,
>> but initial observations are promising.
>
> Here's a good explanation (by the Greg Smith) on the different sync
> methods. It basically says that if you have open_datasync available,
> it'll probably beat everything else.
Where is that explanation?
--
Dan Langille
BSDCan - The Technical BSD Conference : http://www.bsdcan.org/
PGCon - The PostgreSQL Conference: http://www.pgcon.org/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: wal_sync_methods for AIX
@ 2008-02-19 22:09 Erik Jones <erik@myemma.com>
parent: Dan Langille <dan@langille.org>
0 siblings, 0 replies; 7+ messages in thread
From: Erik Jones @ 2008-02-19 22:09 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-performance
On Feb 19, 2008, at 3:58 PM, Dan Langille wrote:
> Erik Jones wrote:
>> On Feb 15, 2008, at 3:55 PM, Dan Langille wrote:
>>> We're using PostgreSQL 8.1.11 on AIX 5.3 and we've been doing
>>> some playing around
>>> with various settings. So far, we've (I say we, but it's another
>>> guy doing the work) found
>>> that open_datasync seems better than fsync. By how much, we have
>>> not yet determined,
>>> but initial observations are promising.
>> Here's a good explanation (by the Greg Smith) on the different
>> sync methods. It basically says that if you have open_datasync
>> available, it'll probably beat everything else.
>
> Where is that explanation?
Sorry, did I leave off the link? http://www.westnet.com/~gsmith/
content/postgresql/TuningPGWAL.htm
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fwd: wal_sync_methods for AIX
@ 2008-02-19 22:57 Greg Smith <gsmith@gregsmith.com>
parent: JP Fletcher <jpfletch@ca.afilias.info>
0 siblings, 0 replies; 7+ messages in thread
From: Greg Smith @ 2008-02-19 22:57 UTC (permalink / raw)
To: JP Fletcher <jpfletch@ca.afilias.info>; +Cc: pgsql-performance
On Tue, 19 Feb 2008, JP Fletcher wrote:
> Shared buffers are set to 50000, bgwriter settings are as follows:
>
> bgwriter_delay = 50 # 10-10000 milliseconds between
> rounds
> bgwriter_lru_percent = 20.0 # 0-100% of LRU buffers scanned/round
> bgwriter_lru_maxpages = 300 # 0-1000 buffers max written/round
> bgwriter_all_percent = 5 # 0-100% of all buffers scanned/round
> bgwriter_all_maxpages = 600 # 0-1000 buffers max written/round
Not that it impacts what you're asking about, but that's probably an
excessive setting for bgwriter_lru_percent. With the reduced delay and
scanning that much, you're burning a lot of CPU time doing that for little
benefit.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-02-19 22:57 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-15 21:55 wal_sync_methods for AIX Dan Langille <dan@langille.org>
2008-02-15 22:04 ` Joshua D. Drake <jd@commandprompt.com>
2008-02-19 20:21 ` JP Fletcher <jpfletch@ca.afilias.info>
2008-02-19 22:57 ` Greg Smith <gsmith@gregsmith.com>
2008-02-15 22:20 ` Erik Jones <erik@myemma.com>
2008-02-19 21:58 ` Dan Langille <dan@langille.org>
2008-02-19 22:09 ` Erik Jones <erik@myemma.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