| Category: Ruby / Database Tools |
| Watchers: 91 |
| Star: 7.2k |
| Fork: 421 |
| Last update: Nov 29, 2023 |
A performance dashboard for Postgres - health checks, suggested indexes, and more
PgHero can be installed as a standalone app or a Rails engine.
Also check out:
A big thanks to Craig Kerstiens and Heroku for the initial queries and Bootswatch for the theme
Know a bit about PostgreSQL? Suggestions are greatly appreciated.
Can we Ignore walsender process queries ?
Logical replication processes are walsender processes that can have the appearance of a long-running query if not specially treated. These should be ignored as they will always show up at the top of the slow query list otherwise. They have backend_type "walsender" (for 10+) and can be found using pg_stat_replication for 9.6.
Add a new tab that shows blocked and blocking queries. Related issue: https://github.com/ankane/pghero/issues/183
Alert on index page:
The new tab:
Hi,
After I have identified and fixed the slow query, the query still appears in the slow query list, causing some confusion. Is it possible to have it reflect immediately.
Thanks!
So I thought I was doing it right, but Im not seeing anything from the query history outside of pgadmin and pghero queries... Maybe my docker-compose command is wrong? I was trying to do a PR (https://github.com/ankane/pghero/pull/324) to explain better how to capture stats and table size... table size seems to be working, but the query portion isn't capturing most of the queries?
EX: I run this 10 times, all are 300+ms... but I dont see this in my data when I check...
This is the command Im running, maybe I need to change this? It runs and lists all of my DBs as expected... and I am seeing other queries for other DBs, but not for this one?
docker-compose run -e DATABASE_URL=postgres://user:pw@ipaddr/postgres pghero bin/rake pghero:capture_query_stats
When I query the table,, this is all I'm seeing and it didn't capture the queries I ran in the first SS?
This is my .sh I run as a cron job to capture every 5 minutes.. I prune after since this creates a new container every time its run.
cd /opt
docker-compose --env-file /opt run pghero bin/rake pghero:capture_query_stats
docker system prune -f
docker-compose.yml
pghero:
image: ankane/pghero
container_name: pghero
restart: unless-stopped
ports:
- 8087:8080
environment:
TZ: America/Chicago
PGHERO_STATS_DATABASE_URL: postgres://redacted@database/postgres
volumes:
- /mnt/ssd/docker/containers/pghero/pghero.yml:/app/config/pghero.yml
pghero.yml
main:
url: postgres://redacted@database/postgres
tesla:
url: postgres://redacted@database/teslamate
nextcloud:
url: postgres://redacted@database/nextcloud
homeassistant:
url: postgres://redacted@database/homeassistant```
Does EXPLAIN ANALYZE apply changes on the table?
For example: if I run a delete on a DB does that directly apply on the DB? if so we are looking for a way where we can restrict. people doing so.
Can you share your thoughts?
I was reading through this documentation which does say that it applies on the DB. The below statement confused me a bit
If you wish to use EXPLAIN ANALYZE on an INSERT, UPDATE, DELETE, CREATE TABLE AS, or EXECUTE statement without letting the command affect your data, use this approach
Ideas
google-cloud-monitoring-v3 gempg_stat_statements no longer has total_time)pg_stat_statements version instead of Postgres version for query_stats - pg_stat_statements_version branchHold
leader_pid to running_queries for Postgres 13+ (leader_pid is not reset after query completes)Probably not
core server and possibly contrib module issues from this data with the gem). Downside: could give false sense of security since it'll be outdated.The Maintenance tab is a huge benefit to us when folks are checking to see if a table might have stopped being auto-vacuumed, auto-analyzed, etc. We have > 250 tables, so it can be difficult to spot problematic tables without reading every row.
It would be really cool if you could sort the tables on this page by the last vacuumed, last analyzed, and dead rows columns to more quickly find problems.
(Also, making dead_rows=true the default would be great, it's super useful!)
I'd be happy to do a PR for this, but I wanted to check if there was interest before doing so.
Hi,
In case PostgreSQL used by different users, then on the page with queries all statements mixed for different users. Is it possible to add filter by user on tab with queries? Somewhere here
Thanks!
I recently attempted an upgrade of Postgres on Heroku, and had the process fail because of this schema extension.
The pg:upgrade command was in no hurry to tell me why, so we incurred some longer downtime than was necessary. Then when re-adding this schema I was reminded of a few steps here that did not seem to apply to database instances there, which I figured were worth calling out for others deploying on Heroku.
It also seems that when using https://elements.heroku.com/buttons/pghero/pghero, while still functioning, it installs software that is a bit out of date. Probably not pressing, and I might be the only one using this these days, but given it works so well otherwise it might be cool to check. (I don't think there is a public repo for me to try to update for that)
PostgreSQL DROP INDEX documentation (linked) says this:
Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table
Background:
When duplicate indexes are detected, PgHero recommends removing one of them. To help make this possible, it provides some commands to generate a Rails Migration. The implementation performs a remove_index
PostgreSQL Background:
In PostgreSQL it's possible to remove an index using the CONCURRENTLY keyword. e.g. DROP INDEX index_name CONCURRENTLY
Concurrent modifications are supported by Active Record in the same way as add_index with remove_index, using the combo of disable_ddl_transaction! and algorithm: :concurrently.
This PR adds a new optional configuration parameter to control this behavior. When this parameter is disabled, there is no change to PgHero. It may make sense to disable this by default and allow users to opt in.
When this parameter is enabled (set to true), behavior changes slightly. Additional text is added to the HTML view showing how to generate a remove_column migration.
Links to Rails docs on disabling DDL transactions, and links to PostgreSQL DROP INDEX documentation are part of the PgHero HTML, to help explain the code changes needed.
Ideally, the new migration could be generated with disable_ddl_transaction! set already. Unfortunately it doesn't seem possible currently in Active Record to do this, so it was added to the explanation text area.
Please create a new issue to discuss any ideas or share your own.
kill branchunused_index_threshold branchpg_proctab, file_fdw, or another method)Debian has a special tool for it.
