Postgres Monitoring
Postgres or PostgreSQL is a relational database management system (RDBMS) emphasizing extensibility and technical standards compliance. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users.
Cavisson enables you to keep an eye on the performance of your database servers. Using the PostgreSQL Monitoring tool, you can optimize the performance of your applications and ensure the availability of your SQL servers. It provides an end-to-end monitoring of PostgreSQL queries with insights into various aspects, such as SQL Activity, Temp files, and Server stats.
Overview
This document provides the capability for profiling Postgres. Cavisson machine agent establishes a connection with Postgres database server. After establishing the connection with database, monitor executes some queries to capture the required stats to get the health status of Postgres. To make connection with Postgres, you need to provide connectivity credential to the monitor program, so that it can make connection to the database. It also provides end-to-end monitoring of Postgres queries with insights into SQL Activity.
Benefits
Implementing effective Postgres monitoring offers the following benefits:
- Improved application availability
- Increased database performance
- Quick detection of database outages, failures, and table corruption
- Prognostic analysis of storage requirements and index performance
Features
- Provides Query Execution Statistics and Query Plan
- Provides all information on sessions created in the database
- Provides information about any type of Locks created in the database.
- Provides ability to view details of Temporary Files
- Provides ability to check server configuration parameters and connection statistics.
Prerequisites
- For viewing PostgreSQL Execution Stats, Postgres server version should be 5 or later.
- Below steps need to be performed for PostgreSQL Execution Stats screen:
- Add the below keywords in postgresql.conf
To enable pg_stat_statements (Queries)
pg_stat_statements.max = 10000
pg_stat_statements.track = all
track_activity_query_size = 2048
For Execution Plan
auto_explain.log_min_duration = 0
auto_explain.log_format = ‘XML’
auto_explain.log_nested_statements = ON — multiple statement
shared_preload_libraries = ‘pg_stat_statements, auto_explain’
-
- Connect the database with super user. Make sure to enable pg_stat_statements extension on the databases which agent will connect.
Execute below command after connecting to the database:
- Connect the database with super user. Make sure to enable pg_stat_statements extension on the databases which agent will connect.
Create extension pg_stat_statements;
-
- Restart the PostgreSQL server.
Monitoring Configuration
- Login to a machine and go to Configuration > Monitors.
2. Navigate to Postgres Monitoring section and expand it.
3. This lists all the Postgres Monitors. Click the Configure Monitor icon for any of the monitors.
Note:
- Here we are providing configuration steps for PostgreSQLBGWriterStats. Similar configuration steps can be applied for other PostgreSQL Monitors.
- In case of PostgreSQLAccesslog monitor configuration, you also need to provide the file path which is the absolute Postgres log file path or pattern.
4. This displays the Monitor Configuration window.
5. Provide the following details:
-
- Server Name / Tier Name: Name of the server / tier on which the Postgres monitoring is to be applied.
- Exclude Server / Tier
- From the drop down list, select a tier(s) / server(s) that to exclude.
- You can type a pattern as a regular expression to exclude a tier / server.
- You can type multiple regular expressions separated by comma.
- User Name and Password: Accessible username and password.
- Advanced Settings
- Host Name: Server IP or server name where PostgreSQL DB server is installed.
- Port: PostgreSQL service running port.
- DB Name: Database name as Postgres.
Once all the configurations are done and monitors are applied, you can see the monitor details and stats on the Monitor UI and Dashboard.
Monitoring UI
To access the Postgres Monitoring UI, login to NetDiagnostics and go to View > DB Monitoring > Postgres Monitoring.
This displays the Postgres Monitoring UI.
Note:
- Each tab is further categorized into various sections. Details of each tab and its corresponding sections are provided in the subsequent topics.
- You can perform some operations within tabs / sections. Details are provided within the “Operations to View Data” section.
Operations to View Data
You can perform the below operations to customize the viewing of data, such as:
- Enable / Disable Aggregate Mode
- Enable / Disable Analysis Mode
- Selection of Database server
- Configuration for viewing Real Time data samples
- Configure Preset option for data samples
- Rewind data samples to view historical data based on last sample time
- Forward data samples which were rewind to view historical data
- Filter or Search for records at column level
- Show / Hide Columns based on the relevancy or requirements
- Changing Chart types for representation of data differently
- Download the records in CSV format for reference or analysis
Enable / Disable Aggregate Mode
- It is represented with
icon.
- It is present in DB Query Stats and Session Stats tabs only.
- If Enabled, the data displayed in the screens are the aggregated data of given time interval.
- If Disabled, the cumulative data is displayed.
Enable / Disable Analysis Mode
- It is represented with
icon.
- It is present in all screens and by default, it is Disabled.
- If Enabled, the data will not be updated as we are analyzing data of a particular time interval.
- If Disabled, the next data sample will update.
Selection of Database server
You can select which database is to monitor. To do this, click the icon. A drop-down list is displayed with the database names. Select the required database for monitoring.
Viewing Real Time Data / Sample Data
You can view either real time data or offline data. Real time data can be viewed for online tests only. To view real time data, click the Switch to Real Time icon at the top-right corner of the window. This prompts the time (in seconds) for the interval. Select the interval, such as 15 sec, 30 sec, or 45 sec for viewing the data. Default interval is 30 sec. You can change the interval from 30 sec to either 15 sec or 45 sec by clicking
.
The data is displayed based upon the interval selection. Post that, there are further two options:
- Pause the real time data for a certain duration by clicking the
icon.
- Move out from the real time viewing of data by clicking the
icon.
Preset
It is the time for which the data is to be displayed for monitoring. For example – Last 10 minutes, last 30 minutes, last 1 hour, and so on.
On selecting the Custom option, specify the Start date / time and End date / time.
View Last Sample Time
You can view the last sample time on the header of each table and graphical representation. This sample time is based on configured progress interval.
Rewind
This feature is used to view the data of last samples. You can view the historical data based on the last samples by clicking the Rewind icon.
Upon clicking this icon, data of previous sample is displayed and last sample time can be seen on the table header. For example: If the current data sample is from 07/30/18 11:50:14 To 07/30/18 11:51:14, after clicking the Rewind icon, the data sample time becomes 07/30/18 11:49:14 To 07/30/18 11:50:14 and data is displayed accordingly. You can further click the Rewind icon to view the corresponding historical data. Each click displays the previous data based on sample interval.
To resume with the current time, click the icon.
Forward
You can view the next to previous data (which was accessed by clicking the rewind icon) by clicking the icon.
Upon clicking this icon, data of next sample is displayed and sample time can be seen on the table header. For example: If the current data sample is from 07/30/18 11:49:14 To 07/30/18 11:50:14 after clicking the Forward icon, the time for sample data becomes 07/30/18 11:50:14 To 07/30/18 11:51:14 and data is displayed accordingly. You can further click the Forward icon to view the corresponding historical data. Each click displays the next data based on sample interval.
To resume with the current time, click the icon.
Filter
You can filter the data or search for specific records based on the value provide in the text boxes that are displayed upon clicking the icon.
For example: To view the records with status as ‘Active’, specify ‘Active’ within the ‘Status’ column. This will filter out the records containing status as ‘Active’ and display accordingly.
Changing Chart Types
You can also change the chart type by clicking the icon. There are various chart types available, such as Line, Pie, Stack Area, Bar, and Area.
Download as CSV
You can also download the records in CSV format. To do this, click the button at the top-right corner of the window. This download the records in CSV format.
These were some operations for viewing data in customized way and downloading the records in CSV format. In the subsequent topics, we are providing how we can analyze the query by monitoring various statistics.
SQL Activity
The SQL Activity tab provides details of DB query for monitoring, such as DB Query Stats. It includes:
- DB Query details
- Locks details
- Sessions details
DB Query Details
In the DB Query Stats section, you can view the details of DB query including the SQL statement and execution plan. If you hover your mouse at a query, the complete query is displayed.
To analyze the DB details:
- Click the DB Query Stats
- The DB Query Stats window is displayed that contains one row per active lockable object, requested lock mode, and relevant transaction.
3. Click a DB Name tab to view the complete details of the query (with all columns).
You can identify the slow DB queries and other details from the DB Query section. This section displays the DB query information, such as count, query, and SQL handle. The following table provides information the DB query fields.
Column Name |
Description |
User Name |
User who executed the statement |
Query |
Text of a representative statement |
Count |
Number of times executed |
Total Time (hh:mm:ss:ms) |
Total time spent in the statement, (in hh:mm:ss:ms) |
Rows |
Total number of rows retrieved or affected by the statement |
Shared Blocks Hit |
Total number of shared block cache hits by the statement |
Shared Blocks Read |
Total number of shared blocks read by the statement |
SQL Handle |
Is a token that refers to the batch or stored procedure that the query is part of |
Shared Blocks Dirtied |
Total number of shared blocks dirtied by the statement |
Shared Blocks Written |
Total number of shared blocks written by the statement |
Local Blocks Hit |
Total number of local block cache hits by the statement |
Local Blocks Read |
Total number of local blocks read by the statement |
Local Blocks Dirtied |
Total number of local blocks dirtied by the statement |
Local Blocks Written |
Total number of local blocks written by the statement |
Temp Blocks Read |
Total number of temp blocks read by the statement |
Temp Blocks Written |
Total number of temp blocks written by the statement |
Block Read Time(ms) |
Total time the statement spent reading blocks, in milliseconds |
Block Write Time(ms) |
Total time the statement spent writing blocks, in milliseconds |
Database Name |
Database in which the statement was executed |
Query Id |
Internal hash code, computed from the statement’s parse tree |
SQL Statement
This section displays SQL statement of the selected query.
Execution Plan
This section provides an interactive and intuitive way to view the query execution plan and statement. The focus is on the SQL Statement selected on the Query Execution Statistics table. This section displays the sequence of execution for the selected query plan.
Click a node to view the node details.
- To download the execution plan XML, click the
icon.
- To download the execution plan as diagrammatical format, click the
icon.
Analyzing the Locks Details
To analyze the information about the locks held by open transactions within the database server:
- Click the Locks tab.
- The SQL Locks window is displayed that contains one row per active lockable object, requested lock mode, and relevant transaction.
Using the Locks tab, the database administrator can view information about the outstanding locks in the lock manager. For example, this capability can be used to:
- View all the locks currently outstanding, all the locks on relations in a particular database, all the locks on a particular relation, or all the locks held by a particular PostgreSQL session.
- Determine the relation in the current database with the most ungranted locks (which might be a source of contention among database clients).
- Determine the effect of lock contention on overall database performance, as well as the extent to which contention varies with overall database traffic.
The following table provides information about the various fields in the SQL Locks window:
Field |
Description |
Process id (SPID) |
Process ID of the server process holding or awaiting this lock |
Resource Lock Type |
Type of lock applied on the lockable object |
DB Name |
Database name associated with the backend |
Lock Mode Name |
Name of the lock mode held or desired |
State Change Time |
Time when the currently active query changed its state |
Request Status(t/f) |
The status of the lock (t/f) |
Analyzing the Session Details
Using the Session tab, you can view access information about all active user connections and internal tasks. To open the Sessions window:
- Click the Sessions tab.
- The Session Aggregate window is displayed, as shown below:
The following table provides information about the various fields in the Session Aggregate window:
Column Name |
Description |
PID |
Process ID of this backend |
SQL User |
Name of the user logged into this backend |
Query |
Text of this backend most recent query |
Status |
Status of the session (active, idle, idle in truncation etc.) |
Client Host Name |
Host name of the connected client |
Login Time |
Time when this process was started, i.e. when the client connected to the server |
DB Name |
Name of the database this backend is connected to |
Client Address |
IP address of the client connected to this backend |
Application Name |
Name of the application that is connected to this backend |
Client Port |
TCP port number that the client is using for communication with this backend |
Exact Start |
Time when this process’ current transaction was started |
Query Start Time |
Time when the currently active query was started |
State Change Time |
Time when the state was last changed |
Waiting |
True if this backend is currently waiting on a lock |
On clicking the PID, the user can further drill down the session and can see the following details:
- Session Stats
- SQL Plan
- SQL Query
- Session Locks
Session Stats
On clicking Session Stats, the aggregated data of session stats is displayed for the selected PID based on the specified time duration.
SQL Plan
On clicking Sql Plan, the complete plan is displayed.
SQL Query
On clicking Sql Query, the complete query is displayed.
Locks
On clicking Locks, the details of session locks are displayed, such as Process ID, Database Name, State Change Time, Resource Lock Type, Lock Mode Name, and Request Status.
Temp Files
You can use the Temp Files tab to view the temporarily stored objects. To view temp files:
- Click the Temp Files tab.
- Click the Usage tab.
Following are the description of the columns in the Temp DB table.
Field |
Description |
DB Name |
Database Name |
Temp Files Count |
Number of Temporary Files |
Temp File Size(MB) |
Size of temporary files in Megabytes |
Temp Files Count and Temp Files Size are separately displayed in the widgets.
Server Stats
Click the Server Stats tab to analyse the details of connection for monitoring.
SQL Configuration
Memory stats graph provides details about free memory and usage of total memory by database. The values of memory space displayed in the graph in MB.
- Total memory of database server
- Total used memory of database server
- Total free memory of database server
- Total server memory used by database
This graph displays data for time interval according to preset as in other screens and refreshes accordingly.
DB Configuration
Click the Configurations tab to view the database configuration detail of different configuration options and settings related to the database performance.
The following table describes the various fields in the SQL Configuration window:
Field |
Description |
Parameter Name |
Configuration parameter name |
Parameter Setting |
Value to be assigned to the parameter |
Minimum |
Parameter Minimum value |
Maximum |
Parameter Maximum value |
Source |
Source of the current parameter value |
Connection Stats
Sub-Section Connection Stats show row per server process, showing information related to the current activity of that process.
Details of connection stats for monitoring.
Field |
Description |
DB Name |
Name of the database this backend is connected |
Login Time |
Time when this process was started, i.e., when the client connected to the server |
Last Batch Time |
Time when the state last changed |
Status |
Current overall state of this backend. Possible values are: · active: The backend is executing a query. · idle: The backend is waiting for a new client command. · idle in transaction: The backend is in a transaction, but is not currently executing a query. · idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error. · fastpath function call: The backend is executing a fast-path function. · disabled: This state is reported if track_activities is disabled in this backend. |
Client Hostname |
Host name of the connected client |
Login Name |
Name of the user logged into Database |
Number of Connections |
Number of established connections per database |
Configuration and Logs
You can configure logs within the ‘Configuration & Logs’ section. This can be viewed only if you have the read-write permission. On selecting icon, a pop up appears. Here you can change the values as per requirement.
Provide the following configurations:
- Sample Progress Interval: The interval at which data is updated in UI.
- Batch Job History Duration: To view job history for batch job based on the specified duration.
- Timeout for Custom Query: To terminate the custom query execution in case data from query is not received.
- Threshold for SQL Plan: To set the min & max value (threshold) for SQL Plan.
This section displays configuration and logs of database that includes Information about Postgres internal connections, Services completion time, Query completion time, and Custom Query execution.
It is further divided into following tabs:
- Postgres
- Services Logs
- DB Query Logs
- Query Execution
Postgres
This displays overall connection stats that includes max connections, reserved connections, used connections, and available connections. Apart from this, you can view the detailed connection stats based on the database and application.
Detailed Connection Stats (Based on Database)
This section displays the detailed connection stats based on the database:
- Database Name: Name of the database.
- Used: Number of used connections.
- Active: Number of active connections. The backend is executing a query.
- Idle: Number of idle connections. The backend is waiting for a new client command.
- Idle in Transaction: Identifies connections against which your app has run a BEGIN but it is now waiting somewhere in a transaction and not doing work.
- Idle in Transaction (Aborted): Identifies connections that were idle in the transactions that have been aborted.
- Fastpath Function Call: This state is similar to idle in transaction, except one of the statements in the transaction caused an error.
- Disabled: This state is reported if track_activities is disabled in the backend.
- Blank: The backend is executing a fast path function.
Detailed Connection Stats (Based on Application)
This section displays the detailed connection stats based on the application. The same fields are displayed in this section too.
Service Logs
This displays the services completion time. The details include – Timestamp, database, class, method, service, and execution time.
DB Query Logs
This displays the query completion time. The details include – Timestamp, database, class, method, service, and execution time.
Query Execution
Here, you can run custom query and predefined procedures and get the output. Select the Custom Query option, provide the query, and click Execute. The out is displayed in the Query Output section.
Monitoring Capabilities
Postgres monitors are categorized into Detailed Monitors and Metrics Monitors.
Detailed Monitors
The description of detailed monitors is provided in the Monitoring UI section.
- PostgreSQLAccesslog
- PostgreSQLConnectionStats
- PostgreSQLExecutionStats
- PostgreSQLLocksStats
- PostgreSQLSessionStats
Metrics Monitors
The metrics of these monitors are captured and displayed on the Dashboard.
- PostgreSQL Background Writer
- PostgreSQL Activity
- PostgreSQL Connections
- PostgreSQL Table Heap
- PostgreSQL IO Activity
- PostgreSQL Lock Mode
- PostgreSQL Query
- PostgreSQL Temporary File
PostgreSQL Background Writer
PostgreSQL background writer stats provides metrics related to background writer activity in the database.
# |
Metric |
Description |
1 |
PostgreSQL Buffer Scheduled Checkpoints/Sec |
Number of scheduled checkpoints that have been performed per second. |
2 |
PostgreSQL Buffer Requested Checkpoints/Sec |
Number of requested checkpoints that have been performed per second. |
3 |
PostgreSQL Buffer Written/Sec |
Number of buffers written during checkpoints per second. |
4 |
PostgreSQL Buffer Written By BG/Sec |
Number of buffers written by the background writer per second. |
5 |
PostgreSQL Max Written Clean/Sec |
Number of times the background writer stopped a cleaning scan because it had written too many buffers. |
6 |
PostgreSQL Buffer Written By Backend/Sec |
Number of buffers written directly by a backend per second. |
7 |
PostgreSQL Buffer Allocated/Sec |
Number of buffers allocated per second. |
PostgreSQL Activity
PostgreSQL database activity stats provides metrics related to activity in the database.
# |
Metric |
Description |
1 |
PostgreSQL Connected Backends |
Number of backends currently connected to this database. |
2 |
PostgreSQL Active Connections |
Number of active connections to this database. |
3 |
PostgreSQL Idle Connections |
Number of idle connections to this database. |
4 |
PostgreSQL Committed Transactions/Sec |
Number of transactions in this database that have been committed per second. |
5 |
PostgreSQL Rollback Transactions/Sec |
Number of transactions in this database that have been rolled back per second. |
6 |
PostgreSQL Disk Read/Sec |
Number of disk blocks read per second. |
7 |
PostgreSQL Cache Hits/Sec |
Number of times disk blocks were found already in the buffer cache per second. |
8 |
PostgreSQL Tuple Returned/Sec |
Number of rows returned by queries per second. |
9 |
PostgreSQL Tuple Fetched/Sec |
Number of rows fetched by queries per second. |
10 |
PostgreSQL Tuple Inserted/Sec |
Number of rows inserted by queries per second. |
11 |
PostgreSQL Tuple Updated/Sec |
Number of rows updated by queries per second. |
12 |
PostgreSQL Tuple Deleted/Sec |
Number of rows deleted by queries per second. |
13 |
PostgreSQL Locks Consumed |
Total number of locks held by open transactions within the database server. |
14 |
PostgreSQL Tuple Write/Sec |
Sum of rows inserted, updated and deleted per second. |
15 |
PostgreSQL Hit (%) |
Disk hits percentage w.r.t total read requests. |
PostgreSQL Connections
PostgreSQL database Connection statistics.
# |
Metric |
Description |
1 |
PostgreSQL Connections |
Total connection statistics database. |
PostgreSQL Table Heap
PostgreSQL database table heap ratio.
# |
Metric |
Description |
1 |
PostgreSQL Table Heap Read/Sec |
Number of disk blocks read from user’s table per second. |
2 |
PostgreSQL Table Heap Hit/Sec |
Number of buffer hits in user’s table per second. |
3 |
PostgreSQL Table Heap Hit (%) |
Buffer hits percentage w.r.t total read requests. |
PostgreSQL IO Activity
PostgreSQL IO activity stats provides metrics related to IO activity in the database.
# |
Metric |
Description |
1 |
PostgreSQL Heap Blocks Read/Sec |
Number of disk blocks read from this schema per second. |
2 |
PostgreSQL Heap Blocks Hit/Sec |
Number of buffer hits in this schema per second. |
3 |
PostgreSQL Index Blocks Read/Sec |
Number of disk blocks read from all indexes on this schema per second. |
4 |
PostgreSQL Index Blocks Hit/Sec |
Number of buffer hits in all indexes on this schema per second. |
5 |
PostgreSQL Toast Blocks Read/Sec |
Number of disk blocks read from this schema TOAST table per second. |
6 |
PostgreSQL Toast Blocks Hit/Sec |
Number of buffer hits in this schema in TOAST table per second. |
7 |
PostgreSQL Toast Index Read/Sec |
Number of disk blocks read from this schema in TOAST table indexes per second. |
8 |
PostgreSQL Toast Index Hit/Sec |
Number of buffer hits in this schema in TOAST table indexes per second |
PostgreSQL Lock Mode
PostgreSQL lock mode stats provide metrics related to lock mode like AccessShareLock, ExclusiveLock etc. Table level lock modes are ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE and Row level lock modes are FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE.
# |
Metric |
Description |
1 |
PostgreSQL Lock Mode Count |
Number of occurrences of specific lock modes for the sample interval. |
PostgreSQL Query
Provide PostgreSQL health stats.
# |
Metric |
Description |
1 |
PostgreSQL Executed Queries/Sec |
Number of queries successfully executed per second in database. |
2 |
PostgreSQL Query Response Time (ms) |
Query average response time. |
3 |
PostgreSQL Active Queries |
Number of queries running in database. |
4 |
PostgreSQL Query With Duration <1 Min |
Number of queries taking less than 1 minute. |
5 |
PostgreSQL Query With Duration 1-5 Min |
Number of queries taking 1-5 minute. |
6 |
PostgreSQL Query With Duration 5-15 Min |
Number of queries taking 5-15 minute. |
7 |
PostgreSQL Query With Duration >15 Min |
Number of queries taking more than 15 minute. |
PostgreSQL Temporary File
PostgreSQL temporary database space stats.
# |
Metric |
Description |
1 |
PostgreSQL Temporary File Count |
Number of temporary file count of database. |
2 |
PostgreSQL Temporary File Size (MB) |
Temporary file size per database in Megabytes. |
Note: You can see the data of temporary files only if there is temporary data in the Postgres.