PostgreSQL Monitoring Within Red Hat OpenShift Container Platform
In this blog post, we will discuss how PostgreSQL metrics are collected and stored as part of the Crunchy Container Suite project running within the Red Hat OpenShift Container Platform. Crunchy Data is a Red Hat Technology Connect and OpenShift Primed partner.
The Crunchy Container Suite is an open source collection of docker container images designed to run and manage PostgreSQL in a cloud-native environment. The Crunchy Container Suite can be found within this Github repository: https://github.com/crunchydata/crunchy-containers
The central concern of the Crunchy Containers started with providing a solid open source set of PostgreSQL database containers, but we quickly found that customers would also want a means of monitoring their provisioned PostgreSQL database containers. PostgreSQL has unique metrics which allows a DBA to gain insight into the health of their databases. The Crunchy Container Suite takes advantage of the Prometheus time series data store for metric collection. Crunchy is pleased that the Prometheus project has released Prometheus 1.0 which is now deployed in the 1.2.2 version of the crunchy-prometheus container described in this blog.
For this purpose, Crunchy developed the following containers:
- crunchy-collect - collects 32 different PostgreSQL metrics from a database container
- crunchy-grafana - provides a web based graphing dashboard for collected PostgreSQL metrics, Grafana connects to a Prometheus data source to query metrics
- crunchy-prometheus - contains Prometheus 1.0 for metrics collection
- crunchy-promgateway - contains a Prometheus Push Gateway
These containers are pre-built and can be found on DockerHub at the following location: https://hub.docker.com/u/crunchydata/dashboard/
Collecting Metrics
The crunchy-collect container can be placed within a database pod to begin metrics collection. An example of a pod that includes the crunchy-collect container is here. In Figure 1, metrics are collected and pushed to the Prometheus Push Gateway where they will be scraped on a scheduled basis by the Prometheus server.
Figure 1 - Metrics Collection
The Prometheus server is configured to treat the Prometheus Push Gateway as a Target on a 15 second interval. The Prometheus configuration file is found in the following location:
https://github.com/CrunchyData/crunchy-containers/blob/master/conf/prometheus/prometheus.yml
Prometheus provides a web console where you can query the metrics directly as shown in Figure 2.
Figure 2 - Prometheus Web Console Showing PostgreSQL Metric
The Prometheus Push Gateway also has a web console that shows the collected metrics ready to be scraped by the Prometheus server. A sample screenshot of the Prometheus Push Gateway web console is shown in Figure 3.
Figure 3 - Prometheus Push Gateway Web Console Showing PostgreSQL Metrics
The grafana graphing product is included in the crunchy-grafana container. Grafana lets you specify the Prometheus server as a data source. Its main feature is to let the user define custom graphs which use the Prometheus data source as the source for metrics. The Grafana web console is shown in Figure 4.
Figure 4: Grafana Dashboard
PostgreSQL Metrics
Currently, the crunchy-collect container collects roughly 30 different types of PostgreSQL metrics from a running database container, a sample of the metrics is shown in the following table:
| Metric Name | Purpose |
|---|---|
| crunchy_connections | the number of active connections |
| crunchy_databasesize | the size in Megabytes of a database |
| crunchy_tup_fetched | Count of tuples selected |
| crunchy_tup_inserted | Count of tuples inserted |
| crunchy_connectionutil | The pct utilization of max connections |
| crunchy_temp_files | Number of temp files |
About Crunchy Data
Crunchy Data is a leading provider of trusted open source PostgreSQL and PostgreSQL support, technology and training. Crunchy Data is the provider of Crunchy Certified PostgreSQL, an open source PostgreSQL 9.5 distribution including popular extensions such as PostGIS and enhanced audit logging capability. Crunchy Certified PostgreSQL is currently the only open source database Common Criteria certified at the EAL2+ level.
When combined with Crunchy’s Secure Enterprise Support, Crunchy Certified PostgreSQL provides enterprises with an open source and trusted relational database management solution backed by enterprise support from leading experts in PostgreSQL technology. For enterprises requiring dedicated PostgreSQL support, Crunchy provides on-premise PostgreSQL professional services and PostgreSQL training. Learn more at www.crunchydata.com
