"service unavailable" error calling api/v1/transactions for certain accounts in mainnet

[dalvizu]

Description

HBF reports they received a "service unavailable" error when accessing these two endpoints by visiting them in a browser. This is not reproducible 100% and appears sporadic. Here are the URLs:

https://mainnet-public.mirrornode.hedera.com/api/v1/transactions?account.id=0.0.1029172&type=credit https://mainnet-public.mirrornode.hedera.com/api/v1/transactions?account.id=0.0.41101&type=credit

These accounts are ones with large number of transactions on them (DM me for details). From DevOps side we see no spike in errors at the time they are reported on mirrornode-public. There are associated stack traces in the logs:

Time this occured:

2022-10-10 08:20:21.988 MDT

 at BoundPool.Pool.queryQuietly (file:///opt/restapi/utils.js:1267:13)
at runMicrotasks (<anonymous>)
at processTicksAndRejections (node:internal/process/task_queues:96:5)
at async getTransactions (file:///opt/restapi/transactions.js:583:28)
at async wrappedMiddleware (/opt/restapi/node_modules/@awaitjs/express/index.js:64:9) {
dbErrorMessage: 'canceling statement due to statement timeout',
isConnectionError: false

See a similar error around this time for 'canceling statement due to conflict with recovery'.

Steps to reproduce

1. Visit these URLS:

   https://mainnet-public.mirrornode.hedera.com/api/v1/transactions?account.id=0.0.1029172&type=credit
   https://mainnet-public.mirrornode.hedera.com/api/v1/transactions?account.id=0.0.41101&type=credit

2. Be particularly unlucky and observe the errors

Additional context

No response

Hedera network

mainnet

Version

N/A

Operating system

macOS

[xin-hedera]

Both accounts have over several tens of millions crypto transfer records, account 0.0.41101 looks to have more than 100 million. Among those, the number of credit crypto transfers are around 10 and happened at the very beginning timestamp wise.

The REST API query would generate a SQL statement to look for those 10 credit crypto transfer transactions with the default descending order on consensus timestamp. The database is very slow for such query based on the indexes and the entity id stats just because the amount of data it has to scan.

Once we move to a distributed SQL database, we can add indexes to accelerate such queries at a relative low cost.