root/trunk/tools/pg_file_stress

Revision 8, 4.6 kB (checked in by jesus, 7 years ago)

pg_file_stress allow you to monitor read/write by table in a form similary to an iostat/prstat hybrid. pgtruss glues pfiles into truss output.

  • Property svn:executable set to *
Line 
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 }
Note: See TracBrowser for help on using the browser.