Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Reduce size of sent_activity table #4113

Closed
4 tasks done
Nutomic opened this issue Oct 27, 2023 · 1 comment
Closed
4 tasks done

Reduce size of sent_activity table #4113

Nutomic opened this issue Oct 27, 2023 · 1 comment
Labels
area: database area: maintenance enhancement New feature or request

Comments

@Nutomic
Copy link
Member

Nutomic commented Oct 27, 2023 •
edited
Loading

Requirements

  • Is this a feature request? For questions or discussions use https://lemmy.ml/c/lemmy_support
  • Did you check to see if this issue already exists?
  • Is this only a feature request? Do not put multiple feature requests in one issue.
  • Is this a backend issue? Use the lemmy-ui repo for UI / frontend issues.

Is your proposal related to a problem?

This table currently takes 22 GB on lemmy.ml. Similarly to received_activity described in #4110, entries are automatically cleared after three months. With 0.19 it will likely get even bigger as it stores a lot of extra data.

It serves two purposes:

  • It serves as a queue for outgoing activities which havent been delivered yet. For this purpose it would be fine to delete rows right after the activity was successfully delivered or permanently failed.
  • Used for the /activities/ endpoint which serves JSON data of sent activities. Lemmy doesnt use this at all and would work just fine without it. However the endpoint can be useful for debugging, and it may be used by other Fediverse platforms (unknown).

So to reduce the size we can simply reduce the cleanup interval. Something like one week might be totally fine. We could go even further and delete activities as soon as they are successfully delivered. If someone complains about /activities endpoint being broken, we have some information how it is used.

cc @phiresky @dessalines
@phiresky
Copy link
Collaborator

phiresky commented Oct 27, 2023 •
edited
Loading

With the persistent queue, this time limit also defines how long a federated instance can be down to not lose any activity. e.g. if it is set to prune to a day, then an instance that's down for less than a day will fully receive all activity from this instance. If the time limit is less than the down time, the activity is lost.

Deleting "as soon as they are delivered" is a bit more complicated because you would have to take the minimum of the last_successful_id column of the federation_queue_state table. This would work, but you'd still need a time limit to make it not baloon if any one instance is not successfully accepting federation events any more.

So i think for this purpose, just using a limit of a day or a week should be fine.

Nutomic added a commit that referenced this issue Nov 3, 2023
@Nutomic
Only keep sent activities for 7 days (fixes #4113)
e468dec
Nutomic added a commit that referenced this issue Nov 6, 2023
@Nutomic
Only keep sent and received activities for 7 days (fixes #4113, fixes #…
0b43377 
…4110)
Nutomic added a commit that referenced this issue Nov 6, 2023
@Nutomic
Only keep sent and received activities for 7 days (fixes #4113, fixes #…
c71a5e2 
…4110)
dullbananas pushed a commit to dullbananas/lemmy that referenced this issue Nov 7, 2023
@dessalines @Nutomic @phiresky
Primary keys dess (#17)
a9d0668 
* Also order reports by oldest first (ref LemmyNet#4123) (LemmyNet#4129)

* Support signed fetch for federation (fixes LemmyNet#868) (LemmyNet#4125)

* Support signed fetch for federation (fixes LemmyNet#868)

* taplo

* add federation queue state to get_federated_instances api (LemmyNet#4104)

* add federation queue state to get_federated_instances api

* feature gate

* move retry sleep function

* move stuff around

* Add UI setting for collapsing bot comments. Fixes LemmyNet#3838 (LemmyNet#4098)

* Add UI setting for collapsing bot comments. Fixes LemmyNet#3838

* Fixing clippy check.

* Only keep sent and received activities for 7 days (fixes LemmyNet#4113, fixes LemmyNet#4110) (LemmyNet#4131)

* Only check auth secure on release mode. (LemmyNet#4127)

* Only check auth secure on release mode.

* Fixing wrong js-client.

* Adding is_debug_mode var.

* Fixing the desktop image on the README. (LemmyNet#4135)

* Delete dupes and add possibly missing unique constraint on person_aggregates.

* Fixing clippy lints.

---------

Co-authored-by: Nutomic <me@nutomic.com>
Co-authored-by: phiresky <phireskyde+git@gmail.com>
dessalines added a commit that referenced this issue Nov 13, 2023
@dullbananas @dessalines
@Nutomic @phiresky
Remove id column and use different primary key on some tables (#4093)
8e2cbc9 
* post_saved

* fmt

* remove unique and not null

* put person_id first in primary key and remove index

* use post_saved.find

* change captcha_answer

* remove removal of not null

* comment_aggregates

* comment_like

* comment_saved

* aggregates

* remove "\"

* deduplicate site_aggregates

* person_post_aggregates

* community_moderator

* community_block

* community_person_ban

* custom_emoji_keyword

* federation allow/block list

* federation_queue_state

* instance_block

* local_site_rate_limit, local_user_language, login_token

* person_ban, person_block, person_follower, post_like, post_read, received_activity

* community_follower, community_language, site_language

* fmt

* image_upload

* remove unused newtypes

* remove more indexes

* use .find

* merge

* fix site_aggregates_site function

* fmt

* Primary keys dess (#17)

* Also order reports by oldest first (ref #4123) (#4129)

* Support signed fetch for federation (fixes #868) (#4125)

* Support signed fetch for federation (fixes #868)

* taplo

* add federation queue state to get_federated_instances api (#4104)

* add federation queue state to get_federated_instances api

* feature gate

* move retry sleep function

* move stuff around

* Add UI setting for collapsing bot comments. Fixes #3838 (#4098)

* Add UI setting for collapsing bot comments. Fixes #3838

* Fixing clippy check.

* Only keep sent and received activities for 7 days (fixes #4113, fixes #4110) (#4131)

* Only check auth secure on release mode. (#4127)

* Only check auth secure on release mode.

* Fixing wrong js-client.

* Adding is_debug_mode var.

* Fixing the desktop image on the README. (#4135)

* Delete dupes and add possibly missing unique constraint on person_aggregates.

* Fixing clippy lints.

---------

Co-authored-by: Nutomic <me@nutomic.com>
Co-authored-by: phiresky <phireskyde+git@gmail.com>

* fmt

* Update community_block.rs

* Update instance_block.rs

* Update person_block.rs

* Update person_block.rs

---------

Co-authored-by: Dessalines <dessalines@users.noreply.github.com>
Co-authored-by: Nutomic <me@nutomic.com>
Co-authored-by: phiresky <phireskyde+git@gmail.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
area: database area: maintenance enhancement New feature or request
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@Nutomic @phiresky