From 3f410a62d05394d649b7eab540a34ac718e122df Mon Sep 17 00:00:00 2001 From: Darold Gilles Date: Thu, 31 Mar 2016 18:34:50 +0200 Subject: [PATCH] Report message "LOG: using stale statistics instead of current ones because stats collector is not responding" in events view. --- pgbadger | 8 + tools/pgbadger_wiki | 1056 ------------------------------------------- 2 files changed, 8 insertions(+), 1056 deletions(-) delete mode 100755 tools/pgbadger_wiki diff --git a/pgbadger b/pgbadger index 16fa338..c1b6233 100644 --- a/pgbadger +++ b/pgbadger @@ -4365,6 +4365,7 @@ sub show_error_as_text $msg =~ s/ERROR: (database system was interrupted while in recovery)/LOG: $1/; $msg =~ s/ERROR: (sending cancel to blocking autovacuum)/LOG: $1/; $msg =~ s/ERROR: (skipping analyze of)/LOG: $1/; + $msg =~ s/ERROR: (using stale statistics)/LOG: $1/; if ($error_info{$k}{count} > 1) { print $fh "$idx) " . &comma_numbers($error_info{$k}{count}) . " - $msg\n"; @@ -4378,6 +4379,7 @@ sub show_error_as_text || ($error_info{$k}{error}[$i] =~ s/ERROR: (recovery has paused)/LOG: $1/) || ($error_info{$k}{error}[$i] =~ s/ERROR: (sending cancel to blocking autovacuum)/LOG: $1/) || ($error_info{$k}{error}[$i] =~ s/ERROR: (skipping analyze of)/LOG: $1/) + || ($error_info{$k}{error}[$i] =~ s/ERROR: (using stale statistics)/LOG: $1/) ) { $logs_type{ERROR}--; @@ -4398,6 +4400,7 @@ sub show_error_as_text || ($error_info{$k}{error}[0] =~ s/ERROR: (recovery has paused)/LOG: $1/) || ($error_info{$k}{error}[0] =~ s/ERROR: (sending cancel to blocking autovacuum)/LOG: $1/) || ($error_info{$k}{error}[0] =~ s/ERROR: (skipping analyze of)/LOG: $1/) + || ($error_info{$k}{error}[0] =~ s/ERROR: (using stale statistics)/LOG: $1/) ) { $logs_type{ERROR}--; @@ -10361,6 +10364,7 @@ sub print_log_level || ($error_info{$k}{error}[$i] =~ s/ERROR: (recovery has paused)/LOG: $1/) || ($error_info{$k}{error}[$i] =~ s/ERROR: (sending cancel to blocking autovacuum)/LOG: $1/) || ($error_info{$k}{error}[$i] =~ s/ERROR: (skipping analyze of)/LOG: $1/) + || ($error_info{$k}{error}[$i] =~ s/ERROR: (using stale statistics)/LOG: $1/) ) { $logs_type{ERROR}--; @@ -10374,6 +10378,7 @@ sub print_log_level || ($error_info{$k}{error}[0] =~ s/ERROR: (recovery has paused)/LOG: $1/) || ($error_info{$k}{error}[0] =~ s/ERROR: (sending cancel to blocking autovacuum)/LOG: $1/) || ($error_info{$k}{error}[0] =~ s/ERROR: (skipping analyze of)/LOG: $1/) + || ($error_info{$k}{error}[0] =~ s/ERROR: (using stale statistics)/LOG: $1/) ) { $logs_type{ERROR}--; @@ -10583,6 +10588,7 @@ sub show_error_as_html $msg =~ s/ERROR: (recovery has paused)/LOG: $1/; $msg =~ s/ERROR: (sending cancel to blocking autovacuum)/LOG: $1/; $msg =~ s/ERROR: (skipping analyze of)/LOG: $1/; + $msg =~ s/ERROR: (using stale statistics)/LOG: $1/; my $error_level_class = 'text-error'; if ($msg =~ /^WARNING: /) { $error_level_class = 'text-warning'; @@ -12000,6 +12006,8 @@ sub parse_query $prefix_vars{'t_loglevel'} = 'ERROR'; } elsif ($prefix_vars{'t_query'} =~ /skipping analyze of/) { $prefix_vars{'t_loglevel'} = 'ERROR'; + } elsif ($prefix_vars{'t_query'} =~ /using stale statistics/) { + $prefix_vars{'t_loglevel'} = 'ERROR'; } } elsif (($prefix_vars{'t_loglevel'} eq 'ERROR') && !$error_only) { diff --git a/tools/pgbadger_wiki b/tools/pgbadger_wiki deleted file mode 100755 index c33f891..0000000 --- a/tools/pgbadger_wiki +++ /dev/null @@ -1,1056 +0,0 @@ -#!/usr/bin/env perl -#------------------------------------------------------------------------------ -# -# pgbadger_wiki - Tool based on pgBadger binary files to report in wiki format -# -# This program is open source, licensed under the PostgreSQL Licence. -# For license terms, see the LICENSE file. -#------------------------------------------------------------------------------ -use vars qw($VERSION); - -use strict; - -use Getopt::Long qw(:config no_ignore_case bundling); -use File::Spec qw/ tmpdir /; -use File::Temp qw/ tempfile /; -use IO::File; -use IO::Handle; -use Storable qw(store_fd fd_retrieve); - -$VERSION = '1.0'; - -my $PSQL_BIN = 'psql'; -my $TMP_DIR = File::Spec->tmpdir() || '/tmp'; - -my @SQL_ACTION = ('SELECT', 'INSERT', 'UPDATE', 'DELETE', 'COPY FROM', 'COPY TO', 'CTE', 'DDL', 'TCL'); - -# Where statistics are stored in pgbadger binary files -my %overall_stat = (); -my %pgb_overall_stat = (); -my %overall_checkpoint = (); -my @top_slowest = (); -my %normalyzed_info = (); -my %error_info = (); -my %pgb_error_info = (); -my %pgb_pool_info = (); -my %logs_type = (); -my %per_minute_info = (); -my %pgb_per_minute_info = (); -my %lock_info = (); -my %tempfile_info = (); -my %connection_info = (); -my %pgb_connection_info = (); -my %database_info = (); -my %application_info = (); -my %user_info = (); -my %host_info = (); -my %session_info = (); -my %pgb_session_info = (); -my %conn_received = (); -my %checkpoint_info = (); -my %autovacuum_info = (); -my %autoanalyze_info = (); -my %cur_info = (); -my %cur_temp_info = (); -my %cur_lock_info = (); -my %tsung_session = (); -my @top_locked_info = (); -my @top_tempfile_info = (); -my @log_files = (); -my $nlines = 0; -my $sample = 3; -my $top = 20; - -################################################################## -# Get the command line parameters -# Add your own option -################################################################## -# General options -my $help = 0; -my $quiet = 0; -my $debug = 0; -my $pghost = ''; -my $pgport = ''; -my $pguser = ''; -my $pgdb = ''; - -# Tools dedicated option -my $format_query = 0; -my $extension = 'docuwiki'; -my $report_title = ''; - -my $result = GetOptions( - 'h|host=s' => \$pghost, - 'p|port=i' => \$pgport, - 'U|username=s' => \$pguser, - 'd|dbname=s' => \$pgdb, - 'q|quiet!' => \$quiet, - 'v|verbose!' => \$debug, - 'help!' => \$help, - # Tool options - 's|sample=i' => \$sample, - 't|title=s' => \$report_title, - 'f|format-query!' => \$format_query, - 'x|extension=s' => \$extension -); - -# Show help an exit -if ($help) { - &usage(); -} - -if (!$extension) { - die("Please choose an extension, available extension are: docuwiki\n"); -} - -# Lookup for binary files to load from command line -my @file_list = (); -foreach my $f (@ARGV) { - push(@file_list, $f) if (-e $f && ($f =~ /\.bin$/)); -} - -die "FATAL: no binary file found, see usage (--help).\n" if ($#file_list == -1); - -# Load all data gathered by all the different processes. This function -# is responsible of loading pgbadger statistics from binary files -foreach my $f (@file_list) { - next if (-z "$f"); - my $fht = new IO::File; - $fht->open("< $f") or die "FATAL: can't open temp file $f, $!\n"; - &load_stats($fht); - $fht->close(); -} - -# Set the psql command follwing the option -my $psql_cmd = ''; -$psql_cmd .= " -h $pghost" if ($pghost); -$psql_cmd .= " -p $pgport" if ($pgport); -$psql_cmd .= " -U $pguser" if ($pguser); -$psql_cmd .= " -d $pgdb" if ($pgdb ); -if ($psql_cmd) { - $psql_cmd = $PSQL_BIN . ' ' . $psql_cmd; -} - -#################################################################### -# Build report in docuwiki with format option set to docuwiki -################################################################## -if ($extension eq 'docuwiki') { - &logmsg('LOG', "Report will be generated in docuwiki format."); - &dump_error_as_docuwiki(); -} - -exit 0; - -################################################################## -# Display pgbadger_tools usage with the list of options -# Add your own in the Tools section with a sample. -################################################################## -sub usage -{ - - print qq{ -Usage: pgbadger_tools [options] [options tools] BINARY_FILE - -Options: - - -d | --dbname DBNAME : same as in psql command, see psql --help - -h | --host HOST : same as in psql command, see psql --help - -p | --port PORT : same as in psql command, see psql --help - -q | --quiet : do not print any information - -U | --username NAME : same as in psql command, see psql --help - -v | --verbose : show debug information - --help : Show this message - -Note: option -d, -h, -p and -U are passed directly to the psql command. -The psql command must be in the PATH environment variable. If you have -authentication for the connection, use .pgpass. This allow to execute -queries to a PostgreSQL backend and get the output. - -Options Tools: - - -f | --format-query : SQL queries in error sample will be formatted - -t | --title STR : set title of the report. - -s | --sample NUM : number of error sample to show - -x | --extension : set the output format to use. Default: docuwiki - -}; - exit 0; -} - -################################################################## -# Internal method, do not edit unless you know what you are doing -################################################################## - -#### -# Display message following the log level -#### -sub logmsg -{ - my ($level, $str) = @_; - - return if ($quiet && ($level ne 'FATAL')); - return if (!$debug && ($level eq 'DEBUG')); - - if ($level =~ /(\d+)/) { - print STDERR "\t" x $1; - } - - print STDERR "$level: $str\n"; -} - -#### -# Stores top N error sample queries -#### -sub set_top_error_sample -{ - my ($q, $date, $real_error, $detail, $context, $statement, $hint, $db, $user, $app, $remote) = @_; - - # Stop when we have our number of samples - if (!exists $error_info{$q}{date} || ($#{$error_info{$q}{date}}+1 < $sample)) { - if ( ($q =~ /deadlock detected/) || ($real_error && !grep(/^\Q$real_error\E$/, @{$error_info{$q}{error}})) ) { - push(@{$error_info{$q}{date}}, $date); - push(@{$error_info{$q}{detail}}, $detail); - push(@{$error_info{$q}{context}}, $context); - push(@{$error_info{$q}{statement}}, $statement); - push(@{$error_info{$q}{hint}}, $hint); - push(@{$error_info{$q}{error}}, $real_error); - push(@{$error_info{$q}{db}}, $db); - push(@{$error_info{$q}{user}}, $user); - push(@{$error_info{$q}{app}}, $app); - push(@{$error_info{$q}{remote}}, $remote); - } - } -} - -#### -# Load statistics from binary file into memory -#### -sub load_stats -{ - - my $fd = shift; - - no strict; - - my %stats = %{ fd_retrieve($fd) }; - my %_overall_stat = %{$stats{overall_stat}}; - my %_pgb_overall_stat = %{$stats{pgb_overall_stat}}; - my %_overall_checkpoint = %{$stats{overall_checkpoint}}; - my %_normalyzed_info = %{$stats{normalyzed_info}}; - my %_error_info = %{$stats{error_info}}; - my %_pgb_error_info = %{$stats{pgb_error_info}}; - my %_pgb_pool_info = %{$stats{pgb_pool_info}}; - my %_connection_info = %{$stats{connection_info}}; - my %_pgb_connection_info = %{$stats{pgb_connection_info}}; - my %_database_info = %{$stats{database_info}}; - my %_application_info = %{$stats{application_info}}; - my %_user_info = %{$stats{user_info}}; - my %_host_info = %{$stats{host_info}}; - my %_checkpoint_info = %{$stats{checkpoint_info}}; - my %_session_info = %{$stats{session_info}}; - my %_pgb_session_info = %{$stats{pgb_session_info}}; - my %_tempfile_info = %{$stats{tempfile_info}}; - my %_cancelled_info = %{$stats{cancelled_info}}; - my %_logs_type = %{$stats{logs_type}}; - my %_lock_info = %{$stats{lock_info}}; - my %_per_minute_info = %{$stats{per_minute_info}}; - my %_pgb_per_minute_info = %{$stats{pgb_per_minute_info}}; - my @_top_slowest = @{$stats{top_slowest}}; - my $_nlines = $stats{nlines}; - my $_first_log_timestamp = $stats{first_log_timestamp}; - my $_last_log_timestamp = $stats{last_log_timestamp}; - my @_log_files = @{$stats{log_files}}; - my %_autovacuum_info = %{$stats{autovacuum_info}}; - my %_autoanalyze_info = %{$stats{autoanalyze_info}}; - my @_top_locked_info = @{$stats{top_locked_info}}; - my @_top_tempfile_info = @{$stats{top_tempfile_info}}; - my @_top_cancelled_info = @{$stats{top_cancelled_info}}; - - ### overall_stat ### - - $overall_stat{queries_number} += $_overall_stat{queries_number}; - - if ($_overall_stat{'first_log_ts'}) { - $overall_stat{'first_log_ts'} = $_overall_stat{'first_log_ts'} - if (!$overall_stat{'first_log_ts'} || - ($overall_stat{'first_log_ts'} gt $_overall_stat{'first_log_ts'})); - } - - $overall_stat{'last_log_ts'} = $_overall_stat{'last_log_ts'} - if not $overall_stat{'last_log_ts'} - or $overall_stat{'last_log_ts'} lt $_overall_stat{'last_log_ts'}; - - if ($_overall_stat{'first_query_ts'}) { - $overall_stat{'first_query_ts'} = $_overall_stat{'first_query_ts'} - if (!$overall_stat{'first_query_ts'} || - ($overall_stat{'first_query_ts'} gt $_overall_stat{'first_query_ts'})); - } - - $overall_stat{'last_query_ts'} = $_overall_stat{'last_query_ts'} - if not $overall_stat{'last_query_ts'} - or $overall_stat{'last_query_ts'} lt $_overall_stat{'last_query_ts'}; - - $overall_stat{errors_number} += $_overall_stat{errors_number}; - $overall_stat{queries_duration} += $_overall_stat{queries_duration}; - - foreach my $a (@SQL_ACTION) { - $overall_stat{$a} += $_overall_stat{$a} - if exists $_overall_stat{$a}; - } - - $overall_checkpoint{checkpoint_warning} += $_overall_checkpoint{checkpoint_warning}; - $overall_checkpoint{checkpoint_write} = $_overall_checkpoint{checkpoint_write} - if ($_overall_checkpoint{checkpoint_write} > $overall_checkpoint{checkpoint_write}); - $overall_checkpoint{checkpoint_sync} = $_overall_checkpoint{checkpoint_sync} - if ($_overall_checkpoint{checkpoint_sync} > $overall_checkpoint{checkpoint_sync}); - foreach my $k (keys %{$_overall_stat{peak}}) { - $overall_stat{peak}{$k}{query} += $_overall_stat{peak}{$k}{query}; - $overall_stat{peak}{$k}{select} += $_overall_stat{peak}{$k}{select}; - $overall_stat{peak}{$k}{write} += $_overall_stat{peak}{$k}{write}; - $overall_stat{peak}{$k}{connection} += $_overall_stat{peak}{$k}{connection}; - $overall_stat{peak}{$k}{session} += $_overall_stat{peak}{$k}{session}; - $overall_stat{peak}{$k}{tempfile_size} += $_overall_stat{peak}{$k}{tempfile_size}; - $overall_stat{peak}{$k}{tempfile_count} += $_overall_stat{peak}{$k}{tempfile_count}; - $overall_stat{peak}{$k}{cancelled_size} += $_overall_stat{peak}{$k}{cancelled_size}; - $overall_stat{peak}{$k}{cancelled_count} += $_overall_stat{peak}{$k}{cancelled_count}; - } - - foreach my $k (keys %{$_overall_stat{histogram}{query_time}}) { - $overall_stat{histogram}{query_time}{$k} += $_overall_stat{histogram}{query_time}{$k}; - } - $overall_stat{histogram}{query_total} += $_overall_stat{histogram}{total}; - $overall_stat{histogram}{query_total} += $_overall_stat{histogram}{query_total}; - foreach my $k (keys %{$_overall_stat{histogram}{session_time}}) { - $overall_stat{histogram}{session_time}{$k} += $_overall_stat{histogram}{session_time}{$k}; - } - $overall_stat{histogram}{session_total} += $_overall_stat{histogram}{session_total}; - - foreach my $k ('prepare', 'bind','execute') { - $overall_stat{$k} += $_overall_stat{$k}; - } - - foreach my $k (keys %{$_overall_checkpoint{peak}}) { - $overall_checkpoint{peak}{$k}{checkpoint_wbuffer} += $_overall_checkpoint{peak}{$k}{checkpoint_wbuffer}; - $overall_checkpoint{peak}{$k}{walfile_usage} += $_overall_checkpoint{peak}{$k}{walfile_usage}; - } - - ### pgbouncer related overall stats ### - foreach my $k (keys %{$_pgb_overall_stat{peak}}) { - $pgb_overall_stat{peak}{$k}{connection} += $_pgb_overall_stat{peak}{$k}{connection}; - $pgb_overall_stat{peak}{$k}{session} += $_pgb_overall_stat{peak}{$k}{session}; - $pgb_overall_stat{peak}{$k}{t_req} += $_pgb_overall_stat{peak}{$k}{t_req}; - $pgb_overall_stat{peak}{$k}{t_inbytes} += $_pgb_overall_stat{peak}{$k}{t_inbytes}; - $pgb_overall_stat{peak}{$k}{t_outbytes} += $_pgb_overall_stat{peak}{$k}{t_outbytes}; - $pgb_overall_stat{peak}{$k}{t_avgduration} += $_pgb_overall_stat{peak}{$k}{t_avgduration}; - } - - foreach my $k (keys %{$_pgb_overall_stat{histogram}{session_time}}) { - $pgb_overall_stat{histogram}{session_time}{$k} += $_pgb_overall_stat{histogram}{session_time}{$k}; - } - $pgb_overall_stat{histogram}{session_total} += $_pgb_overall_stat{histogram}{session_total}; - - $pgb_overall_stat{errors_number} += $_pgb_overall_stat{errors_number}; - - ### Logs level ### - - foreach my $l (qw(LOG WARNING ERROR FATAL PANIC DETAIL HINT STATEMENT CONTEXT)) { - $logs_type{$l} += $_logs_type{$l} if exists $_logs_type{$l}; - } - - ### database_info ### - - foreach my $db (keys %_database_info) { - foreach my $k (keys %{ $_database_info{$db} }) { - $database_info{$db}{$k} += $_database_info{$db}{$k}; - } - } - - ### application_info ### - - foreach my $app (keys %_application_info) { - foreach my $k (keys %{ $_application_info{$app} }) { - $application_info{$app}{$k} += $_application_info{$app}{$k}; - } - } - - ### user_info ### - - foreach my $u (keys %_user_info) { - foreach my $k (keys %{ $_user_info{$u} }) { - $user_info{$u}{$k} += $_user_info{$u}{$k}; - } - } - - ### host_info ### - - foreach my $h (keys %_host_info) { - foreach my $k (keys %{ $_host_info{$h} }) { - $host_info{$h}{$k} += $_host_info{$h}{$k}; - } - } - - - ### connection_info ### - - foreach my $db (keys %{ $_connection_info{database} }) { - $connection_info{database}{$db} += $_connection_info{database}{$db}; - } - - foreach my $db (keys %{ $_connection_info{database_user} }) { - foreach my $user (keys %{ $_connection_info{database_user}{$db} }) { - $connection_info{database_user}{$db}{$user} += $_connection_info{database_user}{$db}{$user}; - } - } - - foreach my $user (keys %{ $_connection_info{user} }) { - $connection_info{user}{$user} += $_connection_info{user}{$user}; - } - - foreach my $host (keys %{ $_connection_info{host} }) { - $connection_info{host}{$host} += $_connection_info{host}{$host}; - } - - $connection_info{count} += $_connection_info{count}; - - foreach my $day (keys %{ $_connection_info{chronos} }) { - foreach my $hour (keys %{ $_connection_info{chronos}{$day} }) { - - $connection_info{chronos}{$day}{$hour}{count} += $_connection_info{chronos}{$day}{$hour}{count} - -############################################################################### -# May be used in the future to display more detailed information on connection -# -# foreach my $db (keys %{ $_connection_info{chronos}{$day}{$hour}{database} }) { -# $connection_info{chronos}{$day}{$hour}{database}{$db} += $_connection_info{chronos}{$day}{$hour}{database}{$db}; -# } -# -# foreach my $db (keys %{ $_connection_info{chronos}{$day}{$hour}{database_user} }) { -# foreach my $user (keys %{ $_connection_info{chronos}{$day}{$hour}{database_user}{$db} }) { -# $connection_info{chronos}{$day}{$hour}{database_user}{$db}{$user} += -# $_connection_info{chronos}{$day}{$hour}{database_user}{$db}{$user}; -# } -# } -# -# foreach my $user (keys %{ $_connection_info{chronos}{$day}{$hour}{user} }) { -# $connection_info{chronos}{$day}{$hour}{user}{$user} += -# $_connection_info{chronos}{$day}{$hour}{user}{$user}; -# } -# -# foreach my $host (keys %{ $_connection_info{chronos}{$day}{$hour}{host} }) { -# $connection_info{chronos}{$day}{$hour}{host}{$host} += -# $_connection_info{chronos}{$day}{$hour}{host}{$host}; -# } -############################################################################### - } - } - - ### pgbouncer connection_info ### - - foreach my $db (keys %{ $_pgb_connection_info{database} }) { - $pgb_connection_info{database}{$db} += $_pgb_connection_info{database}{$db}; - } - - foreach my $db (keys %{ $_pgb_connection_info{database_user} }) { - foreach my $user (keys %{ $_pgb_connection_info{database_user}{$db} }) { - $pgb_connection_info{database_user}{$db}{$user} += $_pgb_connection_info{database_user}{$db}{$user}; - } - } - - foreach my $user (keys %{ $_pgb_connection_info{user} }) { - $pgb_connection_info{user}{$user} += $_pgb_connection_info{user}{$user}; - } - - foreach my $host (keys %{ $_pgb_connection_info{host} }) { - $pgb_connection_info{host}{$host} += $_pgb_connection_info{host}{$host}; - } - - $pgb_connection_info{count} += $_pgb_connection_info{count}; - - foreach my $day (keys %{ $_pgb_connection_info{chronos} }) { - foreach my $hour (keys %{ $_pgb_connection_info{chronos}{$day} }) { - $pgb_connection_info{chronos}{$day}{$hour}{count} += $_pgb_connection_info{chronos}{$day}{$hour}{count} - } - } - - ### log_files ### - - foreach my $f (@_log_files) { - push(@log_files, $f) if (!grep(m#^$f$#, @_log_files)); - } - - ### error_info ### - - foreach my $q (keys %_error_info) { - $error_info{$q}{count} += $_error_info{$q}{count}; - foreach my $day (keys %{ $_error_info{$q}{chronos} }) { - foreach my $hour (keys %{$_error_info{$q}{chronos}{$day}}) { - $error_info{$q}{chronos}{$day}{$hour}{count} += $_error_info{$q}{chronos}{$day}{$hour}{count}; - foreach my $min (keys %{$_error_info{$q}{chronos}{$day}{$hour}{min}}) { - $error_info{$q}{chronos}{$day}{$hour}{min}{$min} += $_error_info{$q}{chronos}{$day}{$hour}{min}{$min}; - } - } - } - for (my $i = 0; $i <= $#{$_error_info{$q}{date}}; $i++) { - &set_top_error_sample( $q, - $_error_info{$q}{date}[$i], - $_error_info{$q}{error}[$i], - $_error_info{$q}{detail}[$i], - $_error_info{$q}{context}[$i], - $_error_info{$q}{statement}[$i], - $_error_info{$q}{hint}[$i], - $_error_info{$q}{db}[$i], - $_error_info{$q}{user}[$i], - $_error_info{$q}{app}[$i], - $_error_info{$q}{remote}[$i] - ); - } - } - - ### pgbouncer error_info ### - - foreach my $q (keys %_pgb_error_info) { - $pgb_error_info{$q}{count} += $_pgb_error_info{$q}{count}; - foreach my $day (keys %{ $_pgb_error_info{$q}{chronos} }) { - foreach my $hour (keys %{$_pgb_error_info{$q}{chronos}{$day}}) { - $pgb_error_info{$q}{chronos}{$day}{$hour}{count} += $_pgb_error_info{$q}{chronos}{$day}{$hour}{count}; - foreach my $min (keys %{$_pgb_error_info{$q}{chronos}{$day}{$hour}{min}}) { - $pgb_error_info{$q}{chronos}{$day}{$hour}{min}{$min} += $_pgb_error_info{$q}{chronos}{$day}{$hour}{min}{$min}; - } - } - } - for (my $i = 0; $i <= $#{$_pgb_error_info{$q}{date}}; $i++) { - &pgb_set_top_error_sample( $q, - $_pgb_error_info{$q}{date}[$i], - $_pgb_error_info{$q}{error}[$i], - $_pgb_error_info{$q}{db}[$i], - $_pgb_error_info{$q}{user}[$i], - $_pgb_error_info{$q}{remote}[$i] - ); - } - } - - ### pgbouncer pool_info ### - - foreach my $q (keys %_pgb_pool_info) { - $pgb_pool_info{$q}{count} += $_pgb_pool_info{$q}{count}; - foreach my $day (keys %{ $_pgb_pool_info{$q}{chronos} }) { - foreach my $hour (keys %{$_pgb_pool_info{$q}{chronos}{$day}}) { - $pgb_pool_info{$q}{chronos}{$day}{$hour}{count} += $_pgb_pool_info{$q}{chronos}{$day}{$hour}{count}; - foreach my $min (keys %{$_pgb_pool_info{$q}{chronos}{$day}{$hour}{min}}) { - $pgb_pool_info{$q}{chronos}{$day}{$hour}{min}{$min} += $_pgb_pool_info{$q}{chronos}{$day}{$hour}{min}{$min}; - } - } - } - } - - ### per_minute_info ### - - foreach my $day (keys %_per_minute_info) { - foreach my $hour (keys %{ $_per_minute_info{$day} }) { - foreach my $min (keys %{ $_per_minute_info{$day}{$hour} }) { - $per_minute_info{$day}{$hour}{$min}{connection}{count} += - ($_per_minute_info{$day}{$hour}{$min}{connection}{count} || 0); - $per_minute_info{$day}{$hour}{$min}{session}{count} += - ($_per_minute_info{$day}{$hour}{$min}{session}{count} || 0); - $per_minute_info{$day}{$hour}{$min}{query}{count} += - ($_per_minute_info{$day}{$hour}{$min}{query}{count} || 0); - $per_minute_info{$day}{$hour}{$min}{query}{duration} += $_per_minute_info{$day}{$hour}{$min}{query}{duration}; - $per_minute_info{$day}{$hour}{$min}{query}{min} = $_per_minute_info{$day}{$hour}{$min}{query}{min} if (!exists $per_minute_info{$day}{$hour}{$min}{query}{min} || ($per_minute_info{$day}{$hour}{$min}{query}{min} > $_per_minute_info{$day}{$hour}{$min}{query}{min})); - $per_minute_info{$day}{$hour}{$min}{query}{max} = $_per_minute_info{$day}{$hour}{$min}{query}{max} if (!exists $per_minute_info{$day}{$hour}{$min}{query}{max} || ($per_minute_info{$day}{$hour}{$min}{query}{max} < $_per_minute_info{$day}{$hour}{$min}{query}{max})); - - foreach my $sec (keys %{ $_per_minute_info{$day}{$hour}{$min}{connection}{second} }) { - $per_minute_info{$day}{$hour}{$min}{connection}{second}{$sec} += - ($_per_minute_info{$day}{$hour}{$min}{connection}{second}{$sec} || 0); - } - foreach my $sec (keys %{ $_per_minute_info{$day}{$hour}{$min}{session}{second} }) { - $per_minute_info{$day}{$hour}{$min}{session}{second}{$sec} += - ($_per_minute_info{$day}{$hour}{$min}{session}{second}{$sec} || 0); - } - foreach my $sec (keys %{ $_per_minute_info{$day}{$hour}{$min}{query}{second} }) { - $per_minute_info{$day}{$hour}{$min}{query}{second}{$sec} += - ($_per_minute_info{$day}{$hour}{$min}{query}{second}{$sec} || 0); - } - foreach my $action (@SQL_ACTION) { - next if (!exists $_per_minute_info{$day}{$hour}{$min}{$action}); - $per_minute_info{$day}{$hour}{$min}{$action}{count} += ($_per_minute_info{$day}{$hour}{$min}{$action}{count} || 0); - $per_minute_info{$day}{$hour}{$min}{$action}{duration} += ($_per_minute_info{$day}{$hour}{$min}{$action}{duration} || 0); - foreach my $sec (keys %{ $_per_minute_info{$day}{$hour}{$min}{$action}{second} }) { - $per_minute_info{$day}{$hour}{$min}{$action}{second}{$sec} += - ($_per_minute_info{$day}{$hour}{$min}{$action}{second}{$sec} || 0); - } - } - foreach my $k ('prepare', 'bind','execute') { - if (exists $_per_minute_info{$day}{$hour}{$min}{$k}) { - $per_minute_info{$day}{$hour}{$min}{$k} += $_per_minute_info{$day}{$hour}{$min}{$k}; - } - } - foreach my $log (keys %{ $_per_minute_info{$day}{$hour}{$min}{log_level} }) { - $per_minute_info{$day}{$hour}{$min}{log_level}{$log} += - ($_per_minute_info{$day}{$hour}{$min}{log_level}{$log} || 0); - } - - $per_minute_info{$day}{$hour}{$min}{cancelled}{count} += $_per_minute_info{$day}{$hour}{$min}{cancelled}{count} - if defined $_per_minute_info{$day}{$hour}{$min}{cancelled}{count}; - $per_minute_info{$day}{$hour}{$min}{tempfile}{count} += $_per_minute_info{$day}{$hour}{$min}{tempfile}{count} - if defined $_per_minute_info{$day}{$hour}{$min}{tempfile}{count}; - $per_minute_info{$day}{$hour}{$min}{tempfile}{size} += $_per_minute_info{$day}{$hour}{$min}{tempfile}{size} - if defined $_per_minute_info{$day}{$hour}{$min}{tempfile}{size}; - - $per_minute_info{$day}{$hour}{$min}{checkpoint}{file_removed} += $_per_minute_info{$day}{$hour}{$min}{checkpoint}{file_removed}; - $per_minute_info{$day}{$hour}{$min}{checkpoint}{sync} += $_per_minute_info{$day}{$hour}{$min}{checkpoint}{sync}; - $per_minute_info{$day}{$hour}{$min}{checkpoint}{wbuffer} += $_per_minute_info{$day}{$hour}{$min}{checkpoint}{wbuffer}; - $per_minute_info{$day}{$hour}{$min}{checkpoint}{file_recycled} += $_per_minute_info{$day}{$hour}{$min}{checkpoint}{file_recycled}; - $per_minute_info{$day}{$hour}{$min}{checkpoint}{total} += $_per_minute_info{$day}{$hour}{$min}{checkpoint}{total}; - $per_minute_info{$day}{$hour}{$min}{checkpoint}{file_added} += $_per_minute_info{$day}{$hour}{$min}{checkpoint}{file_added}; - $per_minute_info{$day}{$hour}{$min}{checkpoint}{write} += $_per_minute_info{$day}{$hour}{$min}{checkpoint}{write}; - $per_minute_info{$day}{$hour}{$min}{autovacuum}{count} += $_per_minute_info{$day}{$hour}{$min}{autovacuum}{count}; - $per_minute_info{$day}{$hour}{$min}{autoanalyze}{count} += $_per_minute_info{$day}{$hour}{$min}{autoanalyze}{count}; - - $per_minute_info{$day}{$hour}{$min}{checkpoint}{sync_files} += $_per_minute_info{$day}{$hour}{$min}{checkpoint}{sync_files}; - $per_minute_info{$day}{$hour}{$min}{checkpoint}{sync_avg} += $_per_minute_info{$day}{$hour}{$min}{checkpoint}{sync_avg}; - $per_minute_info{$day}{$hour}{$min}{checkpoint}{sync_longest} = $_per_minute_info{$day}{$hour}{$min}{checkpoint}{sync_longest} - if ($_per_minute_info{$day}{$hour}{$min}{checkpoint}{sync_longest} > $per_minute_info{$day}{$hour}{$min}{checkpoint}{sync_longest}); - } - } - } - - ### pgbouncer per_minute_info ### - - foreach my $day (keys %_pgb_per_minute_info) { - foreach my $hour (keys %{ $_pgb_per_minute_info{$day} }) { - foreach my $min (keys %{ $_pgb_per_minute_info{$day}{$hour} }) { - $pgb_per_minute_info{$day}{$hour}{$min}{connection}{count} += - ($_pgb_per_minute_info{$day}{$hour}{$min}{connection}{count} || 0); - $pgb_per_minute_info{$day}{$hour}{$min}{session}{count} += - ($_pgb_per_minute_info{$day}{$hour}{$min}{session}{count} || 0); - $pgb_per_minute_info{$day}{$hour}{$min}{t_req} += - ($_pgb_per_minute_info{$day}{$hour}{$min}{t_req} || 0); - $pgb_per_minute_info{$day}{$hour}{$min}{t_inbytes} += - ($_pgb_per_minute_info{$day}{$hour}{$min}{t_inbytes} || 0); - $pgb_per_minute_info{$day}{$hour}{$min}{t_outbytes} += - ($_pgb_per_minute_info{$day}{$hour}{$min}{t_outbytes} || 0); - $pgb_per_minute_info{$day}{$hour}{$min}{t_avgduration} += - ($_pgb_per_minute_info{$day}{$hour}{$min}{t_avgduration} || 0); - } - } - } - - ### lock_info ### - - foreach my $lock (keys %_lock_info) { - $lock_info{$lock}{count} += $_lock_info{$lock}{count}; - - foreach my $day (keys %{ $_lock_info{chronos} }) { - foreach my $hour (keys %{ $_lock_info{chronos}{$day} }) { - $lock_info{chronos}{$day}{$hour}{count} += $_lock_info{chronos}{$day}{$hour}{count}; - $lock_info{chronos}{$day}{$hour}{duration} += $_lock_info{chronos}{$day}{$hour}{duration}; - } - } - - $lock_info{$lock}{duration} += $_lock_info{$lock}{duration}; - - foreach my $type (keys %{$_lock_info{$lock}}) { - next if $type =~ /^(count|chronos|duration)$/; - - $lock_info{$lock}{$type}{count} += $_lock_info{$lock}{$type}{count}; - $lock_info{$lock}{$type}{duration} += $_lock_info{$lock}{$type}{duration}; - } - } - - ### nlines ### - - $nlines += $_nlines; - - ### normalyzed_info ### - - foreach my $stmt (keys %_normalyzed_info) { - - foreach my $dt (keys %{$_normalyzed_info{$stmt}{samples}} ) { - foreach my $k (keys %{$_normalyzed_info{$stmt}{samples}{$dt}} ) { - $normalyzed_info{$stmt}{samples}{$dt}{$k} = $_normalyzed_info{$stmt}{samples}{$dt}{$k}; - } - } - - # Keep only the top N samples - my $i = 1; - foreach my $k (sort {$b <=> $a} keys %{$normalyzed_info{$stmt}{samples}}) { - if ($i > $sample) { - delete $normalyzed_info{$stmt}{samples}{$k}; - } - $i++; - } - - $normalyzed_info{$stmt}{count} += $_normalyzed_info{$stmt}{count}; - - # Set min / max duration for this query - if (!exists $normalyzed_info{$stmt}{min} || ($normalyzed_info{$stmt}{min} > $_normalyzed_info{$stmt}{min})) { - $normalyzed_info{$stmt}{min} = $_normalyzed_info{$stmt}{min}; - } - if (!exists $normalyzed_info{$stmt}{max} || ($normalyzed_info{$stmt}{max} < $_normalyzed_info{$stmt}{max})) { - $normalyzed_info{$stmt}{max} = $_normalyzed_info{$stmt}{max}; - } - - foreach my $day (keys %{$_normalyzed_info{$stmt}{chronos}} ) { - foreach my $hour (keys %{$_normalyzed_info{$stmt}{chronos}{$day}} ) { - $normalyzed_info{$stmt}{chronos}{$day}{$hour}{count} += - $_normalyzed_info{$stmt}{chronos}{$day}{$hour}{count}; - $normalyzed_info{$stmt}{chronos}{$day}{$hour}{duration} += - $_normalyzed_info{$stmt}{chronos}{$day}{$hour}{duration}; - foreach my $min (keys %{$_normalyzed_info{$stmt}{chronos}{$day}{$hour}{min}} ) { - $normalyzed_info{$stmt}{chronos}{$day}{$hour}{min}{$min} += - $_normalyzed_info{$stmt}{chronos}{$day}{$hour}{min}{$min}; - } - foreach my $min (keys %{$_normalyzed_info{$stmt}{chronos}{$day}{$hour}{min_duration}} ) { - $normalyzed_info{$stmt}{chronos}{$day}{$hour}{min_duration}{$min} += - $_normalyzed_info{$stmt}{chronos}{$day}{$hour}{min_duration}{$min}; - } - } - } - - $normalyzed_info{$stmt}{duration} += $_normalyzed_info{$stmt}{duration}; - - if (exists $_normalyzed_info{$stmt}{locks}) { - $normalyzed_info{$stmt}{locks}{count} += $_normalyzed_info{$stmt}{locks}{count}; - $normalyzed_info{$stmt}{locks}{wait} += $_normalyzed_info{$stmt}{locks}{wait}; - if (!exists $normalyzed_info{$stmt}{locks}{minwait} || ($normalyzed_info{$stmt}{locks}{minwait} > $_normalyzed_info{$stmt}{locks}{minwait})) { - $normalyzed_info{$stmt}{locks}{minwait} = $_normalyzed_info{$stmt}{locks}{minwait}; - } - if (!exists $normalyzed_info{$stmt}{locks}{maxwait} || ($normalyzed_info{$stmt}{locks}{maxwait} < $_normalyzed_info{$stmt}{locks}{maxwait})) { - $normalyzed_info{$stmt}{locks}{maxwait} = $_normalyzed_info{$stmt}{locks}{maxwait}; - } - } - - if (exists $_normalyzed_info{$stmt}{tempfiles}) { - $normalyzed_info{$stmt}{tempfiles}{count} += $_normalyzed_info{$stmt}{tempfiles}{count}; - $normalyzed_info{$stmt}{tempfiles}{size} += $_normalyzed_info{$stmt}{tempfiles}{size}; - if (!exists $normalyzed_info{$stmt}{tempfiles}{minsize} || ($normalyzed_info{$stmt}{tempfiles}{minsize} > $_normalyzed_info{$stmt}{tempfiles}{minsize})) { - $normalyzed_info{$stmt}{tempfiles}{minsize} = $_normalyzed_info{$stmt}{tempfiles}{minsize}; - } - if (!exists $normalyzed_info{$stmt}{tempfiles}{maxsize} || ($normalyzed_info{$stmt}{tempfiles}{maxsize} < $_normalyzed_info{$stmt}{tempfiles}{maxsize})) { - $normalyzed_info{$stmt}{tempfiles}{maxsize} = $_normalyzed_info{$stmt}{tempfiles}{maxsize}; - } - } - - if (exists $_normalyzed_info{$stmt}{cancelled}) { - $normalyzed_info{$stmt}{cancelled}{count} += $_normalyzed_info{$stmt}{cancelled}{count}; - } - - foreach my $u (keys %{$_normalyzed_info{$stmt}{users}} ) { - foreach my $k (keys %{$_normalyzed_info{$stmt}{users}{$u}} ) { - $normalyzed_info{$stmt}{users}{$u}{$k} += $_normalyzed_info{$stmt}{users}{$u}{$k}; - } - } - foreach my $u (keys %{$_normalyzed_info{$stmt}{apps}} ) { - foreach my $k (keys %{$_normalyzed_info{$stmt}{apps}{$u}} ) { - $normalyzed_info{$stmt}{apps}{$u}{$k} += $_normalyzed_info{$stmt}{apps}{$u}{$k}; - } - } - } - - ### session_info ### - - foreach my $db (keys %{ $_session_info{database}}) { - $session_info{database}{$db}{count} += $_session_info{database}{$db}{count}; - $session_info{database}{$db}{duration} += $_session_info{database}{$db}{duration}; - } - - $session_info{count} += $_session_info{count}; - - foreach my $day (keys %{ $_session_info{chronos}}) { - foreach my $hour (keys %{ $_session_info{chronos}{$day}}) { - $session_info{chronos}{$day}{$hour}{count} += $_session_info{chronos}{$day}{$hour}{count}; - $session_info{chronos}{$day}{$hour}{duration} += $_session_info{chronos}{$day}{$hour}{duration}; - } - } - - foreach my $user (keys %{ $_session_info{user}}) { - $session_info{user}{$user}{count} += $_session_info{user}{$user}{count}; - $session_info{user}{$user}{duration} += $_session_info{user}{$user}{duration}; - } - - $session_info{duration} += $_session_info{duration}; - - foreach my $host (keys %{ $_session_info{host}}) { - $session_info{host}{$host}{count} += $_session_info{host}{$host}{count}; - $session_info{host}{$host}{duration} += $_session_info{host}{$host}{duration}; - } - - foreach my $app (keys %{ $_session_info{app}}) { - $session_info{app}{$app}{count} += $_session_info{app}{$app}{count}; - $session_info{app}{$app}{duration} += $_session_info{app}{$app}{duration}; - } - - ### pgbouncer session_info ### - - foreach my $db (keys %{ $_pgb_session_info{database}}) { - $pgb_session_info{database}{$db}{count} += $_pgb_session_info{database}{$db}{count}; - $pgb_session_info{database}{$db}{duration} += $_pgb_session_info{database}{$db}{duration}; - } - - $pgb_session_info{count} += $_pgb_session_info{count}; - - foreach my $day (keys %{ $_pgb_session_info{chronos}}) { - foreach my $hour (keys %{ $_pgb_session_info{chronos}{$day}}) { - $pgb_session_info{chronos}{$day}{$hour}{count} += $_pgb_session_info{chronos}{$day}{$hour}{count}; - $pgb_session_info{chronos}{$day}{$hour}{duration} += $_pgb_session_info{chronos}{$day}{$hour}{duration}; - } - } - - foreach my $user (keys %{ $_pgb_session_info{user}}) { - $pgb_session_info{user}{$user}{count} += $_pgb_session_info{user}{$user}{count}; - $pgb_session_info{user}{$user}{duration} += $_pgb_session_info{user}{$user}{duration}; - } - - $pgb_session_info{duration} += $_pgb_session_info{duration}; - - foreach my $host (keys %{ $_pgb_session_info{host}}) { - $pgb_session_info{host}{$host}{count} += $_pgb_session_info{host}{$host}{count}; - $pgb_session_info{host}{$host}{duration} += $_pgb_session_info{host}{$host}{duration}; - } - - ### tempfile_info ### - - $tempfile_info{count} += $_tempfile_info{count} - if defined $_tempfile_info{count}; - $tempfile_info{size} += $_tempfile_info{size} - if defined $_tempfile_info{size}; - $tempfile_info{maxsize} = $_tempfile_info{maxsize} - if defined $_tempfile_info{maxsize} and ( not defined $tempfile_info{maxsize} - or $tempfile_info{maxsize} < $_tempfile_info{maxsize} ); - - ### top_slowest ### - my @tmp_top_slowest = sort {$b->[0] <=> $a->[0]} (@top_slowest, @_top_slowest); - @top_slowest = (); - for (my $i = 0; $i <= $#tmp_top_slowest; $i++) { - push(@top_slowest, $tmp_top_slowest[$i]); - } - - ### top_locked ### - my @tmp_top_locked_info = sort {$b->[0] <=> $a->[0]} (@top_locked_info, @_top_locked_info); - @top_locked_info = (); - for (my $i = 0; $i <= $#tmp_top_locked_info; $i++) { - push(@top_locked_info, $tmp_top_locked_info[$i]); - } - - ### top_tempfile ### - my @tmp_top_tempfile_info = sort {$b->[0] <=> $a->[0]} (@top_tempfile_info, @_top_tempfile_info); - @top_tempfile_info = (); - for (my $i = 0; $i <= $#tmp_top_tempfile_info; $i++) { - push(@top_tempfile_info, $tmp_top_tempfile_info[$i]); - } - - ### checkpoint_info ### - $checkpoint_info{file_removed} += $_checkpoint_info{file_removed}; - $checkpoint_info{sync} += $_checkpoint_info{sync}; - $checkpoint_info{wbuffer} += $_checkpoint_info{wbuffer}; - $checkpoint_info{file_recycled} += $_checkpoint_info{file_recycled}; - $checkpoint_info{total} += $_checkpoint_info{total}; - $checkpoint_info{file_added} += $_checkpoint_info{file_added}; - $checkpoint_info{write} += $_checkpoint_info{write}; - - #### Autovacuum info #### - - $autovacuum_info{count} += $_autovacuum_info{count}; - - foreach my $day (keys %{ $_autovacuum_info{chronos} }) { - foreach my $hour (keys %{ $_autovacuum_info{chronos}{$day} }) { - $autovacuum_info{chronos}{$day}{$hour}{count} += $_autovacuum_info{chronos}{$day}{$hour}{count}; - } - } - foreach my $table (keys %{ $_autovacuum_info{tables} }) { - $autovacuum_info{tables}{$table}{vacuums} += $_autovacuum_info{tables}{$table}{vacuums}; - $autovacuum_info{tables}{$table}{idxscans} += $_autovacuum_info{tables}{$table}{idxscans}; - $autovacuum_info{tables}{$table}{tuples}{removed} += $_autovacuum_info{tables}{$table}{tuples}{removed}; - $autovacuum_info{tables}{$table}{pages}{removed} += $_autovacuum_info{tables}{$table}{pages}{removed}; - } - if ($_autovacuum_info{peak}{system_usage}{elapsed} > $autovacuum_info{peak}{system_usage}{elapsed}) { - $autovacuum_info{peak}{system_usage}{elapsed} = $_autovacuum_info{peak}{system_usage}{elapsed}; - $autovacuum_info{peak}{system_usage}{table} = $_autovacuum_info{peak}{system_usage}{table}; - $autovacuum_info{peak}{system_usage}{date} = $_autovacuum_info{peak}{system_usage}{date}; - } - #### Autoanalyze info #### - - $autoanalyze_info{count} += $_autoanalyze_info{count}; - - foreach my $day (keys %{ $_autoanalyze_info{chronos} }) { - foreach my $hour (keys %{ $_autoanalyze_info{chronos}{$day} }) { - $autoanalyze_info{chronos}{$day}{$hour}{count} += $_autoanalyze_info{chronos}{$day}{$hour}{count}; - } - } - foreach my $table (keys %{ $_autoanalyze_info{tables} }) { - $autoanalyze_info{tables}{$table}{analyzes} += $_autoanalyze_info{tables}{$table}{analyzes}; - } - if ($_autoanalyze_info{peak}{system_usage}{elapsed} > $autoanalyze_info{peak}{system_usage}{elapsed}) { - $autoanalyze_info{peak}{system_usage}{elapsed} = $_autoanalyze_info{peak}{system_usage}{elapsed}; - $autoanalyze_info{peak}{system_usage}{table} = $_autoanalyze_info{peak}{system_usage}{table}; - $autoanalyze_info{peak}{system_usage}{date} = $_autoanalyze_info{peak}{system_usage}{date}; - } - - use strict; - - return; -} - - -# Do minimal query formatting. -# Add carriage return to be more human readable -sub fmtquery -{ - my $qry = shift; - - return $qry if (!$format_query); - - my @KEYWORDS = qw( FROM INNER WHERE AND OR ORDER RETURNING ); - - foreach my $key (@KEYWORDS) { - $qry =~ s/\s+($key\s+)/\n$1/ig; - } - return $qry; -} - -############################################################################### -# TOOL FUNCTIONS -# Add your own below -############################################################################### - -sub dump_error_as_docuwiki -{ - - # Global information - $report_title ||= 'PostgreSQL Log Analyzer'; - - print "==== $report_title ====\n\n"; - - &show_error_as_docuwiki(); - - &show_pgb_error_as_docuwiki(); - -} - -sub show_error_as_docuwiki -{ - return if (scalar keys %error_info == 0); - - print "=== Most frequent events (N) ===\n\n"; - my $idx = 1; - foreach my $k (sort {$error_info{$b}{count} <=> $error_info{$a}{count}} keys %error_info) { - next if (!$error_info{$k}{count}); - last if ($idx > $top); - last if (!$error_info{$k}{count}); - - my $msg = $k; - $msg =~ s/ERROR: (parameter "[^"]+" changed to)/LOG: $1/; - $msg =~ s/ERROR: (database system was shut down)/LOG: $1/; - $msg =~ s/ERROR: (recovery has paused)/LOG: $1/; - $msg =~ s/ERROR: (database system was interrupted while in recovery)/LOG: $1/; - $msg =~ s/ERROR: (sending cancel to blocking autovacuum)/LOG: $1/; - $msg =~ s/ERROR: (skipping analyze of)/LOG: $1/; - - if ($error_info{$k}{count} > 1) { - print " - ) $error_info{$k}{count} - $msg\n"; - my $j = 1; - for (my $i = 0 ; $i <= $#{$error_info{$k}{date}} ; $i++) { - last if ($i == $sample); - if ( ($error_info{$k}{error}[$i] =~ s/ERROR: (parameter "[^"]+" changed to)/LOG: $1/) - || ($error_info{$k}{error}[$i] =~ s/ERROR: (database system was shut down)/LOG: $1/) - || ($error_info{$k}{error}[$i] =~ s/ERROR: (database system was interrupted while in recovery)/LOG: $1/) - || ($error_info{$k}{error}[$i] =~ s/ERROR: (recovery has paused)/LOG: $1/) - || ($error_info{$k}{error}[$i] =~ s/ERROR: (sending cancel to blocking autovacuum)/LOG: $1/) - || ($error_info{$k}{error}[$i] =~ s/ERROR: (skipping analyze of)/LOG: $1/) - ) - { - $logs_type{ERROR}--; - $logs_type{LOG}++; - } - print " * **Example $j:** $error_info{$k}{date}[$i] - $error_info{$k}{error}[$i]\n"; - print " * **Detail:** $error_info{$k}{detail}[$i]\n" if ($error_info{$k}{detail}[$i]); - print " * **Context:** $error_info{$k}{context}[$i]\n" if ($error_info{$k}{context}[$i]); - print " * **Hint:** $error_info{$k}{hint}[$i]\n" if ($error_info{$k}{hint}[$i]); - print " * **Statement:** $error_info{$k}{statement}[$i]\n" if ($error_info{$k}{statement}[$i]); - print " * **Database:** $error_info{$k}{db}[$i]\n" if ($error_info{$k}{db}[$i]); - $j++; - } - } elsif ($error_info{$k}{error}[0]) { - if ( ($error_info{$k}{error}[0] =~ s/ERROR: (parameter "[^"]+" changed to)/LOG: $1/) - || ($error_info{$k}{error}[0] =~ s/ERROR: (database system was shut down)/LOG: $1/) - || ($error_info{$k}{error}[0] =~ s/ERROR: (database system was interrupted while in recovery)/LOG: $1/) - || ($error_info{$k}{error}[0] =~ s/ERROR: (recovery has paused)/LOG: $1/) - || ($error_info{$k}{error}[0] =~ s/ERROR: (sending cancel to blocking autovacuum)/LOG: $1/) - || ($error_info{$k}{error}[0] =~ s/ERROR: (skipping analyze of)/LOG: $1/) - ) - { - $logs_type{ERROR}--; - $logs_type{LOG}++; - } - if ($sample) { - print " - ) $error_info{$k}{count} - $error_info{$k}{error}[0]\n"; - print " * **Date:** $error_info{$k}{date}[0]\n"; - print " * **Detail:** $error_info{$k}{detail}[0]\n" if ($error_info{$k}{detail}[0]); - print " * **Context:** $error_info{$k}{context}[0]\n" if ($error_info{$k}{context}[0]); - print " * **Hint:** $error_info{$k}{hint}[0]\n" if ($error_info{$k}{hint}[0]); - print " * **Statement:** $error_info{$k}{statement}[0]\n" if ($error_info{$k}{statement}[0]); - print " * **Database:** $error_info{$k}{db}[0]\n" if ($error_info{$k}{db}[0]); - } else { - print " - ) $error_info{$k}{count} - $msg\n"; - } - } - $idx++; - } - - if (scalar keys %logs_type > 0) { - print "\n=== Logs per type ===\n\n"; - - my $total_logs = 0; - foreach my $d (keys %logs_type) { - $total_logs += $logs_type{$d}; - } - print "^Logs type^Count^Percentage^\n"; - foreach my $d (sort keys %logs_type) { - next if (!$logs_type{$d}); - print "|$d|$logs_type{$d}|", sprintf("%0.2f", ($logs_type{$d} * 100) / $total_logs), "%|\n"; - } - } -} - -sub show_pgb_error_as_docuwiki -{ - return if (scalar keys %pgb_error_info == 0); - - print "\n=== Most frequent pgbouncer events (N) ===\n\n"; - my $idx = 1; - foreach my $k (sort {$pgb_error_info{$b}{count} <=> $pgb_error_info{$a}{count}} keys %pgb_error_info) { - next if (!$pgb_error_info{$k}{count}); - last if ($idx > $top); - my $msg = $k; - if ($pgb_error_info{$k}{count} > 1) { - print " - ) $pgb_error_info{$k}{count} - $msg\n"; - my $j = 1; - for (my $i = 0 ; $i <= $#{$pgb_error_info{$k}{date}} ; $i++) { - last if ($i == $sample); - print " * **Example $j:** $pgb_error_info{$k}{date}[$i] - $pgb_error_info{$k}{error}[$i]\n"; - my $more_info = ''; - $more_info .= " **Date:** $pgb_error_info{$k}{date}[$i]" if ($pgb_error_info{$k}{db}[$i]); - $more_info .= " **Database:** $pgb_error_info{$k}{db}[$i]" if ($pgb_error_info{$k}{db}[$i]); - $more_info .= " **User:** $pgb_error_info{$k}{user}[$i]" if ($pgb_error_info{$k}{user}[$i]); - $more_info .= " **Client:** $pgb_error_info{$k}{remote}[$i]" if ($pgb_error_info{$k}{remote}[$i]); - print " * $more_info\n"; - $j++; - } - } else { - if ($sample) { - print " - ) $pgb_error_info{$k}{count} - $pgb_error_info{$k}{error}[0]\n"; - my $more_info = ''; - $more_info .= " **Date:** $pgb_error_info{$k}{date}[0]" if ($pgb_error_info{$k}{db}[0]); - $more_info .= " **Database:** $pgb_error_info{$k}{db}[0]" if ($pgb_error_info{$k}{db}[0]); - $more_info .= " **User:** $pgb_error_info{$k}{user}[0]" if ($pgb_error_info{$k}{user}[0]); - $more_info .= " **Client:** $pgb_error_info{$k}{remote}[0]" if ($pgb_error_info{$k}{remote}[0]); - print " * $more_info\n"; - } else { - print " - ) $pgb_error_info{$k}{count} - $msg\n"; - } - } - $idx++; - } - -} - -- 2.40.0