Conversation
Notices
-
Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 05:33:37 JST 
Vivi Nella Verita
@i @colonelj
Ah, there still is not a good way to even maintain the Pleroma/Akkoma database. It took me two days to delete 157354 old posts using it's built-in commands and they just let the Activities table grow and grow without a way to clean it-
:blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 05:33:36 JST 
:blank:
@verita84eva @colonelj if only someone would have been gracious enough to spoon feed you how to clear old activities objects manually † top dog :pedomustdie: likes this. -
(mint@ryona.agency)'s status on Friday, 26-Apr-2024 06:10:59 JST 
@verita84eva @colonelj @i Mine is 70 GB right now and I've never pruned old posts or human activites since I launched it 2 years ago. Had a little bit of performance issues (postgres easting too much CPU), but managed to solve it by deleting all announce activities from relays (which accounted for 30% of activities table and 10 gigs on disk); this likely doesn't apply to DRC since you follow zero relays. -
Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 06:11:00 JST
Vivi Nella Verita
@i @colonelj
Ty. DRC almost 90 GB and is growing fast. Local posts would not be deleted? Only Federated? -
Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 06:11:02 JST
Vivi Nella Verita
@i @colonelj
lol yeah I been doing that daily since you told me via cron. I have no idea how to do the Activities table safely. Your suggestion for said tables does not really do much. It's yuge! -
:blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 06:11:02 JST
:blank:
@verita84eva @colonelj damn, the bulk would be in CREATE activities then, doubt much would break if you deleted them, since you're clearing the objects by age too
time to make a dump and report back if anything breaks after deleting a few months of them
pleroma=> select count(*), date_trunc('month', inserted_at) from activities where data->>'type' = 'Create' and local = 'f' group by date_trunc('month', inserted_at) order by date_trunc('month', inserted_at);
count | date_trunc
---------+---------------------
882593 | 2023-03-01 00:00:00
3404857 | 2023-04-01 00:00:00
3695948 | 2023-05-01 00:00:00
3067730 | 2023-06-01 00:00:00
4033487 | 2023-07-01 00:00:00
4050590 | 2023-08-01 00:00:00
604380 | 2023-09-01 00:00:00
19969 | 2023-10-01 00:00:00
712353 | 2023-11-01 00:00:00
1842379 | 2023-12-01 00:00:00
4198836 | 2024-01-01 00:00:00
3653179 | 2024-02-01 00:00:00
4249558 | 2024-03-01 00:00:00
2461312 | 2024-04-01 00:00:00
(14 rows)
pleroma=> select count(*) from activities where data->>'type' = 'Create' and local = 'f' and inserted_at < now() - interval '365 days';
count
---------
3778169
(1 row) -
(mint@ryona.agency)'s status on Friday, 26-Apr-2024 06:13:06 JST
@verita84eva @colonelj @i This might take a shitton of time if you're doing it on a running instance. Try turning it off, then SET session_replication_role = replica, delete them (which should take maybe up to 20 minutes as compared to days when doing it live), SET session_replication_role = DEFAULT and start the instance back.
-
Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 06:13:07 JST
Vivi Nella Verita
@i @colonelj
Running:
```
detroitriotcity=# delete from activities where data->>'type' = 'Create' and local = 'f' and inserted_at < now() - interval '365 days';
```
Now -
:blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 06:13:09 JST
:blank:
@verita84eva @colonelj yeah that's what the local = 'f' bit means, same as the like/boost pruning in the image
i'll test it on my own install first, or you can go ahead and try in parallel if you trust your own backups too -
:blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 06:15:53 JST
:blank:
@mint @colonelj @verita84eva yeah i wonder where the space is spent, a screenshot of https://detroitriotcity.com/phoenix/live_dashboard/ecto_stats?nav=total_table_size could help
but still, the activities tables has no right to be 44GB of millions of CREATE url json blobs likes this. -
(mint@ryona.agency)'s status on Friday, 26-Apr-2024 06:20:19 JST
@i @colonelj @verita84eva Weird. Objects table on mine is bigger than activites, and that's with most of federation happening from ~170 relays.
Screenshot_20240426_001646.png -
Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 06:24:29 JST
Vivi Nella Verita
@mint @colonelj @i
We did follow a lot. I just recently turned it off . Database grew form 60gb to 93gb in a ween likes this. -
Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 06:25:37 JST
Vivi Nella Verita
@mint @colonelj @i
Ah is that why deletes are always slow and takes days like with the original commands you gave me ? likes this. -
(mint@ryona.agency)'s status on Friday, 26-Apr-2024 06:28:37 JST
@verita84eva @colonelj @i I don't remember myself personally giving you the commands, but yes, I think deleting a row live causes a bunch of indexes to get recalculated, which raises the query time exponentially if you're deleting millions of rows. Setting session role to replica stops that from happening but it shouldn't ever be done on live instance as those indexes are crucial to the operation. -
(mint@ryona.agency)'s status on Friday, 26-Apr-2024 06:30:54 JST
@i @colonelj @verita84eva Maybe deletion spam from mastodon? It's known to bringing low-power instances to a halt, happened even to SPC a while ago, and rejecting deletes goes against ackoma crew's social contract. † top dog :pedomustdie: likes this. -
:blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 06:30:57 JST
:blank:
@verita84eva @colonelj @mint a week? is someone out there trolling instances they hate somehow, disqordia died when their DB started inflating suspiciously quickly -
:blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 06:31:29 JST
:blank:
@mint @colonelj @verita84eva mint is an alt of everyone else confirmed likes this. -
Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 06:33:23 JST
Vivi Nella Verita
@mint @colonelj @i oh boy. Try deleting shit from your own instance and oban_jobs filles up and kills your disk io . That was a first big issue with DRc when trying to clean it up likes this. -
(mint@ryona.agency)'s status on Friday, 26-Apr-2024 09:44:58 JST
@verita84eva @colonelj @i Did you vacuum or repack the DB after that? It would still claim the free space otherwise. -
Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 09:44:59 JST
Vivi Nella Verita
@mint @colonelj @i
I ran it , even ran it again to 30 days. Activities size didn't change. Heavy DIsk IO when running so it was doing something -
Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 09:46:01 JST
Vivi Nella Verita
@mint @colonelj @i
will do repack now. Doing a dump first with pg_backup to see if size is smaller likes this. -
(mint@ryona.agency)'s status on Friday, 26-Apr-2024 09:47:12 JST
@verita84eva @colonelj @i It shouldn't. -
Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 09:47:13 JST
Vivi Nella Verita
@mint @colonelj @i
pg_backup will not include all the deleted data right? -
Vivi Nella Verita (verita84eva@poster.place)'s status on Friday, 26-Apr-2024 15:01:32 JST
Vivi Nella Verita
@mint @colonelj @i @admin @ChristiJunior
Some success. After Repack, Activities table went from 38GB to 15GB. I deleted Federated Activites up to the last 10 days. WTF happened in the last 10 days on Fedi lol
Total Disk Footprint of the Database Dir is now 34GB. It was 93GB before we started the work today.
Thanks everyone! likes this. -
(mint@ryona.agency)'s status on Friday, 26-Apr-2024 16:56:38 JST
@verita84eva @colonelj @i @ChristiJunior @admin 15 gigs out of 38 accounting just for activities in the last 10 days is still pretty weird, it shouldn't grow that fast. -
(mint@ryona.agency)'s status on Friday, 26-Apr-2024 17:23:34 JST
@verita84eva @colonelj @i @ChristiJunior @admin Maybe try sorting by actor, there ought to be some clue on which servers take the most space.
select data->>'actor', count(data->>'actor') as count from activities group by data->>'actor' order by count desc; -
:blank: (i@declin.eu)'s status on Friday, 26-Apr-2024 18:34:17 JST
:blank:
@verita84eva @colonelj @ChristiJunior @admin @mint lol i think i know why, Create activities with more than 5000 mentions, i bet the indexing blows up too on them
like, https://writeworks.uk/@/the_kyiv_independent/ created 13MB in the DB with just 100 activities
wonder what
select actor, pg_size_pretty(sum(pg_column_size(activities.*))), count(*) from activities group by actor order by sum(pg_column_size(activities.*)) desc limit 100;
says for you, and if there's not a really degenerate software bug out there silently killing ick'oma database likes this.
-
All ０７６萌ＳＮＳ content and data are available under the 