Skip to content

Instantly share code, notes, and snippets.

@SmaugPool

SmaugPool/cardano_load.sql

Created September 6, 2024 11:29
Show Gist options
  • Save SmaugPool/4144ee0a76258e5cbd371445e17941a4 to your computer and use it in GitHub Desktop.
Save SmaugPool/4144ee0a76258e5cbd371445e17941a4 to your computer and use it in GitHub Desktop.
Download ZIP
Cardano chain load
Raw
cardano_load.sql
-- Cardano load calculation
-- Note: Protocol parameters are hardcoded to simplify understanding.
-- In practice, parameters should be retrieved for each block to get
-- exact values after parameter changes.
-- 24 hours load
SELECT SUM(load)/COUNT(id) FROM (SELECT block.id,GREATEST(block.size::decimal/90112,SUM(unit_steps)::decimal/20000000000, SUM(unit_mem)::decimal/62000000) AS load FROM block join tx ON tx.block_id=block.id JOIN redeemer ON redeemer.tx_id=tx.id WHERE time > (NOW() AT TIME ZONE 'UTC' - INTERVAL '1 DAY') GROUP BY block.id) s;
-- 1 hour load
SELECT SUM(load)/COUNT(id) FROM (SELECT block.id,GREATEST(block.size::decimal/90112,SUM(unit_steps)::decimal/20000000000, SUM(unit_mem)::decimal/62000000) AS load FROM block join tx ON tx.block_id=block.id JOIN redeemer ON redeemer.tx_id=tx.id WHERE time > (NOW() AT TIME ZONE 'UTC' - INTERVAL '1 HOUR') GROUP BY block.id) s;
-- 5 minutes load
SELECT SUM(load)/COUNT(id) FROM (SELECT block.id,GREATEST(block.size::decimal/90112,SUM(unit_steps)::decimal/20000000000, SUM(unit_mem)::decimal/62000000) AS load FROM block join tx ON tx.block_id=block.id JOIN redeemer ON redeemer.tx_id=tx.id WHERE time > (NOW() AT TIME ZONE 'UTC' - INTERVAL '5 MINUTE') GROUP BY block.id) s;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment