[webui] osd: audit log page takes 5 seconds to render a page
It's possible we just have too many audit logs - but maybe an index can help, or we need to evaluate retention policies
- Status changed from New to Feedback
- Assignee set to mkittler
Trying to improve performance by adding additional indexes is likely an overkill (we already have an index on the
user_id column). So I would go for "retention policies".
I guess our current "retention policy" is to keep those logs forever. So we currently have 2,624,096 entries on OSD (the oldest is from 4 years ago).
The simplest solution is adding a 'Delete logs older then ...' button on the audit log page. I could also make this a Minion job which is executed from time to time and deletes all logs older than an age specified in the config file.
Not sure what the index for the user id is for.
Likely DBIx added it automatically for some relation.
And yes, by default the table is sorted by t_created. Not sure whether adding an index speeds up pagination, though. But I guess simply cleaning old entries up makes more sense.
- Status changed from Feedback to In Progress
- Target version changed from Ready to Current Sprint
Ok, then I'll start with the retention policy and check how much performance an index will gain. I guess the mentioned limits make sense but like the other limits this should likely be configurable.
- Status changed from In Progress to Resolved
OSD is now configured to run the cleanup and it seems to work. The audit events table loads now a little bit faster. If required, we can still reduce the storage duration (of certain event types).
I also tested locally whether adding an index would improve the speed. By default the rows are sorted by
t_created so I added an index for this column: https://github.com/Martchus/openQA/pull/new/audit-events-index
At least locally (with OSD data) this does not make it notably faster (checked query times with Chromium dev tools and nytprof). Admittedly my local machine is much faster than OSD but I still doubt an index will be beneficial.
(And of course I checked whether the index is actually present with
SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' and tablename = 'audit_events';.)
So I would mark this issue as resolved. I'll keep the branch for adding the index as a reference for adding indexes.