[GSoC] Metrics and Monitoring for pgagroal

[An-DJ]

Hi all,

I'm Junduo Dong(@An-DJ). I will join and contribute to pgagroal during the period of GSoC 2021.

I will be working on metrics and monitoring for pgagroal in the next several months. The new metrics
are mainly divided into the following 4 aspects:

• Pool
• Sever
• Client
• Internal

I will start with the following metrics to improve the monitoring of pgagroal:

Module	Metric	Note
Pool	pgagroal_pipeline_mode	pipeline mode(perf, session, tx)
	pgagroal_query_count	counter of total query
	pgagroal_query_time	gauge of last query time
	pgagroal_tx_count	counter of total transaction
	pgagroal_tx_time	gauge of last transaction time
	pgagroal_wait_time	gauge of last wait time
Server	pgagroal_db_connections_bucket	histogram of history connections count(per database)
	pgagroal_db_connections	gauge of current connections(per database)
	pgagroal_db_queries_bucket	histogram of history connections count(per database)
	pgagroal_db_queries	gauge of current connections(per database)
Client	pgagroal_client_wait	gauge of current wait client(per database)
	pgagroal_client_wait_bucket	histogram of history wait client(per database)
	pgagroal_client_inuse	gauge of inuse client
Internal	pgagroal_mem_alloc	gauge of memory allocation
	pgagroal_self_sockets	gauge of socket (main process, prometheus process, ...)
	pgagroal_conn_client_sockets	gauge of client socket
	pgagroal_conn_server_sockets	gauge of server socket
	pgagroal_sent	sent (to clients) bytes per second
	pgagroal_receive	receive (from clients) bytes per second

The total analysis of pgagroal's metrics from my perspective can be found here https://www.postgresql.org/message-id/flat/CAF5jX-xaw_j1z1RNd%3DGNBbOFN_Ew3XNvSWGbBAye7LU8Rb_-6Q%40mail.gmail.com

If you have any better suggestions, please join us!

[jesperpedersen]

I'm very happy that you are working on this !

I think a good start would be to

1. Create a Grafana dashboard of the existing metrics
2. Implement pgagroal_client_wait and pgagroal_client_inuse (or pgagroal_client_active) as a global counter, since it only affects the Prometheus infrastructure and worker.c. Per database could come later

as the 2 first pull requests. That may give people an idea of what is to come and provide feedback on possible metrics.

I would put the Grafana stuff in /contrib/grafana with a README.md that explains how to set it up and use the dashboards.

Looking forward to your contributions during #GSoC2021 !

[An-DJ]

I'm very happy that you are working on this !

I think a good start would be to

1. Create a Grafana dashboard of the existing metrics
2. Implement pgagroal_client_wait and pgagroal_client_inuse (or pgagroal_client_active) as a global counter, since it only affects the Prometheus infrastructure and worker.c. Per database could come later

as the 2 first pull requests. That may give people an idea of what is to come and provide feedback on possible metrics.

I would put the Grafana stuff in /contrib/grafana with a README.md that explains how to set it up and use the dashboards.

Looking forward to your contributions during #GSoC2021 !

Thank you for your advice. I will take it. 😄

[x4m]

Are we going to implement a metric of average query\transaction time in a window? Or maybe time quantiles?

[jesperpedersen]

I think the transaction time/rate/... per database statistics is important in order to see the load on each database. Maybe it is too early to tell which actual metrics will be developed, as they are more complex in nature, but yes, it is something that needs to happen.

As for queries, maybe it is more a matter of identifying queries that runs for a long time rather than their histogram - I don't think we can assume that pg_stat_statements is installed.

I think the easy metrics will give @An-DJ a good idea of how to develop, and how that ties into more advanced areas. The code for identifying the transaction boundaries is already in pipeline_transaction.c so that can be reused.

[An-DJ]

The original purpose of pgagroal_query_time and pgagroal_tx_time is to represent the changing of query and transaction time cost. Maybe the average time cost in a proper time window can be more appropriate than the last time cost.

I will consider it. Thanks.

[An-DJ]

Hi all,

I have done parts of features and new metrics below since June:

• Add grafana dashboard to pgagroal [Add grafana dashboard to pgagroal #152].
  The implementation of dashboard can help DBA or others to get more details about pgagroal. New metric panels will be incrementally added to it in the future.
• Split struct prometheus from struct configuration [Split struct prometheus from struct configuration #155].
  The separation of struct prometheus and struct configuration can make it easily to add per-database metrics and so on by dynamic connection statistics.
• Add metrics of client [Add metrics of client #157].
  pgagroal_client_active and pgagroal_client_wait have been added. Per database version will come later.

Recently I focus on the implementation of server monitoring, and then gradually finish the per database statistics of client and server. New PR will be created soon.

For the rest of the day, I will focus on Pool and Internal metrics according to the table above.

Thanks to @jesperpedersen for his patient guidance. 😄

[jesperpedersen]

Yeah, we have good baseline metrics now in the Grafana dashboard :)

Getting the metrics for traffic and a more detailed load view for each database will improve this a lot - thanks for working on this !