Thread: [PERFORMANCE] how to set wal_buffers
> ---------- Forwarded message ---------- > From: Jaime Casanova <jcasanov@systemguards.com.ec> > To: psql performance list <pgsql-performance@postgresql.org> > 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
On Thu, Aug 20, 2009 at 11:38 PM, Jeff Janes<jeff.janes@gmail.com> wrote: >> ---------- Forwarded message ---------- >> From: Jaime Casanova <jcasanov@systemguards.com.ec> >> To: psql performance list <pgsql-performance@postgresql.org> >> 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
On Sun, Aug 23, 2009 at 1:25 PM, Jaime Casanova<jcasanov@systemguards.com.ec> wrote: > On Thu, Aug 20, 2009 at 11:38 PM, Jeff Janes<jeff.janes@gmail.com> wrote: >>> ---------- Forwarded message ---------- >>> From: Jaime Casanova <jcasanov@systemguards.com.ec> >>> To: psql performance list <pgsql-performance@postgresql.org> >>> 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
On Sun, Aug 23, 2009 at 5:26 PM, Jeff Janes<jeff.janes@gmail.com> wrote: >>> >>> 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). mmm... what about turning log_checkpoint on and look at the recycled segments number... (recycled_segments * wal_segment_size) / number of xact commited in that period do that for some days at the same (hopefully peak) hours... > > maybe the code bracketed by the probes > TRACE_POSTGRESQL_WAL_BUFFER_WRITE_DIRTY* should be counted > and reported under one of the stat tables. > +1, at least could be useful for some of us that do not have dtrace -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157