What is the purpose of the default accounting-on/off queries?

[Terry Burton]

On Sun, 10 Oct 2021, 13:27 Alan DeKok, <aland at deployingradius.com> wrote:

> On Oct 10, 2021, at 8:20 AM, Nathan Ward <lists+freeradius at 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 :-(