search

cardano-community / guild-operators

Artifacts and scripts created by Guild operators
https://cardano-community.github.io/guild-operators
MIT License
354 stars 178 forks source link

504 Gateway Time-out for Tx Metalabels Request #1470

Closed edridudi closed 2 years ago

edridudi commented 2 years ago

Describe the bug Tx Metalabels Requests are timing out (infrequently)

To Reproduce GET https://api.koios.rest/api/v0/tx_metalabels?limit=10

Expected behavior No Timeout

Screenshots

Aug 02, 2022 11:59:12 AM okhttp3.internal.platform.Platform log
INFO: --> GET https://api.koios.rest/api/v0/tx_metalabels?limit=10
Aug 02, 2022 11:59:12 AM okhttp3.internal.platform.Platform log
INFO: --> END GET
Aug 02, 2022 11:59:45 AM okhttp3.internal.platform.Platform log
INFO: <-- 504 Gateway Time-out https://api.koios.rest/api/v0/tx_metalabels?limit=10 (33376ms)
Aug 02, 2022 11:59:45 AM okhttp3.internal.platform.Platform log
INFO: content-length: 92
Aug 02, 2022 11:59:45 AM okhttp3.internal.platform.Platform log
INFO: cache-control: no-cache
Aug 02, 2022 11:59:45 AM okhttp3.internal.platform.Platform log
INFO: content-type: text/html
Aug 02, 2022 11:59:45 AM okhttp3.internal.platform.Platform log
INFO: 
Aug 02, 2022 11:59:45 AM okhttp3.internal.platform.Platform log
INFO: <html><body><h1>504 Gateway Time-out</h1>
The server didn't respond in time.
</body></html>

Aug 02, 2022 11:59:45 AM okhttp3.internal.platform.Platform log
INFO: <-- END HTTP (92-byte body)
rdlrt commented 2 years ago

The current query code (view for select distinct) is not really optimal, we'd want to perhaps improve the tx_metalabels endpoint by moving into a cache table instead - the result set being as small as it currently is. The keys returned are not really changing often, so it might be alright for this table to get refreshed every 10/15 minutes

@dostrelith678 @Scitz0 - thoughts?

Scitz0 commented 2 years ago

Agree, that the current view is not great as it is. I suggest turning the current view into a materialized view (or a cache table) that is updated on a schedule. If a materialized view is used, we should refresh it using CONCURRENTLY option to allow select on it while updating.

The problem with tx_metadata table is that it currently contains ~35M rows on mainnet. The lack of an index on key column doesn't really matter as an index only scan over the entire table isn't much quicker than no index.

Scitz0 commented 2 years ago

Actually, let me investigate loost index scan first before creating a cache table. ref: https://wiki.postgresql.org/wiki/Loose_indexscan

Scitz0 commented 2 years ago

Yip, this is the way to go, current view takes ~15s on a fast dedicated server. While loose index scan approach takes ~10ms.

WITH RECURSIVE t AS (
  (SELECT key FROM tx_metadata ORDER BY key LIMIT 1) 
  UNION ALL
  SELECT (SELECT key FROM tx_metadata WHERE key > t.key ORDER BY key LIMIT 1)
  FROM t
  WHERE t.key IS NOT NULL
)
SELECT key FROM t WHERE key IS NOT NULL;
rdlrt commented 2 years ago

Nice, wasn't aware of skip scan bit, seems straightforward indeed

rdlrt commented 2 years ago

Should be fixed by linked issue, now just need to create a release soon (alongwith open PRs) - and version bump to have it available on instances