Re: Fwd: wal_sync_methods for AIX

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