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

[Alan DeKok]

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.

> Some point soon I'll synthesise some data on the shipped schemas and see how it performs and see if I can tweak them to be faster - if 100k sort of size is a problem then it may be worth putting some effort in to optimising, as that’s not very big. Maybe some alternative schema/queries for high scale, with complexity as a trade off.

  The main benefit I've seen is splitting "live" data from "historical" data.  The "live" data is usually small (one row per online user).  The "historical" data is huge, as it's one row per user session for the last 6-12 months.

  The key realization here is that the "live" database gets a lot of updates and simple queries, while the "historical" database gets no updates and more complex queries.

  We've had good luck putting the "live" data into Redis, which can easily do 40K updates/s.  Then periodically flushing that to SQL, which works much better when you give it 10K rows to update in one transaction.

  Alan DeKok.