Обсуждение: wal_sync_methods for AIX
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
-----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-----
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
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
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/
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
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