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:

relayscan/services/website/webserver.go

Lines 186 to 196 in eb0bb87

	// Query builders for each relay
	startTime = time.Now()
	for _, relay := range topRelays {
	topBuildersForRelay, err := srv.db.GetTopBuilders(since, now, relay.Relay)
	if err != nil {
	return nil, err
	}
	stats.TopBuildersByRelay[relay.Relay] = consolidateBuilderEntries(topBuildersForRelay)
	}
	srv.log.WithField("duration", time.Since(startTime).String()).Debug("got builders per relay")

And the DB query:

relayscan/database/database.go

Lines 124 to 135 in eb0bb87

	func (s *DatabaseService) GetTopBuilders(since, until time.Time, relay string) (res []*TopBuilderEntry, err error) {
	query := `SELECT extra_data, count(extra_data) as blocks FROM (
	SELECT distinct(slot), extra_data FROM mainnet_data_api_payload_delivered WHERE inserted_at > $1 AND inserted_at < $2`
	if relay != "" {
	query += ` AND relay = '` + relay + `'`
	}
	query += ` GROUP BY slot, extra_data
	) as x GROUP BY extra_data ORDER BY blocks DESC;`
	err = s.DB.Select(&res, query, since.UTC(), until.UTC())
	return res, err
	}

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