Performance issue on dashboard view

[Seb-Solon]

Hi there,

My portus instance is having hard time rendering the dashboard view (the first page you have once logged in). Here are my finding :

For portus log I have some line like this:

web_1                | Processing by DashboardController#index as HTML
[...]
web_1                |   Rendered dashboard/index.html.slim within layouts/application (448332.2ms)
[...]
web_1                | Completed 200 OK in 480668ms (Views: 2148.1ms | ActiveRecord: 478493.3ms)

From a source code point of view : Related files are app/controllers/admin/dashboard_controller.rb or app/controllers/dashboard_controller.rb whether I am admin or not If am not wrong the code in app/policies/public_activity/activity_policy.rb is called as there are some MYSQL request in it. And I expect that the application needs data from there

From a DB point of view after enabling some debug : SELECT DISTINCT `activities`.* FROM `activities` WHERE (activities.trackable_type = 'Webhook') ORDER BY `activities`.`id` ASC LIMIT 1000; takes 29.84 sec If I do a limit 10 it goes under a second for the query

SELECT DISTINCT `activities`.* FROM ( (SELECT `activities`.* FROM ( (SELECT `activities`.* FROM ( (SELECT `activities`.* FROM ( (SELECT `activities`.* FROM `activities` INNER JOIN teams ON activities.trackable_id = teams.id INNER JOIN team_users ON teams.id = team_users.team_id WHERE (activities.trackable_type = 'Team' AND team_users.user_id = 2)) UNION ALL (SELECT `activities`.* FROM `activities` INNER JOIN namespaces ON activities.trackable_id = namespaces.id INNER JOIN teams ON teams.id = namespaces.team_id INNER JOIN team_users ON teams.id = team_users.team_id WHERE (activities.trackable_type = 'Namespace' AND (team_users.user_id = 2 OR ( activities.key = 'namespace.change_visibility' AND namespaces.visibility = 2)))) ) activities) UNION ALL (SELECT `activities`.* FROM `activities` INNER JOIN repositories ON activities.trackable_id = repositories.id INNER JOIN namespaces ON namespaces.id = repositories.namespace_id INNER JOIN teams ON namespaces.team_id = teams.id INNER JOIN team_users ON teams.id = team_users.team_id WHERE (activities.trackable_type = 'Repository' AND (team_users.user_id = 2 OR namespaces.visibility = 2))) ) activities) UNION ALL (SELECT `activities`.* FROM `activities` WHERE (activities.trackable_type = 'ApplicationToken' AND activities.owner_id = 2)) ) activities) UNION ALL (SELECT `activities`.* FROM `activities` WHERE `activities`.`id` IN (118612, 118613, 118619, 124011, 118616, 118618, 124012, 124013, 118614, 118615, 118621, 118622, 118610, 118611, 118620, 124014, 2909024)) ) activities ORDER BY created_at desc LIMIT 20 takes 6 min 37.05 sec

I am not sure if the query are too big or not limited in time for only ~20 entry in the dashboard at the end. 5 months ago, there was almost no delay for this page to show, it started to get longer and longer. I have plenty of data in the registry/db now. Is there anything I can do to make this delay shorter? I guess you guys could change some query to limit this but I can also try to cleanup some "old" data if this can speed up the db on my side.

Thanks!

[Seb-Solon]

Any tip on that? @mssola ?

[Seb-Solon]

Small update : I had 2.8M rows for activities table, most of them were from the portus crontab. I did remove a big part of them (everything created before XXX) and now the page takes "only" 30s to load.

Any clues on why the crontab could generate such activity? Any idea on why the registry and the portus DB could have such an unsync state ?

[mssola]

Hi, sorry for not getting back to you sooner...

Small update : I had 2.8M rows for activities table, most of them were from the portus crontab. I did remove a big part of them (everything created before XXX) and now the page takes "only" 30s to load.

That looks like a performance bug indeed. The SQL query that we use for getting the activities is... scary 😅 So, I'd say that we can:

1. Improve the SQL statement into a faster one.
2. Cache the results for a period of time.

Any clues on why the crontab could generate such activity? Any idea on why the registry and the portus DB could have such an unsync state ?

What kinds of activites were you getting ? One wild guess is that maybe your registry and Portus were always out-of-sync for whatever reason, and so cronos updated that every time ? I'm thinking on other issues that have been reported on cronos ...

[vitoravelino]

@Seb-Solon Do you mind to try #1452 and let me know how much time it took on your database?

The improvement seems considerable but I'm not sure about how it performs with a huge table like yours.

Thanks!

[Seb-Solon]

Hi there,

Sorry, I didn't answer on that because I don't have access to the instance anymore. 😢 Maybe @djsly can help on the issue or give you insights.

By the way, any improvements / fixes on the portus crontab part may be relevant to the issue as it could be a "mix" of those 2 factors ( crontab generating rows and query not optimized).

[vitoravelino]

Thanks anyway, @Seb-Solon! 😃

[mssola]

I think can be safely closed:

• The changes from @vitoravelino improves the performance a lot.
• The crono code has been merged into the background process, and it has been done in a way which won't conflict with other tasks.

If this is not the case, please re-open again or submit a new issue with further details.

Thanks 👍

[vitoravelino]

I think we should keep this issue open just to be aware of the problem. I've improved a bit but it's far from perfect.

We can revisit this during 2.4 and try to come up with a plan.

[mssola]

This issue could be a good opportunity to introduce performance testing.

[mssola]

Postponing to the 2.5 release.