.\" Automatically generated by Pod::Man 2.25 (Pod::Simple 3.16) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is turned on, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .ie \nF \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . nr % 0 . rr F .\} .el \{\ . de IX .. .\} .\" .\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2). .\" Fear. Run. Save yourself. No user-serviceable parts. . \" fudge factors for nroff and troff .if n \{\ . ds #H 0 . ds #V .8m . ds #F .3m . ds #[ \f1 . ds #] \fP .\} .if t \{\ . ds #H ((1u-(\\\\n(.fu%2u))*.13m) . ds #V .6m . ds #F 0 . ds #[ \& . ds #] \& .\} . \" simple accents for nroff and troff .if n \{\ . ds ' \& . ds ` \& . ds ^ \& . ds , \& . ds ~ ~ . ds / .\} .if t \{\ . ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u" . ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u' . ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u' . ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u' . ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u' . ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u' .\} . \" troff and (daisy-wheel) nroff accents .ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V' .ds 8 \h'\*(#H'\(*b\h'-\*(#H' .ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#] .ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H' .ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u' .ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#] .ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#] .ds ae a\h'-(\w'a'u*4/10)'e .ds Ae A\h'-(\w'A'u*4/10)'E . \" corrections for vroff .if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u' .if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u' . \" for low resolution devices (crt and lpr) .if \n(.H>23 .if \n(.V>19 \ \{\ . ds : e . ds 8 ss . ds o a . ds d- d\h'-1'\(ga . ds D- D\h'-1'\(hy . ds th \o'bp' . ds Th \o'LP' . ds ae ae . ds Ae AE .\} .rm #[ #] #H #V #F C .\" ======================================================================== .\" .IX Title "PT-TCP-MODEL 1p" .TH PT-TCP-MODEL 1p "2012-06-15" "perl v5.14.2" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "NAME" pt\-tcp\-model \- Transform tcpdump into metrics that permit performance and scalability modeling. .SH "SYNOPSIS" .IX Header "SYNOPSIS" Usage: pt-tcp-model [\s-1OPTION\s0...] [\s-1FILE\s0] .PP pt-tcp-model parses and analyzes tcpdump files. With no \s-1FILE\s0, or when \&\s-1FILE\s0 is \-, it read standard input. .PP Dump \s-1TCP\s0 requests and responses to a file, capturing only the packet headers to avoid dropped packets, and ignoring any packets without a payload (such as ack-only packets). Capture port 3306 (MySQL database traffic). Note that to avoid line breaking in terminals and man pages, the \s-1TCP\s0 filtering expression that follows has a line break at the end of the second line; you should omit this from your tcpdump command. .PP .Vb 4 \& tcpdump \-s 384 \-i any \-nnq \-tttt \e \& \*(Aqtcp port 3306 and (((ip[2:2] \- ((ip[0]&0xf)<<2)) \& \- ((tcp[12]&0xf0)>>2)) != 0)\*(Aq \e \& > /path/to/tcp\-file.txt .Ve .PP Extract individual response times, sorted by end time: .PP .Vb 1 \& pt\-tcp\-model /path/to/tcp\-file.txt > requests.txt .Ve .PP Sort the result by arrival time, for input to the next step: .PP .Vb 1 \& sort \-n \-k1,1 requests.txt > sorted.txt .Ve .PP Slice the result into 10\-second intervals and emit throughput, concurrency, and response time metrics for each interval: .PP .Vb 1 \& pt\-tcp\-model \-\-type=requests \-\-run\-time=10 sorted.txt > sliced.txt .Ve .PP Transform the result for modeling with Aspersa's usl tool, discarding the first and last line of each file if you specify multiple files (the first and last line are normally incomplete observation periods and are aberrant): .PP .Vb 3 \& for f in sliced.txt; do \& tail \-n +2 "$f" | head \-n \-1 | awk \*(Aq{print $2, $3, $7/$4}\*(Aq \& done > usl\-input.txt .Ve .SH "RISKS" .IX Header "RISKS" The following section is included to inform users about the potential risks, whether known or unknown, of using this tool. The two main categories of risks are those created by the nature of the tool (e.g. read-only tools vs. read-write tools) and those created by bugs. .PP pt-tcp-model merely reads and transforms its input, printing it to the output. It should be very low risk. .PP At the time of this release, we know of no bugs that could cause serious harm to users. .PP The authoritative source for updated information is always the online issue tracking system. Issues that affect this tool will be marked as such. You can see a list of such issues at the following \s-1URL:\s0 http://www.percona.com/bugs/pt\-tcp\-model . .PP See also \*(L"\s-1BUGS\s0\*(R" for more information on filing bugs and getting help. .SH "DESCRIPTION" .IX Header "DESCRIPTION" This tool recognizes requests and responses in a \s-1TCP\s0 stream, and extracts the \&\*(L"conversations\*(R". You can use it to capture the response times of individual queries to a database, for example. It expects the \s-1TCP\s0 input to be in the following format, which should result from the sample shown in the \s-1SYNOPSIS:\s0 .PP .Vb 1 \& IP > : .Ve .PP The tool watches for \*(L"incoming\*(R" packets to the port you specify with the \&\*(L"\-\-watch\-server\*(R" option. This begins a request. If multiple inbound packets follow each other, then by default the last inbound packet seen determines the time at which the request is assumed to begin. This is logical if one assumes that a server must receive the whole \s-1SQL\s0 statement before beginning execution, for example. .PP When the first outbound packet is seen, the server is considered to have responded to the request. The tool might see an inbound packet, but never see a response. This can happen when the kernel drops packets, for example. As a result, the tool never prints a request unless it sees the response to it. However, the tool actually does not print any request until it sees the \*(L"last\*(R" outbound packet. It determines this by waiting for either another inbound packet, or \s-1EOF\s0, and then considers the previous inbound/outbound pair to be complete. As a result, the tool prints requests in a relatively random order. Most types of analysis require processing in either arrival or completion order. Therefore, the second type of processing this tool can do requires that you sort the output from the first stage and supply it as input. .PP The second type of processing is selected with the \*(L"\-\-type\*(R" option set to \&\*(L"requests\*(R". In this mode, the tool reads a group of requests and aggregates them, then emits the aggregated metrics. .SH "OUTPUT" .IX Header "OUTPUT" In the default mode (parsing tcpdump output), requests are printed out one per line, in the following format: .PP .Vb 1 \& .Ve .PP The \s-1ID\s0 is an incrementing number, assigned in arrival order in the original \s-1TCP\s0 traffic. The start and end timestamps, and the elapsed time, can be customized with the \*(L"\-\-start\-end\*(R" option. .PP In \*(L"\-\-type=requests\*(R" mode, the tool prints out one line per time interval as defined by \*(L"\-\-run\-time\*(R", with the following columns: ts, concurrency, throughput, arrivals, completions, busy_time, weighted_time, sum_time, variance_mean, quantile_time, obs_time. A detailed explanation follows: .IP "ts" 4 .IX Item "ts" The timestamp that defines the beginning of the interval. .IP "concurrency" 4 .IX Item "concurrency" The average number of requests resident in the server during the interval. .IP "throughput" 4 .IX Item "throughput" The number of arrivals per second during the interval. .IP "arrivals" 4 .IX Item "arrivals" The number of arrivals during the interval. .IP "completions" 4 .IX Item "completions" The number of completions during the interval. .IP "busy_time" 4 .IX Item "busy_time" The total amount of time during which at least one request was resident in the server during the interval. .IP "weighted_time" 4 .IX Item "weighted_time" The total response time of all the requests resident in the server during the interval, including requests that neither arrived nor completed during the interval. .IP "sum_time" 4 .IX Item "sum_time" The total response time of all the requests that arrived in the interval. .IP "variance_mean" 4 .IX Item "variance_mean" The variance-to-mean ratio (index of dispersion) of the response times of the requests that arrived in the interval. .IP "quantile_time" 4 .IX Item "quantile_time" The Nth percentile response time for all the requests that arrived in the interval. See also \*(L"\-\-quantile\*(R". .IP "obs_time" 4 .IX Item "obs_time" The length of the observation time window. This will usually be the same as the interval length, except for the first and last intervals in a file, which might have a shorter observation time. .SH "OPTIONS" .IX Header "OPTIONS" This tool accepts additional command-line arguments. Refer to the \&\*(L"\s-1SYNOPSIS\s0\*(R" and usage information for details. .IP "\-\-config" 4 .IX Item "--config" type: Array .Sp Read this comma-separated list of config files; if specified, this must be the first option on the command line. .IP "\-\-help" 4 .IX Item "--help" Show help and exit. .IP "\-\-progress" 4 .IX Item "--progress" type: array; default: time,30 .Sp Print progress reports to \s-1STDERR\s0. The value is a comma-separated list with two parts. The first part can be percentage, time, or iterations; the second part specifies how often an update should be printed, in percentage, seconds, or number of iterations. .IP "\-\-quantile" 4 .IX Item "--quantile" type: float .Sp The percentile for the last column when \*(L"\-\-type\*(R" is \*(L"requests\*(R" (default .99). .IP "\-\-run\-time" 4 .IX Item "--run-time" type: float .Sp The size of the aggregation interval in seconds when \*(L"\-\-type\*(R" is \*(L"requests\*(R" (default 1). Fractional values are permitted. .IP "\-\-start\-end" 4 .IX Item "--start-end" type: Array; default: ts,end .Sp Define how the arrival and completion timestamps of a query, and thus its response time (elapsed time) are computed. Recall that there may be multiple inbound and outbound packets per request and response, and refer to the following \s-1ASCII\s0 diagram. Suppose that a client sends a series of three inbound (I) packets to the server, which computes the result and then sends two outbound (O) packets back: .Sp .Vb 3 \& I I I ..................... O O \& |<\-\-\-\->|<\-\-\-response time\-\-\-\-\->|<\-\->| \& ts0 ts end end1 .Ve .Sp By default, the query is considered to arrive at time ts, and complete at time end. However, this might not be what you want. Perhaps you do not want to consider the query to have completed until time end1. You can accomplish this by setting this option to \f(CW\*(C`ts,end1\*(C'\fR. .IP "\-\-type" 4 .IX Item "--type" type: string .Sp The type of input to parse (default tcpdump). The permitted types are .RS 4 .IP "tcpdump" 4 .IX Item "tcpdump" The parser expects the input to be formatted with the following options: \f(CW\*(C`\-x \-n \&\-q \-tttt\*(C'\fR. For example, if you want to capture output from your local machine, you can do something like the following (the port must come last on FreeBSD): .Sp .Vb 3 \& tcpdump \-s 65535 \-x \-nn \-q \-tttt \-i any \-c 1000 port 3306 \e \& > mysql.tcp.txt \& pt\-query\-digest \-\-type tcpdump mysql.tcp.txt .Ve .Sp The other tcpdump parameters, such as \-s, \-c, and \-i, are up to you. Just make sure the output looks like this (there is a line break in the first line to avoid man-page problems): .Sp .Vb 2 \& 2009\-04\-12 09:50:16.804849 IP 127.0.0.1.42167 \& > 127.0.0.1.3306: tcp 37 .Ve .Sp All MySQL servers running on port 3306 are automatically detected in the tcpdump output. Therefore, if the tcpdump out contains packets from multiple servers on port 3306 (for example, 10.0.0.1:3306, 10.0.0.2:3306, etc.), all packets/queries from all these servers will be analyzed together as if they were one server. .Sp If you're analyzing traffic for a protocol that is not running on port 3306, see \*(L"\-\-watch\-server\*(R". .RE .RS 4 .RE .IP "\-\-version" 4 .IX Item "--version" Show version and exit. .IP "\-\-watch\-server" 4 .IX Item "--watch-server" type: string; default: 10.10.10.10:3306 .Sp This option tells pt-tcp-model which server \s-1IP\s0 address and port (such as \&\*(L"10.0.0.1:3306\*(R") to watch when parsing tcpdump for \*(L"\-\-type\*(R" tcpdump. If you don't specify it, the tool watches all servers by looking for any \s-1IP\s0 address using port 3306. If you're watching a server with a non-standard port, this won't work, so you must specify the \s-1IP\s0 address and port to watch. .Sp Currently, \s-1IP\s0 address filtering isn't implemented; so even though you must specify the option in IP:port form, it ignores the \s-1IP\s0 and only looks at the port number. .SH "ENVIRONMENT" .IX Header "ENVIRONMENT" The environment variable \f(CW\*(C`PTDEBUG\*(C'\fR enables verbose debugging output to \s-1STDERR\s0. To enable debugging and capture all output to a file, run the tool like: .PP .Vb 1 \& PTDEBUG=1 pt\-tcp\-model ... > FILE 2>&1 .Ve .PP Be careful: debugging output is voluminous and can generate several megabytes of output. .SH "SYSTEM REQUIREMENTS" .IX Header "SYSTEM REQUIREMENTS" You need Perl, \s-1DBI\s0, DBD::mysql, and some core packages that ought to be installed in any reasonably new version of Perl. .SH "BUGS" .IX Header "BUGS" For a list of known bugs, see http://www.percona.com/bugs/pt\-tcp\-model . .PP Please report bugs at https://bugs.launchpad.net/percona\-toolkit . Include the following information in your bug report: .IP "\(bu" 4 Complete command-line used to run the tool .IP "\(bu" 4 Tool \*(L"\-\-version\*(R" .IP "\(bu" 4 MySQL version of all servers involved .IP "\(bu" 4 Output from the tool including \s-1STDERR\s0 .IP "\(bu" 4 Input files (log/dump/config files, etc.) .PP If possible, include debugging output by running the tool with \f(CW\*(C`PTDEBUG\*(C'\fR; see \*(L"\s-1ENVIRONMENT\s0\*(R". .SH "DOWNLOADING" .IX Header "DOWNLOADING" Visit http://www.percona.com/software/percona\-toolkit/ to download the latest release of Percona Toolkit. Or, get the latest release from the command line: .PP .Vb 1 \& wget percona.com/get/percona\-toolkit.tar.gz \& \& wget percona.com/get/percona\-toolkit.rpm \& \& wget percona.com/get/percona\-toolkit.deb .Ve .PP You can also get individual tools from the latest release: .PP .Vb 1 \& wget percona.com/get/TOOL .Ve .PP Replace \f(CW\*(C`TOOL\*(C'\fR with the name of any tool. .SH "AUTHORS" .IX Header "AUTHORS" Baron Schwartz .SH "ABOUT PERCONA TOOLKIT" .IX Header "ABOUT PERCONA TOOLKIT" This tool is part of Percona Toolkit, a collection of advanced command-line tools developed by Percona for MySQL support and consulting. Percona Toolkit was forked from two projects in June, 2011: Maatkit and Aspersa. Those projects were created by Baron Schwartz and developed primarily by him and Daniel Nichter, both of whom are employed by Percona. Visit for more software developed by Percona. .SH "COPYRIGHT, LICENSE, AND WARRANTY" .IX Header "COPYRIGHT, LICENSE, AND WARRANTY" This program is copyright 2011 Baron Schwartz, 2011\-2012 Percona Inc. Feedback and improvements are welcome. .PP \&\s-1THIS\s0 \s-1PROGRAM\s0 \s-1IS\s0 \s-1PROVIDED\s0 \*(L"\s-1AS\s0 \s-1IS\s0\*(R" \s-1AND\s0 \s-1WITHOUT\s0 \s-1ANY\s0 \s-1EXPRESS\s0 \s-1OR\s0 \s-1IMPLIED\s0 \&\s-1WARRANTIES\s0, \s-1INCLUDING\s0, \s-1WITHOUT\s0 \s-1LIMITATION\s0, \s-1THE\s0 \s-1IMPLIED\s0 \s-1WARRANTIES\s0 \s-1OF\s0 \&\s-1MERCHANTABILITY\s0 \s-1AND\s0 \s-1FITNESS\s0 \s-1FOR\s0 A \s-1PARTICULAR\s0 \s-1PURPOSE\s0. .PP This program is free software; you can redistribute it and/or modify it under the terms of the \s-1GNU\s0 General Public License as published by the Free Software Foundation, version 2; \s-1OR\s0 the Perl Artistic License. On \s-1UNIX\s0 and similar systems, you can issue `man perlgpl' or `man perlartistic' to read these licenses. .PP You should have received a copy of the \s-1GNU\s0 General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, \s-1MA\s0 02111\-1307 \s-1USA\s0. .SH "VERSION" .IX Header "VERSION" pt-tcp-model 2.1.2