ankane / pghero Goto Github PK
View Code? Open in Web Editor NEWA performance dashboard for Postgres
License: MIT License
A performance dashboard for Postgres
License: MIT License
I am using Heroku with Ruby 2.1.5 and looking at the below snapshot of the connections you can see that these can't be my connections.
i.e. there is code running in Ruby 1.9 and 2.2 and also sidekiq which I am not using.
Should I expect to see connections from other apps not belonging to me when using this gem on heroku and the hobby ($9/mth) database?
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and other guides.
If you have good resources, please share!
When I try setting the value of shared_preload_libraries to pg_stat_statements in our Parameter Group, I get:
Error saving: Cannot modify a default parameter group. (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterValue; Request ID: ff5b3371-26b2-11e6-8709-e93cdd73ea37)
SELECT n.nspname, c.relname
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n,
pg_catalog.pg_index i
WHERE (i.indisvalid = false OR i.indisready = false) AND
i.indexrelid = c.oid AND c.relnamespace = n.oid AND
n.nspname != 'pg_catalog' AND
n.nspname != 'information_schema' AND
n.nspname != 'pg_toast'
Hi there,
I've installed pghero in my CentOS from YUM and I created the table as mentioned on the docs:
However, I created the pghero_query_stats table in another SCHEMA. And now I'm getting that error when performing:
ERROR:
ERROR: relation "pghero_query_stats" does not exist
sudo pghero run rake pghero:capture_query_stats
Is there anything I need to do to allow pghero to see the other SCHEMA?
Cheers
I a missing a brief introduction to pghero. What is it all about?
Please update the frontpage: https://github.com/ankane/pghero
After running
PgHero.disable_query_stats
the dashboard works okey
After running
PgHero.enable_query_stats
the dasboard give the following error
NoMethodError: undefined method `[]' for nil:NilClass
- 157 non-project frames
1
File "/app/vendor/bundle/ruby/2.1.0/gems/pghero-1.2.4/lib/pghero/methods/suggested_indexes.rb" line 121 in block (2 levels) in best_index_helper
2
File "/app/vendor/bundle/ruby/2.1.0/gems/pghero-1.2.4/lib/pghero/methods/suggested_indexes.rb" line 121 in map
3
File "/app/vendor/bundle/ruby/2.1.0/gems/pghero-1.2.4/lib/pghero/methods/suggested_indexes.rb" line 121 in block in best_index_helper
4
File "/app/vendor/bundle/ruby/2.1.0/gems/pghero-1.2.4/lib/pghero/methods/suggested_indexes.rb" line 103 in each
5
File "/app/vendor/bundle/ruby/2.1.0/gems/pghero-1.2.4/lib/pghero/methods/suggested_indexes.rb" line 103 in best_index_helper
6
File "/app/vendor/bundle/ruby/2.1.0/gems/pghero-1.2.4/lib/pghero/methods/suggested_indexes.rb" line 17 in suggested_indexes_by_query
It's important to says it's a production environment of heroku. Using a postgresql version 9.3
I love using pghero. Are there plans to have a locks section, so I can see the current locks in the database which queries are affected ?
[25722] ! Unable to load application: URI::InvalidURIError: the scheme postgres does not accept registry part: postgres:H!HxDu3dg07@[email protected]:5432 (or bad hostname?)
/opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/uri/generic.rb:214:in `initialize': the scheme postgres does not accept registry part: postgres:F!#D3vds056@3%[email protected]:5432 (or bad hostname?) (URI::InvalidURIError)
from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/uri/common.rb:218:in `new'
from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/uri/common.rb:218:in `parse'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:36:in `initialize'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:270:in `new'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:270:in `resolve_url_connection'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:258:in `resolve_hash_connection'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:215:in `resolve_connection'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:139:in `resolve'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:151:in `block in resolve_all'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:150:in `each'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/connection_specification.rb:150:in `resolve_all'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/connection_handling.rb:69:in `resolve'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/core.rb:46:in `configurations='
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/core.rb:48:in `block in <module:Core>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activesupport-4.2.3/lib/active_support/concern.rb:120:in `class_eval'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activesupport-4.2.3/lib/active_support/concern.rb:120:in `append_features'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/base.rb:284:in `include'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/base.rb:284:in `<class:Base>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/base.rb:269:in `<module:ActiveRecord>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/activerecord-4.2.3/lib/active_record/base.rb:26:in `<top (required)>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/pghero-1.1.0/lib/pghero.rb:8:in `<module:PgHero>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/pghero-1.1.0/lib/pghero.rb:6:in `<top (required)>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler/runtime.rb:76:in `require'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler/runtime.rb:76:in `block (2 levels) in require'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler/runtime.rb:72:in `each'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler/runtime.rb:72:in `block in require'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler/runtime.rb:61:in `each'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler/runtime.rb:61:in `require'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/bundler-1.7.12/lib/bundler.rb:134:in `require'
from /opt/pghero/config/application.rb:2:in `<top (required)>'
from /opt/pghero/config/environment.rb:2:in `require'
from /opt/pghero/config/environment.rb:2:in `<top (required)>'
from config.ru:3:in `require'
from config.ru:3:in `block in <main>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:55:in `instance_eval'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:55:in `initialize'
from config.ru:in `new'
from config.ru:in `<main>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:49:in `eval'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:49:in `new_from_string'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:40:in `parse_file'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/configuration.rb:102:in `load_rackup'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/configuration.rb:69:in `app'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/runner.rb:113:in `load_and_bind'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/cluster.rb:302:in `run'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/cli.rb:216:in `run'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/bin/puma:10:in `<top (required)>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/bin/puma:23:in `load'
from /opt/pghero/vendor/bundle/ruby/2.0.0/bin/puma:23:in `<main>'
Would be very useful to have schema shown for the Index Usage listing since table names can be duplicated across schemas.
When I try to view the RDS CPU info using my PGHERO_ACCESS_KEY_ID, PGHERO_SECRET_ACCESS_KEY and PGHERO_DB_INSTANCE_IDENTIFIER, I get:
["note_type", "Error"], ["note", "NoMethodError: undefined method `match' for nil:NilClass\nDid you mean? catch"], ["action", "pg_hero/home#connection_stats"], ["status", 500]
[26130] * Preloading application
[26130] ! Unable to load application: NameError: uninitialized constant ActionView::Helpers::ActiveModelHelper
/opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionview-4.2.3/lib/action_view/helpers.rb:40:in `<module:Helpers>': uninitialized constant ActionView::Helpers::ActiveModelHelper (NameError)
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionview-4.2.3/lib/action_view/helpers.rb:4:in `<module:ActionView>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionview-4.2.3/lib/action_view/helpers.rb:3:in `<top (required)>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionview-4.2.3/lib/action_view/base.rb:5:in `<top (required)>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionview-4.2.3/lib/action_view/view_paths.rb:1:in `<top (required)>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionpack-4.2.3/lib/abstract_controller/rendering.rb:4:in `<top (required)>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionpack-4.2.3/lib/action_controller/base.rb:204:in `<class:Base>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionpack-4.2.3/lib/action_controller/base.rb:164:in `<module:ActionController>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/actionpack-4.2.3/lib/action_controller/base.rb:5:in `<top (required)>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/pghero-1.1.0/app/controllers/pg_hero/home_controller.rb:2:in `<module:PgHero>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/pghero-1.1.0/app/controllers/pg_hero/home_controller.rb:1:in `<top (required)>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/engine.rb:472:in `block (2 levels) in eager_load!'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/engine.rb:471:in `each'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/engine.rb:471:in `block in eager_load!'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/engine.rb:469:in `each'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/engine.rb:469:in `eager_load!'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/engine.rb:346:in `eager_load!'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/application/finisher.rb:56:in `each'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/application/finisher.rb:56:in `block in <module:Finisher>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/initializable.rb:30:in `instance_exec'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/initializable.rb:30:in `run'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/initializable.rb:55:in `block in run_initializers'
from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:150:in `block in tsort_each'
from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:183:in `block (2 levels) in each_strongly_connected_component'
from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:219:in `each_strongly_connected_component_from'
from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:182:in `block in each_strongly_connected_component'
from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:180:in `each'
from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:180:in `each_strongly_connected_component'
from /opt/pghero/vendor/ruby-2.0.0/lib/ruby/2.0.0/tsort.rb:148:in `tsort_each'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/initializable.rb:54:in `run_initializers'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/railties-4.2.3/lib/rails/application.rb:352:in `initialize!'
from /opt/pghero/config/environment.rb:5:in `<top (required)>'
from config.ru:3:in `require'
from config.ru:3:in `block in <main>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:55:in `instance_eval'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:55:in `initialize'
from config.ru:in `new'
from config.ru:in `<main>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:49:in `eval'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:49:in `new_from_string'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/rack-1.6.4/lib/rack/builder.rb:40:in `parse_file'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/configuration.rb:102:in `load_rackup'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/configuration.rb:69:in `app'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/runner.rb:113:in `load_and_bind'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/cluster.rb:302:in `run'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/lib/puma/cli.rb:216:in `run'
from /opt/pghero/vendor/bundle/ruby/2.0.0/gems/puma-2.11.3/bin/puma:10:in `<top (required)>'
from /opt/pghero/vendor/bundle/ruby/2.0.0/bin/puma:23:in `load'
from /opt/pghero/vendor/bundle/ruby/2.0.0/bin/puma:23:in `<main>'
Fedora 22.
Installed from your rpm repo.
pghero-1.1.0-1435724100.41a868c.fedora20.x86_64
It is clear from the growing pghero_query_stats DB that this can go back further than a day - please make the sliders go back further. Perhaps to a week?
After trying to enable Query Stats while using the Rails plugin and Postgres RDS, I get:
FATAL: remaining connection slots are reserved for non-replication superuser connections
I'm not sure how connection pooling happens inside PgHero, but it doesn't seem to survive for very long when running behind PgBouncer. Ironic, yes.
Is there an internal pool being used for connections? I'm mounting it on a rails app and the rest of the app works fine, so I'm guessing PgHero is doing its own thing with regards to connection pooling. PgHero stops being able to use connections after a few hours, and works again only after a restart.
PgHero looks awesome. Is there any chance to make it available on OS X for noobs like me, e.g. by means of a homebrew formula?
I tried to enable query stats from the dashboard, but after clicking the button got a 500 server error. I checked the logs and saw ERROR: pg_stat_statements must be loaded via shared_preload_libraries
. I decided I don't want to troubleshoot the issue as outlined in the help guide and would rather just turn off query stats.
The problem is I have no idea how to do this. The server replies with a 500 internal server error. I can't access the dashboard at all. I tried uninstalling and re-installing pghero to no avail. Where's the undo button?
Perhaps worth adding functionality to identify redundant indexes, such as when a column is covered by both a compound key and an individual key.
The active_record_doctor Rubygem does this: https://github.com/gregnavis/active_record_doctor#removing-extraneous-indexes
Most queries showing in the Queries page seem to be truncated - is this a PgHero thing? Makes it impossible to figure out what the actual query was - multiple queries start the same way :-/
There are a number of optional settings to pass to an explain
:
www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77705
How you do you think these would best fit into the UI? Perhaps we could have a check box for each?
It would be nice if SQL queries were automatically indented, and had SQL syntax highlighting applied, so they're easier to identify.
What it currently looks like: (note the first query is a raw string in our app, which is why it's indented)
I'd be happy to implement this, if you think it's a worthwhile change @ankane
I get the following error when running on Heroku with gem version 0.1.3:
2014-08-05T19:52:57.111637+00:00 app[web.1]: App 132 stdout: Started GET "/pghero/" for XXX.XXX.XXX.XXXX at 2014-08-05 19:52:56 +0000
2014-08-05T19:52:57.111684+00:00 app[web.1]: App 132 stdout:
2014-08-05T19:52:57.111689+00:00 app[web.1]: App 132 stdout: ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR: column "pid" does not exist
2014-08-05T19:52:57.111691+00:00 app[web.1]: App 132 stdout: LINE 1: SELECT pid, state, application_name AS source, age(now(), xa...
2014-08-05T19:52:57.111695+00:00 app[web.1]: App 132 stdout: ^
2014-08-05T19:52:57.111697+00:00 app[web.1]: App 132 stdout: : SELECT pid, state, application_name AS source, age(now(), xact_start) AS duration, waiting, query, xact_start AS started_at FROM pg_stat_activity WHERE query <> '<insufficient privilege>' AND state <> 'idle' AND pid <> pg_backend_pid() AND now() - query_start > interval '5 minutes' ORDER BY query_start DESC):
2014-08-05T19:52:57.111736+00:00 app[web.1]: App 132 stdout: app/middleware/force_login_page.rb:19:in `call'
2014-08-05T19:52:57.111738+00:00 app[web.1]: App 132 stdout: app/middleware/force_ssl.rb:8:in `call'
If I do a select *
, this is what I see:
{"datid"=>"16385", "datname"=>"db0jgg0q3e3c6s", "procpid"=>"293", "usesysid"=>"16386", "usename"=>"collectd", "application_name"=>"", "client_addr"=>nil, "client_hostname"=>nil, "client_port"=>nil, "backend_start"=>nil, "xact_start"=>nil, "query_start"=>nil, "waiting"=>nil, "current_query"=>"<insufficient privilege>"}
Could this be because Heroku hasn't enabled pg_stat_statements
? Here is what I get when I run PgHero.enable_query_stats
.
(2.0ms) CREATE EXTENSION pg_stat_statements
ActiveRecord::StatementInvalid: PG::InvalidParameterValue: ERROR: extension "pg_stat_statements" is not whitelisted
DETAIL: Installing the extension "pg_stat_statements" failed, because it is not on the whitelist of user-installable extensions.
HINT: Your system administrator has allowed users to install certain extensions. See: SHOW extwlist.extensions;
There is also a gem which aims the same purpose: https://github.com/kirs/pg_web_stats
Would you look at it and say pros and cons?
First off, thank you for an awesome product! It's hard to imagine going back to a world before...
On to the unfortunate issue:
pghero setup on trusty 14.04 with apt.postgresql.org postgresql-9.3; no issue running for over a week.
Upgraded from 9.3 to 9.5-alpha1 via apt.postgresql.org apt and pg_dump
/pg_restore
. All other application behavior is as expected without error.
However, a segmentation fault occurs on the following query issued by pghero. Seems to be related to clicking the "Queries" tab in pghero. Cannot provide the core dump due to proprietary information.
This seems like misbehavior from postgres per se, but since it affects pghero and you are likely more capable of understanding and reproducing with shareable core dump for core development, I am filing here to start.
2015-07-06 00:37:52 UTC [24260-2] LOG: server process (PID 10695) was terminated by signal 11: Segmentation fault
2015-07-06 00:37:52 UTC [24260-3] DETAIL: Failed process was running: SELECT pid, state, application_name AS source, age(now(), xac
t_start) AS duration, waiting, query, xact_start AS started_at FROM pg_stat_activity WHERE query <> '<insufficient privilege>' AND s
tate <> 'idle' AND pid <> pg_backend_pid() AND now() - query_start > interval '60 seconds' ORDER BY query_start DESC
> ActiveRecord::Base.descendants
PG::UndefinedTable: ERROR: relation "pg_hero_connections" does not exist
LINE 5: WHERE a.attrelid = '"pg_hero_connections"'::r...
^
: SELECT a.attname, format_type(a.atttypid, a.atttypmod),
pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
FROM pg_attribute a LEFT JOIN pg_attrdef d
ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = '"pg_hero_connections"'::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
(pry) output error: #<ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: relation "pg_hero_connections" does not exist
LINE 5: WHERE a.attrelid = '"pg_hero_connections"'::r...
^
: SELECT a.attname, format_type(a.atttypid, a.atttypmod),
pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
FROM pg_attribute a LEFT JOIN pg_attrdef d
ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = '"pg_hero_connections"'::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
> PgHero::Connection
PG::UndefinedTable: ERROR: relation "pg_hero_connections" does not exist
LINE 5: WHERE a.attrelid = '"pg_hero_connections"'::r...
^
: SELECT a.attname, format_type(a.atttypid, a.atttypmod),
pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
FROM pg_attribute a LEFT JOIN pg_attrdef d
ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = '"pg_hero_connections"'::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
(pry) output error: #<ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: relation "pg_hero_connections" does not exist
LINE 5: WHERE a.attrelid = '"pg_hero_connections"'::r...
^
: SELECT a.attname, format_type(a.atttypid, a.atttypmod),
pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
FROM pg_attribute a LEFT JOIN pg_attrdef d
ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = '"pg_hero_connections"'::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
>
Happens in dev and prod environment. Version is 0.1.7.
When I try to start pghero on Ubuntu 15.10, it gives me this error:
initctl: Unable to connect to Upstart: Failed to connect to socket /com/ubuntu/upstart: Connection refused
This is evidently because Ubuntu has switched from upstart to systemd in the latest versions. Is there any chance of adding support for systemd? Or is there another way that I can run pghero without installing upstart?
Our single most expensive query shows up as <insufficient privilege>
:
I was able to discover the query in question by manually running slow_queries
as the postgres
user. Turns out it's a query run by a different database user than the one hosting PGHero. From a non-superuser account you can see this by joining the pg_user
table:
SELECT u.usename, (total_time / 1000 / 60) as total_minutes,
(total_time/calls) as average_time, query
FROM pg_stat_statements p
JOIN pg_user u ON (u.usesysid = p.userid) ORDER BY 2 DESC LIMIT 1;
usename | total_minutes | average_time | query
---------+---------------+------------------+--------------------------
foo | 199.0077556 | 333.073461212308 | <insufficient privilege>
bar | 170.0383281 | 165.831729885244 | SELECT ...;
(1 row)
Where bar
is the current account in use, and foo
is the other account.
Assuming there's no way to solve this without running PGHero with superuser permissions, it might be good to document the error in the README / inline on the web page.
Hi there,
I've installed pghero in a Centos 6.7, following the docs.
Also, I'm using Apache as web server.
httpd.conf:
NameVirtualHost *:80
<VirtualHost *:80>
ProxyPreserveHost On
ProxyPass / http://localhost:6000/
ProxyPassReverse / http://localhost:6000/
DocumentRoot /
ServerName 10.208.7.32
</VirtualHost>
When I open the browser to connect into pghero, I got the following error:
(70007)The timeout specified has expired: proxy: error reading status line from remote server localhost
proxy: Error reading from remote server returned by /
[root@localhost]# curl -v http://localhost:6000/
* About to connect() to localhost port 6000 (#0)
* Trying ::1... Connection refused
* Trying 127.0.0.1... connected
* Connected to localhost (127.0.0.1) port 6000 (#0)
> GET / HTTP/1.1
> User-Agent: curl/7.19.7 (x86_64-redhat-linux-gnu) libcurl/7.19.7 NSS/3.19.1 Basic ECC zlib/1.2.3 libidn/1.18 libssh2/1.4.2
> Host: localhost:6000
> Accept: */*
Do you guys have any idea about how can I solve that one?
Cheers
I'm using Debian 8, pghero is a local bundler install with config.ru, Gemfile and Gemlfile.lock stolen from the Docker example.
Everything is fine with one database defined in env DATABASE_URL.
Now, I want multiple databases. I wrote config/pghero.yml as the doc explains.
Here is the stack trace :
$ PGHERO_USERNAME=bob PGHERO_PASSWORD=sponge bundle exec puma -t 8:12 -w 4 -b tcp://1.2.3.4:8000 -e production --preload
[20663] Puma starting in cluster mode...
[20663] * Version 2.9.1 (ruby 2.1.5-p273), codename: Team High Five
[20663] * Min threads: 8, max threads: 12
[20663] * Environment: production
[20663] * Process workers: 4
[20663] * Preloading application
[20663] ! Unable to load application
/var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/application/configuration.rb:105:in `database_configuration': Cannot load `Rails.application.database_configuration`: (RuntimeError)
Could not load database configuration. No such file -
from /var/www/pghero/vendor/ruby/2.1.0/gems/activerecord-4.1.6/lib/active_record/railtie.rb:128:in `block (2 levels) in <class:Railtie>'
from /var/www/pghero/vendor/ruby/2.1.0/gems/activesupport-4.1.6/lib/active_support/lazy_load_hooks.rb:38:in `instance_eval'
from /var/www/pghero/vendor/ruby/2.1.0/gems/activesupport-4.1.6/lib/active_support/lazy_load_hooks.rb:38:in `execute_hook'
from /var/www/pghero/vendor/ruby/2.1.0/gems/activesupport-4.1.6/lib/active_support/lazy_load_hooks.rb:28:in `block in on_load'
from /var/www/pghero/vendor/ruby/2.1.0/gems/activesupport-4.1.6/lib/active_support/lazy_load_hooks.rb:27:in `each'
from /var/www/pghero/vendor/ruby/2.1.0/gems/activesupport-4.1.6/lib/active_support/lazy_load_hooks.rb:27:in `on_load'
from /var/www/pghero/vendor/ruby/2.1.0/gems/activerecord-4.1.6/lib/active_record/railtie.rb:118:in `block in <class:Railtie>'
from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/initializable.rb:30:in `instance_exec'
from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/initializable.rb:30:in `run'
from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/initializable.rb:55:in `block in run_initializers'
from /usr/lib/ruby/2.1.0/tsort.rb:226:in `block in tsort_each'
from /usr/lib/ruby/2.1.0/tsort.rb:348:in `block (2 levels) in each_strongly_connected_component'
from /usr/lib/ruby/2.1.0/tsort.rb:427:in `each_strongly_connected_component_from'
from /usr/lib/ruby/2.1.0/tsort.rb:347:in `block in each_strongly_connected_component'
from /usr/lib/ruby/2.1.0/tsort.rb:345:in `each'
from /usr/lib/ruby/2.1.0/tsort.rb:345:in `call'
from /usr/lib/ruby/2.1.0/tsort.rb:345:in `each_strongly_connected_component'
from /usr/lib/ruby/2.1.0/tsort.rb:224:in `tsort_each'
from /usr/lib/ruby/2.1.0/tsort.rb:205:in `tsort_each'
from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/initializable.rb:54:in `run_initializers'
from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/application.rb:300:in `initialize!'
from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/railtie.rb:194:in `public_send'
from /var/www/pghero/vendor/ruby/2.1.0/gems/railties-4.1.6/lib/rails/railtie.rb:194:in `method_missing'
from config.ru:18:in `block in <main>'
from /var/www/pghero/vendor/ruby/2.1.0/gems/rack-1.5.2/lib/rack/builder.rb:55:in `instance_eval'
from /var/www/pghero/vendor/ruby/2.1.0/gems/rack-1.5.2/lib/rack/builder.rb:55:in `initialize'
from config.ru:in `new'
from config.ru:in `<main>'
from /var/www/pghero/vendor/ruby/2.1.0/gems/rack-1.5.2/lib/rack/builder.rb:49:in `eval'
from /var/www/pghero/vendor/ruby/2.1.0/gems/rack-1.5.2/lib/rack/builder.rb:49:in `new_from_string'
from /var/www/pghero/vendor/ruby/2.1.0/gems/rack-1.5.2/lib/rack/builder.rb:40:in `parse_file'
from /var/www/pghero/vendor/ruby/2.1.0/gems/puma-2.9.1/lib/puma/configuration.rb:96:in `app'
from /var/www/pghero/vendor/ruby/2.1.0/gems/puma-2.9.1/lib/puma/runner.rb:113:in `load_and_bind'
from /var/www/pghero/vendor/ruby/2.1.0/gems/puma-2.9.1/lib/puma/cluster.rb:283:in `run'
from /var/www/pghero/vendor/ruby/2.1.0/gems/puma-2.9.1/lib/puma/cli.rb:499:in `run'
from /var/www/pghero/vendor/ruby/2.1.0/gems/puma-2.9.1/bin/puma:10:in `<top (required)>'
from /var/www/pghero/vendor/ruby/2.1.0/bin/puma:23:in `load'
from /var/www/pghero/vendor/ruby/2.1.0/bin/puma:23:in `<main>'
After d881d0c
PgHero.db_instance_identifier
is returning nil
I have a database.yml
as following:
production:
adapter: postgresql
encoding: unicode
database: database_production
pool: 5
when I try to access /pghero, I see the error on title. Did I missed something?
Pghero shows code snippets for using migration like this,
remove_index :posts, name: "index_posts_on_slug"
And users can make migration code like this.
class RemoveUnusedIndexes < ActiveRecord::Migration
def up
remove_index :posts, name: "index_posts_on_slug"
end
def down
end
end
I think it has problem that cannot retry after rollback so it's better (and safer).
remove_index :posts, name: "index_posts_on_slug" if index_exists?(:posts, name: "index_posts_on_slug")
The following error is received when attempting to run the historical query stats scheduled task:
$ sudo pghero run pghero:capture_query_stats
sh: 1: exec: pghero:capture_query_stats: not found
The instructions to install PgHero on Linux (Ubuntu 14.04) were followed. It appears that PgHero us up and running correctly but the scheduled job produces the error both when run by cron and when run manually.
Chart query stats and other important metrics over time
Trying to create a index concurrently in Rails need the migration transaction to be disabled. Need to mention that.
https://robots.thoughtbot.com/how-to-create-postgres-indexes-concurrently-in
Also, GIST indexes can easily be defined int the Rails format itself. Can update that too.
I'll submit a PR for both changes, just putting them down here so I don't forget.
Use slow queries as a starting point, then programmatically do what a human would
Great gem! ๐
I installed it and it told me query stats are not enabled and, upon trying to enable them, "The database user does not have permission to enable query stats"
Could you quickly explain in the README what the query stats are and how to enable them? Are they same as the Statistics Collector?
A bit more info would be great for Postgres newbies like me.
Also, it might make sense to mention in the README that the performance of the app is not affected by enabling PGHero (I assume it's not ๐).
Since you can't run explain on normalized queries from pg_stat_statements, need to capture live query.
in postgresql.conf
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
CREATE extension pg_stat_statements;
ERROR: function "pg_stat_statements_reset" already exists with same argument types
Still see "Query stats are available but not enabled" in pghero.
psql (9.2.9)
pghero (0.1.3)
Hi there,
I've installed pghero into my DB server, to get some additional information about it.
Pghero is showing me that I should create an index, that already exists.
CREATE INDEX CONCURRENTLY ON ja_customers (clientid)
I've got this index in my DB:
CREATE INDEX ix_clientid_customers ON ja_customers ("clientid");
The query that is presented to me, is this one:
SELECT id, name_first, name_last, company FROM ja_customers WHERE clientid=? AND deleted = ? ORDER BY company, name_last, name_first ASC
If I run it using explain, I can see the query hitting the index.
Why is pghero showing me that index, if it already exists?
Cheers
Lucas
The db user methods as documented here no longer seem to work:
https://github.com/ankane/pghero/blob/master/guides/Rails.md#users
PgHero.create_user("blazer", readonly: true)
NoMethodError: undefined method `create_user' for PgHero:Module
How does one go about using these now?
Postgres Query Plan Visualization
https://github.com/AlexTatiyants/pev
http://tatiyants.com/postgres-query-plan-visualization/
It's awesome.
The Queries section filters on DB with the WHERE below with but other things like long running queries on the main page do not. We run a large shared instance where we'd like to prevent individual DBs (one allocated to each service/team) from seeing/killing the queries of other DBs.
INNER JOIN
pg_database ON pg_database.oid = pg_stat_statements.dbid
WHERE
pg_database.datname = current_database()
Travis will run test for you and give you super awesome badges to stick on the README.md. Also gives you confidence when merging PRs that all tests are passing without having to clone down locally. Would be a good improvement to have.
Build a mechanism for the pghero_query_stats table to be cleaned up such that records older than a week get purged as new records are imported by the pghero:capture_query_stats or provide a pghero:cleanup_query_stats kind of function to do it.
Hi,
PGhero supports multi-connections?
Example,
sudo pghero config:set DATABASE_URL=postgres://user:password@hostname1:5432/dbname
sudo pghero config:set DATABASE_URL=postgres://user:password@hostname2:5432/dbname
if not, whether it is possible to make ?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.