Request a Demo
5.18. Activity
Prev UpChapter 5. Using PPEMHome Next

5.18. Activity #

5.18.1. User Sessions
5.18.2. Vacuum Processes
5.18.3. Statistics Collection Processes
5.18.4. Clustering Processes
5.18.5. Reindexing or Index Creation Processes
5.18.6. Base Backup Processes
5.18.7. Copying Processes

5.18.1. User Sessions #

You can view information about user sessions and background worker processes of an instance. The information is based on the pg_stat_activity view.

This section explains how to manage user sessions. It includes the following instructions:

Viewing User Sessions

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

The table of user sessions with the following columns will be displayed:

  • pid: The backend ID.

  • leader_pid: The backend ID for the process in the parallel query execution group.

  • State: The type of the backend.

    For more information about possible values, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the backend_type column).

  • backend_start: The backend start date and time.

  • client_hostname, client_addr, and client_port: The network name, address, and port number of the client that initiated the user session.

  • usesysid: The ID of the DBMS user under which the session is initiated.

  • username: The name of the DBMS user under which the session is initiated.

  • datid: The ID of the database associated with the user session.

  • database: The name of the database associated with the user session.

  • application_name: The name of the source application of the user session.

  • State: The state of the backend.

    For more information about possible values, refer to the official Postgres Pro documentation on the pg_stat_activity view (see the state column).

  • wait_event_type: The type of the wait event for the backend.

    For more information, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the wait_event_type and wait_event columns).

  • wait_event: The name of the wait event for the backend.

  • transaction_duration_seconds: The duration of the current backend transaction, in seconds.

  • xact_start: The start date and time of the current backend transaction.

  • query_duration_seconds: The duration of the current backend query in seconds.

  • query_start: The start date and time of the current backend query.

  • state_change: The date and time of the last backend state update (see the state column).

  • backend_xid: The ID of the backend top-level transaction.

  • backend_xmin: The current xmin bound of the backend.

  • query_id: The ID of the current or last backend query.

  • query: The text of the current or last backend query.

  • Actions.

    For more information about available actions, refer to other instructions in this section.

Viewing Statistics on Wait Events

PPEM supports integration with the pg_wait_sampling extension for viewing the wait history and profile in user sessions.

Before performing this instruction, install the pg_wait_sampling extension.

To view statistics on wait events:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. In pid, click the backend ID of the user session.

The Wait events profile page with the following web application interface elements will be displayed:

  • The Session tile with the following parameters of the selected user session:

    • Duration: The duration of the user session.

    • Active transaction: The duration of the current transaction in the user session.

    • Active request: The duration of the current query in the user session.

    • Total time spent waiting: The total duration of wait events in the user session.

    • Observations: The number of sampled wait events in the user session.

    • Unique events: The number of unique wait events sampled in the user session.

  • The Sampling settings tile with the following wait profile configuration parameters:

    • profile_period (corresponds to pg_wait_sampling.profile_period)

    • profile_pid (corresponds to pg_wait_sampling.profile_pid)

    • profile_queries (corresponds to pg_wait_sampling.profile_queries)

    • sample_cpu (corresponds to pg_wait_sampling.sample_cpu)

    For the description of the configuration parameters, refer to the official Postgres Pro documentation. If required, you can change the values of these parameters.

  • The graph with statistics on wait events. You can display statistics for the last 5, 10, and 20 wait events using the corresponding buttons at the top of the graph. To scale up or down, use the slider at the bottom of the graph.

  • The History tile with the following wait history parameters:

    • Duration: The duration of the wait history.

    • Start and End: The start and end date and time of the wait history.

  • The History settings tile with the following wait history configuration parameters:

    • history_size (corresponds to pg_wait_sampling.history_size)

    • history_period (corresponds to pg_wait_sampling.history_period)

    For the description of the configuration parameters, refer to the official Postgres Pro documentation. If required, you can change the values of these parameters.

  • The table of events with the following columns:

    • Time: The date and time of the event.

    • Standby event type: The type of the wait event associated with the event.

      For more information about possible values, refer to the official Postgres Pro documentation.

    • Standby event: The wait event associated with the event.

    • Query id: The unique ID of the query associated with the event.

    • Query: The text of the query associated with the event.

      To view the query text separately, click Query next to it.

Viewing the Current Backend Query Locks

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Click Open lock tree next to the user session.

Viewing the Current Backend Query Plan

You can view the current backend query plan if the pg_query_state module is installed.

To view the current backend query plan:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Click Active query plan next to the user session.

Canceling the Current Backend Query

Canceling a current backend query does not terminate a user session.

To cancel the current backend query:

  1. In navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Click Cancel current request next to the user session.

  5. Click Execute.

Terminating a User Session

When the user session is terminated, the current backend query is canceled automatically.

To terminate a user session:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Click Cancel session next to the user session.

  5. Click Execute.

5.18.2. Vacuum Processes #

You can view information about vacuum (VACUUM) and autovacuum (autovacuum) processes. The information is based on the pg_stat_progress_vacuum view.

This section explains how to manage vacuum processes. It includes the following instructions:

Viewing Vacuum Processes

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Vacuum, and then in the top-right corner of the page, select the database.

The table of vacuum processes with the following columns will be displayed:

  • PID: The ID of the backend.

  • State: The state of the backend.

    For more information about possible values, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the state column).

  • Wait event: The name and type of the wait event for the backend.

    For more information, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the wait_event_type and wait_event columns).

  • Phase: The vacuum phase.

    For more information about possible values, refer to the official Postgres Pro documentation.

  • Database: The name of the database being vacuumed.

  • User: The name of the DBMS user on behalf of which vacuum is being performed.

  • Table: The name or ID of the table being vacuumed.

  • Query: The text of the current or last backend query.

  • Query duration: The duration of the current or last backend query.

  • Heap size: The size of the table for which vacuum is being performed.

  • Total size: The total size of the table, including indexes, for which vacuum is being performed.

  • Scanned, %: The percentage of data scanned in the table being vacuumed.

    This column includes additional information:

    Size: The size of scanned table data.

  • Vacuumed, %: The percentage of vacuumed table data.

    This column includes additional information:

    Size: The size of vacuumed table data.

  • Index vacuum: The number of vacuumed table indexes.

  • Memory usage, %: Usage of the memory that stores pointers to expired versions of table rows.

    For more information, refer to the official Postgres Pro documentation on the autovacuum_work_mem and maintenance_work_mem parameters.

  • Actions.

    For more information about available actions, refer to other instructions in this section.

Canceling a Vacuum Process

Canceling a vacuum process does not terminate a user session.

To cancel a vacuum process:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Vacuum, and then in the top-right corner of the page, select the database.

  5. Click Cancel current request next to the vacuum process.

  6. Click Execute.

Terminating the User Session for a Vacuum Process

When the user session is terminated, the vacuum process is canceled automatically.

To terminate the user session for a vacuum process:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Vacuum, and then in the top-right corner of the page, select the database.

  5. Click Cancel session next to the vacuum process.

  6. Click Execute.

5.18.3. Statistics Collection Processes #

You can view information about statistics collection processes for the planner (ANALYZE). The information is based on the pg_stat_progress_analyze view.

This section explains how to manage statistics collection processes. It includes the following instructions:

Viewing Statistics Collection Processes

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Analyze, and then in the top-right corner of the page, select the database.

The table of statistics collection processes with the following columns will be displayed:

  • PID: The ID of the backend.

  • State: The state of the backend.

    For more information about possible values, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the state column).

  • Wait event: The name and type of the wait event for the backend.

    For more information, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the wait_event_type and wait_event columns).

  • Phase: The statistics collection phase.

    For more information about possible values, refer to the official Postgres Pro documentation.

  • Database: The name of the database where the statistics collection is performed.

  • User: The name of the DBMS user on behalf of which the statistics are being collected.

  • Table: The name or ID of the table for which the statistics are being collected.

  • Query: The text of the current or last backend query.

  • Query duration: The duration of the current or last backend query.

  • Current child table: The name of the child table for which the statistics are being collected.

  • Child tables done: The number of child tables for which the statistics were collected.

  • Child tables total: The total number of the child tables for which statistics must be collected.

  • Scanned, %: The percentage of data scanned in the tables for which statistics are being collected.

  • Sample size: The size of the sample used for statistics collection.

  • Stat total: The total number of extended statistics objects.

  • Stat computed: The number of computed extended statistics objects.

  • Actions.

    For more information about available actions, refer to other instructions in this section.

Canceling a Statistics Collection Process

Canceling a statistics collection process does not terminate a user session.

To cancel a statistics collection process:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Analyze, and then in the top-right corner of the page, select the database.

  5. Click Cancel current request next to the statistics collection process.

  6. Click Execute.

Terminating the User Session for a Statistics Collection Process

When the user session is terminated, the statistics collection process is canceled automatically.

To terminate the user session for a statistics collection process:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Analyze, and then in the top-right corner of the page, select the database.

  5. Click Cancel session next to the statistics collection process.

  6. Click Execute.

5.18.4. Clustering Processes #

You can view information about clustering processes — reindexing tables (CLUSTER) and about full vacuum processes (VACUUM FULL). The information is based on the pg_stat_progress_cluster view.

This section explains how to manage clustering processes. It includes the following instructions:

Viewing Clustering Processes

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Cluster, and then in the top-right corner of the page, select the database.

The table of clustering processes with the following columns will be displayed:

  • PID: The ID of the backend.

  • State: The state of the backend.

    For more information about possible values, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the state column).

  • Wait event: The name and type of the wait event for the backend.

    For more information, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the wait_event_type and wait_event columns).

  • Phase: The clustering phase.

    For more information about possible values, refer to the official Postgres Pro documentation.

  • Database: The name of the database where clustering is being performed.

  • User: The name of the DBMS user on behalf of which clustering is being performed.

  • Table: The name or ID of the table for which clustering is being performed.

  • Query: The text of the current or last backend query.

  • Query duration: The duration of the current or last backend query.

  • Blocked totals: The number of user sessions blocked by the clustering process.

  • Heap scanned, %: The percentage of data scanned in the table for which clustering is being performed.

  • Heap total: The total size of the table for which clustering is being performed.

  • Scanned: The size of data scanned in the table for which clustering is being performed.

  • Tuples scanned: The number of rows scanned in the table for which clustering is being performed.

  • Tuples written: The number of rows written to the table for which clustering is being performed.

  • Actions.

    For more information about available actions, refer to other instructions in this section.

Canceling a Clustering Process

Canceling a clustering process does not terminate a user session.

To cancel a clustering process:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Cluster, and then in the top-right corner of the page, select the database.

  5. Click Cancel current request next to the clustering process.

  6. Click Execute.

Terminating the User Session for a Clustering Process

When the user session is terminated, the clustering process is canceled automatically.

To terminate the user session for a clustering process:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Cluster, and then in the top-right corner of the page, select the database.

  5. Click Cancel session next to the clustering process.

  6. Click Execute.

5.18.5. Reindexing or Index Creation Processes #

You can view information about reindexing (REINDEX) or index creation (CREATE INDEX) processes. The information is based on the pg_stat_progress_create_index view.

This section explains how to manage reindexing or index creation processes. It includes the following instructions:

Viewing Reindexing or Index Creation Processes

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Reindex, and then in the top-right corner of the page, select the database.

The table of reindexing or index creation processes with the following columns will be displayed:

  • PID: The ID of the backend.

  • State: The state of the backend.

    For more information about possible values, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the state column).

  • Wait event: The name and type of the wait event for the backend.

    For more information, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the wait_event_type and wait_event columns).

  • Phase: The reindexing or index creation phase.

    For more information about possible values, refer to the official Postgres Pro documentation.

  • Database: The name of the database where reindexing or index creation is being performed.

  • User: The name of the DBMS user under which reindexation or index creation is being performed.

  • Table: The name or ID of the table for which reindexing or index creation is being performed.

  • Index: The name or ID of the index being reindexed or created.

  • Query: The text of the current or last backend query.

  • Query duration: The duration of the current or last backend query.

  • Done, %: The percentage of table data processed in the current phase of reindexing or index creation.

    This column includes additional information:

    • Total size: The total size of the table data to be processed.

    • Done size: The size of the processed table data.

  • Tuples done, %: The percentage of table rows processed in the current phase of reindexing or index creation.

    This column includes additional information:

    • Total size: The total number of table rows to be processed.

    • Done size: The number of processed table rows.

  • Lockers: The processes locked when performing reindexing or index creation.

    This column includes additional information:

    • Done: The number of locked processes for which the waiting is finished.

    • Total: The total number of locked processes.

    • PID: The ID of the currently locked process.

  • Partitions done, %: The percentage of partitioned tables processed when performing reindexing or index creation.

    This column includes additional information:

    • Total: The total number of partitioned tables to be processed.

    • Done: The number of processed partitioned tables.

  • Actions.

    For more information about available actions, refer to other instructions in this section.

Canceling a Reindexing or Index Creation Process

Canceling a reindexing or index creation process does not terminate a user session.

To cancel a reindexing or index creation process:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Reindex, and then in the top-right corner of the page, select the database.

  5. Click Cancel current request next to the reindexing or index creation process.

  6. Click Execute.

Terminating the User Session for a Reindexing or Index Creation Process

When the user session is terminated, the reindexing or index creation process is canceled automatically.

To terminate the user session for a reindexing or index creation process:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Reindex, and then in the top-right corner of the page, select the database.

  5. Click Cancel session next to the reindexing or index creation process.

  6. Click Execute.

5.18.6. Base Backup Processes #

You can view information about base backup processes (usually these are standard backup processes). The information is based on the pg_stat_progress_basebackup view.

This section explains how to manage base backup processes. It includes the following instructions:

Viewing Base Backup Processes

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Base backup, and then in the top-right corner of the page, select the database.

The table of base backup processes with the following columns will be displayed:

  • PID: The ID of the backend.

  • State: The state of the backend.

    For more information about possible values, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the state column).

  • Wait event: The name and type of the wait event for the backend.

    For more information, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the wait_event_type and wait_event columns).

  • Phase: The base backup phase.

    For more information about possible values, refer to the official Postgres Pro documentation.

  • Client address: The network address of the client that initiated the base backup.

  • Backend duration: Backend duration (base backup duration).

  • Started time: The backend start date and time (base backup start date and time).

  • Sent, %: The percentage of data sent to the client when performing the base backup.

    This column includes additional information:

    • Total size: The total size of data to be sent to the client.

    • Sent size: The size of data sent to the client.

  • Tablespace total: The total number of tablespaces to be processed when performing a base backup.

  • Tablespaces streamed: The number of tablespaces processed when performing base backup.

  • Actions.

    For more information about available actions, refer to other instructions in this section.

Canceling a Base Backup Process

Canceling a backup process does not terminate a user session.

To cancel a base backup process:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Base backup, and then in the top-right corner of the page, select the database.

  5. Click Cancel current request next to the base backup process.

  6. Click Execute.

Terminating the User Session for a Base Backup Process

When the user session is terminated, the base backup process is canceled automatically.

To terminate the user session for a base backup process:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Base backup, and then in the top-right corner of the page, select the database.

  5. Click Cancel session next to the base backup process.

  6. Click Execute.

5.18.7. Copying Processes #

You can view information about copying processes (COPY). The information is based on the pg_stat_progress_copy view.

This section explains how to manage copying processes. It includes the following instructions:

Viewing Copying Processes

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Copy, and then in the top-right corner of the page, select the database.

The table of copying processes with the following columns will be displayed:

  • PID: The ID of the backend.

  • State: The state of the backend.

    For more information about possible values, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the state column).

  • Wait event: The name and type of the wait event for the backend.

    For more information, refer to the official Postgres Pro documentation on the pg_stat_activity module (see the wait_event_type and wait_event columns).

  • Database: The name of the database where copying is being performed.

  • User: The name of the DBMS user under which copying is being performed.

  • Table: The name or ID of the table being copied.

  • Query: The text of the current or last backend query.

  • Query duration: The duration of the current or last backend query.

  • Table size: The size of the table being copied.

  • Table tuples total: Rows of the table being copied.

    This column includes additional information:

    • Tuples processed: The number of processed table rows.

    • Tuples excluded: The number of excluded table rows.

  • Source file size: The size of the file being copied.

  • Done, %: The completion percentage of copying.

    This column includes additional information:

    Processed: The size of the processed data.

  • Command: The text of the copying command.

  • Command type: The type of the copying command.

  • Actions.

    For more information about available actions, refer to other instructions in this section.

Canceling a Copying Process

Canceling a copying process does not terminate a user session.

To cancel a copying process:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Copy, and then in the top-right corner of the page, select the database.

  5. Click Cancel current request next to the copying process.

  6. Click Execute.

Terminating the User Session for a Copying Process

When the user session is terminated, the copying process is canceled automatically.

To terminate the user session for a copying process:

  1. In the navigation panel, go to Infrastructure → Instances.

  2. Click the name of the instance.

  3. In the navigation panel, go to Activity.

  4. Select Copy, and then in the top-right corner of the page, select the database.

  5. Click Cancel session next to the copying process.

  6. Click Execute.

Prev Up Next
5.17. Alerts Home 5.19. SQL Statistics
epub pdf