APIs Slowdown & Receipt creation issue (RCA)

Owned by Nithin DV (Unlicensed)
Last updated: Apr 23, 2019

Date
Team DevOps
Participants Gajendran C (Unlicensed) Dhananjay Singh Ghanshyam Rawat Abhishek Jain Nithin DV (Unlicensed) Tarun Lalwani Ranjeet Vimal

Background of the problem:

  1. During the March 2019 peak traffic multiple issues were raised by PMIDC related to long response times of services and few errors (due to a combination of the asynchronous nature of our APIs and the UI not handling these well).
  2. DB CPU utilisation was being maxed out due to long running queries; alerts for the same were received via AWS RDS SNS.

How did we react:

  1. Analyzed AWS RDS utilisation graphs to derive possible patterns of high utilization, which helped us narrow down the key hour wherein we had the highest utilization.
  2. Based on the metrics above we dug deep to analyze API response times and detailed trace of services using our distributed tracing setup, Jaeger. 
  3. Several APIs were taking ~20-40s with majority of the time being spent querying the database; on analyzing the queries it was clear that they were not running optimally. 

What was done as part of the exercise:

  1. Long running queries were analyzed using Postgres tools, and fixed by adding necessary indices on UAT then on PROD.
  2. Further, modules were analyzed and indices were added on all commonly searched columns.
  3. Monitoring was increased in an attempt to stay on top of such situations.

Impact of the exercise:

      What went well:

  • API response times improved from ~20-40s to ~2s, this resulted in great performance improvement at every service also as a whole system, graphs attached below.
  • Random errors which occurred due to slow asynchronous persistance of data was also resolved.
  • AWS RDS utilisation was no longer hitting 100% and remained well within limits, graphs depicting max and average CPU usage attached below.

      What went wrong:

  • None 

 Retrospective

  • Code review queries thoroughly before moving them to production.
  • Add indices to all commonly searched columns, if possible, automate this.
  • Invest in alerting so that we're aware of such situations and act accordingly rather than depending on our users to bring this to our notice.
  • Analyse pros and cons of turning critical APIs such as billing and collection synchronous.
Start doing Stop doing Keep doing
  • Code review queries thoroughly before moving them to production.
  • Add indices to all commonly searched columns, if possible, automate this.
  • Invest in alerting so that we're aware of such situations and can act swiftly rather than depending on our users to bring this to our notice.
  • Invest in database query monitoring at RDS or DB level.
  • Do some sort of basic performance testing to reduce such scenarios on production.
  • DB queries check-in without review.
  • Hurried changes to production.
  • Distributed tracing which has helped us resolve issues quickly.
  • Keep check on DB utilisation.

Action items