On Sun, Aug 23, 2009 at 1:25 PM, Jaime Casanova<jcasanov@xxxxxxxxxxxxxxxxxxx> wrote: > 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? No, you want the amount of WAL data written, not the tablespace data written, which is what pg_stat_bgwriter gives you. Just look at how fast your pg_xlogs are being archived and turned over to determine that WAL volume (unless you have archive_timeout set). However, I don't think this will help. The amount of WAL logs increases dramatically right after a checkpoint, so looking at the bulk average doesn't say anything about how much is being generated at the peak. Does your performance drop right after a checkpoint is started? maybe the code bracketed by the probes TRACE_POSTGRESQL_WAL_BUFFER_WRITE_DIRTY* should be counted and reported under one of the stat tables. Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
