Aral Balkan
The latest Mastodon single-person instance performance/scaling issue:
350+ queries to the database each taking over 2 seconds each basically every time I post something. 👀
Very odd.
(In case you wonder why you can’t reach my instance after I post sometimes.)
The lovely @jan is on it and opened an issue (https://github.com/mastodon/mastodon/issues/25949) but there doesn’t seem to be much progress yet.
Looks like my account is a good canary in the coalmine for this stuff :)
Aral Balkan
@pmj Yeah, I used to experience that a lot when I was on a lower-powered server:
![𝗣𝗠𝗝 👽 [ 🙈 😅 ]](https://social.076.moe/avatar/33045-48-20230720100815.webp){kind=small}
𝗣𝗠𝗝 👽 [ 🙈 😅 ]
during the november 22 wave of twitter refugees someone made a calculation concerning big accounts (25k+ followers) and concluded that each one of them could singlehandedly take down an instance due to the server having to distribute the content to so many users - and then the followers react
Aral Balkan
@pmj Haha, no worries :)
𝗣𝗠𝗝 👽 [ 🙈 😅 ]
lol, dammit! that was the "someone" i was refering to 😅
couldn't remember 🙈
Aral Balkan
Jeroen Baert
@aral @jan Thank you for being the canary :) There are some serious worries about how populair single-user instances will scale.
Aral Balkan
@gytis @jan In their defense, hindsight is always 20/20 and we all learn about what we’re building as we’re building it. It might have made sense at some point (or, at least, wasn’t a hindrance). What matters is we fix things when they become issues. I’d rather that than premature optimisation. The latter is why things never get shipped and/or why we keep shipping over-complicated things. (All within reason, ofc. Not saying we shouldn’t keep learning or follow good practices whenever we can.)
Gytis Repečka
@aral @jan It is insanely bad query programming to have 3.5k IDs in WHERE clause 🤯 Considering table is properly indexed and vacuumed, I would split them into 100-200 microbatches and if each involves pinging a separate server of a follower account, even make those batches smaller. LIMIT and OFFSET should be the start from database side and then some task queueing from backend 🧐 #postgresql #sql #performance :postgresql:
Aral Balkan
@gytis @jan Yep and that gets it out the door. Then the database experts can optimise the query when, seven years down the line, it becomes a performance problem for someone who is an edge case for the particular design ;)
Gytis Repečka
@aral @jan Yet it is often problem when developers use database just to persist data without proper guidance from database experts. Maybe it's not the case with Mastodon (although mentioned query suggests), but I've seen far too many projects where devs just do "dump all" and "select all" with terribly narrow understanding of "if it does not perform - just add another index" 🤓
０７６萌ＳＮＳ is a social network, courtesy of ０７６. It runs on GNU social, version 2.0.2-beta0, available under the GNU Affero General Public License.
All ０７６萌ＳＮＳ content and data are available under the Creative Commons Attribution 3.0 license.