<% if @replica %>
">
<% if @good_replication_lag %>
Healthy replication lag
<% else %>
High replication lag
<% end %>
<%= number_with_delimiter((@replication_lag * 1000).round) %> ms
<% elsif @inactive_replication_slots.any? %>
<%= pluralize(@inactive_replication_slots.size, "inactive replication slot") %>
<% end %>
">
<% if @long_running_queries.any? %>
<%= pluralize(@long_running_queries.size, "long running query") %>
<% else %>
No long running queries
<% end %>
<% if @autovacuum_queries.any? %>
<%= @autovacuum_queries.size %> autovacuum
<% end %>
<% if @extended %>
">
<% if @good_cache_rate %>
Cache hit rate above <%= @database.cache_hit_rate_threshold %>%
<% else %>
Low cache hit rate
<% end %>
<% end %>
">
<% if @good_total_connections %>
Number of connections healthy
<% else %>
High number of connections
<% end %>
<%= @total_connections %>
">
<% if @transaction_id_danger.any? %>
<%= pluralize(@transaction_id_danger.size, "table") %> not vacuuming properly
<% else %>
Vacuuming healthy
<% end %>
">
<% if !@sequence_danger %>
Error checking for columns near integer overflow: <%= @sequence_danger_error %>
<% elsif @sequence_danger.any? %>
<%= pluralize(@sequence_danger.size, "columns") %> approaching overflow
<% else %>
No columns near integer overflow
<% end %>
">
<% if @invalid_indexes.any? %>
<%= pluralize(@invalid_indexes.size, "invalid index", "invalid indexes") %>
<% else %>
No invalid indexes
<% end %>
<% if @duplicate_indexes %>
">
<% if @duplicate_indexes.any? %>
<%= pluralize(@duplicate_indexes.size, "duplicate index", "duplicate indexes") %>
<% else %>
No duplicate indexes
<% end %>
<% end %>
<% if @database.suggested_indexes_enabled? %>
">
<% if @suggested_indexes.any? %>
<%= pluralize(@suggested_indexes.size, "suggested index", "suggested indexes") %>
<% else %>
No suggested indexes
<% end %>
<% end %>
">
<% if !@query_stats_enabled %>
Query stats must be enabled for slow queries
<% elsif @slow_queries.any? %>
<%= pluralize(@slow_queries.size, "slow query") %>
<% else %>
No slow queries
<% end %>
<% if @extended %>
">
<% if @unused_indexes.any? %>
<%= pluralize(@unused_indexes.size, "unused index", "unused indexes") %>
<% else %>
No unused indexes
<% end %>
<% end %>
High Replication Lag
<%= pluralize(@replication_lag.round, "second") %>
<% end %>
<% if @inactive_replication_slots && @inactive_replication_slots.any? %>
Inactive Replication Slots
Inactive replication slots can cause a lot of disk space to be consumed.
For each, run:
SELECT pg_drop_replication_slot('slot_name');
| Name |
<% @inactive_replication_slots.each do |slot| %>
| <%= slot[:slot_name] %> |
<% end %>
<% end %>
<% if @long_running_queries.any? %>
<%= button_to "Kill All", kill_long_running_queries_path, class: "btn btn-danger", style: "float: right;" %>
Long Running Queries
We recommend setting a statement timeout on all non-superusers with:
ALTER ROLE <user> SET statement_timeout TO '60s';
<%= render partial: "live_queries_table", locals: {queries: @long_running_queries} %>
Low Cache Hit Rate
Index Hit Rate: <%= (@index_hit_rate * 100).round(1) %>%
Table Hit Rate: <%= (@table_hit_rate * 100).round(1) %>%
The cache hit rate <%= link_to "should be above 99%", "https://devcenter.heroku.com/articles/understanding-postgres-data-caching", target: "_blank" %> in most cases. You can often increase this by adding more memory.
<% end %>
<% if !@good_total_connections %>
High Number of Connections
<%= pluralize(@total_connections, "connection") %>
<%= link_to "Use connection pooling", "http://www.craigkerstiens.com/2014/05/22/on-connection-pooling/", target: "_blank" %> for better performance. <%= link_to "PgBouncer", "https://wiki.postgresql.org/wiki/PgBouncer", target: "_blank" %> is a solid option.
<%= render partial: "connections_table", locals: {connection_sources: @database.connection_sources.first(10)} %>
<% end %>
<% if @transaction_id_danger.any? %>
Vacuuming Needed
The database will shutdown when there are fewer than 1,000,000 transactions left. <%= link_to "Read more", "http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND", target: "_blank" %>.
Try <%= link_to "tuning autovacuum", "https://blog.2ndquadrant.com/autovacuum-tuning-basics/", target: "_blank" %> - specifically autovacuum_vacuum_cost_limit.
If that doesn’t work, for each table, run:
VACUUM FREEZE VERBOSE table;
| Table |
Transactions Left |
<% @transaction_id_danger.each do |query| %>
|
<%= query[:table] %>
<% if query[:schema] != "public" %>
<%= query[:schema] %>
<% end %>
|
<%= number_with_delimiter(query[:transactions_left]) %> |
<% end %>
<% end %>
<% if @sequence_danger && @sequence_danger.any? %>
Columns Near Overflow
Consider changing columns to bigint to support a larger range of values.
| Column |
Type |
Values Left |
% Left |
<% @sequence_danger.sort_by { |s| [s[:table], s[:column]] }.each do |query| %>
|
<%= query[:table] %>.<%= query[:column] %>
|
<%= query[:column_type] %>
|
<%= number_with_delimiter(query[:max_value] - query[:last_value]) %>
|
<%= number_to_percentage((query[:max_value] - query[:last_value]) * 100.0 / query[:max_value], precision: 2, significant: true) %>
|
<% end %>
<% end %>
<% if @invalid_indexes.any? %>
Invalid Indexes
These indexes exist, but can’t be used. You should recreate them.
| Name |
<% @invalid_indexes.each do |index| %>
|
<%= index[:name] %>
<% if index[:schema] != "public" %>
<%= index[:schema] %>
<% end %>
|
DROP INDEX CONCURRENTLY <%= pghero_pretty_ident(index[:name], schema: index[:schema]) %>;
<%= index[:definition].sub("CREATE INDEX ", "CREATE INDEX CONCURRENTLY ") %>;
|
<% end %>
<% end %>
<% if @duplicate_indexes && @duplicate_indexes.any? %>
Duplicate Indexes
These indexes exist, but aren’t needed. Remove them
<% if @show_migrations %>
with a migration
<% end %>
for faster writes.
rails g migration remove_unneeded_indexes
And paste
<% @duplicate_indexes.each do |query| %>
remove_index <%= query[:unneeded_index][:table].to_sym.inspect %>, name: <%= query[:unneeded_index][:name].to_s.inspect %><% end %>
| Details |
<% @duplicate_indexes.each do |index| %>
<% unneeded_index = index[:unneeded_index] %>
<% covering_index = index[:covering_index] %>
On <%= unneeded_index[:table] %>
<%= unneeded_index[:name] %> (<%= unneeded_index[:columns].join(", ") %>)
is covered by
<%= covering_index[:name] %> (<%= covering_index[:columns].join(", ") %>)
|
<% end %>
<% end %>
<% if @suggested_indexes.any? %>
Suggested Indexes
Add indexes to speed up queries.
<% if @show_migrations %>
Here’s a
migration to help.
<% end %>
rails g migration add_suggested_indexes
And paste
commit_db_transaction
<% @suggested_indexes.each do |index| %>
<% if index[:using] && index[:using] != "btree" %>
connection.execute("CREATE INDEX CONCURRENTLY ON <%= index[:table] %><% if index[:using] %> USING <%= index[:using] %><% end %> (<%= index[:columns].join(", ") %>)")
<% else %>
add_index <%= index[:table].to_sym.inspect %>, [<%= index[:columns].map(&:to_sym).map(&:inspect).join(", ") %>], algorithm: :concurrently<% end %>
<% end %>
<% @suggested_indexes.each_with_index do |index, i| %>
<%= render partial: "suggested_index", locals: {index: index, details: index[:details]} %>
to speed up
<%= render partial: "queries_table", locals: {queries: index[:queries].map { |q| @query_stats_by_query[q] }, suggested_indexes: false} %>
<% end %>
<% end %>
<% if !@query_stats_enabled %>
Query Stats
<% if @query_stats_available && !@query_stats_extension_enabled %>
Query stats are available but not enabled.
<%= button_to "Enable", enable_query_stats_path, class: "btn btn-info" %>
<% else %>
Make them available by adding the following lines to postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
Restart the server for the changes to take effect.
<% end %>
<% end %>
<% if @query_stats_enabled && @slow_queries.any? %>
Slow Queries
Slow queries take <%= @database.slow_query_ms %> ms or more on average and have been called at least <%= @database.slow_query_calls %> times.
<%= link_to "Explain queries", explain_path %> to see where to add indexes.
<%= render partial: "queries_table", locals: {queries: @slow_queries} %>
<% end %>
<% if @extended && @unused_indexes.any? %>
Unused Indexes
Unused indexes cause unnecessary overhead. Remove them
<% if @show_migrations %>
with a migration
<% end %>
for faster writes.
rails g migration remove_unused_indexes
And paste
<% @unused_indexes.each do |query| %>
remove_index <%= query[:table].to_sym.inspect %>, name: <%= query[:index].to_s.inspect %><% end %>
| Name |
Index Size |
<% @unused_indexes.each do |query| %>
| <%= query[:index] %> on <%= query[:table] %> |
<%= query[:size] %> |
<% end %>
<% end %>