On Sun, 10 Oct 2021, 13:27 Alan DeKok, <aland@deployingradius.com> wrote:
On Oct 10, 2021, at 8:20 AM, Nathan Ward <lists+freeradius@daork.net> wrote:
Hmm, interesting! I did some testing on a DB just now and can clear 160k sessions in about 3 seconds, from a table of about 2.8M. My schema is a little different, but not significantly for what is relevant for what accounting-on/off is doing. There are updates going on, maybe 300/s or so (it’s a DB with a mirror of live data, I’m not poking a production DB!).
You've probably got reasonable indexes. I've seen people remove indexes in order to do "optimization". That doesn't work as well as they would think.
I agree in general. Things can become counterintuitive when mixing many update types. With workloads with a high *concurrent* UPDATE rate (e.g. >32 accounting updates) a 3 second bulk update over the table can stall things, or take a significant time to acquire the necessary row locks. Serialising through a buffered reader can help with bulk operations, up to significantly lowered limit introduced by the serialise round trips. It depends on which indexes are selected as the basis for the row locking, and often changing the query isolation level to avoid unnecessary gap locks helps a lot (especially with avoiding deadlocks). MySQL/MariaDB has several issues in which the presence of extra indexes unrelated to the query can prevent the optimal index (in terms of partitioning the data for locking purposes) from appearing as a candidate. This can result in the selection of an index that locks more rows than necessary, e.g. a long-running, repeated bulk close query can block real-time session updates because the row level locking does not partition the data by say {nasipaddress = a.b.c.d; acctstartime < Event-Timestamp} even though such an index is available :-(