<% if @replica %>
">
<% if @good_replication_lag %>
Healthy replication lag
<% else %>
High replication lag
<% end %>
<%= number_with_delimiter((@replication_lag * 1000).round) %> ms
<% 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.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 @long_running_queries.any? %>
<%= button_to "Kill All", kill_long_running_queries_path, class: "btn btn-danger", style: "float: right;" %>
Long Running Queries
<%= render partial: "live_queries_table", locals: {queries: @long_running_queries} %>
<% end %>
<% if @extended && !@good_cache_rate %>
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(by_database_and_user: true).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" %>.
For each table, run:
VACUUM FREEZE VERBOSE table;
| Table |
Transactions Left |
<% @transaction_id_danger.each do |query| %>
| <%= query["table"] %> |
<%= number_with_delimiter(query["transactions_before_shutdown"]) %> |
<% end %>
<% end %>
<% if @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"].to_i - query["last_value"].to_i) %>
|
<%= number_to_percentage((query["max_value"].to_i - query["last_value"].to_i) * 100.0 / query["max_value"].to_i, 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 |query| %>
| <%= query["index"] %> |
<% 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["index_size"] %> |
<% end %>
<% end %>