coroot / coroot-pg-agent Goto Github PK

View Code? Open in Web Editor NEW

119.0 119.0 15.0 57 KB

A Prometheus exporter for Postgres focusing on query performance statistics

Home Page: https://coroot.com/docs/metrics/pg-agent

License: Apache License 2.0

Dockerfile 1.17% Go 98.83%

monitoring postgresql postgresql-metrics postgresql-monitoring prometheus prometheus-exporter

coroot-pg-agent's Introduction

Features | Installation | Documentation | Community & Support | Live demo | Cloud

Open-source observability augmented with actionable insights

Collecting metrics, logs, and traces alone doesn't make your applications observable. Coroot turns that data into actionable insights for you!

Features

Zero-instrumentation observability

Metrics, logs, traces, and profiles are gathered automatically by using eBPF
Coroot provides you with a Service Map that covers 100% of your system with no blind spots
Predefined inspections audit each application without any configuration

Application Health Summary

Easily understand the status of your services, even when dealing with hundreds of them
Gain insight into application logs without the need to manually inspect each one
SLOs (Service Level Objectives) tracking

Explore any outlier requests with distributed tracing

Investigate any anomaly with just one click
Vendor-neutral instrumentation with OpenTelemetry
Are you unable to instrument legacy or third-party services? Coroot's eBPF-based instrumentation can capture requests without requiring any code changes.

Grasp insights from logs with just a quick glance

Log patterns: out-of-the-box event clustering
Seamless logs-to-traces correlation
Lightning-fast search based on ClickHouse

Profile any application in 1 click

Analyze any unexpected spike in CPU or memory usage down to the precise line of code
Don't make assumptions, know exactly what the resources were spent on
Easily investigate any anomaly by comparing it to the system's baseline behavior

Built-in expertise

Coroot can automatically identify over 80% of issues
If an app is not meeting its Service Level Objectives (SLOs), Coroot will send a single alert that includes the results of all relevant inspections
You can easily adjust any inspection for a particular application or an entire project

Deployment Tracking

Coroot discovers and monitors every application rollout in your Kubernetes cluster
Requires no integration with your CI/CD pipeline
Each release is automatically compared with the previous one, so you'll never miss even the slightest performance degradation
With integrated Cost Monitoring, developers can track how each change affects their cloud bill

Cost Monitoring

Understand your cloud costs down to the specific application
Doesn't require access to you cloud account or any other configurations
AWS, GCP, Azure

Installation

You can run Coroot as a Docker container or deploy it into any Kubernetes cluster. Check out the Installation guide.

Documentation

The Coroot documentation is available at coroot.com/docs/coroot-community-edition.

Live demo

A live demo of Coroot is available at community-demo.coroot.com

Community & Support

Contributing

To start contributing, check out our Contributing Guide.

License

Coroot is licensed under the Apache License, Version 2.0.

coroot-pg-agent's People

Contributors

Stargazers

Watchers

Forkers

monotoringta weave-lab ericyu-bebit apetruhin luis-sousa-pinto alzamonn neomantra khestia alvinhom adrienyhuel chungnicholas suyambuganesh82 dorucioclea etherfurnace tmakovet

coroot-pg-agent's Issues

One Agent Can monitor multiple pg instances?

We have multiple pg instance，if one agent can monitor multiple pg instances，it will be very practical！

slow scrape

Hi,

Is it to be expected that pg-agent scrape might take a while?
Mine takes 700ms and that is a bit concerning, if I run it every 15sec.

Few details below, please let me know how can I give you more info.

thank you

Using coroot-pg-agent with PostgreSQL v.15

Hello,

I tried to use coroot-pg-agent for PostgreSQL v.14, it worked perfect according to your instructions.
But with PostgreSQL v.15 I've cought the problem:

[collector.go:166] pq: relation "pg_stat_statements" does not exist

The solvation I've found is:
set up

ALTER USER <USER> SET SEARCH_PATH TO <USER>,pg_catalog,public;

in database,
and disable SSL mode when starting the container (because I found the warning in logs "SSL mode is disabled!")

DSN: "postgresql:// <USER>: <PASSWORD>@<HOST>:<PORT>/<DATABASE_NAME>?sslmode=disable&connect_timeout=1&statement_timeout=30000"

It would be great if you add this notes to the docs.

DSN not supporting special chars

When setting the DSN, if a password contains some characters such as | or @, then a parse error is received

1 collector.go:123] parse "postgresql://user:<**redacted**>@<**redacted**>5432/<**redacted**>?connect_timeout=1&statement_timeout=30000": net/url: invalid userinfo

High CPU load caused by coroot-pg-agent

Environmental Info:
CentOS Linux release 7.9.2009 (Core)
3.10.0-1160.76.1.el7.x86_64
PostgreSQL 9.6.24
pg_stat_statements 1.4

RHEL7.9
5.4.17-2136.312.3.4.el7uek.x86_64
PostgreSQL 12.11
pg_stat_statements 1.7

Describe the bug:
We have few postgres instance where we seeing high CPU load caused by the process coroot-pg-agent. In some situations, the server crashes.
After coroot-pg-agent is stopped load on the processor immediately decreases.
When we start coroot-pg-agent after about a minute, the load increases.

node with 4 cpu:

top - 14:06:42 up 1 day,  3:49,  2 users,  load average: 6.97, 6.86, 6.82
Tasks: 303 total,   3 running, 300 sleeping,   0 stopped,   0 zombie
%Cpu(s): 98.6 us,  1.2 sy,  0.0 ni,  0.0 id,  0.0 wa,  0.0 hi,  0.2 si,  0.0 st
KiB Mem : 26586992 total,  1309816 free, 11908980 used, 13368196 buff/cache
KiB Swap:  2097148 total,  2097148 free,        0 used.  7388264 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 1186 pgagent+  20   0 2400048   1.5g   5336 R 366.2  5.8   5696:09 coroot-pg-agent

Service coroot-pg-agent running via systemd unit.

[Service]
User=pgagent-exporter
Group=pgagent-exporter

ExecStart=/usr/bin/coroot-pg-agent 
--listen=:38888 
--scrape-interval=30s 
postgresql://user:pass@localhost:5432/postgres?connect_timeout=5&statement_timeout=30000&sslmode=disable

Please advise about this issue for some deep research. If you need more information please let me know.

Additional context / logs:

USER       PID %CPU %MEM    VSZ     RSS      TTY      STAT START   TIME     COMMAND
pgagent+  1186  341  5.8   2400048 1557152  ?         Rsl  Dec07   5328:23  /usr/bin/coroot-pg-agent --listen=:38888 --scrape-interval=30s postgresql://localhost:5432/postgres?connect_timeout=5&statement_timeout=30000&sslmode=disable

pstree -p -s 1186
systemd(1)───coroot-pg-agent(1186)─┬─{coroot-pg-agent}(1483)
                                   ├─{coroot-pg-agent}(1486)
                                   ├─{coroot-pg-agent}(1487)
                                   ├─{coroot-pg-agent}(1488)
                                   ├─{coroot-pg-agent}(1489)
                                   └─{coroot-pg-agent}(1546)

lsof

coroot-pg  9660        pgagent-exporter  cwd       DIR                8,1       4096         96 /
coroot-pg  9660        pgagent-exporter  rtd       DIR                8,1       4096         96 /
coroot-pg  9660        pgagent-exporter  txt       REG                8,1   10911712   16968830 /usr/bin/coroot-pg-agent
coroot-pg  9660        pgagent-exporter  mem       REG                8,1      61560   25166896 /usr/lib64/libnss_files-2.17.so
coroot-pg  9660        pgagent-exporter  mem       REG                8,1    2156592   25166783 /usr/lib64/libc-2.17.so
coroot-pg  9660        pgagent-exporter  mem       REG                8,1     142144   25166808 /usr/lib64/libpthread-2.17.so
coroot-pg  9660        pgagent-exporter  mem       REG                8,1     163312   25166775 /usr/lib64/ld-2.17.so
coroot-pg  9660        pgagent-exporter    0r      CHR                1,3        0t0       1031 /dev/null
coroot-pg  9660        pgagent-exporter    1w      CHR                1,3        0t0       1031 /dev/null
coroot-pg  9660        pgagent-exporter    2w      CHR                1,3        0t0       1031 /dev/null
coroot-pg  9660        pgagent-exporter    3u     IPv4          185298491        0t0        TCP localhost:37910->localhost:postgres (ESTABLISHED)
coroot-pg  9660        pgagent-exporter    4u  a_inode               0,10          0       6394 [eventpoll]
coroot-pg  9660        pgagent-exporter    5r     FIFO                0,9        0t0  185298488 pipe
coroot-pg  9660        pgagent-exporter    6w     FIFO                0,9        0t0  185298488 pipe
coroot-pg  9660        pgagent-exporter    7u     IPv6          185298499        0t0        TCP *:38888 (LISTEN)
coroot-pg  9660        pgagent-exporter    8u     sock                0,7        0t0  185311614 protocol: TCPv6
coroot-pg  9660        pgagent-exporter    9u     sock                0,7        0t0  185304469 protocol: TCPv6
coroot-pg  9660        pgagent-exporter   10u     sock                0,7        0t0  185305720 protocol: TCPv6
coroot-pg  9660        pgagent-exporter   11u     sock                0,7        0t0  185309402 protocol: TCPv6
coroot-pg  9660        pgagent-exporter   12u     sock                0,7        0t0  185314100 protocol: TCPv6
coroot-pg  9660        pgagent-exporter   13u     sock                0,7        0t0  185320874 protocol: TCPv6
coroot-pg  9660        pgagent-exporter   14u     sock                0,7        0t0  185325269 protocol: TCPv6
coroot-pg  9660        pgagent-exporter   15u     sock                0,7        0t0  185329732 protocol: TCPv6
coroot-pg  9660        pgagent-exporter   16u     sock                0,7        0t0  185334777 protocol: TCPv6
coroot-pg  9660        pgagent-exporter   17u     sock                0,7        0t0  185336509 protocol: TCPv6
coroot-pg  9660        pgagent-exporter   18u     sock                0,7        0t0  185341141 protocol: TCPv6

Very long metrics values

Hello,

We're using Coroot Pg Agent to get metrics from PostGreSQL, which works great.
Metrics are exposed to FluentBit, which scrape them, and finally send them to OpenTelemetry.

Though there is a problem with some metrics in FluentBit.
Some pg_top_query_* metrics can be very long, thousands of chars, and FluentBit can't process them.

Is there a possibility to add some kind of string truncating to all the query strings, to limit the length of the metrics ?

Thank you :)

pq: column s.total_plan_time does not exist: Switch pg_stat_statements query composer from DB server version to extension version

Hey! I'm having issue with pg-agent on AWS RDS PostgreSQL 13.3. pg-agent logs reports:

W1130 17:51:06.706240       1 collector.go:141] pq: column s.total_plan_time does not exist
W1130 17:51:21.625628       1 collector.go:141] pq: column s.total_plan_time does not exist
W1130 17:51:34.362643       1 collector.go:235] no summaries
W1130 17:51:36.627009       1 collector.go:141] pq: column s.total_plan_time does not exist
W1130 17:51:49.361532       1 collector.go:235] no summaries
W1130 17:51:51.625913       1 collector.go:141] pq: column s.total_plan_time does not exist
W1130 17:52:04.362273       1 collector.go:235] no summaries

pg-agent checks PostgreSQL server version to compose query to pg_stat_statements. If version is =>13.0.0 then it assumes that pg_stat_statements view has column total_plan_time, however:

My PostgreSQL server version:

postgres => SELECT version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
(1 row)

postgres =>SELECT setting FROM pg_settings WHERE name='server_version';
 setting
---------
 13.3
(1 row)

But I have older version of pg_stat_statements extension.

So, I think that pg-agent actually should check extension version (pg_extension.extversion) instead of server version :

postgres => SELECT * FROM pg_extension where extname = 'pg_stat_statements';
  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 17016 | pg_stat_statements |       10 |        17014 | t              | 1.4        |           |
(1 row)

Version 1.4 (at least) has old version of pg_stat_statements view:

postgres => \d pg_stat_statements
                    View "public.pg_stat_statements"
       Column        |       Type       | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
 userid              | oid              |           |          |
 dbid                | oid              |           |          |
 queryid             | bigint           |           |          |
 query               | text             |           |          |
 calls               | bigint           |           |          |
 total_time          | double precision |           |          |
 min_time            | double precision |           |          |
 max_time            | double precision |           |          |
 mean_time           | double precision |           |          |
 stddev_time         | double precision |           |          |
 rows                | bigint           |           |          |
 shared_blks_hit     | bigint           |           |          |
 shared_blks_read    | bigint           |           |          |
 shared_blks_dirtied | bigint           |           |          |
 shared_blks_written | bigint           |           |          |
 local_blks_hit      | bigint           |           |          |
 local_blks_read     | bigint           |           |          |
 local_blks_dirtied  | bigint           |           |          |
 local_blks_written  | bigint           |           |          |
 temp_blks_read      | bigint           |           |          |
 temp_blks_written   | bigint           |           |          |
 blk_read_time       | double precision |           |          |
 blk_write_time      | double precision |           |          |

I fixed the issue re-installing extension to update it to version 1.8:

postgres => drop extension pg_stat_statements;
DROP EXTENSION
postgres => create extension pg_stat_statements;
CREATE EXTENSION
postgres => SELECT * FROM pg_extension where extname = 'pg_stat_statements';
   oid    |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
----------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 35151774 | pg_stat_statements |       10 |        17014 | t              | 1.8        |           |
(1 row)

logfmt log format

Hi,

Please add log format option for easier log parsing. Logfmt or JSON would be great.

Thank you

Change Postgres Driver

Firstly thank you for your work, this is an awesome project. We use google cloud and would like to use a different driver for connecting. Specifically:
https://pkg.go.dev/github.com/GoogleCloudPlatform/cloudsql-proxy/proxy/dialers/postgres
Is it an easy change to make the driver configurable or are there problems with dependencies and/or connection strings that introduce more complexity?

pq: SSL is not enabled on the server

sudo /usr/local/bin/coroot-pg-agent DSN="postgresql://postgres:passwor@localhost:5432/postgres?connect_timeout=1&statement_timeout=30000"
W0513 23:31:18.160103 292209 collector.go:96] probe failed: pq: SSL is not enabled on the server
I0513 23:31:18.160235 292209 main.go:35] static labels: map[]
I0513 23:31:18.160729 292209 main.go:41] listening on: 0.0.0.0:80
W0513 23:31:18.165435 292209 collector.go:123] pq: SSL is not enabled on the server
W0513 23:31:33.168268 292209 collector.go:123] pq: SSL is not enabled on the server
W0513 23:31:48.164631 292209 collector.go:123] pq: SSL is not enabled on the server
W0513 23:32:03.170949 292209 collector.go:123] pq: SSL is not enabled on the server
W0513 23:32:18.171431 292209 collector.go:123] pq: SSL is not enabled on the server
W0513 23:32:33.169117 292209 collector.go:123] pq: SSL is not enabled on the server
W0513 23:32:48.166895 292209 collector.go:123] pq: SSL is not enabled on the server
W0513 23:33:03.168719 292209 collector.go:123] pq: SSL is not enabled on the server
W0513 23:33:18.168518 292209 collector.go:123] pq: SSL is not enabled on the server

Is there a way to bypass SSL and connect coroot-pg-agent to Postgresql database ?

Adding a static label

We'd like to add these agents to every cloud sql instance in our gcp project. It is working well on one instance, but the "instance" label that is surfaced is just the IP address of the deployed pg-agent pod. It would be nicer and cleaner if we could add our own label that we could reference that would be the name of the sql instance the pg-agent is connected to. It seems like it would be fairly simple to add the ability in environment variables or otherwise to add a few static labels if needed/wanted.

Report the original Postgres version (before parsing) in the pg_info metric label

Now the string representation of semver.Version gets into the label.
It causes the following inaccuracies:

14.1.0 is reported instead of 14.1.
0.0.0 is reported if the agent fails to read the version
there may be non-canonical versions, such as 11.2-YB-2.15.0.1-b0 (YugabyteDB)