PR#950: frontend: optimized frontpage - copr/copr

copr / copr

#950 frontend: optimized frontpage

Merged 4 years ago by praiskup. Opened 4 years ago by praiskup.

Unknown source optimized-front-page into master

frontend: optimized frontpage

Pavel Raiskup • 4 years ago

b5ecd43

frontend/coprs_frontend/coprs/logic/builds_logic.py

file modified

+28 -16

		`@@ -7,6 +7,7 @@`
		`import requests`

		`from sqlalchemy.sql import text`
		`+ from sqlalchemy.sql.expression import not_`
		`from sqlalchemy.orm import joinedload`
		`from sqlalchemy import or_`
		`from sqlalchemy import and_`
		`@@ -65,24 +66,35 @@`
		`return result`

		`@classmethod`
		`- def get_recent_tasks(cls, user=None, limit=None):`
		`- if not limit:`
		`- limit = 100`
		`+ def get_recent_tasks(cls, user=None, limit=100, period_days=2):`
		`+ query_args = (`
		`+ models.BuildChroot.build_id,`
		`+ func.max(models.BuildChroot.ended_on).label('max_ended_on'),`
		`+ models.Build.submitted_on,`
		`+ )`
		`+ group_by_args = (`
		`+ models.BuildChroot.build_id,`
		`+ models.Build.submitted_on,`
		`+ )`

		`- query = models.Build.query`
		`- if user is not None:`
		`- query = query.filter(models.Build.user_id == user.id)`

		`- query = query.join(`
		`- models.BuildChroot.query`
		`- .filter(models.BuildChroot.ended_on.isnot(None))`
		`- .order_by(models.BuildChroot.ended_on.desc())`
		`- .subquery()`
		`- ).order_by(models.Build.id.desc())`
		`-`
		- # Workaround - otherwise it could take less records than `limit` even though there are more of them.
		`- query = query.limit(limit if limit > 100 else 100)`
		`- return list(query.all()[:4])`
		`+ if user:`
		`+ query_args += (models.Build.user_id,)`
		`+ group_by_args += (models.Build.user_id,)`
		`+`
		`+ subquery = (db.session.query(*query_args)`
		`+ .join(models.Build)`
		`+ .group_by(*group_by_args)`
		`+ .having(func.count() == func.count(models.BuildChroot.ended_on))`
		`+ .having(models.Build.submitted_on > time.time() - 360024period_days)`
		`+ )`
		`+ if user:`
		`+ subquery = subquery.having(models.Build.user_id == user.id)`
		`+`
		`+ subquery = subquery.order_by(desc('max_ended_on')).limit(limit).subquery()`
		`+`
		`+ query = models.Build.query.join(subquery, subquery.c.build_id == models.Build.id)`
		`+ return list(query.all())`

		`@classmethod`
		`def get_running_tasks_by_time(cls, start, end):`

frontend/coprs_frontend/coprs/templates/recent/all.html

file modified

+1 -1

		`@@ -8,7 +8,7 @@`
		`{%endblock%}`
		`{% block status_body %}`

		`- <h2> All Projects </h2>`
		`+ <h2> Latest builds in all projects, last {{ period_days }} days </h2>`

		`<table class="table table-striped table-bordered">`
		`<thead>`

frontend/coprs_frontend/coprs/templates/recent/my.html

file modified

+2 -2

		`@@ -8,7 +8,7 @@`
		`{%endblock%}`
		`{% block status_body %}`

		`- <h2> My Projects </h2>`
		`+ <h2> Builds in my projects, last {{ period_days }} days</h2>`

		`<table class="table table-striped table-bordered">`
		`<thead>`
		`@@ -22,7 +22,7 @@`
		`</tr>`
		`</thead>`
		`<tbody>`
		`- {% for build in user_builds %}`
		`+ {% for build in builds %}`
		`<tr>`
		`<td>`
		`<a href="{{ build_href(build) }}"><b>{{ build.id }}</b></a>`

frontend/coprs_frontend/coprs/views/recent_ns/recent_general.py

file modified

+7 -20

		`@@ -12,33 +12,20 @@`
		`@recent_ns.route("/")`
		`@recent_ns.route("/all/")`
		`def all():`
		`- # tasks = bilds_logic.BuildsLogic.get_build_tasks(`
		`- builds = builds_logic.BuildsLogic.get_recent_tasks(limit=20)`
		`- # if flask.g:`
		`- # log.info("flask.g")\`
		`- if flask.g.user is not None:`
		`- user_builds = builds_logic.BuildsLogic.get_recent_tasks(user=flask.g.user, limit=20)`
		`- else:`
		`- user_builds = []`
		`-`
		`+ period_days = 2`
		`+ builds = builds_logic.BuildsLogic.get_recent_tasks(period_days=period_days)`
		`return flask.render_template("recent/all.html",`
		`number=len(list(builds)),`
		`builds=builds,`
		`- user_builds=user_builds)`
		`+ period_days=period_days)`

		`@recent_ns.route("/my/")`
		`@login_required`
		`def my():`
		`- # tasks = bilds_logic.BuildsLogic.get_build_tasks(`
		`- builds = builds_logic.BuildsLogic.get_recent_tasks(limit=20)`
		`- # if flask.g:`
		`- # log.info("flask.g")\`
		`- if flask.g.user is not None:`
		`- user_builds = builds_logic.BuildsLogic.get_recent_tasks(user=flask.g.user, limit=20)`
		`- else:`
		`- user_builds = []`
		`-`
		`+ period_days = 30`
		`+ builds = builds_logic.BuildsLogic.get_recent_tasks(user=flask.g.user,`
		`+ period_days=period_days)`
		`return flask.render_template("recent/my.html",`
		`number=len(list(builds)),`
		`builds=builds,`
		`- user_builds=user_builds)`
		`+ period_days=period_days)`

praiskup commented 4 years ago

The longest query on frontpage after this commit takes ~0.1s (compared
to the previous status of 5+ seconds).

I removed the qeury on Build table (with very expensive sub-query on
BuildChroots), and now rather going directly from BuildChroot side
(where it is much cheaper to order by ended_on field, etc., and we can
avoid the expensive merge of subquery results).

While on it, I'm fixing the /recent/all/ and /recent/my/ routes so they
don't suffer from wrongly set query limits (so they in the end contained
at most 4 builds anyways).

I picked some artificial limits on the Build.submitted_on field, because
it pretty dramatically accelerates the DB response. Two days for
all-builds (likely to fill the default limit of latest 100 builds), and
30 for user builds. We don't show older builds anymore.

Fixes: #937

rebased onto a963f9d2cda1daa5e6af0c7508606e05635a652e

4 years ago

rebased onto ccb991f8be527820c15eb91a087f87c42aa542e8

4 years ago

praiskup commented 4 years ago

[copr-build]

praiskup commented 4 years ago

[copr-build]

rebased onto 0e6e7b7f0e1530b0c9c17beeda859608dd8dcaef

4 years ago

Metadata Update from @praiskup:
- Pull-request tagged with: release-blocker

4 years ago

praiskup commented 4 years ago

~~Flagging as blocker, the frontend query takes on production builder about 1 minute (combined work of db and apache). This is pretty important as well.~~ meh, wrong PR

Edited 4 years ago by praiskup

Metadata Update from @praiskup:
- Pull-request untagged with: release-blocker

4 years ago

praiskup commented 4 years ago

nevermind, having this in the next release would be awesome, anyone wants to review @msuchy , @dturecek ?