The issue
Following a number of reports about the slowness of the user interface, we analyzed which parts of the user interface are used most frequently and are perceived as slow by many users.
Most concerns were about the /hosts page. I had a look this kind of logging lines:
Completed 200 OK in 11991ms (Views: 9501.2ms | ActiveRecord: 814.3ms | Allocations: 5770440)
What does it mean? => We need to decrease the number of allocations.
pagination
I recognized, that the pagination setting is often increased to “200” because the users wish to show all content-views, products, activation-keys without using the pager. This does also mean, that 200 hosts are shown if you visit /hosts. Including all the data a host needs to be shown on the hosts page: katello data, user, auth_sources, nics, taxonomy, errata,…
More hosts, more data, more active record, more allocations and in the end, more data to be rendered on the browser itself (oh, we recognized a big difference between old and new notebooks just because of page rendering with Javascript / tables, too)
Does it make sense to show 200 or even more hosts on the /hosts page? I would say no, because in the end if you have 5000 hosts you would hopefully use the search to find the hosts you want to look at. I would prefer to have a second pagination setting for the hosts page - or even better, a possibility to set the paging setting individually on the page itself.
analyze it
The first activity was to enable SQL logging.
To enable it the following has to be changed/added in /usr/share/foreman/config/settings.yaml:
:level: debug # <--- must be set to debug
:production:
:type: file
:layout: multiline_request_pattern
# Individual logging types can be toggled on/off here
:loggers:
:sql:
:enabled: true # <--- must be added and set to true
Second action was to enable further debugging possiblities - the rack mini profiler:
If the /hosts page is opened in the browser, a lot of rails models are loaded. The hosts page is often extended by plugints, too - like katello.
try to improve it
On the test system I had 72 hosts. Most of them are managed hosts with a katello subscription. The system is using foreman_ansible, foreman_remote_execution, katello, foreman_openscap and some other good known foreman plugins.
Note: It does also load a lot to collect the power state. This is included in this summary and all following.
# old Completed 200 OK in 11991ms (Views: 9501.2ms | ActiveRecord: 814.3ms | Allocations: 5770440)
# old 14sek page loading
# sql queries (incl. cached): 2503
# sql queries without cached: 1350
After this, we discovered some SQL statements which can be improved. See the list of links to improve the performance at the end of this post.
The first contribution was to preload the host_traces and use this data later on:
Second one is a little bit more difficult. Each host has errata. Instead of running 3 different SQL queries to collect for each host how many errata exist for a certain type, use one SQL query and reuse this data:
The next one is to improve to determine, if the katello-host-tools-tracer is installed. This is done by verifiying if the package is part of the installed_package_lists. This list can only change, if a new package list is updated - so, lets cache this information. During package upload, the information if the katello-host-tools-tracer is cached and is later re-used if someone calls tracer_installed?
The last one is about re-using data which does already exist:
I applied all performance improvements. 72 hosts. Loading of /hosts on browser.
old Completed 200 OK in 11991ms (Views: 9501.2ms | ActiveRecord: 814.3ms | Allocations: 5770440)
new Completed 200 OK in 5466ms (Views: 4798.0ms | ActiveRecord: 275.4ms | Allocations: 4664516)
-> 1105924 less allocations.
sql queries (incl. cached): 2503 -> 1522
sql queries without cached: 1350 -> 1072
Page loading was at about 14sek and was improved to ~9sek (difficult to measure because of load on the virual server, network, etc…)
next
The next thing I recognized is this:
# grep SELECT /tmp/debug_everything | grep users
2023-11-28T17:31:50 [D|sql|b6580c70] User Load (1.1ms) SELECT "users".* FROM "users" WHERE "users"."lower_login" = $1 LIMIT $2 [["lower_login", "foreman_admin"], ["LIMIT", 1]]
2023-11-28T17:31:56 [D|sql|bdcc4d33] User Load (0.8ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 4], ["LIMIT", 1]]
2023-11-28T17:31:59 [D|sql|fd8b0c13] User Load (0.7ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 4], ["LIMIT", 1]]
2023-11-28T17:32:00 [D|sql|fd8b0c13] CACHE User Load (0.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 4], ["LIMIT", 1]]
2023-11-28T17:32:00 [D|sql|fd8b0c13] CACHE User Load (0.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2 [["id", 4], ["LIMIT", 1]]
=> 75 times
OK, everything is cached but does this make sense? This does mean, that the SQL statement is not executed because the internal, cached data can be re-used. But, I guess, it is still necessary to find out, that the internal data is cached and can be re-used. Additonally, a large number of objects needs to be created. I would asume, that the performance does increase if it simply does re-used the existing data without even thinking about collecting the data from DB.
API / new hosts index page
The things I’ve been working on over the last few days have mainly been on the /hosts UI side. Much of what I found out can be reused to improve the performance of the API. There will also be a new hosts index page. It would be great if this page didn’t have performance issues from the start.
help
Are the contributions done? Not yet - we are still working on and each one should be tested to make sure, it doesn’t break anything or has other drawbacks.
Any thoughts and hints to improve the performance further on is highly appreciated.
additional links:
Thanks to @ekohl / @aruzicka / @Marek_Hulan / @m-bucher for their support.