Transaction statistics
Job details
| Name: |
Transaction statistics |
| Platform: |
Postgres |
| Category: |
Performance |
| Description: |
Gathers information on commits and rollbacks in the databases and generates statistics. |
| Long description: |
Task gathers information about commits and rollbacks in the database and generates statistics. |
| Version: |
1.8 |
| Default schedule: |
0,20,40 * * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘postgres’]/.[hasengine=‘YES’] |
Parameters
| Name |
Default value |
Description |
| keep data for |
7 |
The number of days to keep the data for. |
| ignore database |
template0, template1 |
Databases you which to ignore both in gathering of statistics and displaying in report |
Job Summary
- “Purpose”: The purpose of this job is to monitor and report on transactions within PostgreSQL databases, specifically focusing on commits and rollbacks. It captures and retains data over the specified period to help in analyzing trends in database transaction performance.
- “Why”: This job is important as it allows database administrators to understand transaction patterns and identify any irregularities or issues in committing and rolling back transactions, which could affect the performance and reliability of the database.
- “Manual checking”: To manually check the transaction statistics, the following SQL commands can be used:
SELECT datname, commits, rollbacks FROM pg_stat_database WHERE datname NOT IN (SELECT datname FROM dbw_transaction_statistics_ignore);
Implementations and Implementations Details
- The job involves several SQL and PL/pgSQL commands to create necessary tables and a function to process the transaction statistics:
- Tables like dbw_transaction_statistics_histr and dbw_transaction_statistics_ignore are created to store historical data of transactions and to manage the databases that should not be included in the monitoring.
Dependencies and Cleanup
- The job has dependencies on its own tables and functions to ensure historical data is correctly recorded and the current data processing is performed on the latest available information.
- “Cleanup-on-fail” is set to true for all dependencies, meaning that if an operation fails, associated interim data gets cleaned up to avoid corruption or inaccuracies.
Scheduling and Execution
- “Default Schedule”: The job is scheduled to run every 20 minutes as represented in the XML data as “0,20,40 * * *”, although the provided report-template cites a different schedule (hourly). This discrepancy should be aligned based on actual needs.
Reporting Structure
- The job comes equipped with a report-template that includes two presentations:
- “Commits per minute”: This report element uses a line chart to depict the average number of commits per minute for each recorded history date.
Versioning and Upgrades
- Detailed upgrade paths have been provided for versions from 1.3 to 1.7 to ensure that the functionality remains consistent and up-to-date across various releases of this job.
This job setup offers a comprehensive approach to monitoring transaction statistics effectively in PostgreSQL environments, ensuring high levels of data integrity and performance understanding.