Merge lp:~ilyash/percona-toolkit/tcpdump-raw-queries into lp:percona-toolkit/2.1

[Ilya Sher]

pt-query-digest: added --type tcpdumpq

Similar to tcpdump but output all queries. No digesting is performed. This can
be useful to reporoduce loads facilitating the replay of the actual queries.

[Daniel Nichter (daniel-nichter)]

Thanks for the branch; it's well-written. I think, however, that there's an existing solution for this:

$ ./pt-query-digest --type tcpdump ../t/lib/samples/tcpdump/tcpdump001.txt --print --no-report

# Time: 090412 09:50:16.805123
# Client: 127.0.0.1:42167
# Thread_id: 4294967296
# Query_time: 0.000274 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
select "hello world" as greeting;

So the output is a little different, but still has the same info. Does this work for you?

[Ilya Sher (ilyash)]

Hi.
Yes. Thanks!
This should do the trick and simplify parsing of the output:
./pt-query-digest --type tcpdump ../t/lib/samples/tcpdump/tcpdump001.txt
--no-report --filter 'print $event->{host}, ",", $event->{port}, "\n",
$event->{arg}, "\n"'

It was an RTFM fail I guess :) ... and I think I know why:

man pt-query-digest | wc -l
1604

I was looking near tcpdump, maybe an example there would be helpful noting
this could be helpful for replay.

... or maybe pt-query-digest should be split into several utilities (more
"Unix way"). Currently the pipeline is inside pt-query-digest while it
could be the shell's pipe between smaller utilities. This way it could be
more clear how to do things. In my case I would just cut the pipeline
earlier and just format the events.

Regards,
Ilya

On Fri, Oct 12, 2012 at 6:18 PM, Daniel Nichter <email address hidden> wrote:

> Thanks for the branch; it's well-written. I think, however, that there's
> an existing solution for this:
>
> $ ./pt-query-digest --type tcpdump ../t/lib/samples/tcpdump/tcpdump001.txt
> --print --no-report
>
> # Time: 090412 09:50:16.805123
> # Client: 127.0.0.1:42167
> # Thread_id: 4294967296
> # Query_time: 0.000274 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
> select "hello world" as greeting;
>
> So the output is a little different, but still has the same info. Does
> this work for you?
> --
>
> https://code.launchpad.net/~ilyash/percona-toolkit/tcpdump-raw-queries/+merge/124624
> You are the owner of lp:~ilyash/percona-toolkit/tcpdump-raw-queries.
>

--

Ilya Sher, Coding-Knight LTD.
[phone]
http://coding-knight.com/

[Daniel Nichter (daniel-nichter)]

Ah yes, --no-report --filter would allow you to get customized output. Glad that works better for you.

As for breaking the tool up: we plan to simplify it. I don't know if we'll make more tools, but rather just focus this one tool more.

Since the problem has been solved, I'll delete this merge proposal, else it will continue to show up on Launchpad indefinitely I think. :-)

[Daniel Nichter (daniel-nichter)]

review: Disapprove

[Daniel Nichter (daniel-nichter)]

Rather than delete it, I just marked it "disapprove", which also removed it from the list of branches ready for review.

Unmerged revisions

394. By Ilya Sher on 2012-09-17

Added --type tcpdumpq to print all queries found in a tcpdump

=== modified file 'bin/pt-query-digest'
--- bin/pt-query-digest	2012-09-14 12:33:52 +0000
+++ bin/pt-query-digest	2012-09-17 08:59:25 +0000
@@ -22,6 +22,7 @@
       Processlist
       TcpdumpParser
       MySQLProtocolParser
+      TcpdumpQueriesPrinter
       SysLogParser
       PgLogParser
       SlowLogParser
@@ -4112,6 +4113,44 @@
 # ###########################################################################
 
 # ###########################################################################
+# TcpdumpQueriesPrinter package
+# This package is a copy without comments from the original.  The original
+# with comments and its test file can be found in the Bazaar repository at,
+#   lib/TcpdumpQueriesPrinter.pm
+#   t/lib/TcpdumpQueriesPrinter.t
+# See https://launchpad.net/percona-toolkit for more information.
+# ###########################################################################
+{
+
+package TcpdumpQueriesPrinter;
+use strict;
+use warnings FATAL => 'all';
+use English qw(-no_match_vars);
+use constant PTDEBUG => $ENV{PTDEBUG} || 0;
+use Data::Dumper;
+
+sub new {
+   my ( $class ) = @_;
+   my $self = { };
+   return bless $self, $class;
+}
+
+sub parse_event {
+   my ( $self, %args ) = @_;
+   my $event = $args{event};
+   my @fields = qw(host port user);
+   print join(',', map { "$event->{$_}" } @fields);
+   print "\n$event->{arg}\n";
+   undef $args{event};
+}
+
+1;
+}
+# ###########################################################################
+# End TcpdumpQueriesPrinter package
+# ###########################################################################
+
+# ###########################################################################
 # SysLogParser package
 # This package is a copy without comments from the original.  The original
 # with comments and its test file can be found in the Bazaar repository at,
@@ -13480,6 +13519,7 @@
             binlog    => ['BinaryLogParser'],
             genlog    => ['GeneralLogParser'],
             tcpdump   => ['TcpdumpParser','MySQLProtocolParser'],
+            tcpdumpq  => ['TcpdumpParser','MySQLProtocolParser', 'TcpdumpQueriesPrinter'],
             memcached => ['TcpdumpParser','MemcachedProtocolParser',
                           'MemcachedEvent'],
             http      => ['TcpdumpParser','HTTPProtocolParser'],
@@ -16660,6 +16700,22 @@
 Server-side prepared statements are supported.  SSL-encrypted traffic cannot be
 inspected and decoded.
 
+=item tcpdumpq
+
+Similar to tcpdump but output all queries. No digesting is performed. This can
+be useful to reporoduce loads facilitating the replay of the actual queries.
+
+The output format is 2 lines for each query:
+
+  host,port,user
+  query
+
+Host and port specifiy the remote TCP host and port. User is the MySQL user.
+In order to split the output to files it can be piped for example to the
+following command:
+
+  gawk 'NR % 2 == 1 {f=$0; getline; print >> "thread-" f }'
+
 =item memcached
 
 Similar to tcpdump, but the expected input is memcached packets

=== added file 'lib/TcpdumpQueriesPrinter.pm'
--- lib/TcpdumpQueriesPrinter.pm	1970-01-01 00:00:00 +0000
+++ lib/TcpdumpQueriesPrinter.pm	2012-09-17 08:59:25 +0000
@@ -0,0 +1,51 @@
+# This program is copyright 2007-2011 Baron Schwartz, 2011 Percona Inc.
+# Feedback and improvements are welcome.
+#
+# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
+# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
+# MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
+#
+# 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; OR the Perl Artistic License.  On UNIX and similar
+# systems, you can issue `man perlgpl' or `man perlartistic' to read these
+# licenses.
+#
+# 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., 59 Temple
+# Place, Suite 330, Boston, MA  02111-1307  USA.
+# ###########################################################################
+# TcpdumpParser package
+# ###########################################################################
+{
+# Package: TcpdumpQueriesPrinter
+# TcpdumpQueriesPrinter prints queries captured from tcpdump.
+# Outputs 2 lines per query:
+#   host,port,user
+#   query
+
+package TcpdumpQueriesPrinter;
+use strict;
+use warnings FATAL => 'all';
+use English qw(-no_match_vars);
+use constant PTDEBUG => $ENV{PTDEBUG} || 0;
+use Data::Dumper;
+
+sub new {
+   my ( $class ) = @_;
+   my $self = { };
+   return bless $self, $class;
+}
+
+sub parse_event {
+   my ( $self, %args ) = @_;
+   my $event = $args{event};
+   my @fields = qw(host port user);
+   print join(',', map { "$event->{$_}" } @fields);
+   print "\n$event->{arg}\n";
+   undef $args{event};
+}
+
+1;
+}
+