SSG query slowdown - Throughput Quota Assertion - Counters table

Products

• CA API Gateway

Issue/Introduction

The client experienced an underperforming query.
It has a high impact for the GW API.

 this is a gw 10.1 Kubernetes (AKS) , 
- customer declared, they truncated the counter table (27Millions to 0 rows) but they said the slowness from API responses continues after that.
   This indicate something extra be involved to produce API response slow with an empty table.

 

Environment

Release : 10.1

Cause

High quantity of rows in counter table and scalability on Throughput Quota Assertion properties set on consistency mode.

Resolution

1) to maintain "counters" table more light (less rows on it) 
- if you are using only minute based, then probably can just do a purge every hour. 
- Worst case scenario is once purge, the requester can pass 3 more times. So, Rows on table added and increasing
- if you have more like monthly counter, then needs to delete from counters where counters.lastupdate<timestamp (something like that)
example to clean for MONTH :
 
  DELETE FROM counters 
    WHERE last_update / 1000 < UNIX_TIMESTAMP(NOW() - INTERVAL ${​​​​​​​​timeFrame}​​​​​​​​ MONTH) 
    LIMIT ${​​​​​​​​limitValue}​​​​​​​​

2) In revision found 3 configurations which had set the scalability in "consistency" mode . Changed scalability to a middle range then started to work without slowness