Reduce expensive queries on the database #10

Closed
opened 2 years ago by Moonchild · 1 comments
Owner

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 years ago
Poster
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 years ago
Sign in to join this conversation.
No Milestone
No Assignees
1 Participants
Notifications
Due Date

No due date set.

Dependencies

No dependencies set.

Reference: Moonchild/FSyncMS#10
Loading…
There is no content yet.