Publish autovacuum informations
Hey,
There are times where I would need more informations on the autovacuum
processes.
I'd love to know what each worker is currently doing. I can get something
like this from the pg_stat_activity view but it doesn't give me as much
informations as the WorkerInfoData struct.
I'd also love to have more informations on the contents of the tables list
(how many tables still to process, which table next, what kind of
processing they'll get, etc... kinda what you have in the autovac_table
struct).
All in all, I want to get informations that are typically stored in shared
memory, handled by the autovacuum launcher and autovacuum workers. I first
thought I could get that by writing some C functions embedded in an
extension. But it doesn't seem to me I can access this part of the shared
memory from a C function. If I'm wrong, I'd love to get a pointer on how to
do this.
Otherwise, I wonder what would be more welcome: making the shared memory
structs handles available outside of the autovacuum processes (and then
build an extension to decode the informations I need), or adding functions
in core to get access to this information (in that case, no need for an
extension)?
Thanks.
Regards.
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
Guillaume Lelarge <guillaume@lelarge.info> writes:
All in all, I want to get informations that are typically stored in shared
memory, handled by the autovacuum launcher and autovacuum workers. I first
thought I could get that by writing some C functions embedded in an
extension. But it doesn't seem to me I can access this part of the shared
memory from a C function. If I'm wrong, I'd love to get a pointer on how to
do this.
Otherwise, I wonder what would be more welcome: making the shared memory
structs handles available outside of the autovacuum processes (and then
build an extension to decode the informations I need), or adding functions
in core to get access to this information (in that case, no need for an
extension)?
Either one of those approaches would cripple our freedom to change those
data structures; which we've done repeatedly in the past and will surely
want to do again. So I'm pretty much -1 on exposing them.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2014-12-29 17:03 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Guillaume Lelarge <guillaume@lelarge.info> writes:
All in all, I want to get informations that are typically stored in
shared
memory, handled by the autovacuum launcher and autovacuum workers. I
first
thought I could get that by writing some C functions embedded in an
extension. But it doesn't seem to me I can access this part of the shared
memory from a C function. If I'm wrong, I'd love to get a pointer on howto
do this.
Otherwise, I wonder what would be more welcome: making the shared memory
structs handles available outside of the autovacuum processes (and then
build an extension to decode the informations I need), or addingfunctions
in core to get access to this information (in that case, no need for an
extension)?Either one of those approaches would cripple our freedom to change those
data structures; which we've done repeatedly in the past and will surely
want to do again. So I'm pretty much -1 on exposing them.
I don't see how that's going to deny us the right to change any structs. If
they are in-core functions, we'll just have to update them. If they are
extension functions, then the developer of those functions would simply
need to update his code.
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
On Mon, Dec 29, 2014 at 11:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Either one of those approaches would cripple our freedom to change those
data structures; which we've done repeatedly in the past and will surely
want to do again. So I'm pretty much -1 on exposing them.
We could instead add a view of this information to core --
pg_stat_autovacuum, or whatever.
But to be honest, I'm more in favor of Guillaume's proposal. I will
repeat my recent assertion that we -- you in particular -- are too
reluctant to expose internal data structures to authors of C
extensions, and that this is developer-hostile.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
On Mon, Dec 29, 2014 at 11:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Either one of those approaches would cripple our freedom to change those
data structures; which we've done repeatedly in the past and will surely
want to do again. So I'm pretty much -1 on exposing them.
We could instead add a view of this information to core --
pg_stat_autovacuum, or whatever.
But to be honest, I'm more in favor of Guillaume's proposal. I will
repeat my recent assertion that we -- you in particular -- are too
reluctant to expose internal data structures to authors of C
extensions, and that this is developer-hostile.
Well, the core question there is whether we have a policy of not breaking
extension-visible APIs. While we will very often do things like adding
parameters to existing functions, I think we've tended to refrain from
making wholesale semantic revisions to exposed data structures.
I'd be all right with putting the data structure declarations in a file
named something like autovacuum_private.h, especially if it carried an
annotation that "if you depend on this, don't be surprised if we break
your code in future".
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Dec 31, 2014 at 12:46:17PM -0500, Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Mon, Dec 29, 2014 at 11:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Either one of those approaches would cripple our freedom to change those
data structures; which we've done repeatedly in the past and will surely
want to do again. So I'm pretty much -1 on exposing them.We could instead add a view of this information to core --
pg_stat_autovacuum, or whatever.But to be honest, I'm more in favor of Guillaume's proposal. I will
repeat my recent assertion that we -- you in particular -- are too
reluctant to expose internal data structures to authors of C
extensions, and that this is developer-hostile.Well, the core question there is whether we have a policy of not breaking
extension-visible APIs.
No, we have no policy restricting backend C API changes in major releases.
Though this message is old enough to enroll in first grade, I know of no
policy decision supplanting it:
/messages/by-id/8706.1230569070@sss.pgh.pa.us
While we will very often do things like adding
parameters to existing functions, I think we've tended to refrain from
making wholesale semantic revisions to exposed data structures.
True. I especially look to avoid changes that will cause extensions to build
and run, yet silently misbehave at runtime. For example, had I reviewed the
pg_policy patch, I would have examined whether an unmodified 9.4 extension
might let a user bypass relation policy. I oppose most header reorganization,
which breaks builds in exchange for insubstantial benefits. I don't wish to
extend that anywhere near to the point of saying, "Your C function can't use
struct foo, because exposing struct foo in a header file would imply freezing
it." Desire for backend API stability should not drive us to reject new
functionality.
I'd be all right with putting the data structure declarations in a file
named something like autovacuum_private.h, especially if it carried an
annotation that "if you depend on this, don't be surprised if we break
your code in future".
Such an annotation would be no more true than it is for the majority of header
files. If including it makes you feel better, I don't object.
nm
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 1/1/15, 4:17 PM, Noah Misch wrote:
I'd be all right with putting the data structure declarations in a file
named something like autovacuum_private.h, especially if it carried an
annotation that "if you depend on this, don't be surprised if we break
your code in future".Such an annotation would be no more true than it is for the majority of header
files. If including it makes you feel better, I don't object.
We need to be careful with that. Starting to segregate things into _private headers implies that stuff in non-private headers *is* locked down. We'd need to set clear expectations.
I do think more clarity would be good here. Right now the only distinction we have is things like SPI are spelled out in the docs. Other than that, the there really isn't anything to indicate how safe it is to rely on what's in the headers. For example, I've got some code that's looking at fcinfo->flinfo->fn_expr, and I have no idea how likely that is to get broken. Since it's a parse node, my guess is "likely", but I'm just guessing.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Dec 31, 2014 at 12:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'd be all right with putting the data structure declarations in a file
named something like autovacuum_private.h, especially if it carried an
annotation that "if you depend on this, don't be surprised if we break
your code in future".
Works for me. I am not in general surprised when we do things that
break my code, or anyway, the code that I'm responsible for
maintaining. But I think it makes sense to segregate this into a
separate header file so that we are clear that it is only exposed for
the benefit of extension authors, not so that other things in the core
system can touch it.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2015-01-05 17:40 GMT+01:00 Robert Haas <robertmhaas@gmail.com>:
On Wed, Dec 31, 2014 at 12:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'd be all right with putting the data structure declarations in a file
named something like autovacuum_private.h, especially if it carried an
annotation that "if you depend on this, don't be surprised if we break
your code in future".Works for me. I am not in general surprised when we do things that
break my code, or anyway, the code that I'm responsible for
maintaining. But I think it makes sense to segregate this into a
separate header file so that we are clear that it is only exposed for
the benefit of extension authors, not so that other things in the core
system can touch it.
I'm fine with that too. I'll try to find some time to work on that.
Thanks.
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
2015-01-05 17:44 GMT+01:00 Guillaume Lelarge <guillaume@lelarge.info>:
2015-01-05 17:40 GMT+01:00 Robert Haas <robertmhaas@gmail.com>:
On Wed, Dec 31, 2014 at 12:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'd be all right with putting the data structure declarations in a file
named something like autovacuum_private.h, especially if it carried an
annotation that "if you depend on this, don't be surprised if we break
your code in future".Works for me. I am not in general surprised when we do things that
break my code, or anyway, the code that I'm responsible for
maintaining. But I think it makes sense to segregate this into a
separate header file so that we are clear that it is only exposed for
the benefit of extension authors, not so that other things in the core
system can touch it.I'm fine with that too. I'll try to find some time to work on that.
So I took a look at this this week. I discovered, with the help of a
coworker, that I can already use the AutoVacuumShmem pointer and read the
struct. Unfortunately, it doesn't give me as much details as I would have
liked. The list of databases and tables aren't in shared memory. They are
local to the process that uses them. Putting them in shared memory (if at
all possible) would imply a much bigger patch than I was willing to write
right now.
Thanks anyway for the help.
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
On 04/06/2015 22:10, Guillaume Lelarge wrote:
2015-01-05 17:44 GMT+01:00 Guillaume Lelarge <guillaume@lelarge.info
<mailto:guillaume@lelarge.info>>:2015-01-05 17:40 GMT+01:00 Robert Haas <robertmhaas@gmail.com
<mailto:robertmhaas@gmail.com>>:On Wed, Dec 31, 2014 at 12:46 PM, Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:I'd be all right with putting the data structure declarations in a file
named something like autovacuum_private.h, especially if it carried an
annotation that "if you depend on this, don't be surprised if we break
your code in future".Works for me. I am not in general surprised when we do things that
break my code, or anyway, the code that I'm responsible for
maintaining. But I think it makes sense to segregate this into a
separate header file so that we are clear that it is only
exposed for
the benefit of extension authors, not so that other things in
the core
system can touch it.I'm fine with that too. I'll try to find some time to work on that.
So I took a look at this this week. I discovered, with the help of a
coworker, that I can already use the AutoVacuumShmem pointer and read
the struct. Unfortunately, it doesn't give me as much details as I would
have liked. The list of databases and tables aren't in shared memory.
They are local to the process that uses them. Putting them in shared
memory (if at all possible) would imply a much bigger patch than I was
willing to write right now.Thanks anyway for the help.
Sorry to revive such an old thread.
I think some hooks in the autovacuum could be enough to have good
insight without exposing private structure.
Please find attached a patch that adds some hooks to the autovacuum, and
as an example a quick proof of concept extension that use them and allow
to see what are the autovacuum worker todo list, skipped tables and so on.
I'm not really sure about which information should be provided, so I'm
open to any suggestion to improve this.
--
Julien Rouhaud
http://dalibo.com - http://dalibo.org
On Mon, Feb 29, 2016 at 3:04 PM, Julien Rouhaud <julien.rouhaud@dalibo.com>
wrote:
On 04/06/2015 22:10, Guillaume Lelarge wrote:
2015-01-05 17:44 GMT+01:00 Guillaume Lelarge <guillaume@lelarge.info
<mailto:guillaume@lelarge.info>>:2015-01-05 17:40 GMT+01:00 Robert Haas <robertmhaas@gmail.com
<mailto:robertmhaas@gmail.com>>:On Wed, Dec 31, 2014 at 12:46 PM, Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:I'd be all right with putting the data structure declarations
in a file
named something like autovacuum_private.h, especially if it
carried an
annotation that "if you depend on this, don't be surprised if
we break
your code in future".
Works for me. I am not in general surprised when we do things
that
break my code, or anyway, the code that I'm responsible for
maintaining. But I think it makes sense to segregate this into
a
separate header file so that we are clear that it is only
exposed for
the benefit of extension authors, not so that other things in
the core
system can touch it.I'm fine with that too. I'll try to find some time to work on that.
So I took a look at this this week. I discovered, with the help of a
coworker, that I can already use the AutoVacuumShmem pointer and read
the struct. Unfortunately, it doesn't give me as much details as I would
have liked. The list of databases and tables aren't in shared memory.
They are local to the process that uses them. Putting them in shared
memory (if at all possible) would imply a much bigger patch than I was
willing to write right now.Thanks anyway for the help.
Sorry to revive such an old thread.
I think some hooks in the autovacuum could be enough to have good
insight without exposing private structure.
Interesting idea...
Please find attached a patch that adds some hooks to the autovacuum, and
as an example a quick proof of concept extension that use them and allow
to see what are the autovacuum worker todo list, skipped tables and so on.I'm not really sure about which information should be provided, so I'm
open to any suggestion to improve this.
I have a look at the patch and it's compile without warning and without
regression.
But something goes wrong when installing the extension:
fabrizio@bagual:~/Downloads/pg_stat_autovacuum
$ pg_config
BINDIR = /data/home/fabrizio/pgsql/bin
DOCDIR = /data/home/fabrizio/pgsql/share/doc
HTMLDIR = /data/home/fabrizio/pgsql/share/doc
INCLUDEDIR = /data/home/fabrizio/pgsql/include
PKGINCLUDEDIR = /data/home/fabrizio/pgsql/include
INCLUDEDIR-SERVER = /data/home/fabrizio/pgsql/include/server
LIBDIR = /data/home/fabrizio/pgsql/lib
PKGLIBDIR = /data/home/fabrizio/pgsql/lib
LOCALEDIR = /data/home/fabrizio/pgsql/share/locale
MANDIR = /data/home/fabrizio/pgsql/share/man
SHAREDIR = /data/home/fabrizio/pgsql/share
SYSCONFDIR = /data/home/fabrizio/pgsql/etc
PGXS = /data/home/fabrizio/pgsql/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/home/fabrizio/pgsql' '--enable-cassert'
'--enable-coverage' '--enable-tap-tests' '--enable-depend'
CC = gcc
CPPFLAGS = -DFRONTEND -D_GNU_SOURCE
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
-fprofile-arcs -ftest-coverage
CFLAGS_SL = -fpic
LDFLAGS = -L../../src/common -Wl,--as-needed
-Wl,-rpath,'/home/fabrizio/pgsql/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lz -lreadline -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 9.6devel
fabrizio@bagual:~/Downloads/pg_stat_autovacuum
$ make USE_PGXS=1 install
/bin/mkdir -p '/data/home/fabrizio/pgsql/lib'
/bin/mkdir -p '/data/home/fabrizio/pgsql/share/extension'
/bin/mkdir -p '/data/home/fabrizio/pgsql/share/extension'
/bin/mkdir -p '/data/home/fabrizio/pgsql/share/doc/extension'
/usr/bin/install -c -m 755 pg_stat_autovacuum.so
'/data/home/fabrizio/pgsql/lib/pg_stat_autovacuum.so'
/usr/bin/install -c -m 644 .//pg_stat_autovacuum.control
'/data/home/fabrizio/pgsql/share/extension/'
/usr/bin/install -c -m 644 .//pg_stat_autovacuum--0.0.1.sql
'/data/home/fabrizio/pgsql/share/extension/'
/usr/bin/install -c -m 644 '/data/home/fabrizio/pgsql/share/doc/extension/'
/usr/bin/install: missing destination file operand after
‘/data/home/fabrizio/pgsql/share/doc/extension/’
Try '/usr/bin/install --help' for more information.
make: *** [install] Error 1
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Show quoted text
Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
On 29/02/2016 20:20, Fabrízio de Royes Mello wrote:
On Mon, Feb 29, 2016 at 3:04 PM, Julien Rouhaud
<julien.rouhaud@dalibo.com <mailto:julien.rouhaud@dalibo.com>> wrote:On 04/06/2015 22:10, Guillaume Lelarge wrote:
2015-01-05 17:44 GMT+01:00 Guillaume Lelarge <guillaume@lelarge.info
<mailto:guillaume@lelarge.info>
<mailto:guillaume@lelarge.info <mailto:guillaume@lelarge.info>>>:
2015-01-05 17:40 GMT+01:00 Robert Haas <robertmhaas@gmail.com
<mailto:robertmhaas@gmail.com>
<mailto:robertmhaas@gmail.com <mailto:robertmhaas@gmail.com>>>:
On Wed, Dec 31, 2014 at 12:46 PM, Tom Lane
<tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>
<mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>> wrote:
I'd be all right with putting the data structure
declarations in a file
named something like autovacuum_private.h, especially if
it carried an
annotation that "if you depend on this, don't be surprised
if we break
your code in future".
Works for me. I am not in general surprised when we do
things that
break my code, or anyway, the code that I'm responsible for
maintaining. But I think it makes sense to segregate thisinto a
separate header file so that we are clear that it is only
exposed for
the benefit of extension authors, not so that other things in
the core
system can touch it.I'm fine with that too. I'll try to find some time to work on that.
So I took a look at this this week. I discovered, with the help of a
coworker, that I can already use the AutoVacuumShmem pointer and read
the struct. Unfortunately, it doesn't give me as much details as I would
have liked. The list of databases and tables aren't in shared memory.
They are local to the process that uses them. Putting them in shared
memory (if at all possible) would imply a much bigger patch than I was
willing to write right now.Thanks anyway for the help.
Sorry to revive such an old thread.
I think some hooks in the autovacuum could be enough to have good
insight without exposing private structure.Interesting idea...
Thanks for looking at it!
Please find attached a patch that adds some hooks to the autovacuum, and
as an example a quick proof of concept extension that use them and allow
to see what are the autovacuum worker todo list, skipped tables and so on.I'm not really sure about which information should be provided, so I'm
open to any suggestion to improve this.I have a look at the patch and it's compile without warning and without
regression.But something goes wrong when installing the extension:
[...]
/usr/bin/install -c -m 644 '/data/home/fabrizio/pgsql/share/doc/extension/'
/usr/bin/install: missing destination file operand after
‘/data/home/fabrizio/pgsql/share/doc/extension/’
Try '/usr/bin/install --help' for more information.
make: *** [install] Error 1
Oups, I'm not really sure what I removed and shouldn't have. I attached
v2 of the extension with a "standard" Makefile, which I just tested and
works fine.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQLTimbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
--
Julien Rouhaud
http://dalibo.com - http://dalibo.org
Attachments:
On Tue, Mar 1, 2016 at 4:38 AM, Julien Rouhaud
<julien.rouhaud@dalibo.com> wrote:
On 29/02/2016 20:20, Fabrízio de Royes Mello wrote:
On Mon, Feb 29, 2016 at 3:04 PM, Julien Rouhaud
<julien.rouhaud@dalibo.com <mailto:julien.rouhaud@dalibo.com>> wrote:On 04/06/2015 22:10, Guillaume Lelarge wrote:
2015-01-05 17:44 GMT+01:00 Guillaume Lelarge <guillaume@lelarge.info
<mailto:guillaume@lelarge.info>
<mailto:guillaume@lelarge.info <mailto:guillaume@lelarge.info>>>:
2015-01-05 17:40 GMT+01:00 Robert Haas <robertmhaas@gmail.com
<mailto:robertmhaas@gmail.com>
<mailto:robertmhaas@gmail.com <mailto:robertmhaas@gmail.com>>>:
On Wed, Dec 31, 2014 at 12:46 PM, Tom Lane
<tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>
<mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>> wrote:
I'd be all right with putting the data structure
declarations in a file
named something like autovacuum_private.h, especially if
it carried an
annotation that "if you depend on this, don't be surprised
if we break
your code in future".
Works for me. I am not in general surprised when we do
things that
break my code, or anyway, the code that I'm responsible for
maintaining. But I think it makes sense to segregate thisinto a
separate header file so that we are clear that it is only
exposed for
the benefit of extension authors, not so that other things in
the core
system can touch it.I'm fine with that too. I'll try to find some time to work on that.
So I took a look at this this week. I discovered, with the help of a
coworker, that I can already use the AutoVacuumShmem pointer and read
the struct. Unfortunately, it doesn't give me as much details as I would
have liked. The list of databases and tables aren't in shared memory.
They are local to the process that uses them. Putting them in shared
memory (if at all possible) would imply a much bigger patch than I was
willing to write right now.Thanks anyway for the help.
Sorry to revive such an old thread.
I think some hooks in the autovacuum could be enough to have good
insight without exposing private structure.
Instead of introducing 4 new hooks, which do not represent a general
use actually, why don't you expose a portion of this information in
shared memory as mentioned upthread? This sounds like a good approach
to me. Your extension could then scan them as needed and put that on
view or a function. This information is now private in the autovacuum
processes, exposing them would allow plugin authors to do a bunch of
fancy things I think, in a more flexible way than those hooks. And
there is no need to add more hooks should the structure of the
autovacuum code change for a reason or another in the future.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 01/03/2016 07:50, Michael Paquier wrote:
On Tue, Mar 1, 2016 at 4:38 AM, Julien Rouhaud
<julien.rouhaud@dalibo.com> wrote:On 29/02/2016 20:20, Fabrízio de Royes Mello wrote:
On Mon, Feb 29, 2016 at 3:04 PM, Julien Rouhaud
<julien.rouhaud@dalibo.com <mailto:julien.rouhaud@dalibo.com>> wrote:On 04/06/2015 22:10, Guillaume Lelarge wrote:
2015-01-05 17:44 GMT+01:00 Guillaume Lelarge <guillaume@lelarge.info
<mailto:guillaume@lelarge.info>
<mailto:guillaume@lelarge.info <mailto:guillaume@lelarge.info>>>:
2015-01-05 17:40 GMT+01:00 Robert Haas <robertmhaas@gmail.com
<mailto:robertmhaas@gmail.com>
<mailto:robertmhaas@gmail.com <mailto:robertmhaas@gmail.com>>>:
On Wed, Dec 31, 2014 at 12:46 PM, Tom Lane
<tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>
<mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>> wrote:
I'd be all right with putting the data structure
declarations in a file
named something like autovacuum_private.h, especially if
it carried an
annotation that "if you depend on this, don't be surprised
if we break
your code in future".
Works for me. I am not in general surprised when we do
things that
break my code, or anyway, the code that I'm responsible for
maintaining. But I think it makes sense to segregate thisinto a
separate header file so that we are clear that it is only
exposed for
the benefit of extension authors, not so that other things in
the core
system can touch it.I'm fine with that too. I'll try to find some time to work on that.
So I took a look at this this week. I discovered, with the help of a
coworker, that I can already use the AutoVacuumShmem pointer and read
the struct. Unfortunately, it doesn't give me as much details as I would
have liked. The list of databases and tables aren't in shared memory.
They are local to the process that uses them. Putting them in shared
memory (if at all possible) would imply a much bigger patch than I was
willing to write right now.Thanks anyway for the help.
Sorry to revive such an old thread.
I think some hooks in the autovacuum could be enough to have good
insight without exposing private structure.Instead of introducing 4 new hooks, which do not represent a general
use actually, why don't you expose a portion of this information in
shared memory as mentioned upthread? This sounds like a good approach
to me. Your extension could then scan them as needed and put that on
view or a function. This information is now private in the autovacuum
processes, exposing them would allow plugin authors to do a bunch of
fancy things I think, in a more flexible way than those hooks. And
there is no need to add more hooks should the structure of the
autovacuum code change for a reason or another in the future.
I thought exposing private structures could be a blocking issue. I
tried to see what could be done using hooks, and one thing I like is
that we can compute the process time of each relation, or even aggregate
some statistics. Having the vacuum time is something that we can
actually only obtain by setting log_autovacuum_min_duration and parsing
the logs, and I don't think it would be possible to do this by just
exposing current private structure.
--
Julien Rouhaud
http://dalibo.com - http://dalibo.org
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Mar 1, 2016 at 8:44 AM, Julien Rouhaud <julien.rouhaud@dalibo.com>
wrote:
On 01/03/2016 07:50, Michael Paquier wrote:
On Tue, Mar 1, 2016 at 4:38 AM, Julien Rouhaud
<julien.rouhaud@dalibo.com> wrote:On 29/02/2016 20:20, Fabrízio de Royes Mello wrote:
On Mon, Feb 29, 2016 at 3:04 PM, Julien Rouhaud
<julien.rouhaud@dalibo.com <mailto:julien.rouhaud@dalibo.com>> wrote:On 04/06/2015 22:10, Guillaume Lelarge wrote:
2015-01-05 17:44 GMT+01:00 Guillaume Lelarge <guillaume@lelarge.info
<mailto:guillaume@lelarge.info>
<mailto:guillaume@lelarge.info <mailto:guillaume@lelarge.info>>>:
2015-01-05 17:40 GMT+01:00 Robert Haas <robertmhaas@gmail.com
<mailto:robertmhaas@gmail.com>
<mailto:robertmhaas@gmail.com <mailto:robertmhaas@gmail.com>>>:
On Wed, Dec 31, 2014 at 12:46 PM, Tom Lane
<tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>
<mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>>
wrote:
I'd be all right with putting the data structure
declarations in a file
named something like autovacuum_private.h, especially if
it carried an
annotation that "if you depend on this, don't be surprised
if we break
your code in future".
Works for me. I am not in general surprised when we do
things that
break my code, or anyway, the code that I'm responsible for
maintaining. But I think it makes sense to segregate thisinto a
separate header file so that we are clear that it is only
exposed for
the benefit of extension authors, not so that other things
in
the core
system can touch it.I'm fine with that too. I'll try to find some time to work on
that.
So I took a look at this this week. I discovered, with the help of a
coworker, that I can already use the AutoVacuumShmem pointer and
read
the struct. Unfortunately, it doesn't give me as much details as I
would
have liked. The list of databases and tables aren't in shared
memory.
They are local to the process that uses them. Putting them in shared
memory (if at all possible) would imply a much bigger patch than I
was
willing to write right now.
Thanks anyway for the help.
Sorry to revive such an old thread.
I think some hooks in the autovacuum could be enough to have good
insight without exposing private structure.Instead of introducing 4 new hooks, which do not represent a general
use actually, why don't you expose a portion of this information in
shared memory as mentioned upthread? This sounds like a good approach
to me. Your extension could then scan them as needed and put that on
view or a function. This information is now private in the autovacuum
processes, exposing them would allow plugin authors to do a bunch of
fancy things I think, in a more flexible way than those hooks. And
there is no need to add more hooks should the structure of the
autovacuum code change for a reason or another in the future.I thought exposing private structures could be a blocking issue. I
tried to see what could be done using hooks, and one thing I like is
that we can compute the process time of each relation, or even aggregate
some statistics. Having the vacuum time is something that we can
actually only obtain by setting log_autovacuum_min_duration and parsing
the logs, and I don't think it would be possible to do this by just
exposing current private structure.
We understood (IMHO is an interesting idea) but as Michael said hooks is
for a general purpose. So can you demonstrate other use cases for this new
hooks?
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Show quoted text
Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
On 01/03/2016 13:47, Fabrízio de Royes Mello wrote:
On Tue, Mar 1, 2016 at 8:44 AM, Julien Rouhaud
<julien.rouhaud@dalibo.com <mailto:julien.rouhaud@dalibo.com>> wrote:On 01/03/2016 07:50, Michael Paquier wrote:
On Tue, Mar 1, 2016 at 4:38 AM, Julien Rouhaud
<julien.rouhaud@dalibo.com <mailto:julien.rouhaud@dalibo.com>> wrote:On 29/02/2016 20:20, Fabrízio de Royes Mello wrote:
On Mon, Feb 29, 2016 at 3:04 PM, Julien Rouhaud
<julien.rouhaud@dalibo.com <mailto:julien.rouhaud@dalibo.com><mailto:julien.rouhaud@dalibo.com <mailto:julien.rouhaud@dalibo.com>>>
wrote:I think some hooks in the autovacuum could be enough to have good
insight without exposing private structure.Instead of introducing 4 new hooks, which do not represent a general
use actually, why don't you expose a portion of this information in
shared memory as mentioned upthread? This sounds like a good approach
to me. Your extension could then scan them as needed and put that on
view or a function. This information is now private in the autovacuum
processes, exposing them would allow plugin authors to do a bunch of
fancy things I think, in a more flexible way than those hooks. And
there is no need to add more hooks should the structure of the
autovacuum code change for a reason or another in the future.I thought exposing private structures could be a blocking issue. I
tried to see what could be done using hooks, and one thing I like is
that we can compute the process time of each relation, or even aggregate
some statistics. Having the vacuum time is something that we can
actually only obtain by setting log_autovacuum_min_duration and parsing
the logs, and I don't think it would be possible to do this by just
exposing current private structure.We understood (IMHO is an interesting idea) but as Michael said hooks is
for a general purpose. So can you demonstrate other use cases for this
new hooks?
I can think of several usage. First, since the hook will always be
called, an extension will see all the activity a worker is doing when
exposing private structure will always be some kind of sampling. Then,
you can have other information that wouldn't be available just by
exposing private structure. For instance knowing a VACUUM isn't
performed by the worker (either because another worker is already
working on it or because it isn't needed anymore). IIRC there was a
discussion about concurrency issue in this case. We can also know if the
maintenance was cancelled due to lock not obtained fast enough.
Finally, as long as the hooks aren't use, they don't have any overhead.
I agree that all this is for monitoring purpose.
I'm not sure what are the fancy things that Michael had in mind with
exposing the private structure. Michael, was it something like having
the ability to change some of these data through an extension?
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQLTimbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
--
Julien Rouhaud
http://dalibo.com - http://dalibo.org
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 3/1/16 8:37 AM, Julien Rouhaud wrote:
We understood (IMHO is an interesting idea) but as Michael said hooks is
for a general purpose. So can you demonstrate other use cases for this
new hooks?I can think of several usage. First, since the hook will always be
called, an extension will see all the activity a worker is doing when
exposing private structure will always be some kind of sampling. Then,
I think that's pretty key. If you wanted to create an extension that
logs vacuums (which would be great, since current state of the art is
logs + pgBadger), you'd want to gather your data about what the vacuum
did as the vacuum was ending.
I can certainly see cases where you don't care about that and just want
what's in shared memory, but that would only be useful for monitoring
what's happening real-time, not for knowing what final results are.
BTW, I think as much of this as possible should also work for regular
vacuums.
you can have other information that wouldn't be available just by
exposing private structure. For instance knowing a VACUUM isn't
performed by the worker (either because another worker is already
working on it or because it isn't needed anymore). IIRC there was a
discussion about concurrency issue in this case. We can also know if the
maintenance was cancelled due to lock not obtained fast enough.
Finally, as long as the hooks aren't use, they don't have any overhead.
I agree that all this is for monitoring purpose.I'm not sure what are the fancy things that Michael had in mind with
exposing the private structure. Michael, was it something like having
the ability to change some of these data through an extension?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 01/03/2016 20:29, Jim Nasby wrote:
On 3/1/16 8:37 AM, Julien Rouhaud wrote:
We understood (IMHO is an interesting idea) but as Michael said
hooks is
for a general purpose. So can you demonstrate other use cases for this
new hooks?I can think of several usage. First, since the hook will always be
called, an extension will see all the activity a worker is doing when
exposing private structure will always be some kind of sampling. Then,I think that's pretty key. If you wanted to create an extension that
logs vacuums (which would be great, since current state of the art is
logs + pgBadger), you'd want to gather your data about what the vacuum
did as the vacuum was ending.
Indeed these information are missing. I guess that'd be possible by
adding (or moving) a hook in lazy_vacuum_rel() that provide access to
part or all of the LVRelStats and rusage informations.
I can certainly see cases where you don't care about that and just want
what's in shared memory, but that would only be useful for monitoring
what's happening real-time, not for knowing what final results are.BTW, I think as much of this as possible should also work for regular
vacuums.
You mean for database wide vacuum?
you can have other information that wouldn't be available just by
exposing private structure. For instance knowing a VACUUM isn't
performed by the worker (either because another worker is already
working on it or because it isn't needed anymore). IIRC there was a
discussion about concurrency issue in this case. We can also know if the
maintenance was cancelled due to lock not obtained fast enough.
Finally, as long as the hooks aren't use, they don't have any overhead.
I agree that all this is for monitoring purpose.I'm not sure what are the fancy things that Michael had in mind with
exposing the private structure. Michael, was it something like having
the ability to change some of these data through an extension?
--
Julien Rouhaud
http://dalibo.com - http://dalibo.org
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 3/1/16 3:02 PM, Julien Rouhaud wrote:
You mean for database wide vacuum?
I mean manual vacuum. Some hooks and stats would apply only to autovac
obviously (and it'd be nice to get visibility into the scheduling
decisions both daemons are making). But as much as possible things
should be done in vacuum.c/lazyvacuum.c so it works for manual vacuums
as well.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
