Types of Alerts in Postgres Enterprise Manager: 2.1.

Its being two months, that I didn't blog on any topic. So, I thought to share some information on PEM.

This post is for users, who always look for type of inbuild alerts of PEM. In this Blog, I am listing categories of Alerts and List of alerts in each category, which one can find in Postgres Enterprise Manager 2.1.
Also, if you have list of alerts then you can decide which alert you would like to configure.

Postgres Enterprise Manager (PEM) is very useful tool for monitoring PostgreSQL and it has all the alerts which is require for monitoring postgresql. User can define this alerts in following categories:

1. Server Level Alerts.
2. PG Cluster Level Alerts.
3. Database Level Alerts.
4. Schema Level Alerts
5. Table Level Alerts.

All the alerts which has been made/defined keeping in mind of postgreSQL. Let see what are the alerts in each categories:
1. Server Level Alerts: These alerts are made for monitoring Server Components like Memory,Disk and CPU and Server Level monitoring of PostgreSQL:

 1. Average table bloat on host (Bloats impact CPU and Disk)
 2. CPU utilization
 3. Database size on host
 4. Disk Available
 5. Disk busy percentage
 6. Disk Consumption
 7. Disk consumption percentage
 8. Free memory percentage
 9. Highest table bloat on host 
 10. Load Average (15 minutes)
 11. Load Average (1 minute)
 12. Load Average (5 minutes)
 13. Load Average per CPU Core (15 minutes)
 14. Load Average per CPU Core (1 minutes)
 15. Load Average per CPU Core (5 minutes)
 16. Memory used percentage
 17. Most used disk percentage
 18. Number of CPUs running higher than a threshold
 19. Swap consumption
 20. Swap consumption percentage
 21. Table size on host
 22. Total table bloat on host

User can see above alerts covered server Monitoring which includes: Memory,Disk,CPU and Bloats & Biggest table on host.


2. PG Cluster Level Alert: These alerts are made for Monitoring PostgreSQL, Cluster Level. i.e. Number of Connections, Database Level Stats, User monitoring etc… Following is list of PG Cluster Level alerts:

 1. A user expires in N days
 2. Average table bloat in server
 3. Buffers allocated per second
 4. Buffers written per second
 5. Committed transactions percentage
 6. Connections in idle-in-transaction state
 7. Connections in idle-in-transaction state, as a percentage of max_connections
 8. Connections in idle state
 9. Database size in server
 10. Dead Tuples
 11. Dead tuples percentage
 12. Function Count
 13. Highest table bloat in server
 14. Hot update percentage
 15. Index Scans
 16. Index size as a percentage of table size
 17. InfiniteCache buffers hit percentage
 18. Largest index by table-size percentage
 19. Largest table (by multiple of unbloated size)
 20. Last Analyze
 21. Last AutoAnalyze
 22. Last AutoVacuum
 23. Last Vacuum
 24. Live Tuples
 25. Long-running autovacuums
 26. Long-running idle connections
 27. Long-running idle connections and idle transactions
 28. Long-running idle transactions
 29. Long-running queries
 30. Long-running transactions
 31. Long-running vacuums
 32. Number of prepared transactions
 33. Number of WAL files
 34. Percentage of buffers written by backends
 35. Percentage of buffers written by backends over last N minutes
 36. Percentage of buffers written by checkpoint
 37. Sequence Count
 38. Sequential Scans
 39. Shared buffers hit percentage
 40. Table Count
 41. Table size in server
 42. Total connections
 43. Total connections as percentage of max_connections
 44. Total table bloat in server
 45. Tuples deleted
 46. Tuples fetched
 47. Tuples hot updated
 48. Tuples inserted
 49. Tuples returned
 50. Tuples updated
 51. Ungranted locks
 52. Unused, non-superuser connections
 53. Unused, non-superuser connections as percentage of max_connections

3. Database Level Alerts: These alerts for Monitoring Specific Database in PostgreSQL Cluster. This is useful when you have database, which is important for your Bussiness and monitoring of that database is important for you:

 1. Average table bloat in database
 2. Committed transactions percentage
 3. Connections in idle-in-transaction state
 4. Connections in idle-in-transaction state, as a percentage of max_connections
 5. Connections in idle state
 6. Database Frozen XID
 7. Database size
 8. Dead Tuples
 9. Dead tuples percentage
 10. Function Count
 11. Highest table bloat in database
 12. Hot update percentage
 13. Index Scans
 14. Index size as a percentage of table size
 15. InfiniteCache buffers hit percentage
 16. Largest index by table-size percentage
 17. Largest table (by multiple of unbloated size)
 18. Last Analyze
 19. Last AutoAnalyze
 20. Last AutoVacuum
 21. Last Vacuum
 22. Live Tuples
 23. Long-running autovacuums
 24. Long-running idle connections
 25. Long-running idle connections and idle transactions
 26. Long-running idle transactions
 27. Long-running queries
 28. Long-running transactions
 29. Long-running vacuums
 30. Sequence Count
 31. Sequential Scans
 32. Shared buffers hit percentage
 33. Table Count
 34. Table size in database
 35. Total connections
 36. Total connections as percentage of max_connections
 37. Total table bloat in database
 38. Tuples deleted
 39. Tuples fetched
 40. Tuples hot updated
 41. Tuples inserted
 42. Tuples returned
 43. Tuples updated
 44. Ungranted locks

4. Schema Level Alerts: User can also configure alerts for specific schema in Database. This is important when you have a schema, related to important Business Objects and you have to monitor the performance of tables in schema. List of those alerts is given below:

 1. Average table bloat in schema
 2. Dead Tuples
 3. Dead tuples percentage
 4. Function Count
 5. Highest table bloat in schema
 6. Hot update percentage
 7. Index Scans
 8. Index size as a percentage of table size
 9. Largest index by table-size percentage
 10. Largest table (by multiple of unbloated size)
 11. Last Analyze
 12. Last AutoAnalyze
 13. Last AutoVacuum
 14. Last Vacuum
 15. Live Tuples
 16. Sequence Count
 17. Sequential Scans
 18. Table Count
 19. Table size in schema
 20. Total table bloat in schema
 21. Tuples deleted
 22. Tuples hot updated
 23. Tuples inserted
 24. Tuples updated

5. Table Level Alerts: User can also create alert table level. Some times, user are interested in monitoring important/specific table which has business importance. For them these alerts are important for maintaining the performance of PG.

 1. Dead Tuples
 2. Dead tuples percentage
 3. Hot update percentage
 4. Index Scans
 5. Index size as a percentage of table size
 6. Last Analyze
 7. Last AutoAnalyze
 8. Last AutoVacuum
 9. Last Vacuum
 10. Live Tuples
 11. Row Count
 12. Sequential Scans
 13. Table bloat
 14. Table Frozen XID
 15. Table size
 16. Table size as a multiple of ubloated size
 17. Tuples deleted
 18. Tuples hot updated
 19. Tuples inserted
 20. Tuples updated

You can see that Postgres Enterprise Manager covers PostgreSQL monitoring from all aspects which directly/indirectly responsible for PostgreSQL Performance/Monitoring.

Now, reader of my blog must be thinking, these are the list of alerts. How can they get more information/description on these alerts. Answer is simple, you can get more detail about above alerts by three methods:
1. Using PEM Client HELP

 Open PEM Client -> Go to Help

2. Using Alerting:

  Open PEM Client ->
   For server Level, go to -> PEM Agents -> Right click on particular agent -> click on Alerting

For PG Cluster Level,

go to -> PEM Server Directory -> Right Click on particular PG Cluster -> select Alerting 

Similarly For Database alerts and Table Alerts.

3. Using SQL: Connect to PEM Server Database:

   psql -p 5432 -U postgres pem

And use following SQLs:
Server Level:

SELECT row_number() over (order by display_name)||'. '|| at.display_name AS display_name,description FROM pem.alert_template at WHERE (at.object_type = 100) ORDER BY at.display_name;

CLuster Level:

SELECT row_number() over (order by display_name)||'. '|| at.display_name, description FROM pem.alert_template at WHERE (at.object_type = 200) AND at.applicable_on_server IN ('ALL' , 'POSTGRES_SERVER') ORDER BY at.display_name;

DB Level:

SELECT row_number() over (order by display_name)||'. '|| at.display_name, description FROM pem.alert_template at WHERE (at.object_type = 300) AND at.applicable_on_server IN ('ALL' , 'POSTGRES_SERVER') ORDER BY at.display_name;

Schema Level:

SELECT row_number() over (order by display_name)||'. '|| at.display_name, description FROM pem.alert_template at WHERE (at.object_type = 400) AND at.applicable_on_server IN ('ALL' , 'POSTGRES_SERVER') ORDER BY at.display_name;

Table Level:

SELECT row_number() over (order by display_name)||'. '|| at.display_name, description FROM pem.alert_template at WHERE (at.object_type = 500) AND at.applicable_on_server IN ('ALL' , 'POSTGRES_SERVER') ORDER BY at.display_name;

Have Fun! and Have perfect Monitoring of PostgreSQL.