[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-bugs
Subject:    Re: [BUGS] BUG #12071: Stat collector went crasy (50MB/s constant writes)
From:       Maxim Boguk <maxim.boguk () gmail ! com>
Date:       2014-11-27 10:26:32
Message-ID: CAK-MWwSXXWFA-Rgpo9EE-cO+wCs=xDDjoJP9+P6RR1SY1HiWpQ () mail ! gmail ! com
[Download RAW message or body]

FWIW, I got curious and checked why we decided not to implement this
> while reworking the stats in 9.3, as keeping an is_dirty flag seems as a
> rather straightforward and simple optimization.
>
> Turns out it's actually considerably more complex, because one of the
> sources of statistics updates are (surprise surprise) autovacuum
> workers. So the whole flow may look like this (in >= 9.3):
>
>    1) launcher requests a fresh stats file (dbs list only)
>    2) worker is started for a particular DB (by launcher)
>    3) the worker requests a stats file (with details for the DB)
>


Now for (nearly) idle databases worker do nothing and simple exit in 99.9%.
And if there a lot of idle/low-active db's in cluster - is_dirty tracking
would be beneficial on both pre 9.3 and after 9.3 versions (and in 9.3+ it
will be especially effective because id_dirty tracked per-db basis).

Kind Regards,
Maksym

[Attachment #3 (text/html)]

<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote"><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">FWIW, I got curious and checked why we decided not to \
implement this<br> while reworking the stats in 9.3, as keeping an is_dirty flag \
seems as a<br> rather straightforward and simple optimization.<br>
<br>
Turns out it&#39;s actually considerably more complex, because one of the<br>
sources of statistics updates are (surprise surprise) autovacuum<br>
workers. So the whole flow may look like this (in &gt;= 9.3):<br>
<br>
     1) launcher requests a fresh stats file (dbs list only)<br>
     2) worker is started for a particular DB (by launcher)<br>
     3) the worker requests a stats file (with details for the \
DB)<br></blockquote><div><br><br></div></div>Now for (nearly) idle databases worker \
do nothing and simple exit in 99.9%.<br></div><div class="gmail_extra">And if there a \
lot of idle/low-active db&#39;s in cluster - is_dirty tracking would be beneficial on \
both pre 9.3 and after 9.3 versions (and in 9.3+ it will be especially effective \
because id_dirty tracked per-db basis).<br></div><div \
class="gmail_extra"><br></div><div class="gmail_extra">Kind \
Regards,<br>Maksym<br></div><div class="gmail_extra"><br><br> </div></div>



[prev in list] [next in list] [prev in thread] [next in thread]