Content View list takes too long

[mindo]

Problem:
Since foreman/katello versions 3.7/4.9 listing the content views is very slow.

The web interface takes several minutes and using hammer --no-headers content-view list timeouts most of the time.

Expected outcome:
Listing Content Views should be fast as it was on versions 3.6/4.8.

Foreman and Proxy versions:
foreman: 3.9.1

Foreman and Proxy plugin versions:
foreman-tasks: 9.0.0
foreman_ansible: 13.0.2
foreman_openscap: 7.1.1
foreman_remote_execution: 12.0.2
foreman_statistics: 2.1.0
katello: 4.11.0

Distribution and version:
Rocky Linux release 8.9 (Green Obsidian)

Other relevant data:

We already pointed to this issue in the past:

Katello 4.9 is very slow

I don’t think the number of lines of katello_repository_rpms is really the issue. On our main katello instance we have 29 Content Views but only 2 versions each and we have foreman=# select count(*) from katello_repository_rpms; count --------- 2756444 (1 row) On another katello we have “only” 17 CV but each has 5 versions, so we have: foreman=# select count(*) from katello_repository_rpms; count --------- 3731712 (1 row) which is about 1M more. We updated both instances to katello …

Katello 4.9 is very slow

Hello, I also can confirm that publishing repositories is no longer an issue, but trying to list content views is still very slow. Even when using hammer it takes several minutes and sometimes it timeouts. [PRD root@updates ~ - 16:40:07][1][130]# time hammer content-view list --organization-label MyCompany --fields "Content View ID" --noncomposite 1 Error: Timed out reading data from server real 4m2.367s user 0m1.658s sys 0m0.259s Whenever we need to make some change on a Content …

[lumarel]

Hey @mindo,

Also pointed that out recently, and it’s tracked here now:

Bug #36976: Very slow content view list loading - Katello - Foreman

[mindo]

Oh, I did not noticed that…
Unfortunately my issues are much worse than it takes “ages” to load that page. (~ 10sec for me), for me it takes several minutes on the web interface.
And it is not an issue only with the web interface, because if I try to list the Content Views using hammer on the command line it timeouts most of the time.

[lumarel]

Oh interesting, yes this is definitely much worse!

Just out of curiosity, how many CVs and versions do you have? Maybe it scales with that (aka not enough optimized queries/missing indexes, or whatever), and with luck is connected to the same code.

Also checked the hammer command now and that takes 22s now, with having 13 CVs and 99 versions.

[mindo]

Hello,

We have 19 Content Views with 2 versions each.

[lumarel]

@iballou @Bernhard_Suttner As you seem to be a bit more into that so far, could this be the same code, that makes it slow?

[w4y_secker]

Hi!

We have about 40 Content Views and 60 Composite Content Views.

Loading time for the Content View Page is about 20-40s.

[mindo]

@w4y_secker Are you using the lastest version of foreman and katello?

When we upgraded to foreman 3.7 (and katello 4.9) listing the content views became very slow.
Reverting to the snapshot before the upgrade and everything worked as fast as before (although, if i remember correctly, it already to several tens of seconds).

[w4y_secker]

@mindo we‘re using Foreman 3.8 / Katello 4.10

Yes listing took about ~5-10s before the Upgrade

[jeremylenz]

@mindo

We have 19 Content Views with 2 versions each.

In my dev environment I created 19 content views and published them at least 2x each. My page is still loading in ~3s. Any tips on how I can reproduce this issue?

[Partha_Aji]

@mindo @lumarel Curios to see the timing this curl command takes for you fill in the org id , username and password.

time curl -k -u <username>:<password> 'https://<fqdn>/katello/api/v2/content_views?organization_id=<org_id>&nondefault=true&include_permissions=true&sort_by=name&sort_order=asc&per_page=20&page=1' > /dev/null

[jeremylenz]

Also how many hosts do you have, in total and associated with each content view (on average)?

[w4y_secker]

@Partha_Aji

time curl -k -u : ‘https:///katello/api/v2/content_views?organization_id=<org_id>&nondefault=true&include_permissions=true&sort_by=name&sort_order=asc&per_page=20&page=1’ > /dev/null

time curl -k -u xxxxx ‘https://foreman01.XXXX.XXX/katello/api/v2/content_views?organization_id=1&nondefault=true&include_permissions=true&sort_by=name&sort_order=asc&per_page=20&page=1’ > /dev/null
Enter host password for user ‘xxxxx’:
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 66216 100 66216 0 0 4452 0 0:00:14 0:00:14 --:–:-- 18101

real 0m18.652s
user 0m0.015s
sys 0m0.013s

And regarding @jeremylenz question…
580 hosts
77 Content views
59 Composite content views
with max 6 Versions each content view

90% of composite content views have 3-10 hosts
10% of composite content views have 60-90 hosts

[jeremylenz]

Thanks @w4y_secker !

We’re wondering if perhaps the number of hosts is what’s causing it to slow down. The reason is that the JSON response contains all sorts of (possibly unnecessary) host listings.

(With 0 hosts in my development environment, I get this:)

real	0m1.473s
user	0m0.004s
sys	0m0.003s

Will be good to get some data points from others as well.

[Bernhard_Suttner]

if you enable SQL Queries in your config @jeremylenz you may see a lot of SQL quries per host which is maybe the source of the evil

[jeremylenz]

I do see quite a few long host-related queries. Here is just a sample:

22:02:51 rails.1   | 2024-01-18T22:02:51 [D|sql|95cf7076]    (0.6ms)  SELECT "hosts"."id" FROM "hosts" INNER JOIN "katello_content_facets" ON "hosts"."id" = "katello_content_facets"."host_id" INNER JOIN "katello_content_view_environment_content_facets" ON "katello_content_facets"."id" = "katello_content_view_environment_content_facets"."content_facet_id" INNER JOIN "katello_content_view_environments" ON "katello_content_view_environment_content_facets"."content_view_environment_id" = "katello_content_view_environments"."id" INNER JOIN "katello_content_facets" "content_facets_hosts" ON "content_facets_hosts"."host_id" = "hosts"."id" INNER JOIN "katello_content_view_environment_content_facets" "content_view_environment_content_facets_katello_content_facets" ON "content_view_environment_content_facets_katello_content_facets"."content_facet_id" = "content_facets_hosts"."id" INNER JOIN "katello_content_view_environments" "content_view_environments_katello_content_view_environment_cont" ON "content_view_environments_katello_content_view_environment_cont"."id" = "content_view_environment_content_facets_katello_content_facets"."content_view_environment_id" WHERE "hosts"."organization_id" = $1 AND "hosts"."type" = $2 AND "katello_content_view_environments"."content_view_id" = $3 AND "katello_content_view_environments"."environment_id" = $4  [["organization_id", 1], ["type", "Host::Managed"], ["content_view_id", 18], ["environment_id", 3]]
22:02:51 rails.1   | 2024-01-18T22:02:51 [D|sql|95cf7076]   ↳ /home/vagrant/katello/app/views/katello/api/v2/content_views/base.json.rabl:50:in `block (2 levels) in cached_source_4176567986253823475'
22:02:51 rails.1   | 2024-01-18T22:02:51 [D|sql|95cf7076]    (0.2ms)  SELECT "katello_activation_keys"."id" FROM "katello_activation_keys" WHERE "katello_activation_keys"."content_view_id" = $1 AND "katello_activation_keys"."environment_id" = $2  [["content_view_id", 18], ["environment_id", 1]]
22:02:51 rails.1   | 2024-01-18T22:02:51 [D|sql|95cf7076]   ↳ /home/vagrant/katello/app/views/katello/api/v2/content_views/base.json.rabl:49:in `block (2 levels) in cached_source_4176567986253823475'
22:02:51 rails.1   | 2024-01-18T22:02:51 [D|sql|95cf7076]   CACHE  (0.0ms)  SELECT "taxonomies"."id" FROM "taxonomies" WHERE ("taxonomies"."ancestry" LIKE '1/%' OR "taxonomies"."ancestry" = '1' OR "taxonomies"."id" = 1) ORDER BY "taxonomies"."title" ASC
22:02:51 rails.1   | 2024-01-18T22:02:51 [D|sql|95cf7076]   ↳ app/models/host/base.rb:58:in `taxonomy_conditions'
22:02:51 rails.1   | 2024-01-18T22:02:51 [D|sql|95cf7076]    (0.3ms)  SELECT "hosts"."id" FROM "hosts" INNER JOIN "katello_content_facets" ON "hosts"."id" = "katello_content_facets"."host_id" INNER JOIN "katello_content_view_environment_content_facets" ON "katello_content_facets"."id" = "katello_content_view_environment_content_facets"."content_facet_id" INNER JOIN "katello_content_view_environments" ON "katello_content_view_environment_content_facets"."content_view_environment_id" = "katello_content_view_environments"."id" INNER JOIN "katello_content_facets" "content_facets_hosts" ON "content_facets_hosts"."host_id" = "hosts"."id" INNER JOIN "katello_content_view_environment_content_facets" "content_view_environment_content_facets_katello_content_facets" ON "content_view_environment_content_facets_katello_content_facets"."content_facet_id" = "content_facets_hosts"."id" INNER JOIN "katello_content_view_environments" "content_view_environments_katello_content_view_environment_cont" ON "content_view_environments_katello_content_view_environment_cont"."id" = "content_view_environment_content_facets_katello_content_facets"."content_view_environment_id" WHERE "hosts"."organization_id" = $1 AND "hosts"."type" = $2 AND "katello_content_view_environments"."content_view_id" = $3 AND "katello_content_view_environments"."environment_id" = $4  [["organization_id", 1], ["type", "Host::Managed"], ["content_view_id", 18], ["envi

[lumarel]

It’s real: 0m22,714s, user: 0m0,071s, sys: 0m0,021s here
With 52 host entries (16 active hosts).

Nearly all hosts use 3 or 4 of the custom only rpm repo CVs, 2 inactive hosts use deb repo only repo CVs,
but the CVs are a mix of custom only rpm repos, RH + custom rpm, RH only, and deb only repos.

[Partha_Aji]

@lumarel 22s seems long for only 52 hosts ?
How many CVes and how many versions in the largest CV
and what is the max number of repositories in a CV Version.

[gvde]

Just to add here. I have seen the slow list since 4.9. I have just upgraded to 4.10.

The url https://foreman8.example.com/katello/api/v2/content_views?organization_id=1&nondefault=true&include_permissions=true&sort_by=name&sort_order=asc&per_page=20&page=1 takes approx 26s to load. It’s pretty steady.

I only have 11 content views, no composite views, 6 have filters, 5 have no filters. 274 hosts.

I have checked for queries longer than 100ms but nothing:

2024-01-19 07:29:51 CET LOG:  parameter "log_min_duration_statement" changed to "100"
2024-01-19 07:30:37 CET LOG:  duration: 1002.752 ms  statement: select pg_sleep(1);

I have just stopped my external proxies and stop foreman-proxy.service and puppetserver.service on the main server which reduced pretty much of the background noise. I have set log_statement to all in postgresql.conf. Load the api url, which again took 26s and grep the logs for the timeframe like this:

$ grep SELECT p.log | sed -e 's/^.*statement: //' -e 's/^.*execute [^ ]*: //' | grep -v -e 'core_taskschedule' -e core_worker -e pg_try_advisory_lock -e pg_advisory_unlock -e dynflow_ -e taxonomies -e auditable_type -e core_contentappstatus -e foreman_tasks_tasks | sort -d | uniq -c | sort -n

which got me this list of queries run:

   1 SELECT "katello_activation_keys".* FROM "katello_activation_keys" WHERE "katello_activation_keys"."content_view_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
   1 SELECT "katello_content_view_environments".* FROM "katello_content_view_environments" WHERE "katello_content_view_environments"."content_view_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
   1 SELECT "katello_content_view_repositories".* FROM "katello_content_view_repositories" WHERE "katello_content_view_repositories"."content_view_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
   1 SELECT "katello_content_view_versions".* FROM "katello_content_view_versions" WHERE "katello_content_view_versions"."content_view_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
   1 SELECT "katello_content_views".* FROM "katello_content_views" WHERE "katello_content_views"."id" IN (SELECT DISTINCT "katello_content_views"."id" FROM "katello_content_views" WHERE "katello_content_views"."generated_for" NOT IN ($1, $2, $3, $4) AND "katello_content_views"."organization_id" = $5 AND "katello_content_views"."default" = $6) ORDER BY "katello_content_views"."name" ASC NULLS FIRST , katello_content_views.id DESC LIMIT $7 OFFSET $8
   1 SELECT "katello_environments".* FROM "katello_environments" WHERE "katello_environments"."id" IN ($1, $2, $3)
   1 SELECT "katello_repositories".* FROM "katello_repositories" WHERE "katello_repositories"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100, $101, $102, $103, $104, $105, $106, $107, $108, $109, $110, $111, $112, $113, $114, $115, $116, $117, $118, $119, $120, $121, $122, $123, $124, $125, $126, $127, $128, $129, $130, $131, $132, $133, $134, $135, $136, $137, $138, $139, $140, $141, $142, $143, $144, $145, $146, $147, $148, $149, $150, $151, $152, $153, $154, $155, $156, $157, $158, $159, $160, $161)
   1 SELECT "sessions".* FROM "sessions" WHERE "sessions"."session_id" = $1 ORDER BY "sessions"."id" ASC LIMIT $2
   1 SELECT "settings".* FROM "settings" WHERE (updated_at >= '2024-01-16 06:17:42.619533')
   1 SELECT COUNT(*) FROM "katello_content_views" WHERE "katello_content_views"."id" IN (SELECT DISTINCT "katello_content_views"."id" FROM "katello_content_views" WHERE "katello_content_views"."generated_for" NOT IN ($1, $2, $3, $4) AND "katello_content_views"."organization_id" = $5 AND "katello_content_views"."default" = $6)
   1 SELECT COUNT(*) FROM "katello_environments"
   1 SELECT COUNT(DISTINCT "katello_content_views"."id") FROM "katello_content_views" WHERE "katello_content_views"."generated_for" NOT IN ($1, $2, $3, $4) AND "katello_content_views"."organization_id" = $5 AND "katello_content_views"."default" = $6
   2 SELECT TRIGGER_NAME, TRIGGER_GROUP, NEXT_FIRE_TIME, PRIORITY FROM QRTZ_TRIGGERS WHERE SCHED_NAME = 'QuartzScheduler' AND TRIGGER_STATE = $1 AND NEXT_FIRE_TIME <= $2 AND (MISFIRE_INSTR = -1 OR (MISFIRE_INSTR != -1 AND NEXT_FIRE_TIME >= $3)) ORDER BY NEXT_FIRE_TIME ASC, PRIORITY DESC
   3 SELECT "settings".* FROM "settings" WHERE (updated_at >= '2024-01-16 06:19:04.232826')
   4 SELECT "auth_sources".* FROM "auth_sources" WHERE "auth_sources"."id" = $1 LIMIT $2
   4 SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2
  14 SELECT "katello_events".* FROM "katello_events" WHERE ("katello_events"."process_after" IS NULL OR "katello_events"."process_after" BETWEEN $1 AND $2) AND "katello_events"."in_progress" = $3 ORDER BY "katello_events"."created_at" ASC LIMIT $4
  18 SELECT "katello_content_view_components".* FROM "katello_content_view_components" WHERE "katello_content_view_components"."composite_content_view_id" = $1
  18 SELECT "katello_content_view_components".* FROM "katello_content_view_components" WHERE "katello_content_view_components"."content_view_id" = $1
  18 SELECT "katello_content_view_versions".* FROM "katello_content_view_versions" WHERE "katello_content_view_versions"."content_view_id" = $1 ORDER BY "katello_content_view_versions"."created_at" DESC LIMIT $2
  18 SELECT "katello_content_view_versions".* FROM "katello_content_view_versions" WHERE "katello_content_view_versions"."content_view_id" = $1 ORDER BY major DESC, minor DESC LIMIT $2
  18 SELECT "katello_environments".* FROM "katello_environments" INNER JOIN "katello_content_view_environments" ON "katello_environments"."id" = "katello_content_view_environments"."environment_id" WHERE "katello_content_view_environments"."content_view_version_id" = $1
  18 SELECT 1
  18 SELECT COUNT(*) FROM "katello_content_view_versions" WHERE "katello_content_view_versions"."content_view_id" = $1
  19 SELECT "hosts".* FROM "hosts" INNER JOIN "katello_content_facets" ON "hosts"."id" = "katello_content_facets"."host_id" INNER JOIN "katello_content_view_environment_content_facets" ON "katello_content_facets"."id" = "katello_content_view_environment_content_facets"."content_facet_id" INNER JOIN "katello_content_view_environments" ON "katello_content_view_environment_content_facets"."content_view_environment_id" = "katello_content_view_environments"."id" WHERE "hosts"."organization_id" = $1 AND "hosts"."location_id" = $2 AND "hosts"."type" = $3 AND "katello_content_view_environments"."content_view_id" = $4
  19 SELECT "katello_content_view_filters".* FROM "katello_content_view_filters" WHERE "katello_content_view_filters"."content_view_id" = $1
  19 SELECT "katello_content_view_histories".* FROM "katello_content_view_histories" INNER JOIN "katello_content_view_versions" ON "katello_content_view_versions"."id" = "katello_content_view_histories"."katello_content_view_version_id" WHERE "katello_content_view_versions"."content_view_id" = $1 ORDER BY "katello_content_view_histories"."created_at" DESC LIMIT $2
  19 SELECT "katello_content_view_versions".* FROM "katello_content_view_versions" WHERE "katello_content_view_versions"."content_view_id" = $1 ORDER BY created_at DESC
  54 SELECT "hosts"."id" FROM "hosts" INNER JOIN "katello_content_facets" ON "hosts"."id" = "katello_content_facets"."host_id" INNER JOIN "katello_content_view_environment_content_facets" ON "katello_content_facets"."id" = "katello_content_view_environment_content_facets"."content_facet_id" INNER JOIN "katello_content_view_environments" ON "katello_content_view_environment_content_facets"."content_view_environment_id" = "katello_content_view_environments"."id" INNER JOIN "katello_content_facets" "content_facets_hosts" ON "content_facets_hosts"."host_id" = "hosts"."id" INNER JOIN "katello_content_view_environment_content_facets" "content_view_environment_content_facets_katello_content_facets" ON "content_view_environment_content_facets_katello_content_facets"."content_facet_id" = "content_facets_hosts"."id" INNER JOIN "katello_content_view_environments" "content_view_environments_katello_content_view_environment_cont" ON "content_view_environments_katello_content_view_environment_cont"."id" = "content_view_environment_content_facets_katello_content_facets"."content_view_environment_id" WHERE "hosts"."organization_id" = $1 AND "hosts"."location_id" = $2 AND "hosts"."type" = $3 AND "katello_content_view_environments"."content_view_id" = $4 AND "katello_content_view_environments"."environment_id" = $5
  54 SELECT "katello_activation_keys"."id" FROM "katello_activation_keys" WHERE "katello_activation_keys"."content_view_id" = $1 AND "katello_activation_keys"."environment_id" = $2
  89 SELECT "katello_content_view_histories".* FROM "katello_content_view_histories" WHERE "katello_content_view_histories"."katello_content_view_version_id" = $1 AND "katello_content_view_histories"."action" = $2 ORDER BY "katello_content_view_histories"."id" ASC LIMIT $3
  90 SELECT "katello_environments"."id" FROM "katello_environments" INNER JOIN "katello_content_view_environments" ON "katello_environments"."id" = "katello_content_view_environments"."environment_id" WHERE "katello_content_view_environments"."content_view_version_id" = $1
 272 SELECT "katello_root_repositories".* FROM "katello_root_repositories" WHERE "katello_root_repositories"."id" = $1 LIMIT $2

[lumarel]

It’s all in all 12 CVs (no CCVs)
The majority of hosts hangs on 4 CVs (~11 hosts per CV, the 1st 4 in the list below)
And the CVs are not really distributed evenly:

1. 26 repos - 10 versions
2. 47 repos - 15 versions
3. 14 repos - 10 versions
4. 62 repos - 14 versions
5. 10 repos - 19 versions
6. 28 repos - 9 versions
7. 6 repos - 2 versions
8. 9 repos - 2 versions
9. 6 repos - 2 versions
10. 9 repos - 2 versions
11. 10 repos - 10 versions
12. 10 repos - 9 versions