On Thu, Aug 20, 2009 at 11:38 PM, Jeff Janes<jeff.janes@xxxxxxxxx> wrote: >> ---------- Forwarded message ---------- >> From: Jaime Casanova <jcasanov@xxxxxxxxxxxxxxxxxxx> >> To: psql performance list <pgsql-performance@xxxxxxxxxxxxxx> >> Date: Wed, 19 Aug 2009 19:25:11 -0500 >> Subject: [PERFORMANCE] how to set wal_buffers >> Hi, >> >> Our fine manual says: >> """ >> The amount of memory used in shared memory for WAL data. The default >> is 64 kilobytes (64kB). The setting need only be large enough to hold >> the amount of WAL data generated by one typical transaction, since the >> data is written out to disk at every transaction commit. This >> parameter can only be set at server start. >> """ > > I don't care for that description for several reasons, but haven't > been able to come up with a good alternative. > > One problem is as you note. How is the average user supposed to know > what is the size of the redo that is generated by a typical > transaction? > one way is if there is a way to know how many blocks have been written by postgres (even a total is usefull because we can divide that per pg_stat_database.xact_commits), maybe pg_stat_bgwriter.buffers_checkpoint can give us an idea of that? > > On the other extreme, if you have many connections rapidly firing > small transactions, and you hope for the WAL of many of them to all > get flushed down to disk with a single fsync, > then your wal_buffers should be big enough to hold the WAL data of all > those transactions. Running out of WAL space has a nasty effect on > group commits. > that's exactly my situation... and i was thinking on raising wal_buffers at least to hold variuos transactions... i can use pg_stat_database.xact_commits to calculate an avg of transactions per second... plus i think we need a bit more space for transactions marked as "synchrounous_commit to off" -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
