New Question
 
 
PRTG Network Monitor

Intuitive to Use.
Easy to manage.

200.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free PRTG
Download >>

 

What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

 

Top Tags


View all Tags


How do a monitor PostgreSQL database performance

Votes:

0

Your Vote:

Up

Down

How do a monitor PostgreSQL database performance using the PostgreSQL sensor.

Note Verify you have enabled PostgreSQL's statistics collector on your database server!

postgresql.conf

track_activities = on
track_counts = on
track_io_timing = off
track_functions = none
track_activity_query_size = 1024

mor information on enabling the stat collection is available at the following URL: https://www.postgresql.org/docs/current/static/monitoring-stats.html

1. Create your SQL file

C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\postgresql\pg_stat_database.sql
-- pulls statistics for database passed via PRTG parameter
-- SELECT * FROM pg_stat_database WHERE datname = 'DB' ;
-- datid | datname | numbackends | xact_commit | xact_rollback | blks_read  |   blks_hit    |  tup_returned   | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes  | deadlocks | blk_read_time | blk_write_time |          stats_reset
---------+---------+-------------+-------------+---------------+------------+---------------+-----------------+-------------+--------------+-------------+-------------+-----------+------------+-------------+-----------+---------------+----------------+-------------------------------
-- 16384 | DB      |          11 |  4383088207 |     489151237 | 2567416980 | 4052249441946 | 191586241749080 |   262087158 |   2136300959 |      236563 |       71018 |         0 |        168 | 11429036032 |         0 |             0 |              0 | 2016-05-05 20:31:38.397496+00
SELECT * FROM pg_stat_database WHERE datname = @prtg

2. Restart the PRTG service so that you can choose your new custom sql code

3. Choose the parameters you would like to monitor and create your sensor

BASIC SENSOR SETTINGS

Sensor Namepg_stat_database

DATABASE SPECIFIC

Databasepostgres

DATA

SQL Query Filepg_stat_database.sql
Use Input ParameterUse input parameter
Input ParameterCHANGE_ME_DB
Data ProcessingProcess data table
Select Channel Value byColumn name
Sensor Channel #1 Nametup_inserted
Sensor Channel #1 Column Nametup_inserted
Sensor Channel #1 ModeDifference
Sensor Channel #1 UnitCount
Sensor Channel #2 Namenumbackends
Sensor Channel #2 Column Namenumbackends
Sensor Channel #2 ModeAbsolute (recommended)
Sensor Channel #2 UnitCount

SENSOR DISPLAY

Primary Channeltup_inserted (#/s)

Further

I choose to monitor the number of inserts, you can add or modify the "Sensor Channel" to watch any value returned by pg_stat_database.

A description of the stats available from pg_stat_database is available from the following URL https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW

performance postgres postgresql sql

Created on Oct 31, 2016 5:55:03 PM by  infotek (2) 1

Last change on Oct 31, 2016 6:19:18 PM by  Stephan Linke [Paessler Support]



1 Reply

Votes:

0

Your Vote:

Up

Down

You're on fire, thanks!

Created on Oct 31, 2016 6:19:40 PM by  Stephan Linke [Paessler Support]



Please log in or register to enter your reply.


Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.