Claimable Balances Query slow on Horizon

[jcx120]

Issue:

Claimable Balances query performing slow on SDF Horizon instance sporadically (50% of the time):

Query: https://horizon.stellar.org/claimable_balances/{balance_id}/transactions?limit=1&order=asc

Issue: This query is being used by a Customer to locate a transaction which created a claimable balance and retrieve its memo (since they can’t store a memo with claimable balance itself). While this call was not terribly fast, it got really bad this week, with the call taking approximately 8 seconds to either complete or error out with the following:

{

  "type": "https://stellar.org/horizon-errors/service_unavailable",

  "title": "Service Unavailable",

  "status": 503,

  "detail": "The request cannot be serviced at this time."

}

It works about 50% of the time with a 503 occurring the other 50%.

Sample URL:

https://horizon.stellar.org/claimable_balances/0000000028b32c721976af53fa0419eba129f3da70168f1e39822a25886375abad9599da/transactions?limit=1&order=asc

Curious if the database optimization changes in Core 19 may have impacted this or if anyone has observed issues with this query. Also, is there an easier way to get original transaction which created claimable balance?

Note that this query returns pretty fast on an ingestion filtered instance.

[sreuland]

Hello @jcx120 , we checked out the performance on this query.

Related to observations on more 503's, this looks like it lines up, one ticket included on changes in the 2.17.1 release was #4390 - a sql statement max timeout gets applied to all sql queries, it defaults to the connection timeout param which is 10s by default.

On secondary observation of slowness on the non-filtered horizon instance compared to the faster times on the filtered horizon instance, it's due to the sql query derived from the requested API call, in this case the request is 'find all tx's in history that ref'd the claimable balance id and give me the oldest', this equates to querying full range of history of tx from now and back to the earliest point, which on Prod is since genesis and equates to huge set of data for the DB to scan, compared to the filtered instance, has a very small subset of same original data and far fewer tx's in its history, so the DB can execute the same sql query in much faster time.

thanks to @sydneynotthecity for help on investigating this also.

[jcx120]

Could this be related (ie: performance degradation from accumulated unreaped CB history): https://github.com/stellar/go/issues/4396

[sreuland]

@jcx120 , this appears to be duplicate, reporting slow query response situation on cb API path as reported on #4455 , which had a PR merged for fix - https://github.com/stellar/go/pull/4477, so I think this one can be moved to closed/done same as #4455, I think it will be in next release after 2.19.0.