• Stars
    star
    588
  • Rank 74,013 (Top 2 %)
  • Language
    Python
  • License
    Apache License 2.0
  • Created almost 9 years ago
  • Updated about 1 year ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Tool that extracts and provides metrics on your PostgreSQL database

Spotify PostgreSQL Metrics

Service to extract and provide metrics on your PostgreSQL database.

This tool is a CLI (command line) tool that can be called to extract statistics and create metrics from your PostgreSQL database cluster. CLI is runnable in long running process mode, which will periodically send the gathered metrics forward.

The default metrics format is a Metrics 2.0 compatible JSON, which is created by a set of functions listed in the configuration file.

The extracted metrics can be printed out as direct output of the CLI tool, or sent out of the host the postgresql-metrics process is running in using FFWD.

The FFWD format is plain JSON sent over a UDP socket, so you can use whatever UDP socket endpoint, which understands JSON, to consume the metrics.

Prerequisites

The versions mentioned below are tested to work, but the code should work on many unlisted versions as well. Just add an issue or send a pull-request with missing prerequisites, if you test and confirm postgresql-metrics to work on other versions of the mentioned technologies.

  • Python 3.5 or later
  • PostgreSQL 9.3 or later
  • Debian based distribution with systemd (packaging requirement only)

Building and Installing

You can build a Debian package by running the following in project root:

dpkg-buildpackage -us -uc -b

Notice that postgresql-metrics includes by default a systemd service to run as long running process, pushing metrics to FFWD as gathered. You need to stop the long running process after installing the package for configuration.

sudo systemctl stop postgresql-metrics

If you want to debug the process. For the systemd service, you can run sudo journalctl -u postgresql-metrics to see the service's log.

Edit Configuration

Edit the configuration in /etc/postgresql-metrics/postgresql-metrics.yml and in /etc/postgresql-metrics/default/postgresql-metrics.yml. Notice that the configuration in the default folder will be overwritten value by value from the configuration in the configuration root.

If you are not interested in using the default configuration overwriting functionality, just delete one of the configurations mentioned above, and keep using a single configuration file.

Edit at least the values under postgres section in the configuration to match your PostgreSQL cluster setup. Remember also to list the databases you want to gather metrics from. By database in this context we mean a database name you created within your PostgreSQL cluster.

Prepare Database

Before starting to extract the defined metrics, you need to setup your database cluster using the prepare-db CLI call. This will create the required extensions for your database, and a few functions that are used by the statistics gathering queries from the metrics process. The configured metrics user will be also granted access to the created statistics functions and views.

You need to provide administrator user to the prepare-db call, which the tool is kind enough to ask. You don't need to provide credentials if you are running the prepare-db with a local user that is configured to be trusted locally by the PostgreSQL cluster (in pg_hba.conf), and is a super user, like the default postgres user created by some distribution packages (e.g. Debian). You can do the prepare-db call e.g. as follows:

sudo su -c "postgresql-metrics prepare-db" postgres

It is safe to call the prepare-db multiple times for the same database (the call is idempotent).

Grant Access for Metrics User

In addition to granting access to the statistics gathering functions and views within your PostgreSQL cluster (previous step), you need to also add access to the metrics user into the host based access file (pg_hba.conf).

Add one line per database you are monitoring into the end of the pg_hba.conf file for your cluster:

host my_database_name postgresql_metrics_user 127.0.0.1/32 md5  # metrics user access

Replace the my_database_name and postgresql_metrics_user with the values you configured into the postgresql-metrics configuration in Edit Configuration step above.

You need to reload (or restart) your server after editing pg_hba.conf for the changes to take effect.

Getting Metrics

After you have the postgresql-metrics configured, and the database prepared, you can print out all the metrics that will be extracted from your database by calling:

postgresql-metrics all

You need to call the command above as a user that has access to the WAL log directory under PostgreSQL, or the metric gathering WAL file amounts will fail. Single failed metric calls will not prevent the rest of gathering process.

You can also start the long running process again, if using systemd:

sudo systemctl start postgresql-metrics

Explaining the Gathered Metrics

This section explains the metrics we gather using this tool.

Notice that there are many system specific metrics that you should gather in addition to the Postgres specific metrics, for example:

  • CPU usage
  • Network usage, sent / received bytes per related network interface
  • Memory usage
  • Disk I/O operations
  • I/O await times
  • Disk usage and free space left

Database Specific Metrics

Called once per configured database inside your Postgres cluster.

  • get_stats_disk_usage_for_database: This metric shows the size of each of your databases in bytes. Don't forget to measure the total disk usage of the disk your data directory resides in as well.

  • get_stats_tx_rate_for_database: Shows the rate of transactions executed per second since the last call of this function. Shows the rate of executed rollbacks also separately.

  • get_stats_seconds_since_last_vacuum_per_table: This metric shows the amount of time passed in seconds since the last vacuum was run per table in your database.

  • get_stats_oldest_transaction_timestamp: This metric shows the time the longest running transaction has been open in your database. This should be usually close to zero, but sometimes, for example when an administrator forgets to close a maintenance connection, you will see this value going up. Long running transactions are bad for your database, so fix the issue as soon as you see this metric increase.

  • get_stats_index_hit_rates: This metric shows you the amount of reads hitting the table indexes versus the amount of reads requiring sequential scan through the table. Depending on your table, the amount of data, and the created indexes, the index hit rate varies. You should understand your data well enough to know when high index usage is desirable to low index usage.

  • get_stats_table_bloat: This metric shows the amount of wasted space in the database table due to the MVCC process. Deletes and updates to the table just mark the obsolete data free, but does not really delete it. Vacuums do free some of this wasted data, but to get totally rid of table bloat you must re-create the table with vacuum full. The current implementation of table bloat metric is rather heavy, so you might want to disable it in case you see issues with it.

Database Cluster (Global) Metrics

Called once per your Postgres cluster.

  • get_stats_client_connections: This metrics shows the amount of connections open to the database at the moment. The actual metrics gathering connections should be visible here as well. Notice that having more than a hundred connections open is usually a bad thing. Consider using a connection pooler, like pgbouncer.

  • get_stats_lock_statistics: This metric shows locks being waited upon by queries, and the amount of locks granted. In general having any query waiting for locks for any extended period of time is a sign of problems, like heavy lock contention.

  • get_stats_heap_hit_statistics: This metric shows the amount of reads hitting the memory buffers on your cluster, and also the amount of reads hitting the disk (or disk caches on the operating system). Also the heap hit ratio is calculated based on these values.

    Notice that the read amounts are not actual read queries, but the amount of blocks read. You will get good idea of amount of reads hitting your database, when comparing these values with the transaction rate.

  • get_stats_replication_delays: This metric shows the amount of bytes the replication delay is behind master per each slave. If the slave and the master are in synchronous state, the replication delay is zero.

  • get_stats_wal_file_amount: This graph shows the amount of files in your database clusters WAL log directory (pg_wal or pg_xlog). If the WAL file amount starts to suddenly increase, you probably have issues with your WAL archiving process, which might lead to the disk filling up, and you database cluster crashing.

  • get_xid_remaining_ratio, get_multixact_remaining_ratio, get_multixact_members_remaining_ratio: These metric shows the corresponding remaining % of transaction ids ("xid"), multixact ids ("mxid"), and multixact members that are available for postgres to use before exhaustion. Useful for ensuring that the vacuuming is working as intended for your postgres instance.

  • get_multixact_members_per_mxid: This metric emits the number of multixact members there are per multixact ID. A larger number means that it'll be quicker for the multixact members exhaustion to happen (as can be seen in get_multixact_members_usage_ratio).

Short Overview of Python Modules

  • common.py: Contains code common for the whole package, like logging and configuration parsing.
  • default_metrics.py: Is responsible for turning statistics values into the default metrics JSON format.
  • localhost_postgres_stats.py: Functions for statistics extraction from local Postgres data directory. These calls are automatically global from a database cluster perspective.
  • metrics_gatherer.py: Functions for calling the statistics extraction functions and converting the results into correct metrics format. Called from the metrics_logic.py.
  • metrics_logic.py: Contains the CLI tool, initialization, and the scheduling logic.
  • postgres_queries.py: Functions for statistics extraction from the Postgres database.
  • prepare_db.py: Code for preparing your databases for the metrics gathering process.

How to Add More Metrics

If you want to add more metrics into postgresql-metrics tool, you can do it by making the following changes to the source:

  1. If you gather the metric using a Postgres SQL query, add the code into postgres_queries.py, and if you gather the metric by accessing the local Postgres data directory, add the code into localhost_postgres_stats.py.

  2. Write a function for formatting your gathered metric values into wanted format, as is done in default_metrics.py. You can either expand the default metrics, or write your own format into another module.

  3. Write a function into metrics_gatherer.py, which will call the metric extraction functionality you wrote on the first step, and then the metric value formatting function you wrote on the previous step.

  4. Add the name of your metrics gatherer function, written in the previous step, into postgresql-metrics configuration file, with wanted time interval to call the metric gathering function. Notice that you need to add the function into the correct list of functions depending on whether you gather a metric that covers your whole database cluster, or a metric that targets a single database in your cluster. Data directory based metrics must be a 'global' metric.

  5. Update this README with explanation on what your new metric is about.

More Repositories

1

luigi

Luigi is a Python module that helps you build complex pipelines of batch jobs. It handles dependency resolution, workflow management, visualization etc. It also comes with Hadoop support built in.
Python
17,340
star
2

annoy

Approximate Nearest Neighbors in C++/Python optimized for memory usage and loading/saving to disk
C++
12,751
star
3

docker-gc

INACTIVE: Docker garbage collection of containers and images
Shell
5,068
star
4

pedalboard

๐ŸŽ› ๐Ÿ”Š A Python library for audio.
C++
4,907
star
5

chartify

Python library that makes it easy for data scientists to create charts.
Python
3,486
star
6

basic-pitch

A lightweight yet powerful audio-to-MIDI converter with pitch bend detection
Python
2,972
star
7

dockerfile-maven

MATURE: A set of Maven tools for dealing with Dockerfiles
Java
2,737
star
8

docker-maven-plugin

INACTIVE: A maven plugin for Docker
Java
2,652
star
9

scio

A Scala API for Apache Beam and Google Cloud Dataflow.
Scala
2,485
star
10

helios

Docker container orchestration platform
Java
2,097
star
11

web-api-examples

Basic examples to authenticate and fetch data using the Spotify Web API
HTML
1,889
star
12

HubFramework

DEPRECATED โ€“ Spotifyโ€™s component-driven UI framework for iOS
Objective-C
1,863
star
13

apollo

Java libraries for writing composable microservices
Java
1,648
star
14

dh-virtualenv

Python virtualenvs in Debian packages
Python
1,601
star
15

docker-client

INACTIVE: A simple docker client for the JVM
Java
1,426
star
16

docker-kafka

Kafka (and Zookeeper) in Docker
Shell
1,401
star
17

SPTPersistentCache

Everyone tries to implement a cache at some point in their iOS appโ€™s lifecycle, and this is ours.
Objective-C
1,243
star
18

mobius

A functional reactive framework for managing state evolution and side-effects.
Java
1,213
star
19

voyager

๐Ÿ›ฐ๏ธ An approximate nearest-neighbor search library for Python and Java with a focus on ease of use, simplicity, and deployability.
C++
1,175
star
20

sparkey

Simple constant key/value storage library, for read-heavy systems with infrequent large bulk inserts.
C
1,149
star
21

ruler

Gradle plugin which helps you analyze the size of your Android apps.
Kotlin
1,116
star
22

XCMetrics

XCMetrics is the easiest way to collect Xcode build metrics and improve developer productivity.
Swift
1,089
star
23

web-api

This issue tracker is no longer used. Join us in the Spotify for Developers forum for support with the Spotify Web API โžก๏ธ https://community.spotify.com/t5/Spotify-for-Developers/bd-p/Spotify_Developer
RAML
981
star
24

echoprint-codegen

Codegen for Echoprint
C++
948
star
25

snakebite

A pure python HDFS client
Python
858
star
26

heroic

The Heroic Time Series Database
Java
843
star
27

klio

Smarter data pipelines for audio.
Python
831
star
28

XCRemoteCache

Swift
824
star
29

ios-sdk

Spotify SDK for iOS
Objective-C
627
star
30

apps-tutorial

A Spotify App that contains working examples of the use of Spotify Apps API
627
star
31

SPTDataLoader

The HTTP library used by the Spotify iOS client
Objective-C
626
star
32

JniHelpers

Tools for writing great JNI code
C++
587
star
33

Mobius.swift

A functional reactive framework for managing state evolution and side-effects [Swift implementation]
Swift
552
star
34

reactochart

๐Ÿ“ˆ React chart component library ๐Ÿ“‰
JavaScript
548
star
35

dockerfile-mode

An emacs mode for handling Dockerfiles
Emacs Lisp
525
star
36

threaddump-analyzer

A JVM threaddump analyzer
JavaScript
483
star
37

featran

A Scala feature transformation library for data science and machine learning
Scala
465
star
38

android-sdk

Spotify SDK for Android
HTML
449
star
39

echoprint-server

Server for the Echoprint audio fingerprint system
Java
396
star
40

completable-futures

Utilities for working with futures in Java 8
Java
382
star
41

web-scripts

DEPRECATED: A collection of base configs and CLI wrappers used to speed up development @ Spotify.
TypeScript
381
star
42

SpotifyLogin

Swift framework for authenticating with the Spotify API
Swift
346
star
43

ratatool

A tool for data sampling, data generation, and data diffing
Scala
337
star
44

fmt-maven-plugin

Opinionated Maven Plugin that formats your Java code.
Java
309
star
45

spotify-web-api-ts-sdk

A Typescript SDK for the Spotify Web API with types for returned data.
TypeScript
290
star
46

big-data-rosetta-code

Code snippets for solving common big data problems in various platforms. Inspired by Rosetta Code
Scala
287
star
47

trickle

A small library for composing asynchronous code
Java
284
star
48

coordinator

A visual interface for turning an SVG into XY coรถrdinates.
HTML
283
star
49

pythonflow

๐Ÿ Dataflow programming for python.
Python
281
star
50

styx

"The path to execution", Styx is a service that schedules batch data processing jobs in Docker containers on Kubernetes.
Java
266
star
51

cstar

Apache Cassandra cluster orchestration tool for the command line
Python
254
star
52

netty-zmtp

A Netty implementation of ZMTP, the ZeroMQ Message Transport Protocol.
Java
242
star
53

ios-style

Guidelines for iOS development in use at Spotify
241
star
54

confidence

Python
238
star
55

cassandra-reaper

Software to run automated repairs of cassandra
235
star
56

docker-cassandra

Cassandra in Docker with fast startup
Shell
219
star
57

terraform-gke-kubeflow-cluster

Terraform module for creating GKE clusters to run Kubeflow
HCL
209
star
58

basic-pitch-ts

A lightweight yet powerful audio-to-MIDI converter with pitch bend detection.
TypeScript
206
star
59

dns-java

DNS wrapper library that provides SRV lookup functionality
Java
204
star
60

linux

Spotify's Linux kernel for Debian-based systems
C
204
star
61

git-test

test your commits
Shell
202
star
62

SPStackedNav

[DEPRECATED] Navigation controller which represents its content in stacks of panes, rather than one at a time
Objective-C
195
star
63

quickstart

A CommonJS module resolver, loader and compiler for node.js and browsers.
JavaScript
193
star
64

spotify-json

Fast and nice to use C++ JSON library.
C++
191
star
65

dbeam

DBeam exports SQL tables into Avro files using JDBC and Apache Beam
Java
188
star
66

flink-on-k8s-operator

Kubernetes operator for managing the lifecycle of Apache Flink and Beam applications.
Go
180
star
67

lingon

A user friendly tool for building single-page JavaScript applications
JavaScript
162
star
68

bazel-tools

Tools for dealing with very large Bazel-managed repositories
Java
162
star
69

dataenum

Algebraic data types in Java.
Java
161
star
70

magnolify

A collection of Magnolia add-on modules
Scala
158
star
71

async-google-pubsub-client

[SUNSET] Async Google Pubsub Client
Java
157
star
72

gcp-audit

A tool for auditing security properties of GCP projects.
Python
156
star
73

spark-bigquery

Google BigQuery support for Spark, SQL, and DataFrames
Scala
155
star
74

should-up

Remove most of the "should" noise from your tests
JavaScript
150
star
75

flo

A lightweight workflow definition library
Java
146
star
76

folsom

An asynchronous memcache client for Java
Java
145
star
77

missinglink

Build time tool for detecting link problems in java projects
Java
142
star
78

android-auth

Spotify authentication and authorization for Android. Part of the Spotify Android SDK.
HTML
140
star
79

proto-registry

An implementation of the Protobuf Registry API
TypeScript
140
star
80

zoltar

Common library for serving TensorFlow, XGBoost and scikit-learn models in production.
Java
138
star
81

futures-extra

Java library for working with Guava futures
Java
135
star
82

annoy-java

Approximate nearest neighbors in Java
Java
134
star
83

spydra

Ephemeral Hadoop clusters using Google Compute Platform
Java
133
star
84

spotify-web-playback-sdk-example

React based example app that creates a new player in Spotify Connect to play music from in the browse using Spotify Web Playback SDK.
JavaScript
129
star
85

docker-stress

Simple docker stress test and monitoring tools
Python
125
star
86

spotify-tensorflow

Provides Spotify-specific TensorFlow helpers
Python
124
star
87

crtauth

a public key backed client/server authentication system
Python
118
star
88

redux-location-state

Utilities for reading & writing Redux store state to & from the URL
JavaScript
118
star
89

sparkey-java

Java implementation of the Sparkey key value store
Java
117
star
90

github-java-client

A Java client to Github API
Java
114
star
91

realbook

Easier audio-based machine learning with TensorFlow.
Python
109
star
92

rspec-dns

Easily test your DNS with RSpec
Ruby
107
star
93

web-playback-sdk

This issue tracker is no longer used. Join us in the Spotify for Developers forum for support with the Spotify Web Playback SDK โžก๏ธ https://community.spotify.com/t5/Spotify-for-Developers/bd-p/Spotify_Developer
107
star
94

ffwd-ruby

An event and metrics fast-forwarding agent.
Ruby
106
star
95

gimme

Creating time bound IAM Conditions with ease and flair
Python
103
star
96

super-smash-brogp

Sends and withdraws BGP prefixes for fun.
Python
98
star
97

lighthouse-audit-service

TypeScript
93
star
98

noether

Scala Aggregators used for ML Model metrics monitoring
Scala
91
star
99

python-graphwalker

Python re-implementation of the graphwalker testing tool
Python
91
star
100

spotify.github.io

Showcase site for hand-picked open-source projects by Spotify
HTML
88
star