Module: PgHero::Methods::Maintenance

Included in:
Database
Defined in:
lib/pghero/methods/maintenance.rb

Instance Method Summary collapse

Instance Method Details

#analyze(table, verbose: false) ⇒ Object 



70
71
72
73
 
# File 'lib/pghero/methods/maintenance.rb', line 70

def analyze(table, verbose: false)
  execute "ANALYZE #{verbose ? "VERBOSE " : ""}#{quote_table_name(table)}"
  true
end

#analyze_tables(verbose: false, min_size: nil, tables: nil) ⇒ Object 



75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/pghero/methods/maintenance.rb', line 75

def analyze_tables(verbose: false, min_size: nil, tables: nil)
  tables = table_stats(table: tables).reject { |s| %w(information_schema pg_catalog).include?(s[:schema]) }
  tables = tables.select { |s| s[:size_bytes] > min_size } if min_size
  tables.map { |s| s.slice(:schema, :table) }.each do |stats|
    begin
      with_transaction(lock_timeout: 5000, statement_timeout: 120000) do
        analyze "#{stats[:schema]}.#{stats[:table]}", verbose: verbose
      end
      success = true
    rescue ActiveRecord::StatementInvalid => e
      $stderr.puts e.message
      success = false
    end
    stats[:success] = success
  end
end

#autovacuum_danger ⇒ Object 



31
32
33
34
 
# File 'lib/pghero/methods/maintenance.rb', line 31

def autovacuum_danger
  max_value = select_one("SHOW autovacuum_freeze_max_age").to_i
  transaction_id_danger(threshold: 2000000, max_value: max_value)
end

#maintenance_info ⇒ Object 



52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
# File 'lib/pghero/methods/maintenance.rb', line 52

def maintenance_info
  select_all "    SELECT\n      schemaname AS schema,\n      relname AS table,\n      last_vacuum,\n      last_autovacuum,\n      last_analyze,\n      last_autoanalyze,\n      n_dead_tup AS dead_rows,\n      n_live_tup AS live_rows\n    FROM\n      pg_stat_user_tables\n    ORDER BY\n      1, 2\n  SQL\nend\n"

#transaction_id_danger(threshold: 10000000, max_value: 2146483648) ⇒ Object

www.postgresql.org/docs/9.1/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND “the system will shut down and refuse to start any new transactions once there are fewer than 1 million transactions left until wraparound” warn when 10,000,000 transactions left



8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
# File 'lib/pghero/methods/maintenance.rb', line 8

def transaction_id_danger(threshold: 10000000, max_value: 2146483648)
  max_value = max_value.to_i
  threshold = threshold.to_i

  select_all "    SELECT\n      n.nspname AS schema,\n      c.relname AS table,\n      \#{quote(max_value)} - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_left\n    FROM\n      pg_class c\n    INNER JOIN\n      pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n    LEFT JOIN\n      pg_class t ON c.reltoastrelid = t.oid\n    WHERE\n      c.relkind = 'r'\n      AND (\#{quote(max_value)} - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < \#{quote(threshold)}\n    ORDER BY\n     3, 1, 2\n  SQL\nend\n"

#vacuum_progress ⇒ Object 



36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
# File 'lib/pghero/methods/maintenance.rb', line 36

def vacuum_progress
  if server_version_num >= 90600
    select_all "      SELECT\n        pid,\n        phase\n      FROM\n        pg_stat_progress_vacuum\n      WHERE\n        datname = current_database()\n    SQL\n  else\n    []\n  end\nend\n"