3 ms·
Looking at https://codeberg.org/forgejo/forgejo/issues/220 https://codeberg.org/forgejo/forgejo/issues/220 I'm 99% certain that this is caused by foreign keys b
by aeyes 3y ago
Looking at https://codeberg.org/forgejo/forgejo/issues/220 https://codeberg.org/forgejo/forgejo/issues/220 I'm 99% certain that this is caused by foreign keys because the lock is waiting for primary key index access.
And if you have foreign keys you implicitly lock the referenced rows.
At scale you have to be very careful with your foreign keys, especially on update heavy tables.
A quick fix could be to skip the update of the count if the row is locked (select for update skip locked or nowait). This will fix the lock issue but you'll lose consistency.
The real fix would be to move these counters to separate tables without foreign keys and to batch the updates with background tasks.
It might also be possible to take a weaker lock for these updates but I'd have to review it in much more detail.
Edit: Another problem is that they apparently have not configured reasonable lock / statement timeouts in their database. There is no good reason to wait for a lock for 30 seconds.
- morelisp 3y agoI want Codeberg to succeed, but every time they have a problem they ask for "help", and you go read the issue, and... they're still working on routing contexts through DB queries? Like, really, the problem is essentially a lack of typists? And stuff like https://codeberg.org/forgejo/forgejo/issues/220#issuecomment-956961 https://codeberg.org/forgejo/forgejo/issues/220#issuecomment... is really worrying, like, you're counting non-critical integers one at a time, but you also think you're outsmarting the MariaDB engineers? I don't know how you productively enter a discussion like this to "help".
- aeyes 3y agoThe first iteration of my comment was actually: "Looking at this issue, the developers are clearly clueless." But that wasn't constructive. It seems to be just volunteers working on this so it makes sense that they ask for help because they can't pay their way out of this by actually having people with experience on their team.
- morelisp 3y agoThey are volunteers in the sense participation in the project is voluntary, but are also an organized non-profit accepting donations. Anyway I just logged in to fork the repo on the idea I might automate the context replacement, but... my subscriptions are somehow covered in spam for projects/users I don't follow? That is also worrying...
- aeyes 3y agoBut the organization has no employees, all money goes towards hosting and maintenance of the legal structure. This is a bit of a problem when it comes to maintaining the infrastructure and code of a large-scale system.