In PostgreSQL, you can monitor live sessions using a few different methods:
- pg_stat_activity view: This view provides information about current activity in the database, including information about active sessions. You can query the pg_stat_activity view to see a list of currently running queries and the details of each session, such as the user running the query, the client address, and the query start time.
- pg_stat_replication view: If you are using PostgreSQL replication, you can use this view to monitor the status of replicas, including the current state, lag, and replication progress.
- pg_top utility: pg_top is a utility that provides real-time monitoring information about PostgreSQL processes, similar to the Unix top utility. You can use pg_top to see a list of active queries, including their current state, query start time, and the resources they are consuming.
- Third-party tools: There are also several third-party tools available for monitoring PostgreSQL, including open-source tools like pgAdmin and commercial tools like pgMonitor and PG Sentinel. These tools offer more advanced monitoring and performance tuning capabilities and can be a good choice if you need more robust monitoring capabilities.
You can use any of these methods to monitor live sessions in PostgreSQL, depending on your needs and the resources available to you.
SQL script to monitor Disk I/O generated by PostgreSQL threads and sort by read intensive threads
You can use the following SQL script to monitor disk I/O generated by PostgreSQL threads and sort by read-intensive threads:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
WITH activity AS ( SELECT pid, query, xact_start, state, waiting, read_time + write_time AS io_time, read_time AS read_time, read_bytes + write_bytes AS io_bytes, read_bytes AS read_bytes FROM pg_stat_activity ) SELECT pid, query, xact_start, state, waiting, read_time, read_bytes FROM activity ORDER BY read_time DESC; |
This script first creates a subquery named activity that retrieves information about active sessions from the pg_stat_activity view. The subquery calculates the total I/O time and I/O bytes, as well as the read-specific I/O time and bytes, for each session.
The main query then selects only the columns of interest and orders the results by the read_time column in descending order, so that the sessions with the most read-intensive I/O are listed first.
Note that the values in the read_time and read_bytes columns represent the amount of time and bytes spent on disk reads since the last statistics reset, and do not reflect real-time usage. To get real-time usage, you would need to query the view multiple times and calculate the differences between consecutive results.
SQL script to monitor Disk I/O generated by PostgreSQL threads and sort by write intensive threads
You can use the following SQL script to monitor disk I/O generated by PostgreSQL threads and sort by write-intensive threads:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
WITH activity AS ( SELECT pid, query, xact_start, state, waiting, read_time + write_time AS io_time, write_time AS write_time, read_bytes + write_bytes AS io_bytes, write_bytes AS write_bytes FROM pg_stat_activity ) SELECT pid, query, xact_start, state, waiting, write_time, write_bytes FROM activity ORDER BY write_time DESC; |
This script is similar to the previous script, but it orders the results by the write_time column in descending order, so that the sessions with the most write-intensive I/O are listed first.
Note that the values in the write_time and write_bytes columns represent the amount of time and bytes spent on disk writes since the last statistics reset, and do not reflect real-time usage. To get real-time usage, you would need to query the view multiple times and calculate the differences between consecutive results.
SQL script to monitor expensive queries from PostgreSQL threads and sort by higher Response Time (Latency)
You can use the following SQL script to monitor expensive queries from PostgreSQL threads and sort by higher response time:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
WITH activity AS ( SELECT pid, query, xact_start, state, waiting, query_start, now() - query_start AS response_time FROM pg_stat_activity WHERE query NOT LIKE '%IDLE%' ) SELECT pid, query, xact_start, state, waiting, response_time FROM activity ORDER BY response_time DESC LIMIT 10; |
This script first creates a subquery named activity that retrieves information about active sessions from the pg_stat_activity view. The subquery filters out sessions that are idle and calculates the response time for each active session, which is the difference between the current time and the time the query started.
The main query then selects only the columns of interest and orders the results by the response_time column in descending order, so that the sessions with the longest response times are listed first. The LIMIT 10 clause limits the number of results to the top 10 longest response times.
Note that the response_time column represents the amount of time a query has been running since it started and does not reflect the actual response time for the query as seen by clients. The actual response time for a query depends on many factors, including the complexity of the query, the state of the database, and the resources available on the system.
