I've caught the (actually quite obvious) culprit.
Some users on the server were subscribed to #matrix:matrix.org.
Several hundreds of thousands of state events are from that room alone.
Running /_synapse/admin/v1/purge_history now, maybe I can clean up a lot of garbage before spending a whole day migrating garbage from a DBMS to another, just to dump once it gets on the new DBMS.
Still, I wish that:
1. The logic for compacting state events on #Synapse was something that could be configured to run automatically and periodically (and enabled by default) from the server itself, rather than relying on 3rd-party tools only once the database is already choking.
2. The logic for linking events and its data representation is admittedly quite cool and flexible now that I understand it a bit better. However, you lose all the pros if you can't efficiently run something like "remove everything about this room before this timestamp" without having to transverse potentially millions of events. Even a simple indexed created/updated timestamp column on the events and *state tables would help - just saying.
Fabio Manganiello /
npub1v7…ukv0u
2023-08-22 23:01:14
in reply to nevent1q…87eq
Author Public Key
npub1v78mmuz20p6qd6nve30axhqu74avwn4f6z4grhug7755rat7wh3syukv0uPublished at
2023-08-22 23:01:14Event JSON
{
"id": "8c81dad1c95e33f0ac31cc831507013a1fe5eea29cb64d009a10cb9ac2e4b485",
"pubkey": "678fbdf04a787406ea6ccc5fd35c1cf57ac74ea9d0aa81df88f7a941f57e75e3",
"created_at": 1692745274,
"kind": 1,
"tags": [
[
"e",
"13c70eebba42f43958deedb63a3f478b4cf0af73f15ef09d294b0060c98585ad",
"wss://relay.mostr.pub",
"reply"
],
[
"t",
"matrix"
],
[
"t",
"synapse"
],
[
"proxy",
"https://social.platypush.tech/users/blacklight/statuses/110935754299479126",
"activitypub"
]
],
"content": "I've caught the (actually quite obvious) culprit.\n\nSome users on the server were subscribed to #matrix:matrix.org.\n\nSeveral hundreds of thousands of state events are from that room alone.\n\nRunning /_synapse/admin/v1/purge_history now, maybe I can clean up a lot of garbage before spending a whole day migrating garbage from a DBMS to another, just to dump once it gets on the new DBMS.\n\nStill, I wish that:\n\n1. The logic for compacting state events on #Synapse was something that could be configured to run automatically and periodically (and enabled by default) from the server itself, rather than relying on 3rd-party tools only once the database is already choking.\n\n2. The logic for linking events and its data representation is admittedly quite cool and flexible now that I understand it a bit better. However, you lose all the pros if you can't efficiently run something like \"remove everything about this room before this timestamp\" without having to transverse potentially millions of events. Even a simple indexed created/updated timestamp column on the events and *state tables would help - just saying.",
"sig": "621091f0c89a198a67a79f036fca9eda63374d035304490dd420f5729110f97d2dfd332b5fcf1eeb0c090d3d37760212f14fd528e6b52b926369aa87d20c3edd"
}