Missing (interesting) stats

Spindel commented 6 years ago

Congratulations on 1.0!

I saw your release, and I figured I'd take a look at what I have in PgZabbix that I'd really miss, in order to replace my collection with the one you're doing here. ( If I can maintain less code, that's good for me)

Now, before I sit down and butcher something in Go, I figured I'd chat a bit about it to see:

Insert / Update / Fetch / Delete stats

Rationale: We're monitoring on insertion /update rates in the DB level to track anomalies in production environments. Our loads are usually predictable with some time-based variance, causing this to be a very reliable predictor of anything from a peer net outage to application services not working.

Slow query count

Rationale: A growing amount of queries over a certain threshold of speed is usually the sign of something being wrong.

We're separating this into select like queries, all queries, and dml queries (not "insert/update/delete")

Connections used for replication

Actually, we use two methods here, both tracking connections per application, and connections used for replication.
We're checking the medium term number of connections used here to track backups (should run), and state of applications.

If there's interest here, I'll look at moving over my own code to instead integrate directly with pgmetrics, it seems like a nice way to learn Go.

mdevan commented 6 years ago

Thanks for the feedback.

Insert / Update / Fetch / Delete stats:

This is already present. You can see it in the JSON output as:

...
  "databases": [
    {
      "oid": 12558,
      "name": "postgres",
...
      "tup_returned": 2295255,
      "tup_fetched": 48882,
      "tup_inserted": 0,
      "tup_updated": 0,
      "tup_deleted": 0,
...

(Also in the text output (as percentages), but I assume you want to the use the JSON output to feed the numbers to Zabbix.)

Slow query count:

Would pg_stat_statements help? Adding that is on the roadmap.

Connections used for replication:

The entire list of connections, with application names (eg walreceiver for a streaming replication client) is available. The currently active replication destinations (from pg_stat_replication) are also there.

Spindel commented 6 years ago

pg_stat_statements is what im using already, so that'll be perfect.

I admit I didn't actually check the json output (it wasn't in the documentation… not the best excuse)

Good to see it's on the roadmap. I'll take a look on either moving my code to go, or just wrapping your output and feeding it into Zabbix. (Yes, I want to maintain less code. ;-) Thanks a lot.

On Mon, 5 Mar 2018, 14:05 Mahadevan, notifications@github.com wrote:

Thanks for the feedback.

Insert / Update / Fetch / Delete stats:

This is already present. You can see it in the JSON output as:

... "databases": [ { "oid": 12558, "name": "postgres", ... "tup_returned": 2295255, "tup_fetched": 48882, "tup_inserted": 0, "tup_updated": 0, "tup_deleted": 0, ...

(Also in the text output (as percentages), but I assume you want to the use the JSON output to feed the numbers to Zabbix.)

Slow query count:

Would pg_stat_statements https://www.postgresql.org/docs/current/static/pgstatstatements.html help? Adding that is on the roadmap.

Connections used for replication:

The entire list of connections, with application names (eg walreceiver for a streaming replication client) is available. The currently active replication destinations (from pg_stat_replication) are also there.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/rapidloop/pgmetrics/issues/1#issuecomment-370412818, or mute the thread https://github.com/notifications/unsubscribe-auth/ABmI5hwwwrIx6zmuZC0UBLd0gCJ0eoa7ks5tbTgGgaJpZM4ScMh_ .

mdevan commented 6 years ago

pgmetrics was written with the idea of scripts/tools further processing it's JSON output, so that you can invoke your tool like:

pgmetrics -f json | send-to-zabbix

where send-to-zabbix will:

import json, sys
data = json.loads(sys.stdin.read())
# process data and send to zabbix

Happy to link to your script from pgmetrics.io if it is generally usable for sending pgmetrics output to zabbix.

Tracking the pg_stat_statements as #4. Closing this one, feel free to open another one for any other requests.

rapidloop / pgmetrics