Re: [PERFORMANCE] how to set wal_buffers

On Sun, Aug 23, 2009 at 5:26 PM, Jeff Janes<jeff(dot)janes(at)gmail(dot)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