> ---------- 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? But other that, I still think it is not good advice. If your typical transaction runs for 5 minutes and generates 100's of MB of WAL and there is only one of them at a time, there is certainly no reason to have several hundred MB of wal_buffers. It will merrily run around the buffer ring of just a few MB. 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. The default value of wal_buffers is low because many older systems have a low default value for the kernel setting shmmax. On any decent-sized server, I'd just automatically increase wal_buffers to 1 or 2 MB. It might help and lot, and it is unlikely to hurt. Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
