Dashboard loading issue with 80 million records - a case study

[sabinyan]

Hello.

I'm using daloradius with FreeRADIUS on a single server (4 cores, 8GB RAM, HDD storage).

Recently, I encountered an issue where the dashboard wouldn't load after logging in. The browser would just keep spinning, and subsequent attempts to navigate to the user list screen would fail with a 504 error.

Upon checking the MySQL slow query log, I found that queries against our 80 million records of operational data were taking a very long time.

To resolve this, I added the following indexes:

CREATE INDEX idx_radpostauth_authdate_desc ON radpostauth(authdate DESC);
CREATE INDEX idx_radacct_username_acctstarttime ON radacct(username, acctstarttime);
CREATE INDEX idx_radcheck_username_attribute ON radcheck(username, attribute);
CREATE INDEX idx_radpostauth_authdate_username ON radpostauth(authdate, username);

After adding these indexes, the dashboard and user list now load correctly.

Would you consider adding these indexes by default? I believe this could significantly improve performance for many users.

Thank you for your consideration.