Hello everybody,
in foreman 1.24.2 opening the page for the hostgroup overview or editing hostgroups for non admin user is very slow. Unfortunately I can’t say if the problem came with the update to version 1.24 or if it already existed before.
My test setup contains about 60 servers, 114 host groups, 5 Environment, 136 puppet classes with total of 2594 smart class parameters (but not all are enabled for overwriting). We’re using Foreman as ENC for the puppetserver.
Test as Admin
In the first test i opend the page for the hostgroup overview as admin user. With debug log activated this results in ~ 115 SQL queries. The performance is quiet well (debug out in the attachment: foreman_hostgroup_view_admin.log). Opening the page for editing a hostgroup took some more time. It results in approximately 1200 sql queries. Much more than expected, but the performance was still ok (but noticeably slower) (debug out in the attachment: foreman_hostgroup_edit_admin.log).
Test as Non Admin
For the second test, i created a local testuser with the default role “Viewer”. Furthermore i assigned a role to the testuser, which gave him the possibility to edit all hostgroups. After that i signed into foreman with this user and open the page for the hostgroup overview. This request now took much longer and results in ~ 5000 SQL queries …to show 50 hostgroups, thats quite a lot (debug out in the attachment: foreman_hostgroup_view_testuser.log).
Opening the page for editing a hostgroup took much longer to open. It results in ~ 11000 SQL queries (debug out in the attachment: foreman_hostgroup_edit_testuser.log).
Thank you for bringing this up!
The reason there are many more sql queries for non-admin user is that we need to do check for authorization for all resources for the user - on the index page, for calculating the host counts, and on the edit page, for all of the different attributes on the form.
Looking at the logs, the SQL queries may be very many, but nearly all of them conclude in <1ms, meaning that the issue is not in inefficient queries but rather in the shear number of them - 11k queries would mean around 10 seconds for the page to load - is this consistent with what you are seeing?
We did have some improvements in the authorization speed that will land in 2.0 but i can’t tell for sure if it will fix this issue for you.
You can also try adding config.active_record.verbose_query_logs = Foreman::Logging.logger('sql') to /usr/share/foreman/config/production.rb which should also add source lines for each sql query so we can see where it is triggered from.
To make sure this thread doesn’t get lost in the history, can you please also open a bug report on our bug tracker?
There is an open PR that should significantly improve the performance, any chance you can give it a spin and see if it improves the situation? you will need to manually apply the patch to your server and restart it:
This should hopefully improve performance for non-admin users on all pages, not just these ones.
Thanks for reply. Looks like i fixed this issue by vacuum and indexing database. After that editing hosts and host groups become significantly faster from non-admin users.
…to show 50 hostgroups, thats quite a lot (debug out in the attachment: foreman_hostgroup_view_testuser.log).
(debug out in the attachment: foreman_hostgroup_edit_testuser.log).
.