Test DML performance

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and evaluate the performance of DML (Data Manipulation Language) operations, such as SELECT, INSERT, UPDATE, DELETE, and TRUNCATE, on PostgresSQL instance databases with specific performance criteria.
• Why: This job is significant because it ensures that the performance standards for DML operations meet the necessary thresholds for efficient database operation. By regularly monitoring these metrics, potential performance degradation can be mitigated, ensuring the database’s responsiveness and reliability.
• Manual checking: To check this manually in the database, the following SQL commands are typically used:

SELECT, INSERT, UPDATE, DELETE, TRUNCATE

Implementation Details

```sql
CREATE TABLE dbw_test_tab(col1 int, col2 varchar(1000));
CREATE TABLE dbw_performance(counter_id int, type varchar(20), t_start time, t_end time, rows_number int, h_date date);
CREATE TABLE dbw_performance_histr (counter_id int, type varchar(20), t_start time, t_end time, duration int, avg_dur int, rows_number int, histr_date date);

CREATE OR REPLACE FUNCTION dbw_dml_perf_test (rows_no int) RETURNS void AS $func$
DECLARE /* … code here … */
END;
$func$ LANGUAGE ‘plpgsql’ VOLATILE;
```

Job Execution

The job is scheduled to run at specific intervals (not defined within the provided XML but probably managed through job scheduling tools in dbWatch). It checks the performance of different DML operations across multiple record counts (from 5,000 to 160,000 records), thereby providing a comprehensive view of how the database performs under various loads.

Expected Output and Report

• “Performance statistics” that includes various metrics such as the minimum, average, and maximum times for each DML operation across different tests.
• Output is configured to generate reports containing detailed information about DML performance across several record numbers, aiding in quick visualization and analysis.
• Exposes performance data through charts and result tables that are useful for identifying trends and potential performance bottlenecks in database management activities.

Dependencies and Clean-up

Insurance is in place to clean up after failed operations involving any dependent objects like tables or procedures specified within the job definition (dbw_test_tab, dbw_performance, etc.). This safeguards against leftover artifacts that could otherwise clutter the system or influence subsequent tests inaccurately.

Administration and Monitoring

Because this job involves detailed logging and dependency management, it is a critical part of database health monitoring strategies, particularly for environments where DML performance directly correlates with user experience or system stability. The automatic handling of tasks through dbWatch enhances robust and consistent performance oversight.