[HACKERS] Notification when freespaces empty

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[HACKERS] Notification when freespaces empty

ITAGAKI Takahiro
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 <[hidden email]>
NTT Cyber Space Laboratories
Nippon Telegraph and Telephone Corporation.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

freespace.diff (7K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] Notification when freespaces empty

Simon Riggs
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



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] Notification when freespaces empty

Tom Lane-2
In reply to this post by ITAGAKI Takahiro
ITAGAKI Takahiro <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] Notification when freespaces empty

ITAGAKI Takahiro
In reply to this post by Simon Riggs
Simon Riggs <[hidden email]> 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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly