Postgres Pro Enterprise Manager : Documentation: 2: 5.23. Profiler : Postgres Professional

5.23. Profiler
Prev	Up	Chapter 5. Using PPEM	Home	Next

5.23. Profiler #

5.23.1. Profiler Servers
5.23.2. Samples
5.23.3. Viewing Profiler Graphs
5.23.4. Sampling Schedules
5.23.5. Reports

Profiler is an interface to the pgpro_pwr module. With profiler, you can build detailed reports on database performance. Reports are based on samples of database data and cover the specified periods of time.

To build a report, take at least two samples. The samples are placed in the directory of the profiler server. Before taking samples, prepare profiler servers and create them in the web application.

You can create schedules that allow taking samples automatically at the specified time interval. Such schedules also allow taking a sample for the specified date and time.

5.23.1. Profiler Servers #

This section explains how to manage profiler servers. It includes the following instructions:

Creating a Profiler Server

Before performing this instruction:

Create an instance.
Create a database.
Prepare a profiler server.

To create a profiler server:

Go to profiler servers in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler → Servers.
  2. From Instance, select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Servers.
In the top-right corner of the page, click Create server.
Enter parameters of the new profiler server (parameters marked with an asterisk are required):
- Database: The database for which the profiler server will take samples.
- Server name.
- Description.
- Connection string: The connection string for the profiler server.
- Enabled: Specifies whether the profiler server is enabled.
  Possible values:
  - yes
  - no
- Retention period (days): The number of days during which samples can be stored on the profiler server.
  Samples stored longer than the specified period are deleted.
Click Save.

Viewing Profiler Servers

Go to profiler servers in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler → Servers.
  2. From Instance, select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Servers.
From Database, select the database.
Click Select.

The table of profiler servers with the following columns will be displayed:

Name.
Description.
Connection string: The connection string for the profiler server.
Enabled: Specifies whether the profiler server is enabled.
Possible values:
- yes
- no
Retention period: The number of days during which samples are stored.
Samples stored longer than the specified period are deleted.
Actions.
For more information about available actions, refer to other instructions in this section.

Editing a Profiler Server

Go to profiler servers in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler → Servers.
  2. From Instance, select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Servers.
From Database, select the database.
Click Select.
Click next to the profiler server.
Edit profiler server parameters.
Click Save.

Deleting a Profiler Server

Important

Deleted profiler servers cannot be restored.

Deleting a profiler server also removes all associated samples and disables sampling schedules that place samples to this server directory.

To delete a profiler server:

Go to profiler servers in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler → Servers.
  2. From Instance, select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Servers.
From Database, select the database.
Click Select.
Click next to the profiler server.
Click Delete.

5.23.2. Samples #

This section explains how to take and view samples.

Taking a sample

Before performing this instruction:

To take a sample:

Go to samples in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler → Samples.
  2. From Instance, select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Samples.
In the top-right corner of the page, click Take snapshot.
Enter parameters of the new sample (parameters marked with an asterisk are required):
- Database: The database for which the sample will be taken.
- Server: The profiler server whose directory will be used to store the sample.
- skip_sizes: Specifies whether to skip the collection of relation sizes when taking the sample.
  Possible values:
  - true: The collection of relation sizes will be skipped when taking the sample.
  - false: The collection of relation sizes will not be skipped when taking the sample.
Click Save.

Viewing Samples

Go to samples in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler → Samples.
  2. Select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Samples.
From Database, select the database.
From Server, select the profiler server.
Click Select.

The table of samples with the following columns will be displayed:

ID: The unique identifier (ordinal number) of the sample.
Time: The date and time when the sample was created.
Sizes were collected: Specifies whether relation sizes were collected when taking the sample.
Possible values:
- yes
- no
Database stat reset: Specifies whether database statistics were reset when taking the sample.
Possible values:
- yes
- no
Bgwriter stat reset: Specifies whether bgwriter statistics were reset.
Possible values:
- yes
- no
Archiver stat reset: Specifies whether archiver statistics were reset.
Possible values:
- yes
- no

5.23.3. Viewing Profiler Graphs #

Before performing this instruction:

To view profiler graphs:

Go to profiler graphs in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler.
  2. From Instance, select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler.
From Database, select the database.
From Server, select the profiler server.
(Optional) In Period, specify the time period.
If you do not specify a value, the profiler graphs are displayed for the entire time range.
Click Select.

The following profiler graphs will be displayed:

PostgreSql Instance: tuples: Operations with rows per second in the database. Available metrics:
- Tuples returned: The number of live rows fetched by sequential scans and index entries returned by index scans.
  This metric corresponds to the tup_returned column of the pg_stat_database view.
- Tuples fetched: The number of live rows fetched by index scans.
  This metric corresponds to the tup_fetched column of the pg_stat_database view.
- Tuples inserted: The number of rows inserted by queries.
  This metric corresponds to the tup_inserted column of the pg_stat_database view.
- Tuples updated: The number of rows updated by queries.
  This metric corresponds to the tup_updated column of the pg_stat_database view.
- Tuples deleted: The number of rows deleted by queries.
  This metric corresponds to the tup_deleted column of the pg_stat_database view.
For more information about these metrics, refer to the official Postgres Pro documentation on the pg_stat_database view.
PostgreSql bgwriter buffers: Operations with buffers per second in the database. Available metrics:
- Checkpoints buffers written: The number of buffers written during checkpoints and restartpoints.
  This metric corresponds to the buffers_written column of the pg_stat_checkpointer view.
- Background buffers written: The number of buffers written by the background writer.
  This metric corresponds to the buffers_clean column of the pg_stat_bgwriter view.
- Backend buffers written: The number of buffers written directly by the backend.
  In Postgres Pro 16, this metric corresponds to the buffers_backend column of the pg_stat_bgwriter view. In Postgres Pro 17, this metric is collected by the pg_stat_io view.
- Number of buffers allocated: The number of allocated buffers.
  This metric corresponds to the buffers_alloc column of the pg_stat_bgwriter view.
For more information about these metrics, refer to the official Postgres Pro documentation on the pg_stat_bgwriter, pg_stat_io, and pg_stat_checkpointer views.
PostgreSql bgwriter write/sync: Synchronization and writing operations with buffers per second in the database. Available metrics:
- Bgwriter interrupts: The number of times the background writer stopped a cleaning scan due to writing too many buffers.
  This metric corresponds to the maxwritten_clean column of the pg_stat_bgwriter view.
- Backend fsync count: The number of times the backend executed its own fsync call. Normally, these calls are executed by the background writer, even when the backend performs its own writes.
  In Postgres Pro 16 or lower, this metric corresponds to the buffers_backend_fsync column of the pg_stat_bgwriter view. In Postgres Pro 17, this metric is collected by the pg_stat_io view.
For more information about these metrics, refer to the official Postgres Pro documentation on pg_stat_bgwriter and pg_stat_io.
PostgreSql checkpoints count: Operations with checkpoints per second in the database. Available metrics:
- Scheduled checkpoints: The number of scheduled checkpoints that were completed due to timeout. Scheduled checkpoints can be skipped if the server has been idle since the last checkpoint. Both completed and skipped scheduled checkpoints are counted.
  This metric corresponds to the num_timed column of the pg_stat_checkpointer view.
- Requested checkpoints: The number of requested checkpoints that were completed.
  This metric corresponds to the num_requested column of the pg_stat_checkpointer view.
For more information about these metrics, refer to the official Postgres Pro documentation on the pg_stat_checkpointer view.
PostgreSql checkpoints write/sync: The time per second spent writing and synchronizing files during checkpoints in the database. Available metrics:
- Checkpoint write time (s): The time in seconds spent writing files to disk while completing checkpoints and restartpoints.
  This metric corresponds to the write_time column of the pg_stat_checkpointer view.
- Checkpoint sync time (s): The time in seconds spent synchronizing files to disk while completing checkpoints and restartpoints.
  This metric corresponds to the sync_time column of the pg_stat_checkpointer view.
For more information about these metrics, refer to the official Postgres Pro documentation on the pg_stat_checkpointer view.
PostgreSql Instance: events: Operations with events per second in the database. Available metrics:
- Conflicts: The number of queries canceled due to conflicts with recovery. Conflicts can occur only on standby servers.
  This metric corresponds to the conflicts column of the pg_stat_database view.
  For more information about conflicts, refer to the official Postgres Pro documentation on the pg_stat_database_conflicts view.
- Deadlocks: The number of deadlocks.
  This metric corresponds to the deadlocks column of the pg_stat_database view.
- Rollbacks: The number of rolled back transactions.
  This metric corresponds to the xact_rollback column of the pg_stat_database view.
- Commits: The number of committed transactions.
  This metric corresponds to the xact_commit column of the pg_stat_database view.
For more information about these metrics, refer to the official Postgres Pro documentation on the pg_stat_database view.
PostgreSql: cache hit ratio: This graph displays the Cache hit ratio metric that provides the percentage of the data received from the buffer cache per second in the database.
This metric is based on the blks_hit and blks_read columns of the pg_stat_database view and is calculated as follows:
blks_hit / (blks_hit + blks_read)
For more information about the blks_hit and blks_read columns, refer to the official Postgres Pro documentation on the pg_stat_database view.
PostgreSql temp: bytes written: This graph displays the Bytes written metric that provides the amount of data written to temporary files by queries per second in the database. All temporary files are counted, regardless of why they were created and of the log_temp_files parameter value.
This metric corresponds to the temp_bytes column of the pg_stat_database view.
For more information about this metric, refer to the official Postgres Pro documentation on the pg_stat_database view.
PostgreSql temp: files created: This graph displays the Number of files metric that provides the number of temporary files created by queries per second in the database. All temporary files are counted, regardless of why they were created (sorting, hashing) and of the log_temp_files parameter value.
This metric corresponds to the temp_files column of the pg_stat_database view.
For more information about this metric, refer to the official Postgres Pro documentation on the pg_stat_database view.
PostgreSql archive command: Archiving operations with WAL files per second in the database. Available metrics:
- WAL segments archived: The number of successfully archived WAL files.
  This metric corresponds to the archived_count column of the pg_stat_archiver view.
- WAL segments archive failed: The number of failed attempts to archive WAL files.
  This metric corresponds to the failed_count column of the pg_stat_archiver view.
For more information about these metrics, refer to the official Postgres Pro documentation on the pg_stat_archiver view.
PostgreSQL WAL write speed: This graph displays the WAL generated metric that provides the amount of WAL in bytes generated per second in the database.
This metric corresponds to the wal_bytes column of the pg_stat_wal view.
For more information about this metric, refer to the official Postgres Pro documentation on the pg_stat_wal view.

You can perform the following actions using the icons in the top-right corner of the planner graphs:

To build a report using a graph, click Select the period for the report.
For more information about building a report, refer to Building a Report.
To reset the selected time period when building a report, click Reset.
To download the graph in the PNG format, click Save as Image.

5.23.4. Sampling Schedules #

This section explains how to manage sampling schedules. It includes the following instructions:

Creating a Sampling Schedule

Before performing this instruction:

To create a sampling schedule:

Go to sampling schedules in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler → Schedule.
  2. From Instance, select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Schedule.
In the top-right corner of the page, click Create task.
Enter parameters of the new sampling schedule (parameters marked with an asterisk are required):
- Name.
- Set cron-string execution: Allows specifying the time interval for taking samples in the crontab format.
  If you turn on this toggle, in Execution, enter the string.
- Task planning: The type of the sampling schedule.
  Possible values:
  - Time-delayed: One sample will be created at the specified date and time.
  - On schedule: Samples will be taken at the specified time interval.
    For this value, specify the following parameters:
    Interval: The unit of measurement of the time interval.
    Possible values:
    Minutes
    Hours
    Days
    Repeat every: The time interval for taking samples by minutes or hours.
    This parameter is available only if you select Minutes or Hours from Interval.
    Execution days: The days when samples will be taken.
    Cron total line: The string in the crontab format that specifies the time interval for taking samples.
    The value is entered automatically.
  This parameter is available only if you turn off Set cron-string execution.
- Time: The date and/or time when samples will be taken.
  This parameter is available only if you select Time-delayed from Task planning or Days from Interval.
- Start and Repeat until: The start and end date and time for taking samples.
  These parameters are available only if you turn on Set cron-string execution or select On schedule from Task planning.
- Database: The database for which the sample will be taken.
- Server: The profiler server whose directory will be used to store the sample.
- skip_sizes: Specifies whether to skip the collection of relation sizes when taking the sample.
  Possible values:
  - true: The collection of relation sizes will be skipped when taking the sample.
  - false: The collection of relation sizes will not be skipped when taking the sample.
Click Save.

Viewing Sampling Schedules

Go to sampling schedules in one of the following ways:

Using the monitoring section:
1. In the navigation panel, go to Monitoring → Profiler → Schedule.
2. From Instance, select the instance.
Using the configuration section of the specified instance:
1. In the navigation panel, go to Infrastructure → Instances.
2. Click the name of the instance.
3. In the navigation panel, go to Profiler → Schedule.

The table of sampling schedules with the following columns will be displayed:

Task: The unique name of the sampling schedule.
Instance: The instance where the sampling schedule is created.
Database: The database for which samples are taken.
Schedule: The string in the crontab format that specifies the time interval for taking samples.
User: The user that created the sampling schedule.
Actions.
For more information about available actions, refer to other instructions in this section.

Editing a Sampling Schedule

Go to sampling schedules in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler → Schedule.
  2. From Instance, select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Schedule.
Click → Edit next to the sampling schedule.
Edit sampling schedule parameters.
Click Save.

Executing a Sampling Schedule

You can manually execute a sampling schedule to instantly start taking samples.

To execute a sampling schedule:

Go to sampling schedules in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler → Schedule.
  2. From Instance, select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Schedule.
Click → Execute next to the sampling schedule.

Activating and Deactivating a Sampling Schedule

You can deactivate a sampling schedule to temporarily stop taking samples. Sampling schedules are activated by default.

To deactivate or activate a sampling schedule:

Go to sampling schedules in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler → Schedule.
  2. From Instance, select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Schedule.
Click → Deactivate or Activate next to the sampling schedule.

Deleting a Sampling Schedule

Important

Deleted sampling schedules cannot be restored.

When you delete a sampling schedule, the associated samples are not deleted.

To delete a sampling schedule:

Go to sampling schedules in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler → Schedule.
  2. From Instance, select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Schedule.
Click → Delete next to the sampling schedule.
Click Delete.

5.23.5. Reports #

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

It is recommended to view available profiler graphs first.

Building a Report

Before performing this instruction:

To build a report:

Go to building a report in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler → Reports generating.
  2. From Instance, select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Reports generating.
Enter parameters of the new report (parameters marked with an asterisk are required):
- Database: The database for which the report will be built.
- Server: The profiler server whose directory contains samples.
- Report: The format of the time period that the report will cover.
  Possible values:
  - By snapshot: The report will cover the time period between two samples.
  - By time: The report will cover the time period between two dates.
- Type.
  Possible values:
  - Standard: Provides the database load statistics for a specified time period.
  - Differential: Provides comparative database load statistics for two specified time periods.
- Interval or Interval 1 and Interval 2: The time period covered by the report.
  The period format depends on the value selected in Report:
  - If you select By snapshot, specify the first and last samples.
  - If you select By time, specify the start and end date and time.
Click Generate report.

Building a Report Using a Profiler Graph

You can also build a report when viewing profiler graphs. In this case, the time period between two samples cannot be used as the time period that the report will cover.

Before performing this instruction:

To build a report using a graph profiler:

Go to profiler graphs in one of the following ways:
- Using the monitoring section:
  1. In the navigation panel, go to Monitoring → Profiler.
  2. From Instance, select the instance.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler.
From Database, select the database.
From Server, select the profiler server.
(Optional) In Period, specify the time period.
If you do not specify a value, the profiler graphs are displayed for the entire time range.
Click Select.
In the top-right corner of the profiler graph, click Select the period for the report.
Click and drag on the profiler graph to select the time period for the report.
(Optional) To include comparative database load statistics for two specified time periods in the report, click Select another period and repeat step 7.
Click Generate report.

Viewing All Built Reports

To view all built reports:

Go to reports in one of the following ways:

Using the monitoring section:
In the navigation panel, go to Monitoring → Profiler → Reports.
Using the configuration section of the specified instance:
1. In the navigation panel, go to Infrastructure → Instances.
2. Click the name of the instance.
3. In the navigation panel, go to Profiler → Reports.

The table of reports with the following columns will be displayed:

Instance: The instance where the report is built.
Server: The profiler server whose directory contains samples.
Database: The database for which the report is generated.
Status.
Possible values:
- done: The report is built.
- pending: The report is being built.
- error: An error occurred while building a report.
Period: The time period covered by the report.
Execution start and Execution end: The start and end date and time of report building.
User: The user that built the report.
Actions.
For more information about available actions, refer to other instructions in this section.

Viewing and Downloading a Report

Go to reports in one of the following ways:
- Using the monitoring section:
  In the navigation panel, go to Monitoring → Profiler → Reports.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Reports.
Perform one of the following actions:
- To view the report, click next to it.
  For more information about the report contents, refer to the official Postgres Pro documentation.
- To download the report, in the top-right corner of the page, click Download report.

Deleting a Report

Important

Deleted reports cannot be restored.

To delete a report:

Go to reports in one of the following ways:
- Using the monitoring section:
  In the navigation panel, go to Monitoring → Profiler → Reports.
- Using the configuration section of the specified instance:
  1. In the navigation panel, go to Infrastructure → Instances.
  2. Click the name of the instance.
  3. In the navigation panel, go to Profiler → Reports.
Click next to the report.
Click Delete.

Prev	Up	Next
5.22. Extensions	Home	5.24. Authentication

epub pdf