pgBadger - performance analyzer

pgBadger

pgBadger is a PostgreSQL performance analyzer, built for speed with fully detailed reports based on your PostgreSQL log files. ... In report "Tuples removed per table" additional autovacuum information are tuples remaining, tuples not yet removable and pages remaining.

=====================PGBADGER=======================

cd /var/

mkdir pgdata

initdb -D pgdata

cd pgdata

vi postgresq.conf

port=5001

log_destination = 'stderr'      

logging_collector = on      

log_rotation_age = 0            

client_min_messages = notice

log_min_messages = warning

log_min_error_statement = error

log_min_duration_statement = 0

log_checkpoints = on

log_connections = on

log_disconnections = on

log_duration = on

log_error_verbosity = verbose       

log_hostname = on

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '          

log_lock_waits = on         

log_statement = 'all'       

log_temp_files = 0      

:wq

pg_ctl -D pgdata restart

https://github.com/darold/pgbadger

[root@postgres Desktop]# cp pgbadger-master.zip /opt/

[root@postgres Desktop]# unzip pgbadger-master.zip

[root@postgres Desktop]# su - postgres

-bash-4.1$ psql -p 5001

psql.bin (10.9)

Type "help" for help.

postgres=# create user u01 password '123456';

CREATE ROLE

postgres=# create user hr password '123456';

CREATE ROLE

postgres=# create user admin password '123456';

CREATE ROLE

postgres=# create database db1;

CREATE DATABASE

postgres=# create database db2;

CREATE DATABASE

postgres=# create database db3;

CREATE DATABASE

postgres=# \c db1 u01

You are now connected to database "db1" as user "u01".

db1=> create table tbl1 as select * from pg_class;

SELECT 341

db1=> select count(*) from tbl1;

 count 

-------

   341

(1 row)

db1=> \c db2 hr

You are now connected to database "db2" as user "hr".

db2=> create table tbl1 (id int);

CREATE TABLE

db2=> insert into tbl1 select * from generate_Series(1,100000) order by random();

INSERT 0 100000

db2=> update tbl1 set id=1 where id between 1 and 100;

UPDATE 100

db2=> 

db2=> 

db2=> \db3 admin

   List of tablespaces

 Name | Owner | Location 

------+-------+----------

(0 rows)

db2=> \c db3 admin

You are now connected to database "db3" as user "admin".

db3=> 

db3=> 

db3=> create table tbl1 (id int,name varchar);

CREATE TABLE

db3=> 

db3=> 

db3=> insert into tbl1 values(generate_Series(1,100000), 'postgres');

INSERT 0 100000

db3=> 

db3=> 

db3=> delete from tbl1 where id between 1 and 6000;

DELETE 6000

db3=> 

db3=> 

db3=> select count(*) from tbl1;

 count 

-------

 94000

(1 row)

db3=> 

db3=> explain analyze select * from tbl1;

                                                QUERY PLAN                                                 

-------------------------------------------------------------------------------------------

 Seq Scan on tbl1  (cost=0.00..1228.07 rows=68707 width=36) (actual time=0.128..25.410 rows=94000 loops=1)

 Planning time: 0.057 ms

 Execution time: 39.410 ms

(3 rows)

db3=> \q

=========generating pgbadger report===================

[root@postgres Desktop]# cd /opt/

[root@postgres opt]# ls

[root@postgres opt]# unzip pgbadger-master.zip 

[root@postgres opt]# cd /var/pgdata/log/

[root@postgres log]# ls -l

total 20

-rw-------. 1 postgres postgres 17028 Aug  7 12:34 postgresql-2019-08-07_122642.log

[root@postgres log]# cd /opt/pgbadger-master

[root@postgres pgbadger-master]# ls

ChangeLog  CONTRIBUTING.md  doc  LICENSE  Makefile.PL  MANIFEST  META.yml  pgbadger  README  tools

[root@postgres pgbadger-master]# ./pgbadger -f stderr -o report.html /var/pgdata/log/postgresql-2019-08-07_122642.log

[========================>] Parsed 17028 bytes of 17028 (100.00%), queries: 36, events: 1

LOG: Ok, generating html report...

[root@postgres pgbadger-master]# ls

ChangeLog  CONTRIBUTING.md  doc  LICENSE  Makefile.PL  MANIFEST  META.yml  pgbadger  README  report.html  tools

[root@postgres pgbadger-master]# cp report.html /root/Desktop/