Thread: Notification when freespaces empty

Hello all,

I think that this patch is useful to decide when to vacuum.
It notifies when freespace empties as follows:

$ ./pgbench -i
$ ./pgbench -n -t 1000
LOG:  FreeSpace for "public.accounts" becomes empty. (stored=1, avg=159, min=128)
LOG:  FreeSpace for "public.tellers" becomes empty. (stored=1, avg=238, min=40)
LOG:  FreeSpace for "public.branches" becomes empty. (stored=1, avg=238, min=36)
$ vacuumdb
VACUUM
$ ./pgbench -n -t 2000
LOG:  FreeSpace for "public.history" becomes empty. (stored=1, avg=208, min=52)
LOG:  FreeSpace for "public.accounts" becomes empty. (stored=758, avg=159, min=128)
LOG:  FreeSpace for "public.branches" becomes empty. (stored=5, avg=191, min=36)
LOG:  FreeSpace for "public.tellers" becomes empty. (stored=6, avg=184, min=40)


Furthermore, this patch detaches empty fsmpages then.
Freespaces keep being scanned after they empties,
but it seems to be bootless effort.

Is this useful?

---
ITAGAKI Takahiro <itagaki.takahiro@lab.ntt.co.jp>
NTT Cyber Space Laboratories
Nippon Telegraph and Telephone Corporation.

On Fri, 2005-05-20 at 14:41 +0900, ITAGAKI Takahiro wrote:
> LOG:  FreeSpace for "public.accounts" becomes empty. (stored=1, avg=159, min=128)

Looks useful to me, until we patch up FSMs more fully in the future.

Might need rewording. Stored -> stored pages, Avg -> Avg Row Length.
Probably should be a DEBUG1 log line also.

It would be even more useful if this was signalled (perhaps to stats) in
such a way that autovacuum could act upon this knowledge.

> Furthermore, this patch detaches empty fsmpages then.
> Freespaces keep being scanned after they empties,
> but it seems to be bootless effort.

Does that do anything useful though?

I thought we don't reallocate until VACUUM time, whereupon we identify
any empty slots and reuse them. Who cares whether we deallocate earlier?

Best Regards, Simon Riggs

ITAGAKI Takahiro <itagaki.takahiro@lab.ntt.co.jp> writes:
> I think that this patch is useful to decide when to vacuum.
> It notifies when freespace empties as follows:

No, it doesn't complain that there is no freespace, it complains when
a specific request can't be fulfilled; which for a large request might
not mean much of anything.  I haven't quite worked out what you are
trying to do with the minRequest restriction but I don't think that
really makes things better.  Also, aren't you throwing away remaining
free space in order to prevent the log message from appearing repeatedly?

> $ ./pgbench -n -t 1000
> LOG:  FreeSpace for "public.accounts" becomes empty. (stored=1, avg=159, min=128)
> LOG:  FreeSpace for "public.tellers" becomes empty. (stored=1, avg=238, min=40)
> LOG:  FreeSpace for "public.branches" becomes empty. (stored=1, avg=238, min=36)

A bigger issue is what is the point of logging such a transient
condition --- who's going to read it?  It's possible that autovacuum
would like to know about this, and after we get autovacuum integrated
into the backend I'm sure we'll be taking a look at letting it use FSM
information.  But I can't see that anyone is going to sit and watch
the postmaster log to decide to trigger vacuums.
        regards, tom lane

Simon Riggs <simon@2ndquadrant.com> wrote:

> > Furthermore, this patch detaches empty fsmpages then.
> Does that do anything useful though?
> I thought we don't reallocate until VACUUM time, whereupon we identify
> any empty slots and reuse them. Who cares whether we deallocate earlier?

Yes, we cannot reuse them until next VACUUM.
But I think it is a problem that FSMs keep being scanned after they are almost empty.
In such a case, most stored pages are touched whenever new pages
are requested. I intended to cut FSMs earlier in order to omit the scans.


---
ITAGAKI Takahiro
NTT Cyber Space Laboratories