Thread: wal_sync_methods for AIX

wal_sync_methods for AIX

From
Dan Langille
Date:
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





Re: wal_sync_methods for AIX

From
"Joshua D. Drake"
Date:
-----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-----

Re: wal_sync_methods for AIX

From
Erik Jones
Date:
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




Re: Fwd: wal_sync_methods for AIX

From
JP Fletcher
Date:
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



Re: wal_sync_methods for AIX

From
Dan Langille
Date:
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/

Re: wal_sync_methods for AIX

From
Erik Jones
Date:
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




Re: Fwd: wal_sync_methods for AIX

From
Greg Smith
Date:
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