Django Heroku Tutorial Series:
- Heroku vs AWS Which is Best for Your Django project
- How to deploy Django project to Heroku using Docker
- How to deploy Python project to Heroku in Gitlab CI
- How to use Heroku Pipeline
- Heroku Logs Tutorial
- How to monitor Heroku Postgres using heroku-pg-extras
Django Dokku Tutorial Series:
Introduction
In this Heroku tip, I will talk about how to use heroku-pg-extras plugin to monitor Heroku Postgres.
Background
As you know, Heroku would send some Postgres metric data to the log stream but it is not handy for people to check if the Postgres db is working fine.
heroku-pg-extras can be used to obtain performance data about a Heroku Postgres db, which is very useful when solving the performance issue.
You can check index, lock, connection, cache and other statistics using this plugin.
Postgres has built-in statistics collector which automatically aggregates most of these metrics internally, heroku-pg-extras would help you build SQL query to get the metric value from the predefined statistics views.
Install heroku-pg-extras
$ heroku plugins:install heroku-pg-extras
Please note that this plugin is installed to your Heroku CLI instead of some specific Heroku app, which means, after you install it, you can use it with different Heroku apps in local.
NOTE: If you want to know more about some command, just type $ heroku help pg:<command>
Command list
pg:cache-hit
$ heroku pg:cache-hit
name | ratio
----------------|------------------------
index hit rate | 0.99978961173382966536
table hit rate | 0.99968176561451181147
(2 rows)
This command provides information on the efficiency of the buffer cache, for both index reads (index hit rate) as well as table reads (table hit rate).
If the hit ratio is low, then you might need to consider upgrade your Postgres plan.
pg:index-usage
relname | percent_of_times_index_used | rows_in_table
----------------------------------------------|-----------------------------|---------------
django_session | 99 | 3505
auth_permission | 98 | 451
django_migrations | Insufficient data | 419
This command provides information on the efficiency of indexes, represented as what percentage of query used the index.
A low percentage can indicate under indexing, or wrong data being indexed.
pg:locks
$ heroku pg:locks
This command displays queries that have taken out an exlusive lock on a relation. Exclusive locks typically prevent other operations on that relation from taking place, and can be a cause of "hung" queries that are waiting for a lock to be granted.
pg:outliers
$ heroku pg:outliers
This command displays statements, obtained from pg_stat_statements, ordered by the amount of time to execute in aggregate. This includes the statement itself, the total execution time for that statement, the proportion of total execution time for all statements that statement has taken up, the number of times that statement has been called, and the amount of time that statement spent on synchronous I/O (reading/writing from the filesystem).
Typically, an efficient query will have an appropriate ratio of calls to total execution time, with as little time spent on I/O as possible. Queries that have a high total execution time but low call count should be investigated to improve their performance. Queries that have a high proportion of execution time being spent on synchronous I/O should also be investigated.
pg:calls
$ heroku pg:calls
This command is much like pg:outliers, but ordered by the number of times a statement has been called
pg:blocking
$ heroku pg:blocking
This command displays statements that are currently holding locks that other statements are waiting to be released. This can be used in conjunction with pg:locks to determine which statements need to be terminated in order to resolve lock contention.
pg:seq-scans
$ heroku pg:seq-scans
name | count
----------------------------------------------|-------
django_content_type | 30819
blog_blogcategory | 18928
base_socialmediasettings | 17926
blog_blogpageblogcategory | 15959
blog_blogpagegalleryimage | 14716
base_defaulthomepage | 11568
This command displays the number of sequential scans recorded against all tables, descending by count of sequential scans. Tables that have very high numbers of sequential scans may be underindexed, and it may be worth investigating queries that read from these tables.
pg:long-running-queries
$ heroku pg:long-running-queries
pid | duration | query
-------|-----------------|---------------------------------------------------------------------------------------
19578 | 02:29:11.200129 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1450645 LIMIT 1
19465 | 02:26:05.542653 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1889881 LIMIT 1
19632 | 02:24:46.962818 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1581884 LIMIT 1
This command displays currently running queries, that have been running for longer than 5 minutes, descending by duration.
Conclusion
You can go to heroku-pg-extras homepage to learn more commands.
Django Heroku Tutorial Series:
- Heroku vs AWS Which is Best for Your Django project
- How to deploy Django project to Heroku using Docker
- How to deploy Python project to Heroku in Gitlab CI
- How to use Heroku Pipeline
- Heroku Logs Tutorial
- How to monitor Heroku Postgres using heroku-pg-extras
Django Dokku Tutorial Series:
