misc/postgres_table_total_size_ at master · acunote/misc · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
#!/usr/bin/perl
# -*- cperl -*-
#
# Copyright (C) 2009 Magnus Hagander, Redpill Linpro AB
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; version 2 dated June,
# 1991.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
# 02110-1301 USA.

=head1 NAME

postgres_table_total_size_ - Plugin to monitor total size of PostgreSQL
tables (including their indexes and TOAST data)

=head1 CONFIGURATION

Configuration is done through libpq environment variables, for example
PGUSER, PGDATABASE, etc. For more information, see L<Munin::Plugin::Pgsql>.

In addition number of top tables to show is also configurable, with
the default being 20:

  [postgres_relation_size_]
     env.limit 20

To monitor a specific database, link to postgres_relation_size_<databasename>.

=head1 SEE ALSO

L<Munin::Plugin::Pgsql>

=head1 MAGIC MARKERS

 #%# family=auto
 #%# capabilities=autoconf suggest

=head1 AUTHOR

Gleb Arshinov <gleb+munin@pluron.com>, Pluron, Inc.

=head1 COPYRIGHT/License.

Copyright (c) 2011 Pluron, Inc.

All rights reserved. This program is free software; you can
redistribute it and/or modify it under the terms of the GNU General
Public License as published by the Free Software Foundation; version 2
dated June, 1991.

=cut

use strict;
use warnings;

use Munin::Plugin::Pgsql;

my $limit = $ENV{limit} || 20;

my $pg = Munin::Plugin::Pgsql->new(
    minversion => 8.1,
    basename  => 'postgres_table_total_size_',
    title     => 'PostgreSQL table total size',
    info      => 'Size of $limit largest tables (including their indexes and TOAST data)',
    vlabel    => 'Size',
    basequery =>
      " SELECT relation, size"
      . " FROM ("
      . "   SELECT pg_namespace.nspname || '.' || pg_class.relname AS relation,"
      . "          pg_total_relation_size(pg_class.oid) AS size"
      . "   FROM pg_class"
      . "        LEFT JOIN pg_namespace"
      . "                  ON (pg_namespace.oid = pg_class.relnamespace)"
      . "   WHERE pg_namespace.nspname NOT IN ('pg_catalog',"
      . "                                      'information_schema')"
      . "     AND pg_class.relkind <> 'i'"
      . "     AND pg_namespace.nspname !~ '^pg_toast'"
      . "   ORDER BY pg_total_relation_size(pg_class.oid) DESC"
      . "   LIMIT $limit"
      . " ) top_relations"
      . " UNION ALL"
      . "  SELECT 'all remaining' AS relation,"
      . "         SUM(remaining_relations.size) AS size"
      . "    FROM ( SELECT relname,"
      . "                  pg_total_relation_size(pg_class.oid) as size"
      . "           FROM pg_class "
      . "           WHERE pg_class.relkind <> 'i'"
      . "           ORDER BY pg_total_relation_size(pg_class.oid) DESC"
      . "           OFFSET $limit ) remaining_relations",
    paramdatabase => 1,
    configquery    =>
      " SELECT relation, relation"
      . " FROM ("
      . "   SELECT pg_namespace.nspname || '.' || pg_class.relname AS relation"
      . "   FROM pg_class"
      . "        LEFT JOIN pg_namespace"
      . "                  ON (pg_namespace.oid = pg_class.relnamespace)"
      . "   WHERE pg_namespace.nspname NOT IN ('pg_catalog',"
      . "                                      'information_schema')"
      . "     AND pg_class.relkind <> 'i'"
      . "     AND pg_namespace.nspname !~ '^pg_toast'"
      . "   ORDER BY pg_total_relation_size(pg_class.oid) DESC"
      . "   LIMIT $limit"
      . " ) top_relations"
      . " UNION ALL"
      . "  SELECT 'all remaining' AS relation,"
      . "         'all remaining' AS relation",
    suggestquery =>
        "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate AND NOT datname='postgres' ORDER BY 1 LIMIT 10",
    graphdraw => 'AREA',
    graphmin => 0,
    stack     => 1,
    base      => 1024
);

$pg->Process();
exit(0);

# Queries based on:
# http://wiki.postgresql.org/wiki/Disk_Usage