#10 Reduce expensive queries on the database

Closed
opened 2 weeks ago by Moonchild · 1 comments

There are a number of expensive queries on the database, but the heaviest by far is the constant querying of the amount of data used for users:

select round(sum(length(payload))/1024) from wbo where username = '{{username}}'

Even fully cached in memory this takes 200 ms, and a cold query can take much longer with significant disk I/O.

Suggestion: Upon update-finish, perform this query and store it in the users table as a new column, and query that instead.

There are a number of expensive queries on the database, but the heaviest by far is the constant querying of the amount of data used for users: ```sql select round(sum(length(payload))/1024) from wbo where username = '{{username}}' ``` Even fully cached in memory this takes 200 ms, and a cold query can take much longer with significant disk I/O. Suggestion: Upon update-finish, perform this query and store it in the `users` table as a new column, and query that instead.
Moonchild added the
enhancement
label 2 weeks ago
Moonchild commented 2 weeks ago
Owner

Decided to make the cache only time-dependent and otherwise transparent.

New define: QUOTA_TTL determines how long a calculated quota total should be cached before recalculating.

Live server impact: reduced userland CPU from 12-15% of one core to about 2%.

Decided to make the cache only time-dependent and otherwise transparent. New define: QUOTA_TTL determines how long a calculated quota total should be cached before recalculating. Live server impact: reduced userland CPU from 12-15% of one core to about 2%.
Moonchild closed this issue 2 weeks ago
Sign in to join this conversation.
No Milestone
No Assignees
1 Participants
Notifications
Due Date

No due date set.

Dependencies

This issue currently doesn't have any dependencies.

Loading…
There is no content yet.