Version 4.0

2024-03-06 - Version 4.0

This release is a major release that fixes issues reported since
the past six months and adds several new features.

New features:

• Add report of devices /sys/block/queue informations (scheduler,
  rotational, rq_affinity and add_random) to the System report.
• Add report of PGDATA files to be able to detect symlinks and
  unwanted files in this directory.
• Add reports for Wait Event when the pg_wait_sampling extension is use.
  First report show the distribution by Wait Event Type and second report
  show the distribution by Wait Events.
• Add report for Global subtransactions counters if extension
  pg_subxact_counters is installed in the connection database.
  Subtransactions can lead to performance issue, report Counters
  to monitor the subtransactions (generation rate, overflow, state).
• Add collect of /sys/kernel/debug/sched/migration_cost_ns with kernel >= 5.19
  collect start-end metrics only twice (start and end)
• Add a chapter in documentation about additional statistics
  collected from extensions pg_stat_statements, pg_wait_sampling
  and pg_subxact_counters.
• Add option --no-pg_stats-dump to pgcluu_collectd to avoid collecting such
  statistics for performances reasons. Thanks for the patch to Frederic Yhuel.
• Make SIGINT interrupts gracefully pgcluu_collectd like SIGHUP. Thanks to
  Frederic Yhuel for the patch.
• Add ddump of the pg_stats view to pgcluu_collectd. The point is for the
  DBA to study the statistics, and nothing else. Thanks for the patch to
  Frederic Yhuel.
  It is useful to dump the pg_stats views. The dump can be imported in a table
  created like this:

    CREATE TABLE my_client_pgstats (
      export_time TEXT,
      dbname TEXT,
      schemaname TEXT,
      tablename TEXT,
      attname TEXT,
      inherited BOOLEAN,
      null_frac REAL,
      avg_width INT,
      n_distinct REAL,
      most_common_vals TEXT[],
      most_common_freqs REAL[],
      histogram_bounds TEXT[],
      correlation REAL
    );

Note that the columns most_common_vals and histogram_bounds have
the type TEXT[] instead of anyarray.

Compatibility:

There is not compatibility issues but a change of behavior with default
to dump the pg_stats view. Following the number of table/indexes in the
databases it could take more time collecting the statistic so in this case
the interval of polling should be increase. There is an new option to disable
the pg_stats dump, --no-pg_stats-dump, if you want to get back to the previous
behavior.

Here is the full list of fixes since previous release.

• Fix graph labels for TCPv4 errors. Thanks to Christophe Courtois for
  the report.
• Avoid reporting redundant index if they are not using the same type of
  index. Thanks to Christophe Courtois for the report.
• Fix file not exists error message when looking at block devices information.
• Fix broken report when /sys/kernel/debug/sched/migration_cost_ns is not
  readable by the user running pgcluu_colletd
• Fix devices info (sar -d) for sar >= 12.4.0. Thanks to Christophe Courtois
  for the patch.
• Fix legend of locks reports.
• Fix report of locks per database.
• Fix disable menu for buffercache use
• Fix mount report that was not reporting NFS mount points.
• Fix wait_all_childs() function.

Version 3.5

2023-07-11 - Version 3.5

This is a maintenance release that fixes issues reported since
the past six months and adds support to PostgreSQL 16.

• Fix pgcluu for PG16 changes on pg_stat_user_indexes.
• Fix live/dead tuples statistics. Thanks to Frederic Yhuel for the patch.
• Add e as expression for kind of extended statistics.
• Add --disable-pidstat option to disable the collect of metrics from the
  pidstat command. This can be useful with old versions of pidstat that do
  not support the -U option. Thanks to Guillaume Lelarge for the report.
• If on a secondary server, don't execute pg_current_wal_lsn(). Thanks to
  Guillaume Lelarge for the patch.
• Use relispartition only for PG version 10 and upper. Thanks to Guillaume
  Lelarge for the patch.

Version 3.4

2022-12-26 - Version 3.4

This release is a maintenance release that fixes issues reported since
the past six months.

• Fix unwanted report of missing index from partition. Thanks to
  Christophe Courtois for the report.
• Fix cases where detection of redundant indexes was depending on the
  creation order.Thanks to Guillaume Lelarge for the report.
  Move Cancelled queries report from Replication sub menu to upper level.
  Thanks to Guillaume Lelarge for the report.
• Allow -k option to make pgcluu_collectd exit gracefully. Previous
  behavior was to exit immediately, it now calls the HUP signal (-1).
  This change is done to not interrupt the running execution of the
  metrics collect, the process will die only if all SQL queries are
  executed. For an immediate interrupt use the TERM (-15) signal.
• Remove all ORDER BY clause in the metrics collect queries to improve
  the performances. Ordering is done when reports are generated.
• Fix collect of the pg_stat_user_indexes, pg_stat_user_tables and
  corresponding io stats.
• Fix --rotate-daily spill directory. Thanks to Chistophe Courtois for
  the patch.
• Fix potential use of uninitialized value when sar and pidstat files are
  not present. Thanks to geostart123 for the report.
• [CGI] verify that pidstat statistics exists before trying to load them.
  Thanks to geostar123 foir the report.
• [CGI] Fix wrong error message when attempt to read binary files and remove
  useless code. Thanks to geostar123 for the report.
• Remove newline from indexes definition to avoid data import error when
  building reports.

Version 3.3

2022-06-02 - Version 3.3

This release is a maintenance release that fixes issues reported since previous release. It also adds new reports and features.

  - Add TCP utilization and TCP errors reports, from sar -nTCP,ETCP.
  - Add a comment in the suggested index if it could be redundant
    with existing ones.
  - Add support to run pgcluu_collect as user with the pg_monitor role.

Here is the full list of changes since previous release.

  - Add support to run pgcluu_collect as user with the pg_monitor role.
    Thanks to Nicolas Gollet for the report.
  - Fix redundant index query to work with PG < 10, regexp_match() must be
    replaced by regexp_matches() with some additional rewrite.
  - Replace call to pg_ls_dir by pg_ls_waldir to be able to use pg_monitor
    privilege starting from PG10. Thanks to Nicolas Gollet for the report.
  - Fix impossibility to use --lock-timeout. Thanks to Christophe Courtois
    for the report.
  - Remove lock_timeout from internally set of PGOPTIONS when querying
    pgbouncer, the old value is restored after. Thanks to Christophe Courtois
    for the report.
  - Fix empty pgbouncer statistics file when running in interactive mode.
    Thanks to Christophe Courtois for the report.
  - Add a comment in the CREATE INDEX when the suggested index may be redundant
    with existing ones. Thanks to Christophe Courtois for the patch.
  - Fix false report of redundant index when the WHERE clause is different.
    Thanks to Guillaume Lelarge for the report.
  - Fix in pgcluu for OUTPUT_DIR always be quoted, especially if their value
    may contain a white space or separator character. Thanks to Nicolas Gollet
    for the patch.
  - Fix network errors disabled. Thanks to Christophe Courtois for the patch.
  - Add TCP utilization and TCP errors reports, from sar -nTCP,ETCP. Thanks to
    Christophe Courtois for the patch.
  - Fix pgbouncer command. Thanks to Nicolas Gollet for the patch.
  - Modification of the query on pg_stat_statement so that under PostgreSQL 13
    and higher total_time = total_plan_time + total_execution_time. Thanks to
    Nicolas Gollet for the patch.
  - Fix STATS_REPORT_RETENTION directory in config file. Don't prepend DESTDIR
    to paths in config file. Thanks to Christoph Berg for the patch.

Version 3.2

2021-09-30 - Version 3.2

This release is a maintenance release that fixes issues reported since previous release. It also adds new reports and features.

• Add STATS_REPORT_OUTDIR configuration directive to pgcluu.conf to set the directory where the pgCluu reports will be saved. Default to /var/lib/pgcluu/report/.
• Add command line option -t | --lock-timeout with default to 3 seconds to self terminate a SQL query that could be lock by the activity of an other session like a drop table for example.
• Add collect and report of unused trigger functions.
• Add system statistics dedicated to PostgreSQL process through stats reported by pidstat.
• Add report of CPU scaling_governor to check if it set to powersave, setting it to performances can improve performances up to 20% following the PostgreSQL load. For more information on this kernel parameter see https://wiki.archlinux.org/index.php/CPU_frequency_scaling#Scaling_governors

Backward compatibility:

• Change default retention time to 30 days instead of no storage limit to avoid possible out of memory on uncontrolled installation.

Here is the full list of changes since previous release.

• Remove service time report as per sysstat v12.1.2 it has been removed.
• Add STATS_REPORT_OUTDIR configuration directive to pgcluu.conf to set the directory where the pgCluu reports will be saved. Default to /var/lib/pgcluu/report/. Thanks to Jean Pierre Huart for the feature request.
• Fix error "Use of uninitialized value $pidstat_file in -e at ..." when generating report for sar file only.
• Fix possible bad format issue in pidstat parsing du to command with a space character. Thanks to Christophe Courtois for the report.
• Subtract the lock_timeout setting when looking at pg_settings to have the right values outside pgcluu.
• Fix README typo ; releases come from Github. Thanks to Christophe Courtois for the patch.
• Add BEGIN_STAT/END_STAT variables dedicated to sar data on their timezone, to avoid filtering on the wrong hours.
• Add command line option -t | --lock-timeout with default to 3 seconds to self terminate a SQL query that could be lock by the activity of an other session like a drop table for example. Thanks to Christophe Courtois for the feature request.
• Limit output length of usage to 80 characters.
• Fix for begin/end date filtering for sar data. Thanks to Christophe Courtois for the patch.
• Fix failure of pidstat through ssh. Thanks to Christophe Courtois for the patch.
• Verify that same statistic report is not built twice.
• Remove fork for collect of PostgreSQL metrics and add autodetection of the main process pid when pid file is not found.
• Remove copyright from html footer.
• Split per database report in several HTML pages to avoid too large files. Thanks to Andreas Schrafl for the report.
• Add collect of wait event if extension pg_wait_sampling is available in a database. This collect can be disabled with new command line option -w, --no-waitevent.
• Set hamburger dropdown menu background from transparent to grey. Thanks to Andreas Schrafl for the patch.
• Fix missing report of tables and indexes statistics.
• Fix lookup at database list.
• Add collect of unused trigger functions. Thanks to Adrien Neyrat for the feature request.
• Fix load of database list from binary files in CGI mode.
• Fix pidstat reports about CPU wait and iodelay with old systat version where these information was not available. iodelay < sysstat v10.2.1 and wait < sysstat v11.5.5
• Add CGI reports for pidstat statistics.
• Add pidstat data caching.
• Fix link to page cluster-archive.html. Thanks to cyberioio for the report.
• Fix view of configurations reports that was hidden. Thanks to Lionel for the report.
• Fix formatting of README file for command line option.
• Add collect of PostgreSQL only system metrics through pidstat.
• Fix pgcluu error on reading compressed data files. Thanks to Damien Clochard for the report.
• Fix wrong detection of --output option.
• Fix previous commit that was breaking GetOptions behaviors.
• Do not take care of the STATS_REPORT_CACHING value in config file if pgcluu is run in interactive mode with an ouptut directory (-o) and not caching enabled (-c).
• Update year in copyright
• Fix Storable.pm error when reading empty file. Thanks to Christian Roux for the report.
• Make update of system information stat file sysinfo.txt atomic to prevent zero file size.
• Fix a bgwriter URL. Thanks to Guillaume Lelarge for the patch.
• Fix parsing of sar v12 header. Thanks to Christoph Berg for the report.
• Fix INPUT_DIR in generated pgcluu.conf. The path here must not have DESTDIR prepended. Thanks to Christoph Berg for the patch.
• Prevent look at pg_statistic_ext if major version is bellow 10.0. Thanks to Devrim Gunduz for the report.
• Fix use of DESTDIR vs PREFIX installation process. Thanks to Christoph Berg for the report.

Version 3.1

2019-10-29 - Version 3.1 released

This release is a maintenance release that adds support to PostgreSQL
v12.0. It also add a new report:

• Add report of extended statistics in report Table Statistics per database. Example:

       Table         | Extended Statistic
       public.cities | CREATE STATISTICS sch1.t1_stat (dependencies)
                                 ON city,country FROM public.cities;

It also fixes some issues reported by users since last month:

• Fix some typos in documentation. Thanks to Justin Pryzby for the report.
• Replace obsolete pg_constraint.consrc in PostgreSQL v12 by a call
  to function pg_get_ constraintdef(). Thanks to seadba and Devrim
  Gunduz for the report.

Version 3.0

2019-09-18 - Version 3.0 released

This release is a major release that adds lot of improvement and
new reports. It also improve performances and fully support a CGI
mode with dynamic content for realtime reports.

New or enhanced reports/features:

• The CGI mode to provide dynamic reports on time selection is now
  production ready and has left the beta stage. See documentation
  on how to enable it. Debian packaging enable this mode by default.
• Installation and configuration is fully managed from Makefile.PL
  script. This will help distribution packaging with additional
  configuration directives to control the behavior of pgcluu scripts
  with generic systemd service and timer files.
• Add incremental mode to pgcluu. That mean that you don't have to
  remove the content of the report directory anymore before running
  pgcluu again and when pgcluu detect that you are running in this
  mode. The incremental mode is detected from statistic directory
  when you have used the --rotate-daily or --rotate-hourly option
  to pgcluu_collectd.
• Rewrite Stats I/O reports to display read/hit per table or indexes
  during the audit period reported.
• Move Xlog (Wal bytes written) report under replication menu as
  data comes from pg_stat_replication.
• Explode System and Cluster reports into several reports dedicated
  files to limit the size of the HTML files in incremental mode.
• Add collect and report of vm.nr_overcommit_hugepages.
• Add report on pages scanned to report number of pages scanned by
  the kswapd daemon and number of pages scanned directly per second
  (pgscank/s and pgscand/s). It also reports the number of pages
  the system has reclaimed from cache (pagecache and swapcache) per
  second to satisfy its memory demands (pgsteal/s). On a second
  axis %vmeffto reports the the efficiency of page reclaim.
• All pages reports have been moved as sub menu of the Page menu and
  pgfree has been removed from the page fault report.
• Limit system cache statistics report to pgpgin/s and pgpgout/s and
  a new report is dedicated to Page fault statistics with majflt/s,
  minflt/s and pgfree/s dataset. Dataset minflt/s is calculated from
  sar output as result of "fault/s - majflt/s".
• All statistics about pg_stat_user_* and pg_stat_xact_user_* views
  are now stored as a snapshot at start and at end of the audit
  period and each hour if you are running in incremental mode. The
  old behavior was to append the snapshot at each running interval
  to the data file but this is not necessary and this can use huge
  disk space if you have lot of table and indexes in your database.
  Backward compatibility is preserved.
• New report of json versus jsonb columns in each database.
• Add keepalive kernel configuration in SysInfo report.
• Separate bgwriter "allocated" buffers in a dedicated report for
  better reading.
• Add new report about estimated memory workload based on the value
  of Committed_AS from /proc/meminfo. Committed_AS is the total
  amount of memory estimated to complete the workload. This value
  represents the worst case scenario value, and also includes swap
  memory. The report show the memory workload aver the time.

New command line options:

• Add option -x, --external-menu to pgcluu to save the common menu
  in menu.html and load it into each report using w3-include-html
  attribute from w3.js. This will only work if access to HTML report
  is through a Web server, not using the file:// protocol.
• Add new option --retention to pgcluu to set number of rolling days
  to keep in report directory. Default is to store indefinitely.
• Add option --retention NDAYS to pgcluu_collectd to set the number
  of rolling days to keep in data directory in incremental mode.
  Default is to store indefinitely.

New configuration directives :

• Add configuration directive STATS_REPORT_CACHING to pgcluu.conf
  file. This directive must be enabled in CGI mode. In this case the
  pgcluu script must be run periodically at least each 5 minutes to
  build and update cached statistics. In cache mode pgcluu generate
  binary file in the statistics directory and nothing in the report
  directory. The pgcluu.service and pgcluu.timer can be use, pgcluu
  will read the configuration file and switch automatically to cache
  mode if STATS_REPORT_CACHING is enabled.
• Add MAX_RENDERED_DAYS configuration directive to set the maximum
  number of days in a graph. Using default pgcluu_collectd 1 minute
  interval each, daily graph will have 1440 points. Having too much
  data to render is not a good point for performances with the
  current code. The user can set is own graph period, default is to
  limit to the first seven days of the selected period. This mean a
  maximum of 10080 points for one week, this seems a safe limit.

It also fixes some issues reported by users since last ten months.

Version 2.9

2018-12-13 - Version 2.9 released

This release is a minor release that fix a major issue in the
interval detection. This This led to a bad scale in bg_writter,
replication and most database statistics reports. Every one must
upgrade immediately.

It also fixes some issues reported by users since last release:

  - Improve devices list retrieving and fix several menu issues.
  - Fix checkpoint counter legend.
  - Move query in pg_stat_statements at the right of the table for
    better readability.
  - Fix report of disk use, mounted devices, running processes,
    crontab and installed package to avoid duplicate entries.
  - Empty SysInfo arrays before reading sysinfo file, this prevent
    duplicate data.
  - Improve parsing of sar file.
  - Fix interval that was set in milliseconds instead of second.
    This conduct to wrong scale (ex: Kb instead of Gb) in bg_writter,
    replication and database statistics reports. Thanks to Guillaume
    Lelarge for the report.

Version 2.8

2018-11-28 - Version 2.8 released

This release is a minor release that fix some issues reported by
users over past months, it also adds some new reports:

  - Add disk space utilization report over time using df command
    for sysstat version older than 11.1.4.
  - Add report of percentage of disk space and inode used on each
    file system. The information is available in sar output since
    11.1.4 version.
  - Add PageTables information in SysInfo memory report.
  - Show database information about table/indexes in capture mode.
  - Add report of number of tasks currently blocked, waiting for I/O
    to complete "Run queue" report.
  - Add new report about system dirty memory that need to be written
    to disk as well as amount of active/inactive memory.
  - Show highest dirty memory to write and highest number of processes
    blocked in overall stats page.
  - Add report of crontab entries for the user running pgcluu.
  - Add report of installed PostgreSQL packages in main page.

New pgcluu_collectd command line option:

  - Add command line option --package-list to be able to set a custom
    command to list PostgreSQL packages. Default is to auto-detect
    package type between rpm and dpkg, using command 'rpm -qa' or
    'dpkg -l'. If you have an other system you can use this option to
    set a custom command. A filter on keyword 'postgres' is appended
    to the command: ' | grep postgres'.

Here are the complete list of bug fixes in this release:

  - Change the way sysstat version is checked and save the version
    into to sysinfo.txt file for use in pgcluu script.
  - Remove extra new line at end of data file generated by the
    patch on search_path securing.
  - Add collect of disk space and inode used over time stored in file
    fs_stat_use.csv. Need more work to avoid duplicate data with new
    versions of sar that already report fs space use.
  - Replace all call to Perl ternary operator with usual if-then-else
    statement. In some condition this operator do not works and makes
    pgcluu return negative values in overall stats. This was happening
    only when pgcluu encounter a stat reset in the data files. Thanks
    to Guillaume Lelarge for the report.
  - Remove /dev/loop from devices statistics report.
  - Add PageTables information in SysInfo memory report. Thanks to
    Adrien Neyrat for the patch.
  - Remove some unwanted metrics displayed in capture mode.
  - Show database information about table/indexes in capture mode.
  - Exclude /dev/loop from result of "df" command and squashfs from
    "mount -l" command.
  - Prevent pgcluu_collectd to stop before that all metrics collected
    from the current loop have been written to disk. When it receives
    the terminate signal (-k option) it previously stops immediately,
    the problem was that this can conduct to empty or incomplete data
    file. Now it waits until the loop is terminated before exiting.
  - Fix compression of hourly directory when -R | --rotate-hourly and
    -z | --compress options are enabled. The compression was called
    each loop and not at each hour rotation.
  - Add warning about mandatory use of -d option when pg_buffercache
    reports are enabled (-B) but pg_buffercache extention is not found
    in the connection database.
  - Fix missing buffercache statistics since secure path has been
    included in commit b61bf44. This required that pg_buffercache
    table call was prefixed by the public schema. Thanks to
    lobojohnson for the report.
  - Fix unwanted print of tablespace path in cluster reports.
  - Remove not useful information in buffercache graph legends.
  - CSS: Increase size of the sysinfo panel.
  - Adapt automatically pgCluu to changes in sar sysstat tool since
    version 11.5.7. As per sysstat commit 8d635e0: Replace "rd_sec/s"
    and "wr_sec/s" fields with "rkB/s" and "wkB/s". These fields are
    now expressed in kilobytes instead of sectors. Replace "avgrq-sz"
    field with "areq-sz". This field is now expressed in kilobytes
    instead of sectors. Rename "avgqu-sz" field to "aqu-sz" to make
    it consistent with iostat's output.
  - Add report of number of tasks currently blocked, waiting for I/O
    to complete to "Run queue" report. This information is available
    with sysstat >= 9.1.7
  - Show also highest dirty memory to write and highest number of
    processes blocked in main page.
  - Add new report about system dirty memory that need to be written
    to disk as well as amount of active/inactive memory. These metrics
    are available in recent sar/sysstat versions.
  - Add report of crontab entries.
  - Change icon for packages report.
  - Add report of PostgreSQL installed package in main page.
  - Add storage information about PostgreSQL packages installed on
    the system.
  - Add command line option --package-list to be able to set a custom
    command to list PostgreSQL packages. Default is to auto-detect
    package type between rpm and dpkg, using command 'rpm -qa' or
    'dpkg -l'. If you have an other system you can use this option to
    set a custom command. A filter on keyword 'postgres' is appended
    to the command: ' | grep postgres'.

Version 2.7

2018-07-09 - Version 2.7 released

This release is a minor release that fix some issues reported by
users over past year but it also adds some new interesting reports:

  - Add reports of pgbouncer wait for server statistics.
  - Make pgCluu more compatible with PG10 and pgBouncer 1.8
  - Add kernel vm.overcommit_kbytes configuration to report.

Here are the complete list of bug fixes in this release:

  - Fix Prepared Transaction menu not disabled with no stat data.
  - Fix typos and use proper measure in the graph. Thanks to Fabio Porta for the patch.
  - Add vm.overcommit_kbytes to kernel report. Thanks to Adrien Neyrat for the patch.
  - Since version 1.8 of pgbouncer, the command "show stats" changed output, make pgcluu
    compatible. Thanks to Fabio Pardi for the report and the mapping of changes.
  - Fix issue in partitionning lookup.
  - Secure search_path before executing SQL queries.
  - Fix several use of unintialized variable case.
  - Add missing function IsLeapYear. Thanks to Emmanuel Boucle for the patch.
  - Fix report for network and disk devices in incremental mode.
  - Fix unwanted Network menu when no network device are found.
  - Fetch devices informations. Thanks to Adrien Neyrat for the patch.
  - Dockerise pgcluu, the container helps run both the collection and the report generator. 
    Thanks to Roy Golan for the patch.
  - Disable System submenu when the corresponding report is not available.
  - Append %idle to cpu report.
  - Fix list of resources files. Thanks to Bosstek Consulting for the report.
  - Change fetch_version() to be closer with the new versioning politic in 10.0 and above.
    Thanks to Julien Rouhaud for the patch.
  - Makes pgcluu more compatible with PostgreSQL 10.0.