[Usage] workspace.go:68 Error 1390: Prepared statement contains too many placeholders

[jankeromnes]

Bug description

It looks like our usage component is logging an error containing tons of workspace IDs once every minute:

github.com/gitpod-io/gitpod/usage/pkg/db/workspace.go:68 Error 1390: Prepared statement contains too many placeholders\n[140.200ms] [rows:0] SELECT * FROM `d_b_workspace` WHERE `d_b_workspace`.`id` IN ('techlabapps-myjourney-mysicp2zuyi','antdesign-antdesign-j3ad1xm6oqt',.........

I believe the problem arises here:

gitpod/components/usage/pkg/controller/reconciler.go

Line 204 in 39557c3

workspaces, err := db.ListWorkspacesByID(ctx, u.conn, toSet(workspaceIDs))

when we try to get all the workspaces corresponding to all the workspace instances which ran some time this month.

Reminder: Gitpod runs tons of workspaces and instances each month. We should be careful when querying them all into memory and/or dumping them all into logs. (And, currently, the usage component does both every minute.)

See also:

• slack thread (internal)
• cloud logging (internal)

Steps to reproduce

1. Check usage component logs in production

Workspace affected

No response

Expected behavior

No response

Example repository

No response

Anything else?

No response

[jankeromnes]

As suggested on StackOverflow, a small 🛹 fix here could be to split the workspaces query (see quoted line of code ☝️) into chunks of e.g. 1000 IDs max, then run all the chunks one by one.

Also, I'm a bit uneasy about the usage loop frequency of once per minute. We could dial that one down to e.g. once every 30min or 1h.

[geropl]

/cc @andrew-farries As you're working on the usage component at the moment.

[jankeromnes]

FYI, as a better, longer-term fix, we could refactor the current code (which has large queries & several iterations over the same data) to try and:

• Only query the necessary data into memory (not fully-hydrated DB shapes, but just the columns we actually need)
• Try to iterate over all instances as few times as possible, ideally just once

---

We could e.g. do something like this:

1. Query only id, workspaceId, creationTime, stoppedTime, workspaceClass (and soon attributedTeamId) from d_b_workspace_instance for all instances that ran in the desired month and have a startedTime (unused field, but there to eliminate instances that never started):

SELECT `id`, `workspaceId`, `creationTime`, `stoppedTime`, `workspaceClass`
FROM d_b_workspace_instance
WHERE stoppedTime >= '$BEGINNING_OF_PERIOD' AND creationTime < '$END_OF_PERIOD' AND startedTime != ''

(Currently, we query all the fields from all the instances, even unnecessary fields and instances that never started: source)

2. Iterate over all the instances, just once, and for each instance, begin "transforming" them into a usage record by:

   • Trimming creationTime to no-earlier-than-beginning-of-period (e.g. like so)
   • Trimming (or simply setting) stoppedTime to no-later-than-end-of-period (e.g. like so)
   • Inserting the workspaceId into a Set or Map
     • (the current code inserts into an array with duplicates and later processes the array into a unique set -- using a Set immediately sounds more efficient)
     • here we can already start summing up usage record durations into a per-workspace total duration
     • keeping a reference/pointer to each instance or usage record per workspace ID could allow to efficiently back-fill ownership / usage attribution in later steps 💭

3. Next, iterate over the unique workspace IDs in order to resolve just the userId (a.k.a. ownerId) and projectId for each usage record:

SELECT ownerId, projectId
FROM d_b_workspace
WHERE id IN ('$UNIQUE_WS_IDS')

and then further sum up usage durations per user / per project, and back-fill the ownership information into every relevant usage record.

4. Also, once we have [Usage-based] Attribute workspace instances to a Team on start #10574, we'll no longer need to resolve the relevant teamId in the usage component itself (I believe we currently check the userId against d_b_team_membership to do that, or we were planning to).
   • We'll already have the attributed teamId for every instance's usage, so we can also save these teamIds into a Set or Map, in order to sum up usage durations per team and later resolve Stripe Customer IDs / submit total usage time.

---

An alternative approach could be to do all the joins in SQL directly, but from my understanding, this is typically less efficient, and prevents using caching or sharding on intermediate steps.

[easyCZ]

Reopening as #10778 still needs to land to close this.