Test DML-DDL performance
Job details
| Name: | Test DML-DDL performance |
| Platform: | Sybase |
| Category: | Performance |
| Description: | Runs performance test on database.The procedure executes SELECT, INSERT, UPDATE and DELETE statements on dbwatch engine schema test tables. |
| Long description: | Runs performance test on database.The procedure executes SELECT, INSERT, UPDATE and DELETE statements on dbwatch engine schema test tables. |
| Version: | 1.2 |
| Default schedule: | 10 22 5 1,30 |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sybase’]/instance[maj_version > ‘11′ & hasengine=‘YES’] |
Parameters
| Name | Default value | Description |
|---|---|---|
| records number | -1 | Number of rows to insert into the test table. |
Job Summary
- Purpose: The purpose of this job is to assess database performance by conducting DML (Data Manipulation Language) and DDL (Data Definition Language) operations on test tables within the dbWatch engine schema.
- Why: This job is crucial for evaluating the efficiency of SQL operations including SELECT, INSERT, UPDATE, DELETE, and TRUNCATE. It allows DBAs to understand performance impacts under varying loads, and helps in ensuring that the database operations meet the required standards and performance thresholds. Inadequate performance can lead to slow query responses and potentially impact business operations.
- Manual Checking: You can check this manually in the database by issuing these SQL commands:
-- Replace the "task_id" value with your job's specific task_id
SELECT *
FROM dbw_performance
WHERE task_id = 'your_specific_task_id';Implementation Details
Components Created:
- Tables:
- dbw_test_tab: Used to perform test DML operations.
- dbw_performance and dbw_performance_histr: Stores performance metrics for each operation type and historical performance data.
- Procedures:
- dbw_dml_perf_test: Manages individual DML and DDL performance tests based on threshold row configurations.
- dbw_dml_min_test and dbw_dml_max_test: Handle minimal and maximal row testing scenarios respectively.
- dbw_dml_performance: Central procedure that determines which test scenario to execute based on specified parameters.
Cleanup on Failure:
- If any part of the job fails, the following SQL objects are slated for deletion:
- drop table dbw_test_tab
- drop table dbw_performance
- drop procedure dbw_dml_performance
Dependencies
- Dependencies are mainly on generated procedures and tables necessary for performance tests. Each associated with cleanup actions on task failure to ensure no residue corrupts subsequent tests.
Activation and Control
- Default Scheduling: Job is scheduled to run automatically to ensure continuous monitoring.
- Version Control: Upgraded from version 1.1 to version 1.2 due to enhancements in procedure and parameter handling.
Reporting Template
- Report Title: DML/DDL Performance Test
- Description: Provides a template based on performing SQL DML and DDL operations, distinctly analyzing the performance of SELECT, INSERT, UPDATE, DELETE, and TRUNCATE operations on database test tables.
- Scheduled: Set to run periodically to generate up-to-date performance reports.
Version Upgrade
- Upgrade instructions focus on ensuring the necessary tables and procedures are in place and up-to-date, removing any outdated components, and updating task parameters as required.
- Ensures that the system meets minimum requirements for the version change to facilitate correct operation post upgrade.
| Component | Action |
|---|---|
| Tables and Procedures | Check existence; Create or Replace as necessary |
| Task Parameters | Update description and default values |
| Dependency Check | Confirm required objects exist and are operationally integrated |
Job Performance Metrics
This job helps in tracking several performance matrices, such as the time taken for each type of SQL command and the behavior under different loads, which can be critical data for tuning the database.