Skip to main content

Waterfall: RCA - World Market Message Details Reporting Issue

MC Admin

Title

Waterfall: RCA - World Market Message Details Reporting Issue

Information

TITLE: World Market Message Details Reporting Issue

DATE, TIME, AND DURATION

START TIME: 10/27/2023. RESOLUTION TIME: 1/16/2024 DURATION: 2M 20D

OVERVIEW

On October 30th, Kyle Marvin the database architect at RRD sent an email out to Upland about a Message details report file that was sent and had missing data and the formatting was off.

Columns missing: SHORT URL / LONG URL / TOTAL CLICKS / USER AGENT FIELDS.

The report continued to intermittently be missing these columns.

As this is an automated process. Our services team discovered the issue on Tuesday, October 31st, and started working to correct it with our development team.

When the Mmessage details report was generated, it required short URL data from the iSshortener services. During the days when the data was large, the iSshortener service took a lot of time to return the data and timed. Therefore, the Mmessage details report service couldn't populate or add short URL columns in the report.

ROOT CAUSE & REMEDIATION

ROOT CAUSE:

    • The join operation was unable to use the index in the iSshortener query due to a collation mismatch, causing the database to perform a full scan. This became an expensive query and ran for a few minutes to hours.
    • The default timeout was set as 60 seconds at different nodes.

REMEDIATION PLAN:

    • Our development team has matched the collation between tables and columns to ensure the join query uses the index.
    • Ppagination was added in the iSshortener API and the platform to send page requests. This has made each query lighter on the database and service.
    • The time-out option was modified to 600 seconds at the load balancer, reverse proxy, and JDBC connection. By doing so, any query that runs beyond 1 minute won't cause a connection/socket timeout.
    • The development team has allocated additional memory for the JVM running job server to 50 GB.

PREVENTION OF FUTURE RECURRENCE:

    • Our development team will ensure indexes are used in expensive queries.
    • The team will be actively monitoring the database for any long-running queries.

Our Remediation plan will continue to sustain performance improvements.

  • RCA ISSUE ID: [WF-9426]

Keywords RCA cpwm world market mdr message details reporting

Solution ID

240227130745117

Last Modified Date

02/27/2024 02:34:43 PM

Renew Date

02/27/2025

Status

Published

Author

tomar@uplandsoftware.com

Taxonomy

  • Waterfall > RCA-Root Cause Analysis

Collections

  • Waterfall Support

Go to Original Solution

Upland RightAnswers Portal - Version 2024R2

Related articles