darold / pgcluu Goto Github PK

Hi there,

The statement statistics page does not appear to wrap long queries in either Chrome or Safari. In Safari this is less of an issue as I can horizontally scroll over to see the statistics, but in Chrome I cannot horizontally scroll this page.

While I'm guessing the scrolling issue is more to do with Chrome, our company's standard browser is Chrome, so when I demo this tool to management I'll have to use Safari. But for readability sake, it would be preferred if the entire table was constrained to the size of the browser window and the values in the Query column wrapped instead of forcing an extreme width.

Let me know if you require a screenshot.

/opt/pgcluu# pgcluu -V
Version: 2.1

/opt/pgcluu-report# pgcluu -o /opt/pgcluu-report/ /opt/pgcluu
Use of uninitialized value in concatenation (.) or string at /usr/local/bin/pgcluu line 4360.
Use of uninitialized value in concatenation (.) or string at /usr/local/bin/pgcluu line 4360.

No sar date collected :

/opt/pgcluu# ls -al
total 140
drwxr-xr-x 2 root root 4096 oct. 13 11:36 .
drwxr-xr-x 11 root root 4096 oct. 6 12:35 ..
-rw-r--r-- 1 root root 5606 oct. 13 11:36 pg_class_size.csv
-rw-r--r-- 1 root root 549 oct. 13 11:36 pg_database_size.csv
-rw-r--r-- 1 root root 52838 oct. 13 11:36 pg_settings.csv
-rw-r--r-- 1 root root 291 oct. 13 11:36 pg_stat_bgwriter.csv
-rw-r--r-- 1 root root 111 oct. 13 11:36 pg_stat_connections.csv
-rw-r--r-- 1 root root 564 oct. 13 11:36 pg_stat_database_conflicts.csv
-rw-r--r-- 1 root root 1209 oct. 13 11:36 pg_stat_database.csv
-rw-r--r-- 1 root root 1419 oct. 13 11:36 pg_statio_user_indexes.csv
-rw-r--r-- 1 root root 384 oct. 13 11:36 pg_statio_user_sequences.csv
-rw-r--r-- 1 root root 567 oct. 13 11:36 pg_statio_user_tables.csv
-rw-r--r-- 1 root root 1605 oct. 13 11:36 pg_stat_locks.csv
-rw-r--r-- 1 root root 147 oct. 13 11:36 pg_stat_missing_fkindexes.csv
-rw-r--r-- 1 root root 0 oct. 13 11:36 pg_stat_redundant_indexes.csv
-rw-r--r-- 1 root root 0 oct. 13 11:16 pg_stat_replication.csv
-rw-r--r-- 1 root root 0 oct. 13 11:36 pg_stat_unused_indexes.csv
-rw-r--r-- 1 root root 0 oct. 13 11:16 pg_stat_user_functions.csv
-rw-r--r-- 1 root root 1368 oct. 13 11:36 pg_stat_user_indexes.csv
-rw-r--r-- 1 root root 894 oct. 13 11:36 pg_stat_user_tables.csv
-rw-r--r-- 1 root root 0 oct. 13 11:16 pg_stat_xact_user_functions.csv
-rw-r--r-- 1 root root 351 oct. 13 11:36 pg_stat_xact_user_tables.csv
-rw-r--r-- 1 root root 465 oct. 13 11:36 pg_tablespace_size.csv
-rw-r--r-- 1 root root 213 oct. 13 11:36 pg_xlog_stat.csv
-rw-r--r-- 1 root root 0 oct. 13 11:16 sar_stats.dat
-rw-r--r-- 1 root root 4960 oct. 13 11:31 sysinfo.txt

But sar seems ok :

/opt/pgcluu# sar -u 1 1
Linux 3.2.0-4-amd64 (x) 13/10/2014 x86_64 (4 CPU)
11:38:28 CPU %user %nice %system %iowait %steal %idle
11:38:29 all 0,00 0,00 0,00 0,00 0,00 100,00
Moyenne : all 0,00 0,00 0,00 0,00 0,00 100,00

/opt/pgcluu# sar -V
sysstat version 10.0.5
(C) Sebastien Godard (sysstat orange.fr)

Bonjour,

Il semblerait que la génération du html via pgcluu pose problème soir sur les instances primaires répliquées :

[root@u3recu138 ~]# /var/lib/pgsql/pgcluu/pgcluu -v -o /var/lib/pgsql/pgcluu/html/pgsr021-5432/ /var/lib/pgsql/pgcluu/data/pgsr021-5432/ --disable-sar
(...)
DEBUG: reading statistics in file /var/lib/pgsql/pgcluu/data/pgsr021-5432/2015/09/22/pg_stat_replication.csv
Invalid offset: 0 at /var/lib/pgsql/pgcluu/pgcluu line 8478, line 1.

A noter : la collecte des données SAR a été désactivée.

Cordialement,
Tony LEGEAY

I imported a port of pgcluu into OpenBSD ports earlier today. It generally works fine (the PostgreSQL part that is), however there are two issues:

• there's no sar binary for sysstat is effectively a wrapper for cat /proc/*
• the other sysinfo related parts of pgcluu rely heavily on /proc too, or other Linuxisms such as swap or filesystem information.

Has it been considered to use a more widely supported library to grab the information? Such as libstatgrab or the like? That way sysinfo.txt's os-related content can be replaced with library calls and therefore making it more portable and as a bonus, preventing the need to shell-out.

On the index scan ratio, sequencial --> sequential

On the redundant indexes, "Liste of useless indexe" --> "List of useless indexes"

Hi,
I installed and used the pgCluu as following:
tar xzf pgcluu-2.x.tar.gz
cd pgcluu-2.x/
perl Makefile.PL
make && sudo make install
mkdir /tmp/stat_db1/
pgcluu_collectd -D -i 60 /tmp/stat_db1/ -d tpch
then came:
pgxl@localhost-> LOG: Detach from terminal with pid: 11778

after a while I tried the command:
pgcluu_collect -k
then came the error:
ERROR: can't find /tmp/pgcluu_collectd.pid, is pgcluu_collectd running?

Besides,there was nothing in the /tmp/stat_db1 except the sysinfo.txt

what's the problem?

The "Connection Type" report is enabled in the cluster menu, not in the databases menu. There may be a good reason why, but I can't find it. Otherwise, just enable it :)

Hi Darold,

Need you help, I'm unable execute collectd due to FATAL:psql error
pgcluu_collectd -i 10 /tmp/stat_db1/ -d tpch

*** pgcluu_collectd v2.3 (pid:5527) started at Thu Apr 2 14:11:23 2015
Type Ctrl+c to quit.

FATAL: psql error.

Checked all the queries from script which could lead to above error:

postgres=# SELECT version();

version

PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
(1 row)

postgres=# SELECT extname||'-'||extversion FROM pg_extension;

?column?

plpgsql-1.0
(1 row)

postgres=# SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn;

datname

postgres
rodb
cidb
tedb
ratdb
gerandb
trandb
acdb
(8 rows)

postgres=# SELECT nspname FROM pg_namespace WHERE nspname !~ '^pg_' AND nspname <> 'information_schema' ORDER BY 1;

nspname

public
(1 row)

postgres=# SELECT proname FROM pg_proc WHERE proname = 'pg_buffercache_pages';

proname

(0 rows)

postgres=# SELECT n.nspname||'.'||p.proname FROM pg_proc p, pg_namespace n WHERE p.proname='pg_stat_statements' AND p.pronamespace=n.oid;

?column?

(0 rows)

postgres=# SELECT n.nspname||'.'||p.proname FROM pg_proc p, pg_namespace n WHERE p.pronamespace=n.oid AND n.nspname NOT IN ('pg_catalog', 'information_schema');

?column?

(0 rows)

postgres=# SELECT count(tgname) FROM pg_trigger WHERE NOT tgisinternal;

count

 0

(1 row)

postgres=# SELECT setting FROM pg_settings WHERE name IN ('config_file','hba_file','ident_file','data_directory') ORDER BY name;

setting

/opt/ood/db/data/postgresql.conf
/opt/ood/db/data
/opt/ood/db/data/pg_hba.conf
/opt/ood/db/data/pg_ident.conf
(4 rows)

When I generate a report, the "Databases" menu in the resulting report has one entry for each of my databases: e.g. db1, db2, db3; but then it also has entries for fct-db1, fct-db2, fct-db3. There is no information for these fct- entries.

I think this is arising because the report contains files database-db1.html and also database-fct-db1.html, which contains the function stats. The code that generates the menu seems to be taking database-*.html.

Hello.
pgcluu_collectd --version
Version: 2.3
pgcluu --version
Version: 2.3

Collection statistic command is:
pgcluu_collectd -d database -h host -p5432 --rotate-daily -z -i 10 -S -T -C 1 -U user --password password --pgversion 9.4 /statistics/

Analyze command is:
pgcluu -d database -n 0 -o /reports/statistics/ -S -v /statistics/

In general everything works fine, but have some issues:

1. Some parts of report are empty, for example "background writer clean stats" or "background writer count stats" or "checkpoints counter stats"
   
   
   

I also use pgbadger version 7.0 and it shows me info about background writer and checkpoints, that's why I know, that statistic information about it is present and available.

2. I enable incremental reports. That is structure created by pgcluu:

statistics (pgcluu_collectd) (output dir is /statistics)

ls -1 /statistics/2015/07/28/
pg_class_size.csv
pg_database_size.csv
pg_db_role_setting.csv
pg_settings.csv
pg_stat_archiver.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_unused_indexes.csv
pg_stat_user_functions.csv
pg_stat_user_indexes.csv
pg_stat_user_tables.csv
pg_stat_xact_user_functions.csv
pg_stat_xact_user_tables.csv
sysinfo.txt

reports (pgcluu) (output dir is /reports/statistics)

ls -1 /reports/statistics/
2015/
bootstrap.min.css
bootstrap.min.js
font-awesome.min.css
jquery.min.js
pgcluu.css
pgcluu.js
sorttable.js

reports (pgcluu) (output dir is /reports/statistics) but for one day

ls -1 /reports/statistics/2015/07/28/
activebilling-index-size.html
activebilling-table-size.html
cluster.html
database-activebilling.html
database-all.html
index.html

But, it seems some index-file skipped in /reports/statistics directory. Web server is pointed to /reports/statistics/, where as I understand some skipped index-file generated by pgcluu must be. This file will show some calendar or list of all available reports. But now I have to enter full path manually like: http://my-server/statistics/2015/07/28
Sorry, if I'm wrong about this issue item.

I just downloaded the new 2.0 version. When i run pgcluu to generate report i got this error:

server ~ # su postgres -c "/usr/local/bin/pgcluu -o /home/xxx/stat_pg93/ --sadf=/usr/bin/sadf --timezone +1 --sar-file=/var/lib/postgresql/pgcluu_stat-9.3/sar_stats.dat --top-number=0 /var/lib/postgresql/pgcluu_stat-9.3/"
Can't call method "print" on an undefined value at /usr/local/bin/pgcluu line 3290.

I have perl 5.18.2:

server ~ # perl -V
Summary of my perl5 (revision 5 version 18 subversion 2) configuration:

Platform:
osname=linux, osvers=3.13.4-gentoo-06, archname=x86_64-linux
uname='linux server 3.13.4-gentoo-06 #1 smp sat feb 22 16:43:21 cet 2014 x86_64 intel(r) core(tm) i5-4670 cpu @ 3.40ghz genuineintel gnulinux '
config_args='-des -Duseshrplib -Darchname=x86_64-linux -Dcc=x86_64-pc-linux-gnu-gcc -Doptimize=-O3 -march=native -mtune=native -pipe -msse -msse2 -mmmx -ftree-vectorize -w -floop-block -floop-interchange -floop-strip-mine -Dldflags=-Wl,-O1,--sort-common,--warn-once,--hash-style=gnu,--as-needed -Dprefix=/usr -Dinstallprefix=/usr -Dsiteprefix=/usr/local -Dvendorprefix=/usr -Dscriptdir=/usr/bin -Dprivlib=/usr/lib64/perl5/5.18.2 -Darchlib=/usr/lib64/perl5/5.18.2/x86_64-linux -Dsitelib=/usr/local/lib64/perl5/5.18.2 -Dsitearch=/usr/local/lib64/perl5/5.18.2/x86_64-linux -Dvendorlib=/usr/lib64/perl5/vendor_perl/5.18.2 -Dvendorarch=/usr/lib64/perl5/vendor_perl/5.18.2/x86_64-linux -Dman1dir=/usr/share/man/man1 -Dman3dir=/usr/share/man/man3 -Dsiteman1dir=/usr/local/man/man1 -Dsiteman3dir=/usr/local/man/man3 -Dvendorman1dir=/usr/share/man/man1 -Dvendorman3dir=/usr/share/man/man3 -Dman1ext=1 -Dman3ext=3pm -Dlibperl=libperl.so.5.18.2 -Dlocincpth=/usr/include -Dglibpth=/lib64 /usr/lib64 -Duselargefiles -Dd_semctl_semun -Dcf_by=Gentoo -Dmyhostname=localhost -Dperladmin=root@localhost -Dinstallusrbinperl=n -Ud_csh -Uusenm -Di_ndbm -Di_gdbm -Di_db -DDEBUGGING=none -Dinc_version_list=5.18.0/x86_64-linux 5.18.0 5.18.1/x86_64-linux 5.18.1 -Dlibpth=/usr/local/lib64 /lib64 /usr/lib64 -Dnoextensions=ODBM_File'
hint=recommended, useposix=true, d_sigaction=define
useithreads=undef, usemultiplicity=undef
useperlio=define, d_sfio=undef, uselargefiles=define, usesocks=undef
use64bitint=define, use64bitall=define, uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='x86_64-pc-linux-gnu-gcc', ccflags ='-fno-strict-aliasing -pipe -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64',
optimize='-O3 -march=native -mtune=native -pipe -msse -msse2 -mmmx -ftree-vectorize -w -floop-block -floop-interchange -floop-strip-mine',
cppflags='-fno-strict-aliasing -pipe'
ccversion='', gccversion='4.8.2', gccosandvers=''
intsize=4, longsize=8, ptrsize=8, doublesize=8, byteorder=12345678
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16
ivtype='long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8
alignbytes=8, prototype=define
Linker and Libraries:
ld='x86_64-pc-linux-gnu-gcc', ldflags ='-Wl,-O1,--sort-common,--warn-once,--hash-style=gnu,--as-needed'
libpth=/usr/local/lib64 /lib64 /usr/lib64
libs=-lnsl -lgdbm -ldb -ldl -lm -lcrypt -lutil -lc -lgdbm_compat
perllibs=-lnsl -ldl -lm -lcrypt -lutil -lc
libc=/lib/libc-2.18.so, so=so, useshrplib=true, libperl=libperl.so.5.18.2
gnulibc_version='2.18'
Dynamic Linking:
dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E'
cccdlflags='-fPIC', lddlflags='-shared -O3 -march=native -mtune=native -pipe -msse -msse2 -mmmx -ftree-vectorize -w -floop-block -floop-interchange -floop-strip-mine -Wl,-O1,--sort-common,--warn-once,--hash-style=gnu,--as-needed'

Characteristics of this binary (from libperl):
Compile-time options: HAS_TIMES PERLIO_LAYERS PERL_DONT_CREATE_GVSV
PERL_HASH_FUNC_ONE_AT_A_TIME_HARD PERL_MALLOC_WRAP
PERL_PRESERVE_IVUV PERL_SAWAMPERSAND USE_64_BIT_ALL
USE_64_BIT_INT USE_LARGE_FILES USE_LOCALE
USE_LOCALE_COLLATE USE_LOCALE_CTYPE
USE_LOCALE_NUMERIC USE_PERLIO USE_PERL_ATOF
Locally applied patches:
gentoo/EUMM-RUNPATH - https://bugs.gentoo.org/105054 cpan/ExtUtils-MakeMaker: drop $PORTAGE_TMPDIR from LD_RUN_PATH
gentoo/EUMM_delete_packlist - Don't install .packlist or perllocal.pod for perl or vendor
gentoo/config_over - Remove -rpath and append LDFLAGS to lddlflags
gentoo/cpan_definstalldirs - Provide a sensible INSTALLDIRS default for modules installed from CPAN.
gentoo/cpanplus_definstalldirs - Configure CPANPLUS to use the site directories by default.
gentoo/create_libperl_soname - https://bugs.gentoo.org/286840 Set libperl soname
gentoo/drop_fstack_protector - https://bugs.gentoo.org/348557 Don't force -fstack-protector on everyone.
gentoo/enc2xs - Tweak enc2xs to follow symlinks and ignore missing @inc directories.
gentoo/mod_paths - Add /etc/perl to @inc
gentoo/patchlevel - List packaged patches for perl-5.18.2(#1) in patchlevel.h
gentoo/aix_soname - aix gcc detection and shared library soname support
gentoo/opensolars_headers - Add headers for opensolaris
gentoo/cleanup-paths - Cleanup PATH and shrpenv
gentoo/usr_local - Remove /usr/local paths
gentoo/hints_hpux - Fix hpux hints
gentoo/darwin-cc-ld - https://bugs.gentoo.org/297751 darwin: Use $CC to link
gentoo/interix - Fix interix hints
fixes/net_smtp_docs - [rt.cpan.org #36038] Document the Net::SMTP 'Port' option
debian/cpan-missing-site-dirs - Fix CPAN::FirstTime defaults with nonexisting site dirs if a parent is writable
fixes/memoize_storable_nstore - [rt.cpan.org #77790] Memoize::Storable: respect 'nstore' option not respected
fixes/net_ftp_failed_command - [rt.cpan.org #37700] Net::FTP: cope gracefully with a failed command
fixes/perlbug-patchlist - [3541c11] [perl #118433] Make perlbug look up the list of local patches at run time
fixes/module_metadata_taint_fix - [bff978f] [rt.cpan.org #88576] untaint version, if needed, in Module::Metadata
fixes/IPC-SysV-spelling - [rt.cpan.org #86736] Fix spelling of IPC_CREAT in IPC-SysV documentation
Built under linux
Compiled at Mar 24 2014 18:09:22
@inc:
/etc/perl
/usr/local/lib64/perl5/5.18.2/x86_64-linux
/usr/local/lib64/perl5/5.18.2
/usr/lib64/perl5/vendor_perl/5.18.2/x86_64-linux
/usr/lib64/perl5/vendor_perl/5.18.2
/usr/local/lib64/perl5
/usr/lib64/perl5/vendor_perl
/usr/lib64/perl5/5.18.2/x86_64-linux
/usr/lib64/perl5/5.18.2
.

I wanted to contact you about this before I spent a lot of time tweaking pgcluu to work on postgres-8.1. I got pgcluu-1.0 to work by changing "COPY ($sql) TO STDOUT" to use a temp table, and by adding a couple &backend_minimum_version() calls in places. But now that I updated to pgcluu-1.1 from Git, those patches are no longer sufficient.

So my question is, do you want a patch to make it work for 8.1 or am I fighting a losing battle?

If pg_basebackup has been running during pgcluu_collectd collect, lines has following are included in the pg_stat_replication.csv file :

2014-06-11 08:16:09+00;8406;10;postgres;pg_basebackup;10.240.76.238;"";46676;2014-06-11 07:57:06+00;backup;43/E76D3298;0/0;39/C0AB5710;;;0;async
2014-06-11 08:16:09+00;11889;10;postgres;pg_basebackup;10.240.76.238;"";51375;2014-06-11 07:57:32+00;streaming;43/E76D3298;43/E76D3298;43/E76D3178;;;0;async
2014-06-11 08:16:09+00;30893;10;postgres;walreceiver;10.240.61.239;"";29674;2014-06-09 10:30:29+00;streaming;43/E76D3298;43/E76D3298;43/E76D3298;43/E76D3298;43/E76D3268;0;async

The problem is that in pgcluu code, line 2823, the &getNumericalOffset($data[14]) function call as the &getNumericalOffset($data[13]) function call can fail in case of pg_basebackup because off null value. The test is only done on "streaming" string - if (uc($data[9]) eq 'STREAMING') - and should be done also on (uc($data[4]) eq 'WALRECEIVER') perhaps.

Best regards.
Emmanuel.

The has_pgstatstatement test succeeds because the search in pg_proc does not limit to the search_path.

When the view is in another schema because of create extension ... in schema ... and this schema is not in the search path, the collect query fails:

psql:<stdin>:27: ERROR:  relation "pg_stat_statements" does not exist
LINE 1: ...s_written, temp_blks_read, temp_blks_written FROM pg_stat_st...
                                                             ^

I have used pgcluu on commercial version of PostgreSQL from EnterpriseDB.
It does not present itself as PostgreSQL in "select version();" output, hence pgcluu does not recognise version correctly.

A fix is to add the alternative to the fetch_version subroutine :

--- a/pgcluu_collectd
+++ b/pgcluu_collectd
@@ -1227,7 +1227,7 @@ sub fetch_version
}
chomp($pg_ver);

•   if ($pg_ver =~ /^PostgreSQL (\d+).(\d+)/) {
  

•   if ($pg_ver =~ /^(PostgreSQL|EnterpriseDB) (\d+).(\d+)/) {
          $DB_INFO{major} = $1;
          $DB_INFO{minor} = $2;
  }
  

Regards,
Piotr

Hi,

When I run ./pgcluu_collectd -i 60 /tmp/dbstat -h 127.0.0.1 -U postgres -d dbname -p 5438
I have the above error:
*** pgcluu_collectd v1.0 (pid:11831) started at Fri Nov 29 13:35:41 2013
ERROR: /usr/bin/sar execution failure:

My system is CentOS 5.9 x64, but the same error I have under Ubuntu 13.10.

Units are correct when the graph is displayed. Once we try to zoom in, units are lost.

(yeah, same issue with pgbadger :) )

we are running pgcluu_collectd every 15 mintues with the following command pgcluu/pgcluu_collectd --compress --rotate-daily --daemonize --interval=900 --pgbouncer-args='-p 9999 -U admin -d pgbouncer' /mnt/backup/pgsql/stats/pgcluu

There is duplicated data for the hour for daylight savings.. went back one hour and created duplicate rows.

Longs device names usually found on systems abusing LVM do not display on one line with df. As a result, no values are shown in sysinfo page.

Hello,

Hi,
I installed and used the pgCluu as following:
tar xzf pgcluu-2.x.tar.gz
cd pgcluu-2.x/
perl Makefile.PL
make && sudo make install
mkdir /tmp/stat_db1/
pgcluu_collectd -D -i 60 /tmp/stat_db1/ -d dbname
then came:
pgxl@localhost-> LOG: Detach from terminal with pid: 23754

after a while I tried the command:
pgcluu_collectd -k
then came the error:
ERROR: can't find /tmp/pgcluu_collectd.pid, is pgcluu_collectd running?
Also, there was nothing in the /tmp/stat_db1 except the sysinfo.txt

When i run it without -D then it shows the message pg_cluu started but asks me postgres password many times. I entered the password 5 times and then terminated it.Now the data is present in the stat_db1 folder.

But with -D option I am not able to get the statistics.

Is there any option to provide password in command line itself that I am missing?
I read the command line options, but did not find any.

What am i missing?

The graph is generated based on data from pg_stat_database, and it treads the tup_fetched as SELECT operations. But according to the documentation (http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW) and discussions (http://dba.stackexchange.com/questions/34336/postgres-9-1-statistics-in-pg-stat-database) I think this is wrong.

I think that tup_returned more accurately reflects the amount of data returned from SELECT operations, and tup_fetched reflects the amount of data read from disk, which is not relevant to this graph. In my case, where the database easily fits in memory, I see these values:

tup_returned | 79995264
tup_fetched | 6916876
tup_inserted | 426957
tup_updated | 408431
tup_deleted | 197597

I think the SELECT percentage in the graph should be ~97% (returned)/(returned+inserted+updated+deleted).

Rather than "Returned" and "Fetched", we should probably put "tables" and "indexes". Much more easy to analyze.

Hi,

First of all, thanks for this lovely tool.

I am trying to generate graph using pgcluu with collected stats and got the error:
Can't call method "print" on an undefined value at /opt/pgdata/pgcluu_prod/pgcluubin/pgcluu line 5494

I tried with perl version v5.8.8 and v5.10.1 and in both the cases I faced the issue.

You now have overcommit_kbytes or overcommit_ratio. Only one will be used, so we need to have both values.

I generated the .csv with:

perl pgcluu_collectd `pwd`/report --no-sysinfo --disable-sar -U sensiolabs -h MYHOST --verbose -d postgres

BTW, --no-sysinfo is not enought, I add to add --disable-sar to make it work

The conflicts report menu is enabled on a master. It shouldn't, conflicts only happen on slaves.

During any run of pgcluu_collectd, on a fresh run crashes about a minute in, on a run with an existing stats dir crashes immediately. Error message is:

bash-4.1$ /var/lib/pgsql/pgcluu-master/pgcluu_collectd -S -r -i 60 /var/lib/pgsql/pgcluu_stats -U postgres -d postgres

*** pgcluu_collectd v2.4 (pid:5735) started at Tue Apr 5 15:54:27 2016
Type Ctrl+c to quit.

Can't locate object method "new" via package "IO::File" (perhaps you forgot to load "IO::File"?) at /var/lib/pgsql/pgcluu-master/pgcluu_collectd line 2563.
bash-4.1$

Adding "use IO::File;" to the top of pgcluu_collectd seems to fix the problem.

Replicated on Red Hat Enterprise Linux 6.7 and Centos 6.7.

Being able to tell pgcluu_collectd to run for some time (for example 24 hours) would be nice so that we do not have to remember to stop it.

Hello,

Greate app. Congratulations!
I'm running the collector and get the following result:

  ./pgcluu_collectd -i 60 /tmp/test -U postgres

Pgcluu_collectd *** v2.1 (pid: 1478) started at Tue Oct 21 15:04:44 2014
Type Ctrl + c to quit.

ERROR: sar execution failure:
psql: : 27: ERROR: "shared_blks_hit" column does not exist
LINE 1: ..., E ';', 'SMCLN # #', 'g'), calls, TOTAL_TIME, rows, shared_blk ...

Version: psql (PostgreSQL) 9.3.4

Any idea?

perl /var/lib/pgsql/pgcluu/pgcluu
--output=/var/lib/pgsql/pgcluu_output
/var/lib/pgsql/pgcluu_collectd_stats

Resulted in:
"print() on closed filehandle GEN9 at /var/lib/pgsql/pgcluu/pgcluu line 1942."

From what I could tell it seems to have most (all?) of the data I was expecting.

Line 1942:

sub html_footer
{
        my $fh = shift();

        print $fh qq{

      <hr>

      <footer>
        <p>&copy; Gilles Darold 2012-2013</p>
        <p>Report generated by <a href="http://pgcluu.darold.net/">$PROGRAM</a> $VERSION.</p>
      </footer>

    </div><!--/.container-->
  </body>
</html>
};

}

Hello.
Today pooled latest pgcluu from github. collectd works normal, but pgcluu failed with error:
Invalid offset: 0 at /usr/local/bin/pgcluu line 8398, line 1.

Full flow looks:
/usr/local/bin/pgcluu_collectd -d database -h x.x.x.x -pxxxx --rotate-daily -z -i 10 -S -T -C 1 -U user --password password --pgversion x.x /statistics/ -v
*** pgcluu_collectd v2.3 (pid:31914) started at Thu Jun 18 13:39:11 2015
Type Ctrl+c to quit.
LOG: counter reached, terminating.

Then:
/usr/local/bin/pgcluu -d database -n 0 -o /reports/statistics/ -S /statistics/ -v
DEBUG: Cleaning memory from PostgreSQL statistics storages
DEBUG: Cleaning memory from all statistics storages
DEBUG: Loading system information from file /statistics/2015/06/18/sysinfo.txt
DEBUG: looking for CSV file into directory /statistics/2015/06/18/
DEBUG: autodetected interval value 618
DEBUG: Building PostgreSQL statistics from CSV files
DEBUG: reading statistics in file /statistics/2015/06/18/pg_class_size.csv
DEBUG: reading statistics in file /statistics/2015/06/18/pg_database_size.csv
DEBUG: reading statistics in file /statistics/2015/06/18/pg_settings.csv
DEBUG: reading statistics in file /statistics/2015/06/18/pg_stat_user_indexes.csv
DEBUG: reading statistics in file /statistics/2015/06/18/pg_stat_user_tables.csv
DEBUG: reading statistics in file /statistics/2015/06/18/pg_stat_bgwriter.csv
DEBUG: reading statistics in file /statistics/2015/06/18/pg_stat_connections.csv
DEBUG: reading statistics in file /statistics/2015/06/18/pg_stat_database.csv
DEBUG: reading statistics in file /statistics/2015/06/18/pg_stat_database_conflicts.csv
DEBUG: reading statistics in file /statistics/2015/06/18/pg_stat_locks.csv
DEBUG: reading statistics in file /statistics/2015/06/18/pg_stat_redundant_indexes.csv
DEBUG: reading statistics in file /statistics/2015/06/18/pg_stat_replication.csv

Invalid offset: 0 at /usr/local/bin/pgcluu line 8398, line 1.

I tried the 'sar report only' but apparently it is broken:

$ sar -p -A 5 10 > /tmp/my_sar_file.txt
$ ./pgcluu -o /tmp/report_sar/ -i /tmp/my_sar_file.txt
FATAL: Can't open directory : No such file or directory

It seems that it broke after 'daily rotation' feature.

When I run pgcluu_collectd on our system, the pg_stat_user_functions.csv data file contains data about several different functions. When this is run through pgcluu, the call counts on the database-functions page are completely wrong, including negative counts for some functions.

The code in pg_stat_user_functions() seems to assume that there is data on only one function - it subtracts call counts from one row from the next, irrespective of which function the data relates to.

I've modified pgcluu as follows , which seems to work for me:

diff --git a/pgcluu b/pgcluu
index b4b2c82..2a34cd2 100755
--- a/pgcluu
+++ b/pgcluu
@@ -3432,6 +3432,7 @@ sub pg_stat_user_functions
 {
        my ($in_dir, $file) = @_;

+       my %save_start_vals = ();
        my @start_vals = ();
        my $tmp_val = 0;
        # Load data from file
@@ -3441,10 +3442,11 @@ sub pg_stat_user_functions
                next if (!&normalize_line(\@data));

                # timestamp | datname | funcid | schemaname | funcname | calls | total_time | self_ti
-               push(@start_vals, @data) if ($#start_vals < 0);
-
                $data[4] = "$data[3].$data[4]";

+               $save_start_vals{$data[4]} = [@data] if not exists $save_start_vals{$data[4]};
+               @start_vals = @{$save_start_vals{$data[4]}};
+
                (($data[5] - $start_vals[5]) < 0) ? $tmp_val = 0 : $tmp_val = ($data[5] - $start_vals
                $all_stat_user_functions{$data[1]}{$data[4]}{calls} += $tmp_val;
                (($data[6] - $start_vals[6]) < 0) ? $tmp_val = 0 : $tmp_val = ($data[6] - $start_vals
@@ -3452,8 +3454,7 @@ sub pg_stat_user_functions
                (($data[7] - $start_vals[7]) < 0) ? $tmp_val = 0 : $tmp_val = ($data[7] - $start_vals
                $all_stat_user_functions{$data[1]}{$data[4]}{self_time} += $tmp_val;

-               @start_vals = ();
-               push(@start_vals, @data);
+               $save_start_vals{$data[4]} = [@data];
        }
        $curfh->close();
 }

pgcluu is a great tool!

Steve Pritchard

Hello,

I'm doing some tests with your tool, and for now, I have a small problem: I try to get only stats for one database in my server, but I always get stats for all DB. I tried:

pgcluu -o /tmp/report_mathon_143/ /tmp/stat_mathon_dev/ --db-only mathon_143

and

pgcluu_collectd -D -i 10 /tmp/stat_mathon_dev/ --dbname mathon_143

Thkx

Hi,

I used --enable-ssh and it started taking sar data from remote host. But it is collecting sysinfo from the local host.

Regards.

Which is kinda weird. The other config files are present but not the recovery.conf one. Probably pgcluu_collected should grab it too (on slaves of course).

poisonivy@poppy ➤➤ ~ % sudo /usr/bin/pgcluu_collectd -i 240 /storage/psqlStat
[sudo] password for poisonivy:

*** pgcluu_collectd v2.4 (pid:18549) started at Fri Jan 1 23:35:14 2016
Type Ctrl+c to quit.

Password:
FATAL: psql error. psql: FATAL: password authentication failed for user "root"

password is indeed correct. Why am I asked a second time for the root password?
Using fresh build from git, and cant remember such issue before.

command to generate stats

time pgcluu --output=/data/pgcluu_output --all-table /data/pgcluu_collectd_stats

Output

Use of uninitialized value $val in substitution (s///) at /usr/local/bin/pgcluu line 3270, line 183.
Use of uninitialized value $val in substitution (s///) at /usr/local/bin/pgcluu line 3270, line 185.
Use of uninitialized value $val in substitution (s///) at /usr/local/bin/pgcluu line 3270, line 186.
Use of uninitialized value in concatenation (.) or string at /usr/local/bin/pgcluu line 1825.
Use of uninitialized value in concatenation (.) or string at /usr/local/bin/pgcluu line 1825.

There are definitely results that get generated, so this seems to be a pretty minor issue.

I have a 60MB tarball of gzipped src files (sar, pg_xxx) that I can send as well, though I'm not sure if I'll be able to attach it here or if gmail will allow a file of that size to be sent. Let me know if you need them and we can figure out a way to transfer the tar archive.

Hi Darold,

Thanks for this beautiful tool.

I have an issue using metric option to collect statistics. I'm interested to run certain statistics at a time. However, I see pgcluu_collectd is collecting all the information. Probably I am missing some thing here.

Using below, I am expecting only database_usagecount to be collected. Am I wrong here?
[postgres@localhost pgcluu]$ pgcluu_collectd -m database_usagecount -d postgres -i 5 -P /opt/PostgreSQL/9.2/bin/psql /tmp/pgcluu.

The above command generates many files under the destination directory and most of the files have information collected.

The other request I have is to try collecting batch of metrics together with --metric option. However these did not work with an error.

[postgres@localhost pgcluu]$ pgcluu_collectd -B --metric=database_usagecount,lock_granted,lock_modes,lock_types,statements -d postgres -D -i 5 -P /opt/PostgreSQL/9.2/bin/ /tmp/pgcluu
FATAL: metric database_usagecount,lock_granted,lock_modes,lock_types,statements does not exist. Use --list-metric to show the available metrics.

[postgres@localhost pgcluu]$ pgcluu_collectd --list-metric
List of available metrics:

archiver
bgwriter
class_size
conflict
connections
database_buffercache
database_isdirty
database_size
database
database_usagecount
functions
lock_granted
lock_modes
lock_types

pgcluu_collectd -B --metric=database_usagecount,lock_granted,lock_modes,lock_types,statements -d postgres -D -i 5 -P /opt/PostgreSQL/9.2/bin/ /tmp/pgcluu
pgcluu_collectd -B --metric='database_usagecount,lock_granted,lock_modes,lock_types,statements' -d postgres -D -i 5 -P /opt/PostgreSQL/9.2/bin/psql /tmp/pgcluu
pgcluu_collectd -B --metric="database_usagecount, lock_granted, lock_modes, lock_types, statements" -d postgres -D -i 5 -P /opt/PostgreSQL/9.2/bin/psql /tmp/pgcluu
pgcluu_collectd --metric='database_usagecount','archiver' -d postgres -i 5 -P /opt/PostgreSQL/9.2/bin/psql /tmp/pgcluu
pgcluu_collectd --metric="database_usagecount","archiver" -d postgres -i 5 -P /opt/PostgreSQL/9.2/bin/psql /tmp/pgcluu

Below command seems to work but collecting all other statistics too.
pgcluu_collectd -m bgwriter -m archiver -m class_size -m conflict -m lock_granted -m lock_modes -m lock_types -d postgres -D -i 5 -P /opt/PostgreSQL/9.2/bin/psql /tmp/pgcluu

[postgres@localhost pgcluu]$ cat /proc/version
Linux version 2.6.32-431.el6.x86_64 ([email protected]) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-4) (GCC) ) #1 SMP Fri Nov 22 03:15:09 UTC 2013
[postgres@localhost pgcluu]$ cat /etc/redhat-release
CentOS release 6.5 (Final)

Kumar.

Hi,

I am running pgcluu -o /var/lib/pgsql/reports /var/lib/pgsql/stats and receive Use of uninitialized value in concatenation (.) or string at /usr/local/bin/pgcluu line 4414.

Any thoughts on where I should look to resolve this issue?

Joe

Hello.
I use latest pgcluu but I have a problem similar to #47: creating report failed with error

pgcluu -v -o /pgdb/report/ /pgdb/stat/
DEBUG: Cleaning memory from PostgreSQL statistics storages
DEBUG: Cleaning memory from all statistics storages
DEBUG: Loading system information from file /pgdb/stat/./sysinfo.txt
DEBUG: looking for CSV file into directory /pgdb/stat/./
DEBUG: autodetected interval value 58
DEBUG: Building PostgreSQL statistics from CSV files
DEBUG: reading statistics in file /pgdb/stat/./pg_class_size.csv
DEBUG: reading statistics in file /pgdb/stat/./pg_database_size.csv
DEBUG: reading statistics in file /pgdb/stat/./pg_hba.conf
DEBUG: reading statistics in file /pgdb/stat/./pg_ident.conf
DEBUG: reading statistics in file /pgdb/stat/./pg_settings.csv
DEBUG: reading statistics in file /pgdb/stat/./pg_stat_user_indexes.csv
DEBUG: reading statistics in file /pgdb/stat/./pg_stat_user_tables.csv
DEBUG: reading statistics in file /pgdb/stat/./pg_stat_bgwriter.csv
DEBUG: reading statistics in file /pgdb/stat/./pg_stat_connections.csv
DEBUG: reading statistics in file /pgdb/stat/./pg_stat_database.csv
DEBUG: reading statistics in file /pgdb/stat/./pg_stat_database_conflicts.csv
DEBUG: reading statistics in file /pgdb/stat/./pg_stat_locks.csv
DEBUG: reading statistics in file /pgdb/stat/./pg_stat_missing_fkindexes.csv
DEBUG: reading statistics in file /pgdb/stat/./pg_stat_redundant_indexes.csv
DEBUG: reading statistics in file /pgdb/stat/./pg_stat_replication.csv
Invalid offset: 0 at /usr/local/bin/pgcluu line 8479, line 1.

Several strings from pg_stat_replication.csv:
2015-08-11 09:34:03+03;19541;10;postgres;pg_receivexlog;127.0.0.1;;33828;2015-08-03 08:58:24+03;streaming;36/2068C330;36/2068C330;36/2068A120;;;0;async
2015-08-11 09:34:03+03;19539;10;postgres;walreceiver;192.168.1.51;;45010;2015-08-03 08:58:24+03;streaming;36/2068C330;36/2068C330;36/2068C330;36/2068C330;36/2068C330;0;async
2015-08-11 09:35:03+03;19541;10;postgres;pg_receivexlog;127.0.0.1;;33828;2015-08-03 08:58:24+03;streaming;36/2068E730;36/2068E730;36/2068E730;;;0;async
2015-08-11 09:35:03+03;19539;10;postgres;walreceiver;192.168.1.51;;45010;2015-08-03 08:58:24+03;streaming;36/2068E730;36/2068E730;36/2068E730;36/2068E730;36/2068E730;0;async
2015-08-11 09:36:03+03;19541;10;postgres;pg_receivexlog;127.0.0.1;;33828;2015-08-03 08:58:24+03;streaming;36/20695D58;36/20695D58;36/20693CE0;;;0;async
2015-08-11 09:36:03+03;19539;10;postgres;walreceiver;192.168.1.51;;45010;2015-08-03 08:58:24+03;streaming;36/20695D58;36/20695D58;36/20695D58;36/20695D58;36/20695D58;0;async
2015-08-11 09:37:03+03;19541;10;postgres;pg_receivexlog;127.0.0.1;;33828;2015-08-03 08:58:24+03;streaming;36/206B6B10;36/206B6B10;36/206B6B10;;;0;async
2015-08-11 09:37:03+03;19539;10;postgres;walreceiver;192.168.1.51;;45010;2015-08-03 08:58:24+03;streaming;36/206B6B10;36/206B6B10;36/206B6B10;36/206B6B10;36/206B6B10;0;async
2015-08-11 09:38:03+03;19541;10;postgres;pg_receivexlog;127.0.0.1;;33828;2015-08-03 08:58:24+03;streaming;36/20754CB8;36/20754CB8;36/20754CB8;;;0;async
2015-08-11 09:38:03+03;19539;10;postgres;walreceiver;192.168.1.51;;45010;2015-08-03 08:58:24+03;streaming;36/20754CB8;36/20754CB8;36/20754CB8;36/20754CB8;36/20754CB8;0;async

Thank you.

pgcluu_collectd needs to be fixed to get statistics from an 8.4 cluster. (it tries to access not-available-yet statistics catalogs)

On the "connection by type" graph, there is two wrong labels:

• Waiting should be Idle
• Waiting in xact should be Idle in xact

And a fourth label could be "Waiting for a lock"... that would add a great intel to this graph.

On tables and indexes "Size and tuples" reports, there is a type column. It's always table on the tables report, and index on the indexes report. Kinda useless. Let's get more space by dropping it.

postgres@poppy ➤➤ ~ % /usr/bin/pgcluu_collectd --end-after=300 /storage/psqlStat/

*** pgcluu_collectd v2.4 (pid:24227) started at Sat Jan  2 18:48:26 2016
Type Ctrl+c to quit.

Password:

Entered postgres password.

poisonivy@poppy ➤➤ systemd/system % date
Sat Jan  2 18:54:27 CET 2016
poisonivy@poppy ➤➤ systemd/system % myps pgcluu
47:postgres 24227 24010  0 18:48 pts/1    00:00:00 pgcluu_collectd

pgcluu_collectd is still running, Is it not supposed to stop after 300 sec ?

postgres@poppy ➤➤ ~ % ls -al /storage/psqlStat                                   
total 0
drwxrwxr-x 1 postgres postgres   0 Jan  2 18:48 ./
drwxrwxr-x 1 root     wheel    194 Jan  2 11:42 ../

Why is the directory empty? NOTE: my postgresql DB is not writing anything and do not see activity, but the service is running. Is it the reason?

Hi,
Can we generate dynamic report using pgcluu.....?
So that we get current statistics from my server as soon as it is generated.

Any plans to release packages for Debian / RHEL?
This would remove the need to install make etc... and make upgrades easier.

https://github.com/darold/pgcluu/blob/master/pgcluu#L2702 is obviously wrong.

$all_connections_stat{$data[0]}{'all'}{idle_in_xact} += ($data[1] - $data[2] - $data[4]);
should be:
$all_connections_stat{$data[0]}{'all'}{idle} += ($data[1] - $data[2] - $data[4]);

Thanks.

Is there a way I can set pgcluu to adjust the localtime output for my timezone? It seems the stats being dumped are using UTC time. I would like to configure my system to adjust the time in relation to my timezone if possible.