rdunklau / pgcluu Goto Github PK
View Code? Open in Web Editor NEWThis project forked from darold/pgcluu
PostgreSQL Cluster performances monitoring and auditing tool
Home Page: http://pgcluu.darold.net/
License: PostgreSQL License
This project forked from darold/pgcluu
PostgreSQL Cluster performances monitoring and auditing tool
Home Page: http://pgcluu.darold.net/
License: PostgreSQL License
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/
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.