Re: New statistics for WAL buffer dirty writes

On Sat, Jul 7, 2012 at 7:06 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Jul 7, 2012, at 8:54 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> On Sat, Jul 7, 2012 at 3:52 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> On Jul 7, 2012, at 9:07 AM, Euler Taveira <euler(at)timbira(dot)com> wrote:
>>>> On 07-07-2012 09:00, Satoshi Nagayasu wrote:
>>>>> I've created new patch to get/reset statistics of WAL buffer
>>>>> writes (flushes) caused by WAL buffer full.
>>>>>
>>>> This new statistic doesn't solve your problem (tune wal_buffers). It doesn't
>>>> give you the wal_buffers value. It only says "hey, I needed more buffers so I
>>>> write those dirty ones". It doesn't say how many. I would like to have
>>>> something that says "hey, you have 1000 buffers available and you are using
>>>> 100 buffers (10%)". This new statistic is only useful for decreasing the
>>>> WALWriteLock contention.
>>>
>>> The number of WAL buffers that you are using is going to change so quickly as to be utterly meaningless. I don't really see that there's any statistic we could gather that would tell us how many WAL buffers are needed. This patch seems like it's on the right track, at least telling you how often you're running out.
>>
>> We could keep a high watermark of "what's the largest percentage we've
>> used", perhaps?
>
> Sure, but I doubt that would be as informative as this. It's no big deal if you hit 100% every once in a while; what you really want to know is whether it's happening once per second or once per week.

I'm not suggesting one or the other, I'm suggesting that both values
might be interesting. Though in reality, you'd want that high
watermark to only count if it was the state for more than <n>, which
is a lot more difficult to get. so yeah, maybe that's overkill to even
try.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/