love 2 see 56MB of postgres queries whizz by lol

wonder how much time postgres is actually wasting to print it all

Show thread
Follow

biggest room on pixie.town went from 47519774 rows to 1205630

after compressing a bunch more, and a REINDEX + Vacuum full overnight, i reclaimed 50GB of actual storage space

Show thread

@dumpsterqueer with github.com/matrix-org/rust-syn
basically optimizing a Matrix DAG, takes a looot of processing (and ram and storage) while running though, that compression took more than an hour (running from my desktop so doesn't affect operation too much)

@f0x ooh thanks...! So you dumped the DB onto your desktop from the server, did the processing there, and moved it back?

@dumpsterqueer ah no, db is still on the server, connecting remotely through ssh tunnel (over LAN here). So it fetches all the rows, into memory (4gb+) then crunching happens, no way the matrix server would survive that additional load

@f0x ahhh right okay! That's clear. Neato, might try it on my synapse too for fun

@dumpsterqueer you can run
SELECT room_id, count(*) AS count
FROM state_groups_state
GROUP BY room_id
ORDER BY count DESC;

to get an idea for state groups per room, and then start compression for the biggest ones (only really makes sense if the numbers are big tho)

@f0x cool. Right now there's only like 5 or 6 people using the ondergrond matrix and only one or two public rooms with not many messages yet. But when that changes it's nice to know this. Bookmarking for future use :)

Sign in to participate in the conversation
Pixietown

Smol server part of the pixie.town infrastructure. Registration is approval-based, and will probably only accept people I know elsewhere or with good motivation.