6 ms·
Postgres Health Check and SQL Performance Analysis
- evadne 7y agoBelated. https://github.com/jfcoz/postgresqltuner https://github.com/jfcoz/postgresqltuner
- whycombagator 7y agoRelated. https://github.com/ankane/pghero https://github.com/ankane/pghero
- 1996 7y agoInteresting, but how heavy is it on the database server?
- samokhvalov 7y agoHi, the founder of Postgres.ai here. The footprint is very minimalistic. I have 15+ years of Postgres DBA experience, and what this tool does basically is what I usually do myself with performing Postgres health checks under heavy load. But in automated fashion :) We very carefully choose approaches and queries that we run on production servers. And it is used under heavy loads (dozens of thousands of TPS) daily. There are certain places that can be heavy. For example, if you have 1 million indexes (yes, it happens, sometimes), the SELECT query for bloat analysis will be slow. Actually, with default settings, the tool limits itself setting `statement_timeout = '30s'` (can be adjusted using CLI option `--statement-timeout`). So, in databases with a huge number of objects, you should expect that F004 and F005 reports will be missing.
- mewwts 7y agoThat Joe tool of yours looks really cool!
- matthewaveryusa 7y ago> tested on real-world databases containing 500,000+ tables and 1,000,000+ indexes. Would love to hear about this use case out of curiosity
- KoenDG 7y agohalf a million tables... In how many databases? Spread across how many machines? Using hardware that is how old? It may well be a good project, but statements like that aren't inspiring of confidence.
- samokhvalov 7y agoOne single database. Yes, quite unusual case of multi-tenant database. We needed to use checkup there a while ago so made some optimizations. I agree with you, the statement is not 100% clear. We will double-check it (and maybe will simulate the case in CI), and will improve the statement. Thank you.
- tomnipotent 7y agoI'm guessing those numbers are cumulative across all the databases scanned, not within one.
- samokhvalov 7y agoNo, it was a single real-world database. Just in case if it looks surprisingly that Postgres can deal with such numbers of objects, I suggest checking this out: https://www.pgcon.org/2013/schedule/attachments/283_Billion_Tables_Project-PgCon2013.pdf https://www.pgcon.org/2013/schedule/attachments/283_Billion_...
- zlepper 7y agoThis looks really really nice. Does it also work with something like Citus?
- nbrempel 7y agoThis seems like a great idea. I used to have a collection of magic Postgres queries that would give me metrics for optimizing data models and indices. I’m surprised there isn’t a visual tool you can bolt into your Postgres install and get valuable metrics out.
- ahachete 7y agoWe have been using postgres-checkup for quite a while. Indeed, we have standardized on it as the basis on which we elaborate the health check and performance report analysis that we provide to our support customers. It is a great project, constantly improving. Keep up with the work!
- craigg 7y agoAs an Engineering Manager for the recently formed Database team here at GitLab, this tool has proven to be incredibly valuable. We floated the idea of running this on a regular basis back in April https://gitlab.com/gitlab-com/gl-infra/infrastructure/issues/6602 https://gitlab.com/gitlab-com/gl-infra/infrastructure/issues... and now we have daily reports that we review https://gitlab.com/gitlab-com/gl-infra/infrastructure/issues?scope=all&utf8=%E2%9C%93&state=opened&search=postgres-checkup https://gitlab.com/gitlab-com/gl-infra/infrastructure/issues... samokhvalov helped us to get this set up
- sciurus 7y agoThat's a huge wall of text to digest every time the report is run. It would be nice if there was a diff mode that could summarize what changed between two runs.
- NicoJuicy 7y agoCTRL + F: Recommendations Find next occurence 30 times and look when it's filled in. If all is good, i think you need a minute or so.