Troubleshooting
Problem
The inability to identify and address Key Performance Indicators (KPIs) that affect database performance hinders the ability to detect and resolve issues that compromise database functionality.
Robust monitoring techniques are essential to ensure good database functionality, as this oversight can result in system inefficiencies and increased risk of operational interruptions.
Diagnosing The Problem
To facilitate efficient diagnosis and swift resolution of database performance issues, please provide the following information to the support team:
- Database Index Information:
- What indices are currently set up on your database? When were these indices last updated or set up?
- Performance Data Collection:
- In the customer_overrides.properties ensure the property is set: yfs.yfs.app.identifyconnection=Y
- This will enable the application to set contextual information (Agent, API name) on the connection on the pg_stat_activity table.
- To capture performance data, execute the script oms_postgrescollect.sh on the PostgreSQL host machine, the script will run for 15 minutes.
-
Copy the oms_postgrescollect.sh script to the PostgreSQL server. Provide appropriate read, write and execute permissions.
-
Run the script - Usage - ./oms_postgresCollect.sh <serverIP> <username> <databasename> <password>
-
This script will generate diagnostics in two directories psql_1 and psql_2, export the results of both.
-
- In the customer_overrides.properties ensure the property is set: yfs.yfs.app.identifyconnection=Y
-
Database Maintenance and Backup Schedule:
- Describe your current database maintenance schedule.
- How frequently are backups taken?
- How often are runtime statistics captured?
- Statistical Data Comparison:
- Provide records from the YFS_STATISTICS_DETAIL table from both before and after the incident.
- Logs and Core Dumps:
- Generate Java core dumps and server error logs, additional guidance on capturing this information is available
- If you are using v10 container offering, MustGather for IBM Order Management Software Certified Containers: Performance Issues.
- If you are using v10 traditional offering, MustGather for IBM Sterling Order Management: Performance Issues
- Generate Java core dumps and server error logs, additional guidance on capturing this information is available
- Information from Monitoring Tools:
- Share screenshots from your integrated monitoring tools to illustrate the issue.
- Application Overview:
- Provide a brief overview of the application affected by the database performance issues.
Submit diagnostic data to IBM Support
After you have collected the preceding information, and the case is opened, please see:
Exchanging information with IBM Technical Support
Exchanging information with IBM Technical Support
For more details see:
Additional Resources
- For more detailed instructions on monitoring your PostgreSQL database, refer to:
Resolving The Problem
Common Issues and Resolutions
- Incorrect/ineffective indices.
- Problem: Indices that are poorly designed or outdated can degrade performance.
- Resolution: Regularly review and update indices based on query performance and system demands. Consider dropping unnecessary indices and adding more appropriate ones based on query patterns.
- Missed Database maintenance causing poor performance.
- Problem: Neglecting routine database maintenance tasks can lead to performance bottlenecks.
- Resolution: Establish and adhere to a regular maintenance schedule, including updates, optimizations, and checks to ensure consistent database performance.
- Lock waits & Long running queries.
- Problem: Transactions waiting for locks and queries that take a long time to execute can slow down the entire database.
- Resolution: Monitor and analyze lock waits and long-running queries using tools like pg_stat_activity and pg_locks. Optimize query performance and adjust transaction management to reduce wait times.
Key Performance Indicators (KPIs) to Monitor
- Statement Execution Time & Slow Queries
- Monitoring: Track execution times to identify slow queries indicating issues such as heavy load or complex query structures.
- Action: Enable pg_stat_statements to collect detailed statistics on SQL execution, helping to pinpoint inefficient queries. Instructions for enabling and querying this extension can be found in the PostgreSQL documentation.
- Database Size and Utilization
- Monitoring: Monitor database size and its growth to manage resource allocation effectively.
- Action: Regularly check the database size against your system’s capacity to ensure it does not exceed performance thresholds.
- Active Connections / % of Max Connections in Use
- Monitoring: Assess the number of active connections relative to the configured maximum to understand the system load.
- Action: High usage can lead to resource contention and degraded performance. Consider scaling your setup or optimizing connection management strategies.
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB59","label":"Sustainability Software"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SS6PEW","label":"IBM Sterling Order Management"},"ARM Category":[{"code":"a8m0z000000cy01AAA","label":"Performance"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
10 May 2024
UID
ibm17147622