search

hashgraph / hedera-mirror-node

Hedera Mirror Node archives data from consensus nodes and serves it via an API
Apache License 2.0
117 stars 110 forks source link

Optimize transactions Rest API filtering #8219

Open xin-hedera opened 1 month ago

xin-hedera commented 1 month ago

Problem

With billions of transactions and all the different combinations of transactions filtering, the rest api times out more frequently. The underlying problem is when the filtering ends up to pick matching transactions from very few among billions and the those transactions are placed far away in time space, lots of data needs to be loaded from disk, thus timing out.

Solution

Research different ways including but not limited to high scalable high performance index types, custom indexing strategy, different database just for transaction filtering, and etc.

Alternatives

No response

### Tasks
- [ ] https://github.com/hashgraph/hedera-mirror-node/issues/4642
- [ ] https://github.com/hashgraph/hedera-mirror-node/issues/4685
- [ ] https://github.com/hashgraph/hedera-mirror-node/issues/6627
- [ ] https://github.com/hashgraph/hedera-mirror-node/issues/6825
- [ ] https://github.com/hashgraph/hedera-mirror-node/issues/7860
- [ ] https://github.com/hashgraph/hedera-mirror-node/issues/8101
kasey-alusi-vcc commented 3 weeks ago

We've been running into this with our internal nodes as well. As an example: api/v1/transactions?account.id=0.0.3286580&limit=1&transactiontype=ETHEREUMTRANSACTION - this is pretty fast and works, but increasing limit to even 10 (as noted above) times out:

2024-06-05T20:43:03.855Z ERROR 10dfd50f ::ffff:10.159.0.135 GET /api/v1/transactions?account.id=0.0.3286580&limit=10&transactiontype=ETHEREUMTRANSACTION in 20107 ms: 503 Service unavailable canceling statement due to statement timeout

None of the typical infra-related bottlenecks are present from our monitoring. CPU/Mem etc on both the DB servers and mirror node microservices are fine, and the deployment is handling 10s of other, unrelated requests concurrently (and with reasonable latency).

Neurone commented 2 weeks ago

Hi @kasey-alusi-vcc, those timeouts are set on the mirror node side, so you can tweak them if you prefer to offer longer waiting time for more resource-consuming queries.

In particular, I suggest looking at hedera.mirror.grpc.db.statementTimeout, hedera.mirror.restJava.db.statementTimeout, and hedera.mirror.web3.db.statementTimeout, which are all set by default to 10 seconds.

These configurations don't increase or optimize query's performance, but they offers a workaround for specific contexts like yours.

You can find other interesting timeouts in the configuration doc.