Differential D635

Removed unnecessary count; added index on submit_time
ClosedPublic
Actions

Authored by jskladan on Nov 3 2015, 12:11 PM.

Details

Reviewers
tflink
mkrizek
Maniphest Tasks
T613: resultsdb frontend is painfully slow
Commits
rRSDBe120940631e5: Removed unnecessary count; index on submit_time
Summary

As mentionted in T613, the ResulsDB is painfully slow when showing results,
because we call q.count() for each query to be able to compute total number
of results meeting the query criteria.
This is unnecessary, thus removing it.

As we also sort all the data by submit_time, it is worth having the column
indexed

Test Plan

none

Diff Detail

Repository
rRSDB resultsdb
Lint
Automatic diff as part of commit; lint not applicable.
Unit
Automatic diff as part of commit; unit tests not applicable.
jskladan retitled this revision from to Removed unnecessary count; added index on submit_time.Nov 3 2015, 12:11 PM
jskladan updated this object.
jskladan edited the test plan for this revision. (Show Details)
jskladan added reviewers: tflink, mkrizek.
tflink accepted this revision.Nov 3 2015, 2:20 PM
This revision is now accepted and ready to land.Nov 3 2015, 2:20 PM
mkrizek accepted this revision.Nov 3 2015, 2:32 PM
Closed by commit rRSDBe120940631e5: Removed unnecessary count; index on submit_time (authored by Josef Skladanka <jskladan@redhat.com>). · Explain WhyNov 4 2015, 9:16 AM
This revision was automatically updated to reflect the committed changes.

Revision Contents

Diff 1661

View Options

alembic/versions/4bf1390f06d1_added_index_on_submit_time.py

  • This file was added.
1​"""Added index on submit_time
2​
3​Revision ID: 4bf1390f06d1
4​Revises: 34760e10040b
5​Create Date: 2015-11-03 13:06:35.167227
6​
7​"""
8​
9​# revision identifiers, used by Alembic.
10​revision = '4bf1390f06d1'
11​down_revision = '34760e10040b'
12​branch_labels = None
13​depends_on = None
14​
15​from alembic import op
16​import sqlalchemy as sa
17​
18​
19​def upgrade():
20​ ### commands auto generated by Alembic - please adjust! ###
21​ op.create_index('result_submit_time', 'result', ['submit_time'], unique=False)
22​ ### end Alembic commands ###
23​
24​
25​def downgrade():
26​ ### commands auto generated by Alembic - please adjust! ###
27​ op.drop_index('result_submit_time', table_name='result')
28​ ### end Alembic commands ###
View Options

resultsdb/controllers/api_v1.py

Show First 20 Lines • Show All 81 Lines • ▼ Show 20 Line(s)
82SERIALIZE = __serializer.serialize 82​SERIALIZE = __serializer.serialize
83 83​
84# ============================================================================= 84​# =============================================================================
85# GLOBAL METHODS 85​# GLOBAL METHODS
86# ============================================================================= 86​# =============================================================================
87 87​
88def pagination(q, page, limit): 88​def pagination(q, page, limit):
89 89​
90 total = q.count() ​
91 pages = int(math.ceil(total / float(limit))) ​
92 ​
93 # pagination offset 90​ # pagination offset
94 try: 91​ try:
95 page = int(page) 92​ page = int(page)
96 if page > 0: 93​ if page > 0:
97 offset = page * limit 94​ offset = page * limit
98 q = q.offset(offset) 95​ q = q.offset(offset)
99 except (TypeError, ValueError): 96​ except (TypeError, ValueError):
100 pass 97​ pass
101 98​
102 # apply the query limit 99​ # apply the query limit
103 try: 100​ try:
104 limit = int(limit) 101​ limit = int(limit)
105 except (ValueError, TypeError): 102​ except (ValueError, TypeError):
106 limit = QUERY_LIMIT 103​ limit = QUERY_LIMIT
107 104​
108 q = q.limit(limit) 105​ q = q.limit(limit)
109 return total, pages, q 106​ return q
110 107​
111#TODO: find a better way to do this 108​#TODO: find a better way to do this
112def prev_next_urls(): 109​def prev_next_urls():
113 global RE_PAGE 110​ global RE_PAGE
114 try: 111​ try:
115 match = RE_PAGE.findall(request.url) 112​ match = RE_PAGE.findall(request.url)
116 flag, page = match[0][0], int(match[0][1]) 113​ flag, page = match[0][0], int(match[0][1])
117 except IndexError: #page not found 114​ except IndexError: #page not found
▲ Show 20 Lines • Show All 170 Lines • ▼ Show 20 Line(s)275def get_jobs(): #page = None, limit = QUERY_LIMIT):
288 except iso8601.iso8601.ParseError: 285​ except iso8601.iso8601.ParseError:
289 return jsonify({"message": "'since' parameter not in ISO8601 format"}), 400 286​ return jsonify({"message": "'since' parameter not in ISO8601 format"}), 400
290 287​
291 if args['status'] is not None and args['status'] not in JOB_STATUS: 288​ if args['status'] is not None and args['status'] not in JOB_STATUS:
292 return jsonify({'message': "status must be one of %r" % (JOB_STATUS,) }), 400 289​ return jsonify({'message': "status must be one of %r" % (JOB_STATUS,) }), 400
293 290​
294 q = select_jobs(since_start = s, since_end = e, status = args['status'], name = args['name']) 291​ q = select_jobs(since_start = s, since_end = e, status = args['status'], name = args['name'])
295 292​
296 total, pages, q = pagination(q, args['page'], args['limit']) 293​ q = pagination(q, args['page'], args['limit'])
297 prev, next = prev_next_urls() 294​ prev, next = prev_next_urls()
298 295​
299 return jsonify(dict( 296​ return jsonify(dict(
300 href=request.url, 297​ href=request.url,
301 prev=prev, 298​ prev=prev,
302 next=next, 299​ next=next,
303 total=total, ​
304 pages=pages, ​
305 data=[ 300​ data=[
306 SERIALIZE(o, job_load_results=args['load_results']) 301​ SERIALIZE(o, job_load_results=args['load_results'])
307 for o in q.all() 302​ for o in q.all()
308 ], 303​ ],
309 )) 304​ ))
310 305​
311@api.route('/v1.0/jobs/<job_id>', methods = ['GET']) 306​@api.route('/v1.0/jobs/<job_id>', methods = ['GET'])
312def get_job(job_id): 307​def get_job(job_id):
▲ Show 20 Lines • Show All 177 Lines • ▼ Show 20 Line(s)483 q = select_results(
490 since_end = args['since']['end'], 485​ since_end = args['since']['end'],
491 since_source = args['since_source'], 486​ since_source = args['since_source'],
492 outcome = args['outcome'], 487​ outcome = args['outcome'],
493 result_data = p['result_data'], 488​ result_data = p['result_data'],
494 job_id = j_id, 489​ job_id = j_id,
495 testcase_name = t_nm, 490​ testcase_name = t_nm,
496 ) 491​ )
497 492​
498 total, pages, q = pagination(q, args['page'], args['limit']) 493​ q = pagination(q, args['page'], args['limit'])
499 prev, next = prev_next_urls() 494​ prev, next = prev_next_urls()
500 495​
501 return jsonify(dict( 496​ return jsonify(dict(
502 href=request.url, 497​ href=request.url,
503 prev=prev, 498​ prev=prev,
504 next=next, 499​ next=next,
505 total=total, ​
506 pages=pages, ​
507 data=[SERIALIZE(o) for o in q.all()], 500​ data=[SERIALIZE(o) for o in q.all()],
508 )) 501​ ))
509 502​
510@api.route('/v1.0/jobs/<job_id>/results', methods = ['GET']) 503​@api.route('/v1.0/jobs/<job_id>/results', methods = ['GET'])
511@api.route('/v1.0/testcases/<testcase_name>/results', methods = ['GET']) 504​@api.route('/v1.0/testcases/<testcase_name>/results', methods = ['GET'])
512def get_results_by_job_testcase(job_id = None, testcase_name = None): 505​def get_results_by_job_testcase(job_id = None, testcase_name = None):
513 # check whether the job/testcase exists. If not, throw 404 506​ # check whether the job/testcase exists. If not, throw 404
514 if job_id is not None: 507​ if job_id is not None:
▲ Show 20 Lines • Show All 193 Lines • ▼ Show 20 Line(s)700 except JSONBadRequest as error:
708 return jsonify({"message": "Malformed JSON data"}), error.code 701​ return jsonify({"message": "Malformed JSON data"}), error.code
709 except HTTPException as error: 702​ except HTTPException as error:
710 return jsonify(error.data), error.code 703​ return jsonify(error.data), error.code
711 704​
712 705​
713 q = db.session.query(Testcase) 706​ q = db.session.query(Testcase)
714 q.order_by(db.asc(Testcase.name)) 707​ q.order_by(db.asc(Testcase.name))
715 708​
716 total, pages, q = pagination(q, args['page'], args['limit']) 709​ q = pagination(q, args['page'], args['limit'])
717 prev, next = prev_next_urls() 710​ prev, next = prev_next_urls()
718 711​
719 return jsonify(dict( 712​ return jsonify(dict(
720 href=request.url, 713​ href=request.url,
721 prev=prev, 714​ prev=prev,
722 next=next, 715​ next=next,
723 total=total, ​
724 pages=pages, ​
725 data=[SERIALIZE(o) for o in q.all()], 716​ data=[SERIALIZE(o) for o in q.all()],
726 )) 717​ ))
727 718​
728@api.route('/v1.0/testcases/<testcase_name>', methods = ['GET']) 719​@api.route('/v1.0/testcases/<testcase_name>', methods = ['GET'])
729def get_testcase(testcase_name): 720​def get_testcase(testcase_name):
730 721​
731 try: 722​ try:
732 testcase = Testcase.query.filter_by(name = testcase_name).one() 723​ testcase = Testcase.query.filter_by(name = testcase_name).one()
▲ Show 20 Lines • Show All 60 Lines • Show Last 20 Lines
View Options

resultsdb/models/results.py

Show First 20 Lines • Show All 78 Lines • ▼ Show 20 Line(s)70class Result(db.Model, DBSerialize):
79 log_url = db.Column(db.Text) 79​ log_url = db.Column(db.Text)
80 80​
81 testcase = db.relation('Testcase', backref = 'results') #, lazy = False) 81​ testcase = db.relation('Testcase', backref = 'results') #, lazy = False)
82 result_data = db.relation('ResultData', backref = 'result') #, lazy = False) 82​ result_data = db.relation('ResultData', backref = 'result') #, lazy = False)
83 83​
84 __table_args__ = ( 84​ __table_args__ = (
85 db.Index('result_fk_job_id', 'job_id'), 85​ db.Index('result_fk_job_id', 'job_id'),
86 db.Index('result_fk_testcase_id', 'testcase_id'), 86​ db.Index('result_fk_testcase_id', 'testcase_id'),
87​ db.Index('result_submit_time', 'submit_time')
87 ) 88​ )
88 89​
89 def __init__(self, job, testcase, outcome, log_url = None, summary = None): 90​ def __init__(self, job, testcase, outcome, log_url = None, summary = None):
90 self.job = job 91​ self.job = job
91 self.testcase = testcase 92​ self.testcase = testcase
92 self.outcome = outcome 93​ self.outcome = outcome
93 self.log_url = log_url 94​ self.log_url = log_url
94 self.summary = summary 95​ self.summary = summary
Show All 21 Lines