Monitoring in PostgreSQL

Database Monitoring:

1. Database monitoring consists of capturing and recording the database events.
2. This information helps DBA to detect, identify and fix potential database performance issues.
3. Database monitoring statistics makes it easy for DBA’s to monitor the health and performance of PostgreSQL Database. 
4. Several tools are available for monitoring database activity and analyzing performance.

Database Statistics:

• DB statistics catalog tables store DB activity information like

1. Current running sessions
2. Running SQL
3. Locks
4. DML Counts
5. Row Counts
6. Index Usage

• Stat collector collects all the above stats and update pg_catalog(statistics table)
• Pg_catalog is a schema which contains all the statistics table

CATALOG TABLE - Which needs to be updated with analyze(pg_class, pg_catalog)

SYSTEM VIEWS - Which automatically gets updated in occurrence to an event(pg_stat_activity)

The statistics Collector:

• Stats collector is a process that collect and report information about database activities.
• Stats collector  adds some overhead to query execution.
• Stats Parameter:

1. Track counts - controls table and index statistics
2. Track activities - enables monitoring of the current command.

• The stats collector use temp files stored in pg_stat_tmp subdirectory.
• Permanent statistics are stored in pg_catalog schema in global subdirectory
• Performance TIP: Parameter stats_temp_directory can be pointed at a RAM based file System.

Database Statistics Table:

1. Pg_catalog schema contains a set of table,view and function which store and report database statistics.
2. Pg_class and pg_stats catalog tables stores all statistics. They are two main statistics table. It stores index and table statistics.
3. Pg_stat_database view can be used to view stats information about a database
4. Pg_stat_bgwriter shows background writer steps
5. Pg_stat_user_tables shows information about activities on a table like inserts, updates, deletes, vacuum, autovacuum etc
6. Pg_stat_user_indexes shows information about index usage for all user table. 
7. \dS - shows view and statistics table in a server. 
8. Select * from pg_stat_database
9. Pg_stat_% are all views

Pg_Stat_Database:

postgres=# \x

Expanded display is on.

postgres=# select * from pg_stat_database where datname='postgres';

-[ RECORD 1 ]--+---------------------------------

datid          | 12896            --- Database OID

datname        | postgres    --- Database Name

numbackends    | 1            --- Number of connection at this time

xact_commit    | 16791      --- How many connections are committed

xact_rollback  | 2               --- How many connections are rollbacked

blks_read      | 187288      --- How many bulk reads happened

blks_hit       | 273549         --- How many bulks blocks are hit

tup_returned   | 7688150   --- How many tuples were returned

tup_fetched    | 93446        --- How many tuples were fetched

tup_inserted   | 0                --- How many tuples were inserted

tup_updated    | 0               --- How many tuples were updates

tup_deleted    | 0                --- How many tuples were deleted

conflicts      | 0                    ---   

temp_files     | 0

temp_bytes     | 0

deadlocks      | 0

blk_read_time  | 0

blk_write_time | 0

stats_reset    | 2016-10-07 12:29:11.117365+05:30  -- When statistics were reset

Pg_stat_bgwriter:

postgres=# select * from pg_stat_bgwriter;

-[ RECORD 1 ]---------+---------------------------------

checkpoints_timed     | 96              -- How many checkpoint were timed/occurred

checkpoints_req       | 11394         -- How many checkpoint were actually required

checkpoint_write_time | 210405805  -- 

checkpoint_sync_time  | 8204341

buffers_checkpoint    | 49477813   -- How many buffers were checkpoint

buffers_clean         | 234507961    -- How many buffers were cleaned

maxwritten_clean      | 1296814     -- How many maximum written clean buffer

buffers_backend       | 1768849795 -- How many buffers at backend 

buffers_backend_fsync | 0              -- How many buffers used by fsync

buffers_alloc         | 2155806772     -- Buffer allocation

stats_reset           | 2016-10-07 12:29:04.686137+05:30  -- Stats were reset on

When checkpoint_req is more than checkpoint_times, there is an issue. Because there are too many transaction. Checkpoint are happening because checkpoint_segment is getting full.

SOLUTION: Increase the shared_buffer, Increase checkpoint_segment.

Pg_stat_user_tables:

postgres=# Select * from pg_stat_user_tables where relname='a';

-[ RECORD 1 ]-----+-----------

relid             | 2338166798

schemaname        | public   -- Schemaname

relname           | a                -- Table Name

seq_scan          | 0               -- How many seq_scan

seq_tup_read      | 0            -- How many tuples were read sequentially

idx_scan          |                   -- How many index scan

idx_tup_fetch     |                 -- How many tuples returned using index

n_tup_ins         | 0                -- How many tuples inserted

n_tup_upd         | 0              -- How many tuples updated

n_tup_del         | 0               -- How many tuples deleted

n_tup_hot_upd     | 0           -- 

n_live_tup        | 0

n_dead_tup        | 0              -- How many tuples were dead

last_vacuum       |                 -- When the table was last vacuum

last_autovacuum   |              -- when the table was last auto vacuum 

last_analyze      |                  -- when the table was last analyzed

last_autoanalyze  |               -- When the table was last auto analyzed

vacuum_count      | 0            -- How many vacuum was done

autovacuum_count  | 0         -- How many autovacuum was done

analyze_count     | 0             -- How many analyze was done

autoanalyze_count | 0          -- How many auto analyze was done.

Autoanalyze: Autovacuum daemon does auto analyze. But if table is rarely inserted, then have to analyze manually. If the table which is being analyzed have parent and child table. It will analyze parent table and then parent and child table. 

Sequential scan should be less than index scan. 

IMPORTANTS VIEWS:

Below are some important views which are very much essential for a DBA.

pg_stat_activity
pg_stat_bgwriter
pg_stat_database
pg_stat_all_tables
pg_stat_sys_tables
pg_stat_user_tables
pg_stat_xact_all_tables
pg_stat_xact_sys_tables
pg_stat_xact_user_tables
pg_stat_all_indexes
pg_stat_sys_indexes
pg_stat_user_indexes
pg_statio_all_tables
pg_statio_sys_tables
pg_statio_user_tables
pg_statio_all_indexes
pg_statio_sys_indexes
pg_statio_user_indexes
pg_statio_all_sequences
pg_statio_sys_sequences
pg_statio_user_sequences
pg_stat_user_functions
pg_stat_xact_user_functions
pg_stat_replication
Pg_stat_database_conflicts

Pg_stat_activity:

postgres=# select * from pg_stat_activity;

-[ RECORD 1 ]----+---------------------------------------------------------------------------------------------------------------------------------------------
datid         | 16384  -- DB ID
datname       | ewcloud  -- DB Name
pid           | 32438     -- Process ID matches with pid of ps -ef | grep post
usesysid      | 32217507
usename       | t_a3efe696b07a44f9ae84ef5b5f096e0c  -- schema Name
application_name | Cisco Energy Management Server Processor Message Queue reportin
client_addr   | 172.30.2.64
client_hostname  |
client_port   | 61742
backend_start | 2016-10-07 12:29:05.183022+05:30  -- Connection started
xact_start    |
query_start   | 2016-10-23 00:30:32.395862+05:30  -- Query start time
state_change  | 2016-10-23 00:30:32.396797+05:30
waiting       | f  -- Query is in waiting state or not
state         | idle
query         | COMMIT