[API]: Slow query: epoch by block

[edd]

API Overview

In order to please clients
We will speed up the API to fetch an epoch by block, because 1 second is too slow
So that epoch data can be fetched efficiently

REST: 1.7 seconds

time curl -L -X GET 'https://api.n06.stagnet1.vega.rocks/api/v2/epoch?block=9134384' -H 'Accept: application/json'

Notes

• This API was broken pre 0.74.0-preview-11 (see [Bug]: rest API: get epoch by block height is broken #10301) but fixed in fix: get epoch by block query fixed #10489
• This ticket previously mentioned a 10s GraphQL query. This was due to migrations.

[guoguojin]

I don't think this is an API problem, when running the queries against the same node, the timing is roughly the same:

On n00:

time curl 'https://api.n00.stagnet1.vega.rocks/graphql' -X POST -H 'User-Agent: Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0' -H 'Accept: application/json, multipart/mixed' -H 'Accept-Language: en-GB,en;q=0.5' -H 'Accept-Encoding: gzip, deflate, br' -H 'Referer: https://api.stagnet1.vega.rocks/graphql/' -H 'content-type: application/json' -H 'Origin: https://api.stagnet1.vega.rocks' -H 'DNT: 1' -H 'Connection: keep-alive' -H 'Sec-Fetch-Dest: empty' -H 'Sec-Fetch-Mode: cors' -H 'Sec-Fetch-Site: same-origin' -H 'Pragma: no-cache' -H 'Cache-Control: no-cache' -H 'TE: trailers' --data-raw '{"query":"{\n  epoch(block: \"9134384\") {\n    id\n  }\n}"}'
{"data":{"epoch":{"id":"116143"}}}
real	0m9.823s
user	0m0.007s
sys	0m0.015s

time curl -L -X GET 'https://api.n00.stagnet1.vega.rocks/api/v2/epoch?block=9134384' -H 'Accept: application/json'
{"epoch":{"seq":"116143","timestamps":{"startTime":"1707482950718875000","expiryTime":"1707484750718875000","endTime":"0","firstBlock":"9133550","lastBlock":"0"},"validators":[{"id":"11cd9840d290e178d103d4337bfdbfa005f6da7b6fb45f3501a405ce749d5fac","pubKey":"6438a831c86c32683cf225f0f5fab28bc62c2000c66d86d27772a75960966612","tmPubKey":"tw21wknY6kJu0hq6EOLqGraTEgH/t/xe1F+ly6GTd8Q=","ethereumAddress":"0x0bcb473865e28d9e4f13851633da33c9064e8029","infoUrl":"https://en.wikipedia.org/wiki/Holy_Name_of_Jesus_Church_(Redlands,_California)","location":"BF","stakedByOperator":"3000000000000000000000","stakedByDelegates":"0","stakedTotal":"3000000000000000000000","maxIntendedStake":"0","pendingStake":"0","epochData":null,"status":"NODE_STATUS_VALIDATOR","delegations":[{"party":"6438a831c86c32683cf225f0f5fab28bc62c2000c66d86d27772a75960966612","nodeId":"11cd9840d290e178d103d4337bfdbfa005f6da7b6fb45f3501a405ce749d5fac","amount":"3000000000000000000000","epochSeq":"116143"}],"rewardScore":null,"rankingScore":{"stakeScore":"0.25","performanceScore":"1","previousStatus":"VALIDATOR_NODE_STATUS_TENDERMINT","status":"VALIDATOR_NODE_STATUS_TENDERMINT","votingPower":2500,"rankingScore":"0.5"},"name":"mango-honey","avatarUrl":"https://www.gravatar.com/avatar/hg0vuhx0ridl21uk2imqlgzekplbpij0?d=identicon"},{"id":"92f1db69a9a4231b427b25cd91017fb001a4d6623954c4838c89cd0917ffe1a9","pubKey":"5a1e95067a1f1004d027213458af75658cdc19691217b974079c490ed336b7aa","tmPubKey":"ZZc+4Y2hfHeHbigpSlSoEv+ZfN0SVadwOx4MgFPfxv4=","ethereumAddress":"0x54a047b392874c7a0c47aa24d81cc79d33313709","infoUrl":"https://en.wikipedia.org/wiki/La_viuda_negra_(TV_series)","location":"ZH","stakedByOperator":"3000000000000000000000","stakedByDelegates":"0","stakedTotal":"3000000000000000000000","maxIntendedStake":"0","pendingStake":"0","epochData":null,"status":"NODE_STATUS_VALIDATOR","delegations":[{"party":"5a1e95067a1f1004d027213458af75658cdc19691217b974079c490ed336b7aa","nodeId":"92f1db69a9a4231b427b25cd91017fb001a4d6623954c4838c89cd0917ffe1a9","amount":"3000000000000000000000","epochSeq":"116143"}],"rewardScore":null,"rankingScore":{"stakeScore":"0.25","performanceScore":"1","previousStatus":"VALIDATOR_NODE_STATUS_TENDERMINT","status":"VALIDATOR_NODE_STATUS_TENDERMINT","votingPower":2500,"rankingScore":"0.5"},"name":"perplexed-pond","avatarUrl":"https://www.gravatar.com/avatar/6l2jvijhg7gb6nbj2hps0abx8rgmmarh?d=identicon"},{"id":"c3423f09428da22d984dd9a43df501e73291c360ad7bfac8795739dc046cdbd0","pubKey":"399abb77e1dafb8304d3866618cb53bc00b2d578bb643630b9a8b08eefe63291","tmPubKey":"G3YPeYQ2FttOPsb+DPRBM2teUAWpBHw14o5WUj73zWo=","ethereumAddress":"0xdddfa1974b156336b9c49579a2bc4e0a7059cad0","infoUrl":"https://en.wikipedia.org/wiki/Rjana_%C5%81u%C5%BEica","location":"JC","stakedByOperator":"3000000000000000000000","stakedByDelegates":"0","stakedTotal":"3000000000000000000000","maxIntendedStake":"0","pendingStake":"0","epochData":null,"status":"NODE_STATUS_VALIDATOR","delegations":[{"party":"399abb77e1dafb8304d3866618cb53bc00b2d578bb643630b9a8b08eefe63291","nodeId":"c3423f09428da22d984dd9a43df501e73291c360ad7bfac8795739dc046cdbd0","amount":"3000000000000000000000","epochSeq":"116143"}],"rewardScore":null,"rankingScore":{"stakeScore":"0.25","performanceScore":"1","previousStatus":"VALIDATOR_NODE_STATUS_TENDERMINT","status":"VALIDATOR_NODE_STATUS_TENDERMINT","votingPower":2500,"rankingScore":"0.5"},"name":"fluky-key","avatarUrl":"https://www.gravatar.com/avatar/i6ivthz2ax2m2h79i2fk0qim30acyeo3?d=identicon"},{"id":"e07d2cd299659590c16ec1cc1c69936ad747083c379ea6b6cfeaa6e22c8af0cb","pubKey":"68bd4d9ee8f918ad434491d56a3e0a1359e016b020bcbeac38bf384b10c87c8a","tmPubKey":"no7LX/AQkjvXwE0kKAnQ1tkAR5Q0LbfeZ2cfucOv0KM=","ethereumAddress":"0xcf3e68e25fbd0f4ba0bcd862bfed00274f705668","infoUrl":"https://en.wikipedia.org/wiki/Andreas_Troupis","location":"TD","stakedByOperator":"3000000000000000000000","stakedByDelegates":"0","stakedTotal":"3000000000000000000000","maxIntendedStake":"0","pendingStake":"0","epochData":null,"status":"NODE_STATUS_VALIDATOR","delegations":[{"party":"68bd4d9ee8f918ad434491d56a3e0a1359e016b020bcbeac38bf384b10c87c8a","nodeId":"e07d2cd299659590c16ec1cc1c69936ad747083c379ea6b6cfeaa6e22c8af0cb","amount":"3000000000000000000000","epochSeq":"116143"}],"rewardScore":null,"rankingScore":{"stakeScore":"0.25","performanceScore":"1","previousStatus":"VALIDATOR_NODE_STATUS_TENDERMINT","status":"VALIDATOR_NODE_STATUS_TENDERMINT","votingPower":2500,"rankingScore":"0.5"},"name":"famous-monkey","avatarUrl":"https://www.gravatar.com/avatar/8lsuodwwmt61alxiu6e1uq2ht2bhdkbz?d=identicon"}],"delegations":[{"party":"399abb77e1dafb8304d3866618cb53bc00b2d578bb643630b9a8b08eefe63291","nodeId":"c3423f09428da22d984dd9a43df501e73291c360ad7bfac8795739dc046cdbd0","amount":"3000000000000000000000","epochSeq":"116143"},{"party":"5a1e95067a1f1004d027213458af75658cdc19691217b974079c490ed336b7aa","nodeId":"92f1db69a9a4231b427b25cd91017fb001a4d6623954c4838c89cd0917ffe1a9","amount":"3000000000000000000000","epochSeq":"116143"},{"party":"6438a831c86c32683cf225f0f5fab28bc62c2000c66d86d27772a75960966612","nodeId":"11cd9840d290e178d103d4337bfdbfa005f6da7b6fb45f3501a405ce749d5fac","amount":"3000000000000000000000","epochSeq":"116143"},{"party":"68bd4d9ee8f918ad434491d56a3e0a1359e016b020bcbeac38bf384b10c87c8a","nodeId":"e07d2cd299659590c16ec1cc1c69936ad747083c379ea6b6cfeaa6e22c8af0cb","amount":"3000000000000000000000","epochSeq":"116143"}]}}
real	0m11.160s
user	0m0.012s
sys	0m0.009s

On n06:

time curl 'https://api.n06.stagnet1.vega.rocks/graphql' -X POST -H 'User-Agent: Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0' -H 'Accept: application/json, multipart/mixed' -H 'Accept-Language: en-GB,en;q=0.5' -H 'Accept-Encoding: gzip, deflate, br' -H 'Referer: https://api.stagnet1.vega.rocks/graphql/' -H 'content-type: application/json' -H 'Origin: https://api.stagnet1.vega.rocks' -H 'DNT: 1' -H 'Connection: keep-alive' -H 'Sec-Fetch-Dest: empty' -H 'Sec-Fetch-Mode: cors' -H 'Sec-Fetch-Site: same-origin' -H 'Pragma: no-cache' -H 'Cache-Control: no-cache' -H 'TE: trailers' --data-raw '{"query":"{\n  epoch(block: \"9134384\") {\n    id\n  }\n}"}'
{"data":{"epoch":{"id":"116143"}}}
real	0m1.298s
user	0m0.012s
sys	0m0.009s

time curl -L -X GET 'https://api.n06.stagnet1.vega.rocks/api/v2/epoch?block=9134384' -H 'Accept: application/json'
{"epoch":{"seq":"116143","timestamps":{"startTime":"1707482950718875000","expiryTime":"1707484750718875000","endTime":"0","firstBlock":"9133550","lastBlock":"0"},"validators":[{"id":"11cd9840d290e178d103d4337bfdbfa005f6da7b6fb45f3501a405ce749d5fac","pubKey":"6438a831c86c32683cf225f0f5fab28bc62c2000c66d86d27772a75960966612","tmPubKey":"tw21wknY6kJu0hq6EOLqGraTEgH/t/xe1F+ly6GTd8Q=","ethereumAddress":"0x0bcb473865e28d9e4f13851633da33c9064e8029","infoUrl":"https://en.wikipedia.org/wiki/Holy_Name_of_Jesus_Church_(Redlands,_California)","location":"BF","stakedByOperator":"3000000000000000000000","stakedByDelegates":"0","stakedTotal":"3000000000000000000000","maxIntendedStake":"0","pendingStake":"0","epochData":null,"status":"NODE_STATUS_VALIDATOR","delegations":[{"party":"6438a831c86c32683cf225f0f5fab28bc62c2000c66d86d27772a75960966612","nodeId":"11cd9840d290e178d103d4337bfdbfa005f6da7b6fb45f3501a405ce749d5fac","amount":"3000000000000000000000","epochSeq":"116143"}],"rewardScore":null,"rankingScore":{"stakeScore":"0.25","performanceScore":"1","previousStatus":"VALIDATOR_NODE_STATUS_TENDERMINT","status":"VALIDATOR_NODE_STATUS_TENDERMINT","votingPower":2500,"rankingScore":"0.5"},"name":"mango-honey","avatarUrl":"https://www.gravatar.com/avatar/hg0vuhx0ridl21uk2imqlgzekplbpij0?d=identicon"},{"id":"92f1db69a9a4231b427b25cd91017fb001a4d6623954c4838c89cd0917ffe1a9","pubKey":"5a1e95067a1f1004d027213458af75658cdc19691217b974079c490ed336b7aa","tmPubKey":"ZZc+4Y2hfHeHbigpSlSoEv+ZfN0SVadwOx4MgFPfxv4=","ethereumAddress":"0x54a047b392874c7a0c47aa24d81cc79d33313709","infoUrl":"https://en.wikipedia.org/wiki/La_viuda_negra_(TV_series)","location":"ZH","stakedByOperator":"3000000000000000000000","stakedByDelegates":"0","stakedTotal":"3000000000000000000000","maxIntendedStake":"0","pendingStake":"0","epochData":null,"status":"NODE_STATUS_VALIDATOR","delegations":[{"party":"5a1e95067a1f1004d027213458af75658cdc19691217b974079c490ed336b7aa","nodeId":"92f1db69a9a4231b427b25cd91017fb001a4d6623954c4838c89cd0917ffe1a9","amount":"3000000000000000000000","epochSeq":"116143"}],"rewardScore":null,"rankingScore":{"stakeScore":"0.25","performanceScore":"1","previousStatus":"VALIDATOR_NODE_STATUS_TENDERMINT","status":"VALIDATOR_NODE_STATUS_TENDERMINT","votingPower":2500,"rankingScore":"0.5"},"name":"perplexed-pond","avatarUrl":"https://www.gravatar.com/avatar/6l2jvijhg7gb6nbj2hps0abx8rgmmarh?d=identicon"},{"id":"c3423f09428da22d984dd9a43df501e73291c360ad7bfac8795739dc046cdbd0","pubKey":"399abb77e1dafb8304d3866618cb53bc00b2d578bb643630b9a8b08eefe63291","tmPubKey":"G3YPeYQ2FttOPsb+DPRBM2teUAWpBHw14o5WUj73zWo=","ethereumAddress":"0xdddfa1974b156336b9c49579a2bc4e0a7059cad0","infoUrl":"https://en.wikipedia.org/wiki/Rjana_%C5%81u%C5%BEica","location":"JC","stakedByOperator":"3000000000000000000000","stakedByDelegates":"0","stakedTotal":"3000000000000000000000","maxIntendedStake":"0","pendingStake":"0","epochData":null,"status":"NODE_STATUS_VALIDATOR","delegations":[{"party":"399abb77e1dafb8304d3866618cb53bc00b2d578bb643630b9a8b08eefe63291","nodeId":"c3423f09428da22d984dd9a43df501e73291c360ad7bfac8795739dc046cdbd0","amount":"3000000000000000000000","epochSeq":"116143"}],"rewardScore":null,"rankingScore":{"stakeScore":"0.25","performanceScore":"1","previousStatus":"VALIDATOR_NODE_STATUS_TENDERMINT","status":"VALIDATOR_NODE_STATUS_TENDERMINT","votingPower":2500,"rankingScore":"0.5"},"name":"fluky-key","avatarUrl":"https://www.gravatar.com/avatar/i6ivthz2ax2m2h79i2fk0qim30acyeo3?d=identicon"},{"id":"e07d2cd299659590c16ec1cc1c69936ad747083c379ea6b6cfeaa6e22c8af0cb","pubKey":"68bd4d9ee8f918ad434491d56a3e0a1359e016b020bcbeac38bf384b10c87c8a","tmPubKey":"no7LX/AQkjvXwE0kKAnQ1tkAR5Q0LbfeZ2cfucOv0KM=","ethereumAddress":"0xcf3e68e25fbd0f4ba0bcd862bfed00274f705668","infoUrl":"https://en.wikipedia.org/wiki/Andreas_Troupis","location":"TD","stakedByOperator":"3000000000000000000000","stakedByDelegates":"0","stakedTotal":"3000000000000000000000","maxIntendedStake":"0","pendingStake":"0","epochData":null,"status":"NODE_STATUS_VALIDATOR","delegations":[{"party":"68bd4d9ee8f918ad434491d56a3e0a1359e016b020bcbeac38bf384b10c87c8a","nodeId":"e07d2cd299659590c16ec1cc1c69936ad747083c379ea6b6cfeaa6e22c8af0cb","amount":"3000000000000000000000","epochSeq":"116143"}],"rewardScore":null,"rankingScore":{"stakeScore":"0.25","performanceScore":"1","previousStatus":"VALIDATOR_NODE_STATUS_TENDERMINT","status":"VALIDATOR_NODE_STATUS_TENDERMINT","votingPower":2500,"rankingScore":"0.5"},"name":"famous-monkey","avatarUrl":"https://www.gravatar.com/avatar/8lsuodwwmt61alxiu6e1uq2ht2bhdkbz?d=identicon"}],"delegations":[{"party":"399abb77e1dafb8304d3866618cb53bc00b2d578bb643630b9a8b08eefe63291","nodeId":"c3423f09428da22d984dd9a43df501e73291c360ad7bfac8795739dc046cdbd0","amount":"3000000000000000000000","epochSeq":"116143"},{"party":"5a1e95067a1f1004d027213458af75658cdc19691217b974079c490ed336b7aa","nodeId":"92f1db69a9a4231b427b25cd91017fb001a4d6623954c4838c89cd0917ffe1a9","amount":"3000000000000000000000","epochSeq":"116143"},{"party":"6438a831c86c32683cf225f0f5fab28bc62c2000c66d86d27772a75960966612","nodeId":"11cd9840d290e178d103d4337bfdbfa005f6da7b6fb45f3501a405ce749d5fac","amount":"3000000000000000000000","epochSeq":"116143"},{"party":"68bd4d9ee8f918ad434491d56a3e0a1359e016b020bcbeac38bf384b10c87c8a","nodeId":"e07d2cd299659590c16ec1cc1c69936ad747083c379ea6b6cfeaa6e22c8af0cb","amount":"3000000000000000000000","epochSeq":"116143"}]}}
real	0m1.351s
user	0m0.016s
sys	0m0.006s

[edd]

@gordsport I have unassigned myself - I will clarify the ticket. @guoguojin agreed that 1.3s for a simple API call is very slow, which needs attention. 10s is unacceptable, but that only happens during (for example) a migration.

[guoguojin]

The underlying issue is at the database layer and the query that is used to get the first block height and last block height of an epoch. The block table has lots of rows, but we have to join to it twice for the API query and this is causing the inefficiency.

It would be better for us to update the epochs table to store the start/end height of an epoch and create the appropriate triggers to update those columns when it happens which is much nicer than asking for it every time the API is called.