pgCluu 2.0 released

pgCluu 2.0 released
-------------------

Version 2.0 of pgCluu, a PostgreSQL performances monitoring and auditing
tool, has been officially released and is publicly available for download.

This major release adds more than twenty new reports. pgCluu now reports
everything you want to know about your PostgreSQL server from a cluster,
database, or operating system point of view. It is also able to collect
statistics about pgBouncer, shared buffer utilization using
pg_buffercache, most frequent and time consuming queries with
pg_stat_statements and really much more, see full list bellow.

Here's a demo site to show all reports that pgCluu can provide in this
v2.0 release: http://pgcluu.darold.net/example/index.html

About pgCluu:
-------------

pgCluu is a PostgreSQL performances monitoring and auditing tool. It
will show you the entire informations of the PostgreSQL Cluster and the
system utilization. It is divided in two parts, a collector used to grab
statistics on the PostgreSQL cluster using psql and sar, a grapher that
will generate all HTML and charts output. pgCluu is written in Perl and
doesn't need any dependency to be run on any server easily. There is
around 80 different reports, here follows the complete list of pgCluu
features.

Clusters performances:
----------------------

- System informations like kernel, OS version, kernel tuning, CPUs
and memory details.
- Filesystem usage and mount type.
- Databases sizes.
- Tablespaces sizes.
- Number of connection per database.
- Type of connection (active, idle, idle in transaction).
- Number of deadlocks detected in databases.
- Cache hit/miss ratio on all database.
- Background writer cache cleaning statistics by checkpoints, lru
and backends.
- Background writer counter stats.
- Number of temporary files created by queries per database.
- Size of temporary files created.
- Number of WAL file in the xlog directory.
- Statistics of checkpoints issued because of checkpoint_timeout and
checkpoints issued by request.
- Checkpoint statistics of total amount of time spent in writing
file to disk and time spent in synchronization to disk.
- Statistics about archiving (new pg_stat_archiver view).
- Statistics about entries returned from the indexes and live rows
fetched from the tables.
- Write ratio on databases excluding templates and postgres databases.
- Affected rows on databases grouped by statement family (insert,
delete, update, select).
- Shows all uncommented configuration directives from
postgresql.conf file.
- Show all entries from pg_settings.
- Shows all uncommented entries from pg_hba.conf file.
- Shows all uncommented entries from pg_ident.conf file.
- Shows all uncommented entries from pgbouncer.ini file.
- Lag of replication between primary and secondary servers.
- Number of queries canceled due to conflicts with recovery.
- Conflicts per type occurring due to conflicts with recovery on
standby servers.

If pg_buffercache is installed there's some additional reports:

- Show statistics about percentage of shared buffers used per database.
- Show statistics about percentage of each databases loaded in
shared buffers.
- Show statistics about usagecount (1-5)distribution in shared buffer.
- Show statistics about usagecount distribution in dirty shared buffer.

Databases performances:
-----------------------

- General database information (size, installed extension, defined
schemas, last manual vacuum/analyze) and count of all different objects
(stored procedures, triggers, sequences, composite types, indexes,
tables, toast tables and views).
- Number of sequential scan versus index scan per table.
- Number of analyze, autoanalyze, vacuum and autovacuum count per table.
- Number of Insert/update/delete and hot update tuples per table.
- Number of live and dead tuples per table.
- Disk space used per tables with number of rows.
- Top statistics about I/O per table.
- Number of index entries returned by index scans, and number of
live table rows fetched per index.
- Disk space used by the index with number of rows.
- Top statistics about I/O per index.
- List of indexes never used.
- List of useless indexes because they are redundant.
- List of missing indexes on foreign keys.
- Evolution of the size of the database.
- Number of clients connected to the database.
- Cache hit/miss ratio on the database.
- Number of deadlocks detected in the database.
- Number of locks per type in a database.
- Number of locks per lock mode held or desired.
- Number of locks held (granted) or awaited (waiting).
- Number of temporary file created for this database.
- Size of temporary file created for this database.
- Show entries returned from the index and live rows fetched from
the tables.
- Write ratio on the database.
- Affected rows on the database grouped by statement family (insert,
delete, update, select).
- Number of commits / rollbacks per second and number of backends
per database.
- Number of queries canceled due to conflicts with recovery in the
database.
- Conflicts per type on the database.

If pg_buffercache is installed there's some additional reports:

- Number of shared buffer/pages used by a relation.
- Number of buffers loaded in cache for a relation and the
percentage of the relation loaded.

and if pg_stat_statements is installed:

- Top statistics about slowest or most used queries

pgBouncer performances:
-----------------------

- Number of active/waiting clients, active/idle/used server
connections and maximum wait duration for client connections in each
pgbouncer pool.
- Average queries duration in each pgbouncer pool.
- Number of queries per second in each pgbouncer pool.

System performances:
--------------------

- Percentage of CPU utilization.
- Amount of memory used to cache data or as buffers by the kernel
and free memory.
- Total number of swap pages the system brought in/out per second.
- System load average for the last minute, the past 5 and 15 minutes.
- Number of tasks in the task list.
- Number of tasks waiting for run time.
- Total amount of data read/write from the devices in blocks per second.
- Total number of kilobytes the system paged in/out from/to disk,
and number of major faults the system has made per second.
- Percentage of CPU time used by I/O requests issued to a device.
- Number of bytes read/write from/to a device.
- The average service time (in milliseconds) for I/O requests that
were issued to a device.
- Statistics about network devices utilization.
- Statistics on failures from the network device.

All charts are zoomable and can be saved as PNG images. All tables are
sortable on all columns. If you think that some reports are missing,
feel free to submit a feature request. pgCluu should works on any
platform supported by psql and sysstat, it is available under the
PostgreSQL licence.

pgCluu is created and maintained by Gilles Darold (also author of ora2pg
and pgbadger).

Useful Links:
-------------

* Website: http://pgcluu.darold.net/
* Download: http://sourceforge.net/projects/pgcluu/
* Development: https://github.com/darold/pgcluu
* Changelog: https://github.com/darold/pgcluu/blob/master/ChangeLog

--
Gilles Darold
Administrateur de bases de données
http://dalibo.com - http://dalibo.org