Thread: Re: Huge number of disk writes after migration to 8.1

> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > Maybe the fact that the stat file is completely rewritten=20
> every 500 ms=20
> > should be reconsidered, if in the future someone chooses to rewrite=20
> > the stat system.  We can reconsider this part then, as well.
>=20
> Yeah, it's becoming pretty obvious that that design does not=20
> scale very well.  I don't immediately have any ideas about a=20
> better way though.
>=20
> I am working on some marginal hacks like not writing more of=20
> the backend activity strings than is needed, but it'd be=20
> nicer to think of a different solution.

In most cases you're going to see extremely few reads compared to writes
on pg_stats, right? So why not have the backends connect to the stats
process (or perhaps use UDP, or use the pipe, or whatever) and fetch the
data when needed. So when nobody fetches any data, there is no overhead
(except for the stats process adding up values, of course).=20

Then you could also push down some filtering to the stats process - for
example, when you are reading from pg_stat_activity there is no need to
send over the row level stats. IIRC, today you have to read (and write)
the whole stats file anyways.

//Magnus

On Jan 19, 2006, at 06:51, Magnus Hagander wrote:

> In most cases you're going to see extremely few reads compared to
> writes
> on pg_stats, right? So why not have the backends connect to the stats
> process (or perhaps use UDP, or use the pipe, or whatever) and
> fetch the
> data when needed. So when nobody fetches any data, there is no
> overhead
> (except for the stats process adding up values, of course).

Do the stats need to persist across postmaster restarts (or crashes)?
If so, then they need to be written to disk at some point, so we
can't get rid of the stats file and the frequent writes to it. Or
maybe there could be a GUC setting for persisting stats -- if it's
enabled, it works as implemented today (flushed to disk every 500ms).
If it's disabled, the stats are only stored in-memory and never
written to a file; the postmaster could automatically do an "ANALYZE"
on startup to fill the in-memory stats.

We could move the stat file writes to happen only at a checkpoint.
Backends would get up-to-date info directly from the stats process as
you described, but it would always be persisted at a checkpoint.
Those *should* be less-frequent than every half a second. :)

- Chris

Chris Campbell wrote:
> On Jan 19, 2006, at 06:51, Magnus Hagander wrote:
>
> >In most cases you're going to see extremely few reads compared to
> >writes
> >on pg_stats, right? So why not have the backends connect to the stats
> >process (or perhaps use UDP, or use the pipe, or whatever) and
> >fetch the
> >data when needed. So when nobody fetches any data, there is no
> >overhead
> >(except for the stats process adding up values, of course).
>
> Do the stats need to persist across postmaster restarts (or crashes)?

After a crash we forcibly _delete_ the stat file.  OTOH, on clean
shutdown it would be quite possible to write it (which would be the only
time at which the file is written).

--
Alvaro Herrera                        http://www.advogato.org/person/alvherre
Licensee shall have no right to use the Licensed Software
for productive or commercial use. (Licencia de StarOffice 6.0 beta)

"Magnus Hagander" <mha@sollentuna.net> writes:
> In most cases you're going to see extremely few reads compared to writes
> on pg_stats, right? So why not have the backends connect to the stats
> process (or perhaps use UDP, or use the pipe, or whatever) and fetch the
> data when needed. So when nobody fetches any data, there is no overhead
> (except for the stats process adding up values, of course).

That's a thought.  You'd still want the stats file to preserve the data
across shutdowns, but the update rate could be far slower, maybe once
every few minutes.  The other nice thing is that when you do want the
stats, you could get current values, not half-a-second-behind values.

> Then you could also push down some filtering to the stats process - for
> example, when you are reading from pg_stat_activity there is no need to
> send over the row level stats. IIRC, today you have to read (and write)
> the whole stats file anyways.

No; the current behavior of grabbing a snapshot of the whole stats
dataset is a feature, not a bug.  It lets you sit there and correlate
the data using multiple queries, without worrying that the numbers are
changing under you.  We'd lose this ability if the data had to be
re-fetched for each query because we didn't grab it all.

            regards, tom lane

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Chris Campbell wrote:
>> Do the stats need to persist across postmaster restarts (or crashes)?

> After a crash we forcibly _delete_ the stat file.

I'm not sure why, though --- the writing technique seems fairly safe,
and in any case the reader checks what it's reading.  Maybe we shouldn't
throw away the old stats.

            regards, tom lane

Added to TODO:

    * Allow statistics collector information to be pulled from the collector
      process directly, rather than requiring the collector to write a
      filesystem file twice a second?


---------------------------------------------------------------------------

Tom Lane wrote:
> "Magnus Hagander" <mha@sollentuna.net> writes:
> > In most cases you're going to see extremely few reads compared to writes
> > on pg_stats, right? So why not have the backends connect to the stats
> > process (or perhaps use UDP, or use the pipe, or whatever) and fetch the
> > data when needed. So when nobody fetches any data, there is no overhead
> > (except for the stats process adding up values, of course).
>
> That's a thought.  You'd still want the stats file to preserve the data
> across shutdowns, but the update rate could be far slower, maybe once
> every few minutes.  The other nice thing is that when you do want the
> stats, you could get current values, not half-a-second-behind values.
>
> > Then you could also push down some filtering to the stats process - for
> > example, when you are reading from pg_stat_activity there is no need to
> > send over the row level stats. IIRC, today you have to read (and write)
> > the whole stats file anyways.
>
> No; the current behavior of grabbing a snapshot of the whole stats
> dataset is a feature, not a bug.  It lets you sit there and correlate
> the data using multiple queries, without worrying that the numbers are
> changing under you.  We'd lose this ability if the data had to be
> re-fetched for each query because we didn't grab it all.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073