1 |
#!/opt/perl/bin/perl |
---|
2 |
|
---|
3 |
# Copyright 2006 OmniTI, Inc. |
---|
4 |
# Author: Theo Schlossnagle |
---|
5 |
# All rights reserved. |
---|
6 |
|
---|
7 |
use DBI; |
---|
8 |
use Getopt::Long; |
---|
9 |
use strict; |
---|
10 |
|
---|
11 |
my %oidcache; |
---|
12 |
my %files; |
---|
13 |
|
---|
14 |
my $clear = `clear`; |
---|
15 |
my $sortkey = "avg"; |
---|
16 |
my $topn = 40; |
---|
17 |
my $interval = 5; |
---|
18 |
my $database = 'postgres'; |
---|
19 |
my $user = $ENV{USER} || 'postgres'; |
---|
20 |
my $pass = ''; |
---|
21 |
my $others = 0; |
---|
22 |
my $usage = 0; |
---|
23 |
my @valid; |
---|
24 |
|
---|
25 |
GetOptions("n=i" => \$topn, |
---|
26 |
"i=i" => \$interval, |
---|
27 |
"s=s" => \$sortkey, |
---|
28 |
"d=s" => \$database, |
---|
29 |
"u=s" => \$user, |
---|
30 |
"p=s" => \$pass, |
---|
31 |
"o" => \$others, |
---|
32 |
"h" => \$usage); |
---|
33 |
|
---|
34 |
if($usage) { |
---|
35 |
print qq^ |
---|
36 |
$0: |
---|
37 |
-n # display top # files/objects |
---|
38 |
-i # report stats every # seconds |
---|
39 |
-s <key> sort descending on <key> where <key> is: |
---|
40 |
((read|write):)?(cnt|min|avg|max) |
---|
41 |
default 'avg' (which is read:avg + write:avg) |
---|
42 |
-d <dbname> connect the <dbname> postgres database |
---|
43 |
-u <user> connect as <user> (default you) |
---|
44 |
-p <pass> connect with <pass> (default '') |
---|
45 |
-o display files other than database objects |
---|
46 |
-h this message |
---|
47 |
|
---|
48 |
Copyright 2006 OmniTI, Inc. All rights reserved. |
---|
49 |
Distributable under a New-BSD license. |
---|
50 |
^; |
---|
51 |
exit 0; |
---|
52 |
} |
---|
53 |
|
---|
54 |
my $dbh; |
---|
55 |
# Connect to PostgreSQL for mapping filenames to database objects |
---|
56 |
$dbh = DBI->connect("dbi:Pg:dbname=$database", $user, $pass); |
---|
57 |
|
---|
58 |
# Find our tablespaces (files under these _could_ be DB objects |
---|
59 |
my $q = $dbh->prepare(q^ |
---|
60 |
select distinct(CASE spclocation |
---|
61 |
WHEN '' |
---|
62 |
THEN (select setting||'/base' |
---|
63 |
from pg_settings |
---|
64 |
where name = 'data_directory') |
---|
65 |
ELSE spclocation END) |
---|
66 |
from pg_tablespace |
---|
67 |
^); |
---|
68 |
$q->execute(); |
---|
69 |
while(my ($base) = $q->fetchrow()) { |
---|
70 |
# Foreach of them make a regexp to match against filenames |
---|
71 |
$base = '^' . $base . '/(\d+)/(\d+)(?:\.\d+)?$'; |
---|
72 |
push @valid, qr/$base/; |
---|
73 |
} |
---|
74 |
$q->finish; |
---|
75 |
|
---|
76 |
# Prep our oid to object name mapping query |
---|
77 |
my $oid2name = $dbh->prepare(q^ |
---|
78 |
select relname |
---|
79 |
from pg_class |
---|
80 |
where relfilenode = ? |
---|
81 |
^); |
---|
82 |
|
---|
83 |
# Do the dtrace to report on statistics per filename |
---|
84 |
open(D, q^/usr/sbin/dtrace -q -n ' |
---|
85 |
syscall::read:entry |
---|
86 |
/execname == "postgres"/ |
---|
87 |
{ |
---|
88 |
self->fd = arg0; |
---|
89 |
self->start = timestamp; |
---|
90 |
} |
---|
91 |
syscall::write:entry |
---|
92 |
/execname == "postgres"/ |
---|
93 |
{ |
---|
94 |
self->fd = arg0; |
---|
95 |
self->start = timestamp; |
---|
96 |
} |
---|
97 |
syscall:::return |
---|
98 |
/self->start/ |
---|
99 |
{ |
---|
100 |
@[probefunc, fds[self->fd].fi_pathname] = |
---|
101 |
avg((timestamp - self->start)/1000000); |
---|
102 |
@min[probefunc, fds[self->fd].fi_pathname] = |
---|
103 |
min((timestamp - self->start)/1000000); |
---|
104 |
@max[probefunc, fds[self->fd].fi_pathname] = |
---|
105 |
max((timestamp - self->start)/1000000); |
---|
106 |
@cnt[probefunc, fds[self->fd].fi_pathname] = count(); |
---|
107 |
self->start = 0; |
---|
108 |
} |
---|
109 |
|
---|
110 |
tick-^ . $interval . q^sec |
---|
111 |
{ |
---|
112 |
printa("avg:%@d:%s:%s\n", @); |
---|
113 |
printa("cnt:%@d:%s:%s\n", @cnt); |
---|
114 |
printa("min:%@d:%s:%s\n", @min); |
---|
115 |
printa("max:%@d:%s:%s\n", @max); |
---|
116 |
/* print a clear so that the reader knows to draw stuff */ |
---|
117 |
printf("clear:0:0:0\n"); |
---|
118 |
trunc(@); |
---|
119 |
trunc(@cnt); |
---|
120 |
trunc(@min); |
---|
121 |
trunc(@max); |
---|
122 |
} |
---|
123 |
'|^); |
---|
124 |
|
---|
125 |
|
---|
126 |
my @vals = qw/read:cnt read:min read:avg read:max write:cnt write:min write:avg write:max/; |
---|
127 |
|
---|
128 |
# routine to draw out to the screen. |
---|
129 |
sub display { |
---|
130 |
# Sort the list as requested. |
---|
131 |
my @filelist = |
---|
132 |
sort { $files{$b}->{$sortkey} <=> $files{$a}->{$sortkey} } |
---|
133 |
keys %files; |
---|
134 |
|
---|
135 |
# limit to top n |
---|
136 |
splice(@filelist, $topn); |
---|
137 |
|
---|
138 |
# print a header (yes, it is wide) |
---|
139 |
print " FILENAME/DBOBJECT ". |
---|
140 |
" READS WRITES \n" . |
---|
141 |
" ". |
---|
142 |
" # min avg max # min avg max\n"; |
---|
143 |
|
---|
144 |
# run through each of our files and print stats. |
---|
145 |
foreach my $filename (@filelist) { |
---|
146 |
printf("%-50s %5d %5d %5d %5d %5d %5d %5d %5d\n", |
---|
147 |
substr($filename, -50), |
---|
148 |
map { $files{$filename}->{$_} } @vals); |
---|
149 |
} |
---|
150 |
} |
---|
151 |
|
---|
152 |
# Map filenames to database object names and cache the answer in %oidcache |
---|
153 |
|
---|
154 |
sub map2pg { |
---|
155 |
my $file = shift; |
---|
156 |
foreach my $r (@valid) { |
---|
157 |
if($file =~ $r) { |
---|
158 |
my $name = $oidcache{$2}; |
---|
159 |
unless($name) { |
---|
160 |
$oid2name->execute($2); |
---|
161 |
($name) = $oid2name->fetchrow(); |
---|
162 |
$oid2name->finish; |
---|
163 |
$oidcache{$2} = $name if($name); |
---|
164 |
} |
---|
165 |
return $name || "pg:$2"; |
---|
166 |
} |
---|
167 |
} |
---|
168 |
# only return the original filename if -o is set |
---|
169 |
return $others?$file:undef; |
---|
170 |
} |
---|
171 |
|
---|
172 |
while(<D>) { |
---|
173 |
# read out dtrace output |
---|
174 |
chomp; |
---|
175 |
my($attr,$v,$op,$file) = split /\:/; |
---|
176 |
|
---|
177 |
# if it is a clear message, then clear, display and truncate stats |
---|
178 |
if($attr eq 'clear') { |
---|
179 |
print $clear; |
---|
180 |
display(); |
---|
181 |
%files = (); |
---|
182 |
} |
---|
183 |
# map the file, set the stats |
---|
184 |
else { |
---|
185 |
$file = map2pg($file); |
---|
186 |
if($file) { |
---|
187 |
$files{$file}->{"$op:$attr"} = $v; |
---|
188 |
$files{$file}->{"$attr"} += $v; |
---|
189 |
} |
---|
190 |
} |
---|
191 |
} |
---|