Easy Compose for PostgreSQL Performance Stats with pgHero and Docker

Enabling a browsable dashboard for database performance statistics

The first thing you’ll find about PostgreSQL is that every scrap of information about the performance of the database is inside the system tables of PostgreSQL. It’s packed full of stats, but they are not easy to interpret. That’s where utilities such as the web application pgHero come in.

What pgHero gives you

pgHero gives you a browsable dashboard to read current performance statistics. There’s an easy to interpret overview — green is good, not green means you should pay attention — which gives the state of connections, long-running queries, vacuuming health, invalid, duplicate or suggested indexes and slow queries. There’s also views to dig down on various aspects of performances:

• Queries gives a log of execution times for commands.
• Space shows what data sets and indexes are taking how much memory.
• Connections shows where connections are coming from.
• Live Queries show which queries are running and gives a chance to kill all connections.
• Maintenance reports on the last vacuum and analyze times.
• Explain and analyze queries for you.
• Begin to Tune your database with essential settings.

Getting pgHero running quickly

Now, you can install pgHero in various ways but for that quick drop in to do some diagnostics, I recommend using the Docker route. There’s already a Docker image ready to roll out there, so, assuming you have Docker installed (if not, look at the Docker site for instructions) then let’s get going…

What you’ll need is your connection string for your PostgreSQL deployment. Find that by going to the service instance on the IBM Cloud dashboard, select Manage, and then look on the Overview for the Connection Strings:

Finding the connection string on the Manage Overview

For example, the connection string here looks like this:

postgres://admin:SECRETPASSWORD@sl-eu-lon-2-portal.7.dblayer.com:23961/compose

Once you have that, you can assemble your Docker run command. The pgHero Docker page shows the command to run as:

docker run -ti -e DATABASE_URL=postgres://user:password@hostname:5432/dbname -p 8080:8080 ankane/pghero

So just switch out the DATABASE_URL value for your own to give (in our example’s case):

docker run -ti -e DATABASE_URL=postgres://admin:SECRETPASSWORD@sl-eu-lon-2-portal.7.dblayer.com:23961/compose -p 8080:8080 ankane/pghero

The parameters, after run, in this command are:

• -ti to keep the docker session attached so it can be halted easily with Ctrl-C.
• -e to set an environment variable (DATABASE_URL) inside the container.
• -p 8080:8080 to map the containers port 8080 to the hosts port 8080
• ankane/pghero the name of the image we want to use to populate this Docker container.

Hit return and, if it’s your first time running the command, the various parts of the Docker image for pghero will download. Once done, you’ll see something like this…

[5] Puma starting in cluster mode…
[5] * Version 3.11.0 (ruby 2.4.2-p198), codename: Love Song
[5] * Min threads: 1, max threads: 16
[5] * Environment: development
[5] * Process workers: 3
[5] * Preloading application
[5] * Listening on tcp://0.0.0.0:8080
[5] Use Ctrl-C to stop
[5] — Worker 0 (pid: 7) booted, phase: 0
[5] — Worker 1 (pid: 14) booted, phase: 0
[5] — Worker 2 (pid: 18) booted, phase: 0

Indicating the app is up and running. Bring up your browser and navigate to http://localhost:8080/ to see pgHero’s overview.

pgHero Overview

All’s well and from here we can begin exploring pgHero’s reports. Take note of that warning at the top of the page; don’t use pgHero in a development environment and extrapolate from that to your production environment. If anything, consider running pgHero against your production environment to gather statistics if you can.

A side note: If pgHero fails to connect, you’ll likely only see a lot of traceback from the Ruby stack in the terminal. It is, unfortunately, not good at reporting failed connections beyond that.

A second side note: If it’s a new, fresh database instance, you may be prompted to enable Query Stats. Simply click the button.

You can configure historical query and space stats too, allowing you to browse through collected history. The collection is handled by running tasks every 5 minutes and day. Details of those tasks and the tables needed to support them are in the pgHero Docker page.

In this form, pgHero makes a great quick-check tool for IBM Cloud Compose for PostgreSQL, offering results in easily consumable form. Try it today.

Originally published on Compose.com/articles on February 8, 2018.