-
Notifications
You must be signed in to change notification settings - Fork 2
Expand file tree
/
Copy pathpgsi.html
More file actions
262 lines (245 loc) · 10.2 KB
/
pgsi.html
File metadata and controls
262 lines (245 loc) · 10.2 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
<?xml version="1.0" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>pgsi.pl - Produce system impact reports for a PostgreSQL database.</title>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
</head>
<body style="background-color: white">
<!-- INDEX BEGIN -->
<div name="index">
<p><a name="__index__"></a></p>
<ul>
<li><a href="#name">NAME</a></li>
<li><a href="#version">VERSION</a></li>
<li><a href="#usage">USAGE</a></li>
<li><a href="#description">DESCRIPTION</a></li>
<ul>
<li><a href="#log_data">Log Data</a></li>
<li><a href="#options">Options</a></li>
</ul>
<li><a href="#bugs">BUGS</a></li>
<li><a href="#author">AUTHOR</a></li>
<li><a href="#license_and_copyright">LICENSE AND COPYRIGHT</a></li>
</ul>
<hr name="index" />
</div>
<!-- INDEX END -->
<p>
</p>
<h1><a name="name">NAME</a></h1>
<p>pgsi.pl - Produce system impact reports for a PostgreSQL database.</p>
<p>
</p>
<hr />
<h1><a name="version">VERSION</a></h1>
<p>This documentation refers to version 1.7.2</p>
<p>
</p>
<hr />
<h1><a name="usage">USAGE</a></h1>
<p>pgsi.pl [options] < pglog_slice.log</p>
<pre>
or...</pre>
<p>pgsi.pl --file pglog_slice.log [options]</p>
<dl>
<dt><strong><a name="options" class="item">Options</a></strong></dt>
<dd>
<pre>
--file
--query-types
--top-10
--all
--pg-version
--offenders</pre>
</dd>
</dl>
<p>
</p>
<hr />
<h1><a name="description">DESCRIPTION</a></h1>
<p>System Impact (SI) is a measure of the overall load a given query imposes on a
server. It is expressed as a percentage of a query's average duration over its
average interval between successive calls. E.g., SI=80 indicates that a given
query is active 80% of the time during the entire log interval. SI=200
indicates the query is running twice at all times on average. Thus, the lower
the SI, the better.</p>
<p>The goal of SI is to identify those queries most likely to cause performance
degradation on the database during heaviest traffic periods. Focusing
exclusively on the least efficient queries can hide relatively fast-running
queries that saturate the system more because they are called far more
frequently. By contrast, focusing only on the most-frequently called queries
will tend to emphasize small, highly optimized queries at the expense of
slightly less popular queries that spend much more of their time between
successive calls in an active state. These are often smaller queries that have
failed to be optimized and punish a system severely under heavy load.</p>
<p>One thing SI does not do is distinguish between high-value queries represented
by extended active states or long durations due to blocking locks. Either
condition is worthy of attention, but determining which is to blame will
require independent investigation.</p>
<p>Queries are canonized with placeholders representing literals or arguments.
Further, IN lists are canonized so that variation from query to query only
in the number of elements in the IN list will not be treated as distinct
queries.</p>
<p>Some examples of the "same" query:</p>
<ul>
<li>
<pre>
SELECT col FROM table WHERE code = 'One';
SELECT col FROM table WHERE code = 'Sixty-Three';</pre>
</li>
<li>
<pre>
SELECT foo FROM bar WHERE fuzz = $1 AND color IN ('R','G','B');
Select FOO
from bar
WhErE fuzz = '56'
AND color IN ('R', $1);</pre>
</li>
</ul>
<p>Differences in capitalization and whitespace are irrelevant.</p>
<p>
</p>
<h2><a name="log_data">Log Data</a></h2>
<p>Pass in log data on stdin:</p>
<pre>
pgsi.pl < some_log_slice.log
cat some_log_slice.log | pgsi.pl</pre>
<p>Or use the --file option:</p>
<pre>
pgsi.pl --file=some_log_slice.log</pre>
<p>Or read in more than one file at a time:</p>
<pre>
pgsi.pl --file=logfile1.log --file=logfile2.log</pre>
<p>If more than one file is given, they must be given in chronological order.</p>
<p>Log data must comply with a specific format and must be from contiguous
activity. The code makes the assumption that the overall interval of activity
is the time elapsed between the first and last log entries. If there are
several blocks of logs to analyze, they must be run separately.</p>
<p>Required format is the following in syslog:</p>
<p>YYYY-MM-DDTHH24:MI:SS(-TZ:00)? server postgres[<em>pid</em>]:</p>
<p>This also requires that log_statement is set to 'all' and
that log_duration be set to 'on' in postgresql.conf.
If you are not using syslog, you can simulate the format with the following:</p>
<p>log_line_prefix = '%t %h postgres[%p]: [%l-1] ' ## Simulate syslog for pgsi.</p>
<p>
</p>
<h2><a name="options">Options</a></h2>
<dl>
<dt><strong><a name="query_types" class="item">--query-types</a></strong></dt>
<dd>
<p>Query impact is segregated by types. I.e., all the SELECTs together, all
UPDATEs together, etc. Typically it is assumed that SELECT is the most
interesting (and is by itself the default), but any query type may be analyzed.
Multiples are provided as space- or comma-separated lists.</p>
<pre>
pgsi.pl --query-types="select, update, copy, create"</pre>
<p>The code will produce a unique report for each type when used with the --all
and/or --top-10 file-pattern options (see below).</p>
</dd>
<dt><strong><a name="top_10_all" class="item">--top-10, --all</a></strong></dt>
<dd>
<p>Supplies a file <em>pattern</em> and optional directory path into which the reports
should be written per --query-type. The pattern is prefixed with the
--query-type and host for this report and placed into the requested directory
(or cwd if no path is present).</p>
<p>--all will list every canonized query encountered, which is likely to
contain a large number of queries of no interest (those with negligible
impact).</p>
<p>--top-10 limits the report to only the 10 entries with the greatest SI.</p>
<pre>
pgsi.pl \
--query-types=select,update \
--all=si_reports/monday_10am_1pm.all.txt \
--top-10=si_reports/monday_10am_1pm.t10.txt</pre>
<p>This will produce the following reports in si_reports/ for a database running
on server db1:</p>
<pre>
SELECT-db1-monday_10am_1pm.all.txt
UPDATE-db1-monday_10am_1pm.all.txt
SELECT-db1-monday_10am_1pm.t10.txt
UPDATE-db1-monday_10am_1pm.t10.txt</pre>
<p>If --top-10 is not supplied, then no top 10 report is generated. If --all is
not supplied, then the report(s) print to stdout.</p>
</dd>
<dt><strong><a name="pg_version" class="item">--pg-version</a></strong></dt>
<dd>
<p>Currently, this might better be described as either "before DETAIL" or "after
DETAIL". The code was written against PG 8.1 originally, but when 8.2 came out
the addition of DETAIL log entries forced a different parser. That unfortunate
timing led to the assumption that log construction would change with each
release. Going forward, --pg-version will be (other than 8.1) the first version
in which this log format was encountered.</p>
<p>--pg-version is only either 8.1 or 8.2 (8.2 is default). It's unknown how far
back in versions the 8.1 format holds, but 8.2 holds for itself and 8.3. So,
unless you're working against logs generated by a PG version less than 8.2, you
do not need to include this option (but it might save you some trouble if a new
format comes at a later version and the default bumps up to the most recent
while you stay on your older version).</p>
<pre>
pgsi.pl --pg-version=8.1</pre>
</dd>
<dt><strong><a name="offenders" class="item">--offenders</a></strong></dt>
<dd>
<p>Number of best and worst queries to included with the report, in terms of
overall duration of execution. Enough log information is listed along with the
duration such that tracking down the original query (not the canonized
version) is straightforward. The offenders list can be very useful for a query
that is causing trouble in a handful of permutations, but most of the time is
behaving well.</p>
<p>The list in conjunction with standard deviation gives an overall indication of
performance volatility.</p>
<p>--offenders=5 produces additional output in the report that looks something
like the following example:</p>
<pre>
Best
1. 2009-01-12T10:11:49-07:00 db1 postgres[4692] -- 4.833 ms
2. 2009-01-12T10:31:19-07:00 db1 postgres[1937] -- 4.849 ms
3. 2009-01-12T09:16:20-07:00 db1 postgres[20294] -- 4.864 ms
4. 2009-01-12T10:16:54-07:00 db1 postgres[20955] -- 4.867 ms
5. 2009-01-12T10:32:16-07:00 db1 postgres[5010] -- 4.871 ms</pre>
<pre>
Worst
1. 2009-01-12T10:00:07-07:00 db1 postgres[2804] -- 2175.650 ms
2. 2009-01-12T09:30:07-07:00 db1 postgres[2804] -- 2090.914 ms
3. 2009-01-12T10:00:18-07:00 db1 postgres[2804] -- 2046.608 ms
4. 2009-01-12T09:30:10-07:00 db1 postgres[2804] -- 1954.604 ms
5. 2009-01-12T11:20:11-07:00 db1 postgres[2804] -- 1788.576 ms</pre>
</dd>
</dl>
<p>
</p>
<hr />
<h1><a name="bugs">BUGS</a></h1>
<ul>
<li>
<p>If queries contain exceptionally long IN lists, the regex that attempts to
flatten them can run into a perl recursion limit. In that event, the query will
keep the placeholders of the IN list, making it unique compared to the same
query with a different cardinality of list params in the same IN. This
deficiency should only surface on IN lists with composite parameters [e.g., IN
((?,?,...,?),(?,?,...,?),...,(?,?,...,?))]. For scalar IN lists, there should
be no such limit.</p>
</li>
</ul>
<p>
</p>
<hr />
<h1><a name="author">AUTHOR</a></h1>
<p>Original code:
Mark Johnson (<a href="mailto:mark@endpoint.com">mark@endpoint.com</a>), End Point Corp.</p>
<p>Contributions:
Ethan Rowe (<a href="mailto:ethan@endpoint.com">ethan@endpoint.com</a>), End Point Corp.
Greg Sabino Mullane (<a href="mailto:greg@endpoint.com">greg@endpoint.com</a>), End Point Corp.
Daniel Browning (<a href="mailto:db@endpoint.com">db@endpoint.com</a>), End Point Corp.
Joshua Tolley <<a href="mailto:josh@endpoint.com">josh@endpoint.com</a>>, End Point Corp.
Abraham Ingersoll <<a href="mailto:abe@abe.us">abe@abe.us</a>></p>
<p>
</p>
<hr />
<h1><a name="license_and_copyright">LICENSE AND COPYRIGHT</a></h1>
<p>Copyright 2008-2011 Mark Johnson (<a href="mailto:mark@endpoint.com">mark@endpoint.com</a>)</p>
<p>This module is free software; you can redistribute it and/or modify it
under the same terms as Perl itself. See the LICENSE file.</p>
</body>
</html>