test

My posts don't seem to be reliably reaching FSE for whatever reason

@kirby Oh there now it's doin something

@kirby Your posts weren't reliably reaching my instance, either. I'd only see them when pernia interacted with them. I blocked and refollowed you yesterday and it seems to be working now

@VD15 I'm not sure I'm mentally prepared to handle actually maintaining this server for a change, but it looks like I'm gonna have to stare at graphs to make sure my job queue doesn't become constipated. Fuck me

@kirby @VD15 i can see you btw

@kirby fwiw, I just leave my instance to rot unless there's a problem. Stick Restart=always in the unit file for when it falls over

@VD15 @kirby repack ur database weekly bro

@graf @kirby @VD15 Not needed unless you are pruning and under high pressure like Poast. Haven't repacked in like 3+ months, because it takes a whole day.

/pleroma/live_dashboard/ecto_stats?nav=bloat
image.png

@kirby @VD15 >stare at grafs
hey blud

@graf @VD15 Didn't make the typo this time. u misread

@graf @kirby @VD15 bald

@graf @kirby You're talking to someone who's DB is two major postgres versions behind

@VD15 @kirby we are running 15 because a repack can only do so much. we have to take it offline to move to our chassis we got in july last year so I will full vacuum then with 256 threads~

@VD15 @kirby @graf 13 here.

@phnt @kirby @VD15 it takes you a whole day to repack? its about 1-2hr before the sunday full backup

@graf @kirby @VD15 IO limited to 35MB/s and the DB is 47GB so it takes a long time. IOPS is good though.

@phnt @kirby @VD15 I feel for you man

@phnt @VD15 @kirby thoughts on caching common queries and storing them in memory (where available, say on rum-enabled servers)

@graf @VD15 @kirby
>Haven't repacked in like 3+ months

Turns out it is more like 6+ months.

@phnt @kirby @VD15 @graf did you guys see that Postgres is getting native repack in 19?

@phnt @kirby @VD15 @graf you guys repack?

@lain @phnt @kirby @VD15 @graf still hoping for an engine that doesn't generate as much garbage to collect instead

@snacks @kirby @VD15 @graf As you can see in the screenshot, not really. autovacuum just kinda handles it good enough for half a year. It's not like fedi is an UPDATE heavy workload.

@phnt @kirby @snacks @VD15 @graf I keep forgetting to mention that I think we really need to change our vacuum settings on our Activites and Objects tables because they're terribly inefficient for our schema.

e.g., I use this:

ALTER TABLE objects SET ( autovacuum_vacuum_scale_factor=0, autovacuum_vacuum_threshold=100 );

the default values are:

autovacuum_vacuum_scale_factor=0.2 (20%)
autovacuum_vacuum_threshold=50

My settings might not be perfect, but they seem to be working very well.

So to explain what we're seeing here: if you have a small table, autovacuum kicks in when 20% of the table + 50 rows has changed -- INSERT, UPDATE, or DELETE. e.g., 1000 rows? When 250 rows change, autovacuum kicks in. Stats get updated. Query plans are better, dead tuples are swept. (NOT the same as a VACUUM FULL / repack, but still good)

Now what happens when you have 50 million activities in your database? With the default scale factor of 0.2, PostgreSQL would wait until ~10 million rows changed before vacuuming. So... it never autovacuums again. Your stats are way stale. Query plans suck ass. And because it does wait so long to autovacuum, when it has to do it there's so much work to do that it's slow. And it can fail to complete because it keeps getting paused for other work.

But by setting it to scale factor of 0 and threshold 100, Postgres now does a very quick and efficient sweep of the table after every 100 rows that change, so my stats stay correct and my query plans are better.

After making this change do a VACUUM FULL, and then never worry about it again. If you don't do a VACUUM FULL after this it might get stuck trying to autovacuum and never complete because there's too much work to do.

More help experimenting to figure out what sane settings we should use on these tables would be appreciated, but mine is running great on my tiny server with 4GB of RAM:

cheese_prod=# select COUNT(1) from activities;
count
----------
10259407
(1 row)

cheese_prod=# select COUNT(1) from objects;
count
---------
6783698
(1 row)



I should really cc @lain on this :)


edit: this doesn't fix BLOAT, so it won't reduce disk usage, but should keep performance good

@phnt @kirby @VD15 @graf can you run this for me? Curious what you have

SELECT
relname,
n_live_tup,
n_dead_tup,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;


look at how few dead tuples I have on activities and objects

@phnt @VD15 @graf @kirby @lain @snacks after talking to a robot for a bit, considering our use case and type of data this might be a more ideal approach that won't cause too much pain for people on the VMs with shitty disks:

ALTER TABLE your_table SET (
-- Vacuum: keep dead tuples under control
autovacuum_vacuum_scale_factor = 0,
autovacuum_vacuum_threshold = 50000,

-- Analyze: run much more frequently than vacuum
autovacuum_analyze_scale_factor = 0,
autovacuum_analyze_threshold = 10000,

-- Let vacuum work harder when it does run
autovacuum_vacuum_cost_limit = 2000
);

@feld @kirby @lain @snacks @VD15 @graf
>But by setting it to scale factor of 0 and threshold 100, Postgres now does a very quick and efficient sweep of the table after every 100 rows that change, so my stats stay correct and my query plans are better.

It is in no way quick. VACUUM is a sequential table scan, the whole table is read, minus the rows outside the visibility map, when performing a VACUUM. And that takes ages on hardware people run Pleroma on. You run it on a small thin-client with an SSD, I run it on a VPS with IO throughput limited to ~35MB/s. More frequent VACUUMs don't help each other either much, since Fedi isn't UPDATE nor DELETE heavy which would create dead rows.

Running a vacuum on the objects table takes 30 minutes here (see attached log).
vacuum.log
image.png

@feld @kirby @VD15 @graf
image.png

@phnt @kirby @lain @snacks @VD15 @graf

Right, the regular vacuum doesn't read every row. It reads the visibility map and only visits heap pages that have been modified since the last vacuum -- so if you autovacuum frequently, this should be fast to complete. So why is yours taking so damn long? 🤔

@feld @kirby @lain @snacks @VD15 @graf
a) it hasn't ran probably in a very long time, so visibility map is horribly out of date
b) the table is 16GB and most of it is live anyway
c) IO is really bad (that's why I still use this VPS, as a benchmark for how bad the hardware can really be)

@feld @VD15 @graf @kirby @lain @snacks Instead of bumping both autovacuum and autovacuum_analyze, bumping only analyze will probably help with the query planner. I don't think autovacuum analyze triggers a table scan.

@phnt @kirby @lain @snacks @VD15 @graf I haven't thought about this since December so my brain is rusty and you're right about everything here ;)

There's also CREATE STATISTICS which might be useful as well if we can figure out the best use of them on the JSONB columns. Is there something we should pursue here to get cheap gains vs putting a full index covering it?

@feld @kirby @lain @snacks @VD15 @graf
>Is there something we should pursue here to get cheap gains vs putting a full index covering it?

Not really sure, the DB schema is one of the last things I want to touch. Grouped notifs probably would like some index help as well (unrelated), but I don't wanna balloon the DB sizes much.