Differential D338

Added indexes to Foreign keys
ClosedPublic
Actions

Authored by jskladan on Apr 14 2015, 12:34 PM.

Details

Reviewers
kparal
tflink
mkrizek
Maniphest Tasks
T452: HTTP 504 Gateway Timeout for ResultsDB calls
Commits
rRSDBd3793bc9c571: Added indexes to Foreign keys
Summary

PostgreSQL does not automatically create Indexes for Foreign keys.
This was found out while solving T452. Without indexes, queries like:

SELECT * FROM result WHERE 115515 = result.job_id;

take about 250 ms. With index on the Foreign key, it is 0.048 ms.

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 Added indexes to Foreign keys.Apr 14 2015, 12:34 PM
jskladan updated this object.
jskladan edited the test plan for this revision. (Show Details)
jskladan added reviewers: tflink, kparal, mkrizek.
tflink accepted this revision.Apr 14 2015, 2:28 PM
This revision is now accepted and ready to land.Apr 14 2015, 2:28 PM
jskladan updated this revision to Diff 919.Apr 14 2015, 3:24 PM
  • Use better query to order jobs by start_time
  • Allow LIKE to use index on result_data.key/value search
jskladan added a comment.Apr 14 2015, 3:27 PM

@tflink, you are way too fast and I am way too slow - I have been digging around for more slow queries, and I found out, that the key-value index on result_data is not used for LIKE queries.

I'm not saying we need to remake the index now, but if we're going to have downtime for creating the other indexes anyway, this might be well worth it.

Let me know whether this is OK with you, or if I should keep it at the Accepted state and do this in a separate Diff.

tflink added a comment.Apr 14 2015, 4:02 PM
In D338#6147, @jskladan wrote:

@tflink, you are way too fast and I am way too slow - I have been digging around for more slow queries, and I found out, that the key-value index on result_data is not used for LIKE queries.

I'm not saying we need to remake the index now, but if we're going to have downtime for creating the other indexes anyway, this might be well worth it.

Let me know whether this is OK with you, or if I should keep it at the Accepted state and do this in a separate Diff.

Makes sense to me - fewer downtimes scheduled the better.

I'm starting to wonder if the mysql stuff should be removed, though. While I'm not against having it in there if it doesn't cost us much, it kinda implies that we support mysql in addition to postgres and I don't see that as a priority. Postgres is in our deployments, sqlite can be used for dev but I don't see enough value in adding mysql to justify spending time on any mysql-specific issues that may come up.

jskladan added a comment.Apr 14 2015, 6:15 PM
In D338#6150, @tflink wrote:

I'm starting to wonder if the mysql stuff should be removed, though.

Yeah, it probably should. I had some dev setup with mysql in the past, but with the rise and shine of Taskotron-Ansible, I don't see myself using it anyway.
Will, do, and push.

Closed by commit rRSDBd3793bc9c571: Added indexes to Foreign keys (authored by Josef Skladanka <jskladan@redhat.com>). · Explain WhyApr 15 2015, 8:37 AM
This revision was automatically updated to reflect the committed changes.

Revision Contents

Diff 920

View Options

alembic/versions/153c416322c2_create_indexes_on_foreign_keys.py

  • This file was added.
1​"""Create indexes on Foreign Keys
2​
3​Revision ID: 153c416322c2
4​Revises: 17ec41bd6e9a
5​Create Date: 2015-04-14 14:30:03.096651
6​
7​"""
8​
9​# revision identifiers, used by Alembic.
10​revision = '153c416322c2'
11​down_revision = '17ec41bd6e9a'
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_fk_job_id', 'result', ['job_id'], unique=False)
22​ op.create_index('result_fk_testcase_id', 'result', ['testcase_id'], unique=False)
23​ op.create_index('result_data_fk_result_id', 'result_data', ['result_id'], unique=False)
24​ ### end Alembic commands ###
25​
26​
27​def downgrade():
28​ ### commands auto generated by Alembic - please adjust! ###
29​ op.drop_index('result_data_fk_result_id', table_name='result_data')
30​ op.drop_index('result_fk_testcase_id', table_name='result')
31​ op.drop_index('result_fk_job_id', table_name='result')
32​ ### end Alembic commands ###
View Options

alembic/versions/4ace44a44bf_change_index_on_result_data_so_like_can_.py

  • This file was added.
1​"""Change index on result_data so LIKE can use it
2​
3​Revision ID: 4ace44a44bf
4​Revises: 153c416322c2
5​Create Date: 2015-04-14 17:20:32.575195
6​
7​"""
8​
9​# revision identifiers, used by Alembic.
10​revision = '4ace44a44bf'
11​down_revision = '153c416322c2'
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_data_idx_key_value', 'result_data', ['key', 'value'], unique=False,
22​ postgresql_ops={'value': 'text_pattern_ops', 'key': 'text_pattern_ops'})
23​ op.drop_index('rd_key_value_idx', table_name='result_data')
24​ ### end Alembic commands ###
25​
26​
27​def downgrade():
28​ ### commands auto generated by Alembic - please adjust! ###
29​ op.create_index('rd_key_value_idx', 'result_data', ['key', 'value'], unique=False)
30​ op.drop_index('result_data_idx_key_value', table_name='result_data')
31​ ### end Alembic commands ###
View Options

resultsdb/controllers/api_v1.py

Show First 20 Lines • Show All 159 Lines • ▼ Show 20 Line(s)151def select_jobs(since_start = None, since_end = None, status = None, name = None):
160 # Filter by status 160​ # Filter by status
161 if status is not None: 161​ if status is not None:
162 q = q.filter(Job.status.in_([o.strip().upper() for o in status.split(',')])) 162​ q = q.filter(Job.status.in_([o.strip().upper() for o in status.split(',')]))
163 163​
164 # Filter by name 164​ # Filter by name
165 if name is not None: 165​ if name is not None:
166 q = q.filter(Job.name.like('%%%s%%' % name)) 166​ q = q.filter(Job.name.like('%%%s%%' % name))
167 167​
168 q = q.order_by(db.desc(Job.start_time == None), db.desc(Job.start_time)) 168​ q = q.order_by(db.desc(Job.start_time))
169 169​
170 return q 170​ return q
171 171​
172def select_results(since_start = None, since_end = None, outcome = None, since_source = None, job_id = None, testcase_name = None, result_data = None): 172​def select_results(since_start = None, since_end = None, outcome = None, since_source = None, job_id = None, testcase_name = None, result_data = None):
173 q = db.session.query(Result) 173​ q = db.session.query(Result)
174 174​
175 # Time constraints 175​ # Time constraints
176 176​
▲ Show 20 Lines • Show All 473 Lines • Show Last 20 Lines
View Options

resultsdb/models/results.py

Show First 20 Lines • Show All 75 Lines • ▼ Show 20 Line(s)70class Result(db.Model, DBSerialize):
76 submit_time = db.Column(db.DateTime, default = datetime.datetime.utcnow) 76​ submit_time = db.Column(db.DateTime, default = datetime.datetime.utcnow)
77 outcome = db.Column(db.Enum(*RESULT_OUTCOME, name='resultoutcome')) 77​ outcome = db.Column(db.Enum(*RESULT_OUTCOME, name='resultoutcome'))
78 summary = db.Column(db.Text) 78​ summary = db.Column(db.Text)
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__ = (
85​ db.Index('result_fk_job_id', 'job_id'),
86​ db.Index('result_fk_testcase_id', 'testcase_id'),
87​ )
88​
84 def __init__(self, job, testcase, outcome, log_url = None, summary = None): 89​ def __init__(self, job, testcase, outcome, log_url = None, summary = None):
85 self.job = job 90​ self.job = job
86 self.testcase = testcase 91​ self.testcase = testcase
87 self.outcome = outcome 92​ self.outcome = outcome
88 self.log_url = log_url 93​ self.log_url = log_url
89 self.summary = summary 94​ self.summary = summary
90 95​
91class ResultData(db.Model, DBSerialize): 96​class ResultData(db.Model, DBSerialize):
92 97​
93 id = db.Column(db.Integer, primary_key = True) 98​ id = db.Column(db.Integer, primary_key = True)
94 result_id = db.Column(db.Integer, db.ForeignKey('result.id')) 99​ result_id = db.Column(db.Integer, db.ForeignKey('result.id'))
95 100​
96 key = db.Column(db.Text) 101​ key = db.Column(db.Text)
97 value = db.Column(db.Text) 102​ value = db.Column(db.Text)
98 103​
99 __table_args__ = (db.Index('rd_key_value_idx', 'key', 'value', mysql_length={'key': 20, 'value': 50}), ) 104​ __table_args__ = (
105​ db.Index('result_data_idx_key_value', 'key', 'value',
106​ postgresql_ops={'key': 'text_pattern_ops', 'value': 'text_pattern_ops'},
107​ ),
108​ db.Index('result_data_fk_result_id', 'result_id'),
109​ )
100 110​
101 def __init__(self, result, key, value): 111​ def __init__(self, result, key, value):
102 self.result = result 112​ self.result = result
103 self.key = key 113​ self.key = key
104 self.value = value 114​ self.value = value
105 115​