Dan Langille wrote:
Begin forwarded message:
From: "Joshua D. Drake" <jd@xxxxxxxxxxxxxxxxx>
Date: February 15, 2008 5:04:53 PM EST
To: Dan Langille <dan@xxxxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: wal_sync_methods for AIX
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Fri, 15 Feb 2008 16:55:45 -0500
Dan Langille <dan@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
