Thread: Proposal: Adding isbgworker column to pg_stat_activity
Hey, all,
It is currently slightly difficult to determine how many background worker
processes are currently running, which is useful when trying to manage
the max_worker_processes parameter. It seems the best bet is to use the
backend_type column and filter out the many types that are defined in
miscinit.c:
https://github.com/postgres/postgres/blob/REL_13_1/src/backend/utils/init/miscinit.c#L201-L253
I would like to propose adding a simple column isbgworker, that simply
stores the value of the expression `beentry->st_backendType == B_BG_WORKER`,
which is used in pg_stat_get_activity.
https://github.com/postgres/postgres/blob/REL_13_1/src/backend/utils/adt/pgstatfuncs.c#L854-L867
Such a column would make it easier to determine a suitable value for the
max_worker_processes parameter. Similar internal resource parameters all
seem to have more straightforward ways to gauge their current usage:
max_wal_senders:
-> COUNT(*) FROM pg_stat_replication
max_parallel_workers:
-> COUNT(*) FROM pg_stat_activity WHERE backend_type = 'parallel worker'
max_replication_slots:
-> COUNT(*) FROM pg_replication_slots
max_connections:
-> COUNT(*) FROM pg_stat_activity WHERE backend_type = 'client backend'
Thoughts? I think it should be pretty easy to implement, and it would
also be beneficial to update the documentation for all of the above
parameters with notes about how to determine their current usage.
Thanks,
Paul
(Note: I asked a question related to this on pgsql-general:
https://www.postgresql.org/message-id/CACqFVBaH7OPT-smiE0xG6b_KVGkWNNhZ2-EoLNrbzLFSUgN2eQ%40mail.gmail.com)
It is currently slightly difficult to determine how many background worker
processes are currently running, which is useful when trying to manage
the max_worker_processes parameter. It seems the best bet is to use the
backend_type column and filter out the many types that are defined in
miscinit.c:
https://github.com/postgres/postgres/blob/REL_13_1/src/backend/utils/init/miscinit.c#L201-L253
I would like to propose adding a simple column isbgworker, that simply
stores the value of the expression `beentry->st_backendType == B_BG_WORKER`,
which is used in pg_stat_get_activity.
https://github.com/postgres/postgres/blob/REL_13_1/src/backend/utils/adt/pgstatfuncs.c#L854-L867
Such a column would make it easier to determine a suitable value for the
max_worker_processes parameter. Similar internal resource parameters all
seem to have more straightforward ways to gauge their current usage:
max_wal_senders:
-> COUNT(*) FROM pg_stat_replication
max_parallel_workers:
-> COUNT(*) FROM pg_stat_activity WHERE backend_type = 'parallel worker'
max_replication_slots:
-> COUNT(*) FROM pg_replication_slots
max_connections:
-> COUNT(*) FROM pg_stat_activity WHERE backend_type = 'client backend'
Thoughts? I think it should be pretty easy to implement, and it would
also be beneficial to update the documentation for all of the above
parameters with notes about how to determine their current usage.
Thanks,
Paul
(Note: I asked a question related to this on pgsql-general:
https://www.postgresql.org/message-id/CACqFVBaH7OPT-smiE0xG6b_KVGkWNNhZ2-EoLNrbzLFSUgN2eQ%40mail.gmail.com)
Hello, I just noticed that this proposal from 2020 didn't get any backers: On 2020-Dec-01, Paul Martinez wrote: > It is currently slightly difficult to determine how many background worker > processes are currently running, which is useful when trying to manage > the max_worker_processes parameter. It seems the best bet is to use the > backend_type column and filter out the many types that are defined in > miscinit.c: > > https://github.com/postgres/postgres/blob/REL_13_1/src/backend/utils/init/miscinit.c#L201-L253 > > I would like to propose adding a simple column isbgworker, that simply > stores the value of the expression `beentry->st_backendType == B_BG_WORKER`, > which is used in pg_stat_get_activity. Having recently had the chance to interact with this --and not at all happy about how it went-- I think we should do this, or something like it. It should be easy to filter out or in processes which correspond to bgworkers in pg_stat_activity. One simple idea would be to prefix something to backend_type (IIRC we used to do that), but a separate flag is probably more appropriate. Are you up for writing a patch? I'm guessing it should be easy. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "Puedes vivir sólo una vez, pero si lo haces bien, una vez es suficiente"