root/trunk/Sniffer-Postgres/bin/pgsniff

Revision 93, 7.2 kB (checked in by jesus, 8 years ago)

cleanup docs and add copyright/license to pgsniff

  • Property svn:executable set to *
Line 
1 #!/usr/bin/perl -w
2 use strict;
3 use Sniffer::Postgres;
4 use POSIX qw/strftime/;
5 use Getopt::Long;
6 use IO::File;
7 use IO::Handle;
8
9 =head1 NAME
10
11 pgsniff - Dump info about queries as they happen
12
13 =head1 SYNOPSIS
14
15   pgsniff < -d interface | -f filename >
16           [ -l <filename> ] [ --hist ]
17           [ -n <cnt> ] [ -t <cnt> ] [-pg]
18           [ --inflight <port> ]
19           [ BPF filter syntax ]
20
21 =head1 DESCRIPTION
22
23 This tool will analyze either live packet streams or post-mortem
24 packet dumps to extract PostgreSQL session information.  It will
25 track the client<->server communication and reveal information
26 about client requests (queries, prepares, etc.) and various
27 metadata and statistics.
28
29 This tool was derived from the fine work of Max Maischein on
30 C<Sniffer::HTTP>.
31
32 =over 4
33
34 =item -d <interface>
35
36 Specifies a network C<interface> for live packet capture. This option
37 is not allowed in combination with -f.
38
39 =item -f <filename>
40
41 Specifies the C<filename> of a  pcap file dump (output of tcpdump).
42 This option is not allowed in combination with -d.
43
44 =item -l <filename>
45
46 Write the witnessed queries out to the specified C<filename>.  If "-"
47 is specified, standard output is used.  If omitted, not log file is
48 generated.
49
50 =item -pg
51
52 If writing a log file (see -l), use a logging format that looks like
53 PostgreSQL's native query logging format to allow easy consumption by
54 other PostgreSQL log processing tools.
55
56 =item --hist
57
58 Generate a historgram of time spent and tuples returned from each
59 query sorted by total cummulative execution time.  This can be
60 limited using the -t option.
61
62 =item -t <cnt>
63
64 Limit the histogram (--hist) output to the top C<cnt> most time comsuming
65 queries.  If omitted, all queries are displayed.
66
67 =item -n <cnt>
68
69 If specified, stop the program after C<cnt> queries can been witnessed.
70
71 =item --inflight <port>
72
73 By default, the system will only consider newly established postgresql
74 client connections (those that progress through a normal TCP handshake).
75 If this option is specified, it will attempt to start analyzing TCP
76 sessions that are currently "in flight" by noticing packets targeted
77 at the specified destination tcp C<port>.
78
79 =back
80
81 An optional BPF filter string may be specified to limit the packet capture
82 output.  If not specified, the default BPF filter is "port 5432"
83
84 =head1 LICENSE
85
86 Copyright (c) 2010 OmniTI Computer Consulting, Inc.
87
88 Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
89
90   1. Redistributions of source code must retain the above
91      copyright notice, this list of conditions and the following
92      disclaimer.
93   2. Redistributions in binary form must reproduce the above
94      copyright notice, this list of conditions and the following
95      disclaimer in the documentation and/or other materials provided
96      with the distribution.
97
98 THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESS OR IMPLIED
99 WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
100 MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO
101 EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
102 SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
103 PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
104 OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
105 HETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
106 OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
107 ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
108
109
110 =cut
111
112 my $max = 0;
113 my $pgformat = 0;
114 my $hist = {};
115 my $VERBOSE = 0;
116 my $inflight = 0;
117 my $limit = 0;
118 my $topn = 100;
119 my $device;
120 my $file;
121 my $logfile;
122 my $log_output;
123 my $histogram;
124 GetOptions(
125   "inflight=i" => \$inflight,
126   "pg" => \$pgformat,
127   "d=s" => \$device,
128   "f=s" => \$file,
129   "n=i" => \$limit,
130   "t=i" => \$topn,
131   "l=s" => \$logfile,
132   "hist" => \$histogram,
133 );
134 my $filter = shift;
135 die "one of -f <file> or -d <device> is required\n"
136   if((!$file && !$device) || ($file && $device));
137 if(defined $logfile) {
138   if($logfile eq '-') {
139     $log_output = IO::Handle->new();
140     $log_output->fdopen(fileno(STDOUT), "w");
141   }
142   else {
143     $log_output = IO::File->new(">$logfile");
144   }
145 }
146
147 sub operation {
148   my $q = shift;
149   if ($log_output) {
150     if ($pgformat) {
151       # time (ip(port)) [pid]: [?-?] dsn LOG:  duration: <time>  statement: <statement>
152       my $ts = strftime "%Y-%m-%d %H:%M:%S UTC", gmtime(int($q->{start_time}));
153       (my $qoneline = $q->{query}) =~ s/[\r\n]/ /gm;
154       printf "%s (%s(%d)) [1]: [1-1] user=unknown,db=unknown LOG:  duration: %.3f ms  statement: %s\n",
155              $ts, $q->{connection}->src_host, $q->{connection}->src_port, $q->{query_time} * 1000, $qoneline;
156     } else {
157       printf $log_output "%s %s:%d %.3f $q->{tuples} $q->{query}\n",
158              $q->{start_time}, $q->{connection}->src_host, $q->{connection}->src_port,
159              $q->{query_time} * 1000;
160     }
161   }
162
163   my $log2 = int(log($q->{query_time}*1000 + 1)/log(2));
164   $hist->{$q->{query}}->{query} ||= $q->{query};
165   my $cnt = ++$hist->{$q->{query}}->{runtime}->{$log2};
166   $hist->{$q->{query}}->{total_time} += $q->{query_time};
167   $max = ($max < $cnt) ? $cnt : $max;
168
169   $log2 = $q->{tuples} ? int(log($q->{tuples})/log(2)) : -1;
170   ++$hist->{$q->{query}}->{results}->{$log2};
171   $hist->{$q->{query}}->{total_tuples} += $q->{tuples};
172   finish() if (--$limit == 0);
173 }
174
175 sub print_query_hist {
176   my ($n, $q) = @_;
177   my $invocations = 0;
178   my ($bar, $cnt);
179   printf "== %d ==\n$q->{query}\n--- total run time: %2.f ms ---\n", $n, $q->{total_time} * 1000;
180   my ($top) = sort { $b <=> $a } keys %{$q->{runtime}};
181   my ($max) = sort { $b <=> $a } values %{$q->{runtime}};
182   while($top >= 0) {
183     $cnt = exists $q->{runtime}->{$top} ? $q->{runtime}->{$top} : 0;
184     $invocations += $cnt;
185     $bar = "#" x int(40 * $cnt / $max);
186     $bar .= " " x (40 - length($bar));
187     printf " %10d ms | $bar     ($cnt)\n", (2 ** $top);
188     $top--;
189   }
190
191   printf "--- average tuples returned: %.3f ---\n", $q->{total_tuples} / $invocations;
192   ($top) = sort { $b <=> $a } keys %{$q->{results}};
193   ($max) = sort { $b <=> $a } values %{$q->{results}};
194   if($max > 0) {
195     while($top >= -1) {
196       $cnt = exists $q->{results}->{$top} ? $q->{results}->{$top} : 0;
197       $bar = "#" x int(40 * $cnt / $max);
198       $bar .= " " x (40 - length($bar));
199       printf "    %10d | $bar     ($cnt)\n", ($top >= 0) ? (2 ** $top) : 0;
200       $top--;
201     }
202   }
203   print "\n";
204 }
205 sub finish {
206   if($histogram) {
207     my $qn = 1;
208     foreach my $q (sort { $b->{total_time} <=> $a->{total_time } } values %$hist) {
209       print_query_hist($qn++, $q);
210       last if --$topn == 0;
211     }
212   }
213   exit;
214 }
215
216 my $sniffer = Sniffer::Postgres->new(
217   callbacks => {
218       request  => sub { },
219       operation => \&operation,
220       log      => sub { print STDERR "$_[0]\n" if $VERBOSE },
221       tcp_log  => sub { print $_[0] if $VERBOSE > 1 },
222   }
223 );
224
225 $sniffer->inflight($inflight) if $inflight;
226
227 if($device) {
228   $sniffer->run( $device, $filter );
229 } elsif($file) {
230   $sniffer->run_file( $file, $filter );
231 }
Note: See TracBrowser for help on using the browser.