forked from darold/pgcluu
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathREADME
345 lines (272 loc) · 16 KB
/
README
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
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
NAME
pgCluu - PostgreSQL Cluster utilization
DESCRIPTION
pgCluu is a PostgreSQL performance monitoring and auditing tool.
It is a Perl program used to perform a full audit of a PostgreSQL
Cluster and System performance. It is divided in two parts:
- A collector used to grab statistics on the PostgreSQL cluster using
the psql command line utility and sar from the sysstat package.
- A pure Perl grapher that will generate all HTML and charts output
without any requirements.
If you don't need system utilization reports or don't want to install
the sysstat package, you can disable it at command line. You will only
have reports about your PostgreSQL Cluster. If you are running pgCluu
from a central server using option -h to monitor remotely a PostgreSQL
Cluster, the call to sar is automatically disabled.
If you just want to have system utilization reports or generate graphs
from a sar data file, it's also possible.
SYNOPSIS
PostgreSQL and System metrics collector.
pgcluu_collectd [options] output_dir
Report generator.
pgcluu [options] -o report_dir input_dir
REQUIREMENT
pgCluu comes with two Perl scripts. You need a modern Perl distribution,
the psql client and the sar command line utility (sysstat). The sysstat
package is optional, you can still use pgCluu to generate reports about
your PostgreSQL Cluster without it.
Charts are rendered using a Javascript library so you don't need
anything else. Your browser will do all the work.
INSTALLATION
Download the tarball from SourceForge and unpack the archive:
tar xzf pgcluu-2.x.tar.gz
cd pgcluu-2.x/
perl Makefile.PL
make && sudo make install
This will copy the Perl scripts pgcluu_collectd and pgcluu into the
default /usr/local/bin directory and the man page to
/usr/local/share/man/man1/pgcluu.1. Those are the default installation
directories for 'site' install.
If you want to install all under /usr location, use INSTALLDIRS='perl'
as an argument of Makefile.PL. The script will be installed into
/usr/bin/pgcluu and the manpage into /usr/share/man/man1/pgcluu.1.
For example, to install everything just like Debian does, proceed as
follows:
perl Makefile.PL INSTALLDIRS=vendor
By default INSTALLDIRS is set to site.
USAGE
See next two chapters for a complete description of the command line
options. For the impatients, here some simple commands that could be run
as postgres user:
mkdir /tmp/stat_db1/
pgcluu_collectd -D -i 60 /tmp/stat_db1/
LOG: Detach from terminal with pid: 11323
or with more options
pgcluu_collectd -D -i 60 /tmp/stat_db1/ -h 10.10.1.1 -U postgres -d mydb
LOG: Detach from terminal with pid: 14671
wait some time and activity on your PostgreSQL Cluster... Then stop the
pgcluu_collectd daemon and generate the report:
pgcluu_collectd -k
LOG: Received terminating signal.
mkdir /tmp/report_db1/
pgcluu -o /tmp/report_db1/ /tmp/stat_db1/
You should obtain something like example at
http://pgcluu.darold.net/example/
By default all javascript, css and the webfont fontawesome are
automatically generated into the output directory if those files does
not already exits.
COLLECTING STATISTICS
To generate reports about your PostgreSQL Cluster Utilization you must
collect statistics before. pgcluu_collectd is here for that. It can be
run in a daemon mode (option -D) or in interactive mode for debugging
purpose. All you need is to provide a directory where data will be
stored. Statistics will be pooled at a default interval of 60 seconds,
using option -i you can customize it. See below for a complete list of
command line options.
usage: pgcluu_collectd [options] output_dir
output_dir: full path to directory where pgcluu_collectd will
store statistics.
options:
-B, --enable-buffercache enable buffercache statistics if pg_buffercache
extension is installed.
-C, --end-counter=NUM terminate program after NUM reports.
-d, --dbname=DATABASE database name to connect to. Default to current user.
-D, --daemonize detach from console and enter in daemon mode.
-E, --end-after=SECOND self terminate program after a given number of seconds.
Can be written: 7200 or 120M or 2H, for days use 7D for
example to stop collecting data after seven days.
-f, --pid-file=FILE path to pid file. Default: /tmp/pgcluu_collectd.pid.
-h, --host=HOSTNAME database server host or socket directory
-i, --interval=NUM time to wait between runs
-k, --kill stop current pgcluu_collectd running daemon.
-m, --metric=METRIC set a coma separated list of metrics to perform.
-M, --max-size=SIZE self terminate program when the size of the output dir
exceed a given size. Can be written: 2GB or 2000MB.
-p, --port=PORT database port(s) to connect to. Defaults to 5432.
-P, --psql=BIN path to the psql command. Default: psql.
-Q, --no-statement do not collect queries statistics from pg_stat_statements.
-s, --sar=BIN path to sar sysstat command. Default: sar.
-S, --disable-sar disable collect of system statistics with sar.
-T, --notablespace disable lookup at tablespace when the connect user
is not superuser to avoid printing an error message.
-U, --dbuser=USERNAME database user to connect as. Default to current user.
-v, --verbose Print out debug informations.
-V, --version Show pgcluu_collectd version and exit.
--included-db=DATABASE do not collect statistics for the comma separated list
of database name.
--list-metric list available metrics actions that can be performed.
--sysinfo get operating system information and exit (sysinfo.txt).
--no-sysinfo do not collect operating system information at all.
--no-database do not collect database statistics at all.
--pgbouncer-args=OPTIONS Option to used to connect to the pgbouncer system
database. Ex: -p 6432 -U postgres -h 192.168.1.100
You must at least give one parameter to enable
pgbouncer monitoring.
--sar-file=FILE path to sar output data file for sysstat stats
Default to output_dir/sar_stats.dat.
--stat-type all|user Set stats tables to read. Values: 'all' or 'user' to
look at pg_stat_(all|user) tables. Default: user.
--pgversion X.Y force the PostgreSQL version to the given value.
--pgservice NAME Name of service inside of the pg_service.conf file.
--exclude-time RANGE exclude a laps of time by giving the start and end
hours.
--help print usage
Use those options to execute sar on the remote host defined by the -h
option, otherwise it will be executed locally:
--enable-ssh activate the use of ssh to run sysstat remotely.
--ssh-program ssh path to the ssh program to use. Default: ssh.
--ssh-user username connection login name. Default to running user.
--ssh-identity file path to the identity file to use.
--ssh-timeout second timeout to ssh connection failure. Default 10 seconds.
--ssh-options options list of -o options to use for the ssh connection. Options
always used:
-o ConnectTimeout=$ssh_timeout
-o PreferredAuthentications=hostbased,publickey
For example, as postgres user to monitor locally a full PostgreSQL
cluster:
mkdir /tmp/stat_db1/
pgcluu_collectd -D -i 60 /tmp/stat_db1/
to collect statistics from pgbouncer too, and limit database statistics
to a single database:
pgcluu_collectd -D -i 60 /tmp/stat_db1/ -h 10.10.1.1 -U postgres -d mydb
--pgbouncer-args='-p 5342'
to disable statistics collect between 22:30 and 06:30 the next day:
pgcluu_collectd -D -i 60 /tmp/stat_db1/ --exclude-time "22:30-06:30"
to collect statistics from a remote server:
pgcluu_collectd -D -i 60 /tmp/stat_db1/ -h 10.0.0.1 -U postgres --disable-sar
the same but collecting system statistics using remote sar calls:
pgcluu_collectd -D -i 60 /tmp/stat_db1/ -h 10.0.0.1 -U postgres --enable-ssh
--ssh-user postgres --ssh-identity /var/lib/postgresql/.ssh/id_rsa.pub
You may need a .pgpass and be able to establish passwordless ssh
connections to be able to collect statistics from remote hosts.
Then after some time and activities on the database, stop the daemon as
follow:
pgcluu_collectd -k
or by sending sigterm to the pgcluu_collectd's pid.
The output directory with all statistics collected should look likes:
/tmp/stat_db1/
|--- pgbouncer.ini
|--- pgbouncer_req_stats.csv
|--- pgbouncer_stats.csv
|--- pg_class_size.csv
|--- pg_database_buffercache.csv
|--- pg_database_isdirty.csv
|--- pg_database_size.csv
|--- pg_database_usagecount.csv
|--- pg_hba.conf
|--- pg_ident.conf
|--- pg_relation_buffercache.csv
|--- pg_settings.csv
|--- pg_stat_bgwriter.csv
|--- pg_stat_connections.csv
|--- pg_stat_database_conflicts.csv
|--- pg_stat_database.csv
|--- pg_statio_user_indexes.csv
|--- pg_statio_user_sequences.csv
|--- pg_statio_user_tables.csv
|--- pg_stat_locks.csv
|--- pg_stat_missing_fkindexes.csv
|--- pg_stat_redundant_indexes.csv
|--- pg_stat_replication.csv
|--- pg_stat_statements.csv
|--- pg_stat_unused_indexes.csv
|--- pg_stat_user_functions.csv
|--- pg_stat_user_indexes.csv
|--- pg_stat_user_tables.csv
|--- pg_tablespace_size.csv
|--- pg_xlog_stat.csv
|--- postgresql.conf
|--- sar_stats.dat
|--- sysinfo.txt
Then now you can proceed with pgcluu to generate reports.
GENERATING REPORTS
To generate a pgCluu report about a PostgreSQL Cluster you must, at
least, have a directory that contains all data files generated by
pgcluu_collectd or pgstats. In this directory, if you have a file named
sar_stats.dat or sadc_stats.dat for binary sadc data file, it will be
taken to build report about system utilization. If you just want to make
a report from a sar file use the -i or -I options.
usage: pgcluu [options] [-i sar_file [--from-sa-file] | -I sadc_file]
[input_dir]
input_dir: directory where pgcluu_collectd or pgstats and sar data
files are stored.
options: -b, --begin datetime start date/time for the data to be parsed.
-d, --db-only dbname Only report for the whole cluster and the given
database name. You can use it multiple time or give a comma separated
list of database name. -D, --device-only dev only report I/O stats for a
particular device You can use it multiple time or give a comma separated
list of device name, ex: sda,sdc. -e, --end datetime end date/time for
the data to be parsed. -i, --sar-file=FILE path to the sar text data
file to read to generate system reports. Default to
input_dir/sar_stats.dat. -I, --sadc-file=FILE sadc binary data file to
read to generate system reports. Default to input_dir/sadc_stats.dat.
-n, --top-number Top number of tables or indexes I/O stats to show.
Default is set to top 10. Set it to 0 to show all. -N, --network-only
iface only report stats for a particular network interface. You can use
it multiple time or give a comma separated list of network ingterface,
ex: eth0,eth1. -o, --output=DIR output directory -r, --reverse-date By
default pgcluu look at mm/dd/yy format in sar file. When enabled pgcluu
will look at dd/mm/yy format. -s, --sadf=BIN path to the sadf sysstat
command used to read the sadc binary data file. Default: /usr/bin/sadf.
-S, --disable-sar disable collect of system statistics with sar. -t,
--with-table table Only report for the whole tables and the given table
name. You can use it multiple time or give a comma separated list of
database name. -T, --no-table Do not report statistics related to
tables. -v, --verbose Print out debug informations. -V, --version Show
pgcluu_collectd version and exit. -z, --timezone +/-XX Set the number of
hour(s) from GMT of the timezone. Use this to adjust date/time from the
sar output, pgcluu use GMT time to draw charts. --from-sa-file instruct
pgcluu that file specified by the -i option uses the standard system
activity daily data file. --help print usage
For example, you can generate all HTML reports from data files stored
into /tmp/stat_db1/ with the following commands:
mkdir /tmp/report_db1/
pgcluu -o /tmp/report_db1/ /tmp/stat_db1/
If you just want reports of some databases, use the following:
pgcluu -o /tmp/report_db1/ /tmp/stat_db1/ --db-only "db1,db2,db3"
If you just want to create a report from a sar output file:
sar -p -A 10 60 > /root/my_sar_file.txt
pgcluu -o /tmp/report_sar/ -i /root/my_sar_file.txt
or from a daily sa file:
sar -p -A -f /var/log/sa/sa18 > /root/my_sar_file.txt
pgcluu -o /tmp/report_sar/ -i /root/my_sar_file.txt --from-sa-file
LICENSE
Copyright (c) 2012-2014, Gilles Darold
pgCluu is licenced under the PostgreSQL Licence a liberal Open Source
license, similar to the BSD or MIT licenses. That mean that all parts of
the program are open source and free of charge.
Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, without fee, and without a written agreement
is hereby granted, provided that the above copyright notice and this
paragraph and the following two paragraphs appear in all copies.
IN NO EVENT SHALL Dalibo BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT,
SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS,
ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
Dalibo HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
Gilles DArold SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS,
AND Gilles Darold HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT,
UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
This is the case for both, pgcluu_collectd and the grapher pgcluu
programs.
AUTHORS
pgCluu is an original development of Gilles Darold.
Some parts of the collector are taken from pgstats a C program writen by
Guillaume Lelarge and especially the SQL queries including the
compatibility with all PostgreSQL versions. See
https://github.com/gleu/pgstats
Btw pgCluu grapher is compatible with files generated by pgstats, sar
and sadc so you can use it independantly to graph those data. Some part
of the sar output parser are taken from SysUsage. See
http://sysusage.darold.net/