Thursday, March 14, 2013

Heroku plugin providing insight into the Postgres database for your application

Heroku provides a lovely plugin called heroku-pg-extras which can provide some insight into the inner workings of the postgres databases connected to your application. By default it will hit the database listed in the DATABASE_URL variable for Heroku. However you can specify the database using the database color url; e.g. HEROKU_POSTGRES_PURPLE_URL.

The plugin does the following:
  • cache_hit - calculates your cache hit rate (effective databases are at 99% and up)
  • index_usage - calculates your index hit rate (effective databases are at 99% and up)
  • ps - view active queries with execution time
  • locks - display queries with active locks
  • blocking - display queries holding locks other queries are waiting to be released
  • kill - -f,--force; terminates the connection in addition to cancelling the query
  • total_index_size - show the total size of the indexes in MB
  • index_size - show the size of the indexes in MB descending by size
  • seq_scans - show the count of seq_scans by table descending by order
  • long_running_queries - show queries taking longer than 5 minutes 
  • bloat - show table and index bloat in your database ordered by most wasteful
  • mandlebrot - show the mandelbrot set
These are based on the stats collections process used by postgres which has its own body of literature in collection and analysis starting with their main documentation. It may be worth opening a ticket with Heroku to clear the stats first as they accumulate over time. Changes to the application will not be readily apparent if you still have the stats from the previous app state. Therefore you may want to open a ticket with Heroku to clean the stats (you need to be a superuser to do this yourself and Heroku does not give you superuser access).

Disclaimer: I am a contributer to the project

No comments:

Post a Comment