Server & Application Monitor

PostgreSQL

PostgreSQL

This template assesses the performance of a PostgreSQL 9 Server database by retrieving performance data from the built-in pg_stat_database, pg_locks, pg_trigger, pg_proc, pg_stat_all_tables and pg_indexes views.

Prerequisites:

On the Orion APM Server, download and install the PostgreSQL 9 ODBC driver from here:http://www.postgresql.org/ftp/odbc/versions/msi/.

On the PostgreSQL Server, find the following file:

C:\Program Files\PostgreSQL\9.0\data\pg_hba.conf

and add the next line to allow remote connections for Orion APM Server:

host all all 192.168.3.198/32 trust

where 192.168.3.198 is IP address of your APM Server.

Credentials: Database user name and password.

Monitored Components

Note: Components without predetermined threshold values will provide guidance such as "Use the lowest threshold possible" or "Use the highest threshold possible" to help you find a threshold appropriate for your application. For more information, seehttp://knowledgebase.solarwinds.com/kb/questions/2415.

Database Cache Hit Ratio (%)

This counter returns the percentage of pages found in the buffer cache without having to read from the disk in the specified database. This is the formula used: "cache reads"/("cache reads"+"physical reads")*100

This ratio should exceed 90%, and ideally be over 99%. If your counter is lower than 90%, you should consider adding more RAM if possible. A higher ratio value returned indicates improved performance by your PostgreSQL Server.

Note: By default, this counter looks in the postgres database. If you want monitor another database, you should change the database name in the Connection String field: (...DataBase=postgres;...) and the Sql Query field: (...where datname = 'postgres').

Database Success Rate (%)

This counter returns the percentage of successful transactions in the specified database. This is the formula used: "committed transactions"/("committed transactions"+"rolled back transactions")*100

This ratio should exceed 90%, and ideally be over 99%.

Note: By default, this counter looks in postgres database. If you want to monitor another database, you should change the database name in the Connection String field: (...DataBase=postgres;...) and Sql Query field: (...where datname = 'postgres').

Total Active Server Connections

This counter returns the total number of server connections that are active. You should set the thresholds appropriate to your environment.

Active Connections in Specified Database

This counter returns the number of connections to the specified database. You should set the thresholds appropriate to your environment.

Note: By default, this counter looks in postgres database. If you want to monitor another database, you should change database name in the Connection String field, (...DataBase=postgres;...) and the Sql Queryfield: (...where datname = 'postgres').

Database Size (MB)

This counter returns the size of the specified database in MB.

Note: By default, this counter looks in the postgres database. If you want to monitor another database, you should change the database name in theConnection String field, (...DataBase=postgres;...) and the Sql Query field,(...pg_database_size('postgres')...).

Current Number of Locks on Server

This counter returns the total number of locks on this server.

This counter should be as low as possible. For more information about locks you should create a query and look in the pg_locks view.

Total Indexes in Current Database

This counter returns the total number of indexes for the current database.

For more information about locks, you should look in the pg_indexes view.

Note: By default this counter looks in the postgres database. If you want monitor another database, you should change the database name in the Connection String field: (...DataBase=postgres;...).

Table with the biggest number of Sequential Scans

This counter returns the name of the table and its number of sequential scans for the current database.

Note: By default, this counter looks in the postgres database. If you want monitor another database, you should change the database name in theConnection String field: (...DataBase=postgres;...).

Table with the biggest number of Index Scans

This counter returns the name of the table and its number of index scans for the current database.

Note: By default, this counter looks in the postgres database. If you want monitor another database, you should change the database name in theConnection String field: (...DataBase=postgres;...).

Table with the biggest number of Row Reads

This counter returns the name of the table and its number of row reads for the current database.

Total Number of Triggers

This counter returns the total number of triggers for the current database.

Note: By default this counter looks in the postgres database. If you want monitor another database, you should change the database name in theConnection String field: (...DataBase=postgres;...).

Size of the Largest Table (MB)

This counter returns the name of the largest table and its size in MB for the current database.

Note: By default this counter looks in the postgres database. If you want monitor another database, you should change database name in theConnection String filed: (...DataBase=postgres;...).

Total Number of Tables in Current Database

This counter returns the total number of tables for the current database.

Replication Byte Lag

This counter returns replication byte lag between master and specific slave servers.

       Note: By default, this monitor is disabled.
       Note: Before using this monitor you should provide the correct slave server IP address in the last line of the SQL query. Otherwise monitor will return nothing. For example: client_addr = '192.168.2.204';
       Note: This monitor should be enabled only on master Postgres server.

Portions of this document were originally created by and are excerpted from the following sources:

The PostgreSQL Global Development Group, “PostgreSQL 9.1.3 Documentation”, Copyright 1996-2011 The PostgreSQL Global Development Group.
All rights reserved. Available at http://www.postgresql.org/docs/9.1/interactive/index.html

Last updated: 8/19/2014