Website: updating 7d stats data is super slow

[metachris]

Seems updating the 7d stats in the webserver can take up to 8 minutes. This is also blocking the webserver from starting at program startup.

Todo:

• Look at why it's so slow. Probably the Postgres database, either queries or the amount of data.
• Consider doing the aggregations in database query instead of loading all data and aggregating in the application

[metachris]

Trying an index on (inserted_at, relay):

CREATE INDEX CONCURRENTLY mainnet_data_api_payload_delivered_insertedat_relay_idx ON mainnet_data_api_payload_delivered (inserted_at, relay);

[metachris]

Added some more logging in #31

Looks like the slowness is in this step, when querying top builders per relay:

https://github.com/flashbots/relayscan/blob/eb0bb8797491a3e73a0d9abc369683fd36e3a6b6/services/website/webserver.go#L186-L196

And the DB query:

https://github.com/flashbots/relayscan/blob/eb0bb8797491a3e73a0d9abc369683fd36e3a6b6/database/database.go#L124-L135

Logs:

time="2024-02-24T10:50:27Z" level=info msg="updating 24h stats..."
time="2024-02-24T10:50:27Z" level=debug msg="got top relays" duration=445.801973ms
time="2024-02-24T10:50:27Z" level=debug msg="got top builders" duration=315.08539ms
time="2024-02-24T10:50:28Z" level=debug msg="got builder profits" duration=580.871337ms
time="2024-02-24T10:50:32Z" level=debug msg="got builders per relay" duration=4.357786752s
time="2024-02-24T10:50:32Z" level=info msg="updated 24h stats" duration=5.702968613s

time="2024-02-24T10:50:32Z" level=info msg="updating 12h stats..."
time="2024-02-24T10:50:32Z" level=debug msg="got top relays" duration=11.359386ms
time="2024-02-24T10:50:32Z" level=debug msg="got top builders" duration=13.641251ms
time="2024-02-24T10:50:32Z" level=debug msg="got builder profits" duration=19.881114ms
time="2024-02-24T10:50:33Z" level=debug msg="got builders per relay" duration=45.93248ms
time="2024-02-24T10:50:33Z" level=info msg="updated 12h stats" duration=92.412429ms

time="2024-02-24T10:50:33Z" level=info msg="updating 1h stats..."
time="2024-02-24T10:50:33Z" level=debug msg="got top relays" duration=1.902724ms
time="2024-02-24T10:50:33Z" level=debug msg="got top builders" duration=2.332632ms
time="2024-02-24T10:50:33Z" level=debug msg="got builder profits" duration=4.284649ms
time="2024-02-24T10:50:33Z" level=debug msg="got builders per relay" duration=13.068928ms
time="2024-02-24T10:50:33Z" level=info msg="updated 1h stats" duration=22.476412ms

time="2024-02-24T10:50:33Z" level=info msg="updating 7d stats..."
time="2024-02-24T10:51:17Z" level=debug msg="got top relays" duration=44.951431905s
time="2024-02-24T10:52:53Z" level=debug msg="got top builders" duration=1m35.186015748s
time="2024-02-24T10:54:01Z" level=debug msg="got builder profits" duration=1m8.756918094s
time="2024-02-24T10:59:32Z" level=debug msg="got builders per relay" duration=5m31.053148979s
time="2024-02-24T10:59:32Z" level=info msg="updated 7d stats" duration=8m59.951154406s

[metachris]

it's only taking about 11 seconds nowadays, after updating the indexes

time="2024-05-29T08:22:57Z" level=info msg="updating 7d stats..."
time="2024-05-29T08:22:57Z" level=debug msg="- loading top relays..."
time="2024-05-29T08:23:00Z" level=debug msg="- got top relays" duration=3.681666368s
time="2024-05-29T08:23:00Z" level=debug msg="- loading top builders..."
time="2024-05-29T08:23:04Z" level=debug msg="- got top builders" duration=3.898279648s
time="2024-05-29T08:23:04Z" level=debug msg="- loading builder profits..."
time="2024-05-29T08:23:07Z" level=debug msg="- got builder profits" duration=3.094568352s
time="2024-05-29T08:23:07Z" level=debug msg="- loading builders per relay..."
time="2024-05-29T08:23:08Z" level=debug msg="- got builders per relay" duration=585.248249ms
time="2024-05-29T08:23:08Z" level=info msg="updated 7d stats" duration=11.263324194s