Ubuntu CI Dashboard

Merge lp:~joetalbott/qa-dashboard/sql_optimization into lp:qa-dashboard

  1. sql_optimization
  2. Merge into dev
Proposed by Joe Talbott
Status: Merged
Approved by: Chris Johnston
Approved revision: 739
Merged at revision: 740
Proposed branch: lp:~joetalbott/qa-dashboard/sql_optimization
Merge into: lp:qa-dashboard
Diff against target: 283 lines (+234/-4)
4 files modified
common/migrations/0012_add_index_Artifact_name.py (+114/-0)
common/migrations/0013_add_index_Artifact_url.py (+114/-0)
common/models.py (+2/-2)
smokeng/views.py (+4/-2)
To merge this branch: bzr merge lp:~joetalbott/qa-dashboard/sql_optimization
Reviewer Review Type Date Requested Status
PS Jenkins bot continuous-integration Approve
Andy Doan (community) Approve
Chris Johnston Approve
Review via email: mp+217927@code.launchpad.net

Commit message

smokeng - Optimize crash gathering code.

Description of the change

smokeng - Optimize crash gathering code.

To post a comment you must log in.
Revision history for this message
Chris Johnston (cjohnston) wrote :

11:14:13 cjohnston | do you have any before and after data?
11:15:10 josepht | it halved the query time for utopic results for me

review: Approve
Revision history for this message
Andy Doan (doanac) wrote :

i did zero investigation, but i was hoping we just needed an index or two added to the db to help this.

review: Approve
Revision history for this message
PS Jenkins bot (ps-jenkins) wrote :

PASSED: Continuous integration, rev:739
http://s-jenkins.ubuntu-ci:8080/job/dashboard-ci/323/
Executed test runs:

Click here to trigger a rebuild:
http://s-jenkins.ubuntu-ci:8080/job/dashboard-ci/323/rebuild

review: Approve (continuous-integration)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added file 'common/migrations/0012_add_index_Artifact_name.py'
2--- common/migrations/0012_add_index_Artifact_name.py 1970-01-01 00:00:00 +0000
3+++ common/migrations/0012_add_index_Artifact_name.py 2014-05-01 15:18:22 +0000
4@@ -0,0 +1,114 @@
5+# -*- coding: utf-8 -*-
6+import datetime
7+from south.db import db
8+from south.v2 import SchemaMigration
9+from django.db import models
10+
11+
12+class Migration(SchemaMigration):
13+
14+ def forwards(self, orm):
15+ # Adding index on 'Artifact', fields ['name']
16+ db.create_index('common_artifacts', ['name'])
17+
18+
19+ def backwards(self, orm):
20+ # Removing index on 'Artifact', fields ['name']
21+ db.delete_index('common_artifacts', ['name'])
22+
23+
24+ models = {
25+ u'auth.group': {
26+ 'Meta': {'object_name': 'Group'},
27+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
28+ 'name': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '80'}),
29+ 'permissions': ('django.db.models.fields.related.ManyToManyField', [], {'to': u"orm['auth.Permission']", 'symmetrical': 'False', 'blank': 'True'})
30+ },
31+ u'auth.permission': {
32+ 'Meta': {'ordering': "(u'content_type__app_label', u'content_type__model', u'codename')", 'unique_together': "((u'content_type', u'codename'),)", 'object_name': 'Permission'},
33+ 'codename': ('django.db.models.fields.CharField', [], {'max_length': '100'}),
34+ 'content_type': ('django.db.models.fields.related.ForeignKey', [], {'to': u"orm['contenttypes.ContentType']"}),
35+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
36+ 'name': ('django.db.models.fields.CharField', [], {'max_length': '50'})
37+ },
38+ u'auth.user': {
39+ 'Meta': {'object_name': 'User'},
40+ 'date_joined': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}),
41+ 'email': ('django.db.models.fields.EmailField', [], {'max_length': '75', 'blank': 'True'}),
42+ 'first_name': ('django.db.models.fields.CharField', [], {'max_length': '30', 'blank': 'True'}),
43+ 'groups': ('django.db.models.fields.related.ManyToManyField', [], {'to': u"orm['auth.Group']", 'symmetrical': 'False', 'blank': 'True'}),
44+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
45+ 'is_active': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
46+ 'is_staff': ('django.db.models.fields.BooleanField', [], {'default': 'False'}),
47+ 'is_superuser': ('django.db.models.fields.BooleanField', [], {'default': 'False'}),
48+ 'last_login': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}),
49+ 'last_name': ('django.db.models.fields.CharField', [], {'max_length': '30', 'blank': 'True'}),
50+ 'password': ('django.db.models.fields.CharField', [], {'max_length': '128'}),
51+ 'user_permissions': ('django.db.models.fields.related.ManyToManyField', [], {'to': u"orm['auth.Permission']", 'symmetrical': 'False', 'blank': 'True'}),
52+ 'username': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '30'})
53+ },
54+ u'common.artifact': {
55+ 'Meta': {'object_name': 'Artifact', 'db_table': "'common_artifacts'"},
56+ 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
57+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
58+ 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
59+ 'jenkins_build': ('django.db.models.fields.related.ForeignKey', [], {'to': u"orm['common.JenkinsBuild']"}),
60+ 'name': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'db_index': 'True'}),
61+ 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
62+ 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'}),
63+ 'url': ('django.db.models.fields.URLField', [], {'max_length': '4096', 'db_index': 'True'})
64+ },
65+ u'common.bug': {
66+ 'Meta': {'object_name': 'Bug', 'db_table': "'bugs'"},
67+ 'assignee': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'null': 'True'}),
68+ 'bug_no': ('django.db.models.fields.CharField', [], {'max_length': '4096'}),
69+ 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
70+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
71+ 'importance': ('django.db.models.fields.CharField', [], {'default': "u'unknown'", 'max_length': '4096'}),
72+ 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
73+ 'project': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'null': 'True'}),
74+ 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
75+ 'status': ('django.db.models.fields.CharField', [], {'default': "u'unknown'", 'max_length': '4096'}),
76+ 'title': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'null': 'True'}),
77+ 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'})
78+ },
79+ u'common.jenkinsbuild': {
80+ 'Meta': {'unique_together': "(('job', 'build_number'),)", 'object_name': 'JenkinsBuild', 'db_table': "'jenkins_builds'"},
81+ 'bugs': ('django.db.models.fields.related.ManyToManyField', [], {'related_name': "'build_bugs'", 'symmetrical': 'False', 'to': u"orm['common.Bug']"}),
82+ 'build_description': ('django.db.models.fields.CharField', [], {'default': "''", 'max_length': '4096'}),
83+ 'build_number': ('django.db.models.fields.CharField', [], {'max_length': '4096'}),
84+ 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
85+ 'failed': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
86+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
87+ 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
88+ 'job': ('django.db.models.fields.related.ForeignKey', [], {'to': u"orm['common.JenkinsJob']"}),
89+ 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
90+ 'ran_at': ('django.db.models.fields.DateTimeField', [], {}),
91+ 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'})
92+ },
93+ u'common.jenkinsjob': {
94+ 'Meta': {'object_name': 'JenkinsJob', 'db_table': "'jenkins_jobs'"},
95+ 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
96+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
97+ 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
98+ 'name': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '4096'}),
99+ 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
100+ 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'}),
101+ 'url': ('django.db.models.fields.URLField', [], {'max_length': '4096'})
102+ },
103+ u'common.userprofile': {
104+ 'Meta': {'object_name': 'UserProfile', 'db_table': "'custom_user_profile'"},
105+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
106+ 'use_private_url': ('django.db.models.fields.BooleanField', [], {'default': 'False'}),
107+ 'user': ('django.db.models.fields.related.OneToOneField', [], {'to': u"orm['auth.User']", 'unique': 'True'})
108+ },
109+ u'contenttypes.contenttype': {
110+ 'Meta': {'ordering': "('name',)", 'unique_together': "(('app_label', 'model'),)", 'object_name': 'ContentType', 'db_table': "'django_content_type'"},
111+ 'app_label': ('django.db.models.fields.CharField', [], {'max_length': '100'}),
112+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
113+ 'model': ('django.db.models.fields.CharField', [], {'max_length': '100'}),
114+ 'name': ('django.db.models.fields.CharField', [], {'max_length': '100'})
115+ }
116+ }
117+
118+ complete_apps = ['common']
119\ No newline at end of file
120
121=== added file 'common/migrations/0013_add_index_Artifact_url.py'
122--- common/migrations/0013_add_index_Artifact_url.py 1970-01-01 00:00:00 +0000
123+++ common/migrations/0013_add_index_Artifact_url.py 2014-05-01 15:18:22 +0000
124@@ -0,0 +1,114 @@
125+# -*- coding: utf-8 -*-
126+import datetime
127+from south.db import db
128+from south.v2 import SchemaMigration
129+from django.db import models
130+
131+
132+class Migration(SchemaMigration):
133+
134+ def forwards(self, orm):
135+ # Adding index on 'Artifact', fields ['url']
136+ db.create_index('common_artifacts', ['url'])
137+
138+
139+ def backwards(self, orm):
140+ # Removing index on 'Artifact', fields ['url']
141+ db.delete_index('common_artifacts', ['url'])
142+
143+
144+ models = {
145+ u'auth.group': {
146+ 'Meta': {'object_name': 'Group'},
147+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
148+ 'name': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '80'}),
149+ 'permissions': ('django.db.models.fields.related.ManyToManyField', [], {'to': u"orm['auth.Permission']", 'symmetrical': 'False', 'blank': 'True'})
150+ },
151+ u'auth.permission': {
152+ 'Meta': {'ordering': "(u'content_type__app_label', u'content_type__model', u'codename')", 'unique_together': "((u'content_type', u'codename'),)", 'object_name': 'Permission'},
153+ 'codename': ('django.db.models.fields.CharField', [], {'max_length': '100'}),
154+ 'content_type': ('django.db.models.fields.related.ForeignKey', [], {'to': u"orm['contenttypes.ContentType']"}),
155+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
156+ 'name': ('django.db.models.fields.CharField', [], {'max_length': '50'})
157+ },
158+ u'auth.user': {
159+ 'Meta': {'object_name': 'User'},
160+ 'date_joined': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}),
161+ 'email': ('django.db.models.fields.EmailField', [], {'max_length': '75', 'blank': 'True'}),
162+ 'first_name': ('django.db.models.fields.CharField', [], {'max_length': '30', 'blank': 'True'}),
163+ 'groups': ('django.db.models.fields.related.ManyToManyField', [], {'to': u"orm['auth.Group']", 'symmetrical': 'False', 'blank': 'True'}),
164+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
165+ 'is_active': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
166+ 'is_staff': ('django.db.models.fields.BooleanField', [], {'default': 'False'}),
167+ 'is_superuser': ('django.db.models.fields.BooleanField', [], {'default': 'False'}),
168+ 'last_login': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}),
169+ 'last_name': ('django.db.models.fields.CharField', [], {'max_length': '30', 'blank': 'True'}),
170+ 'password': ('django.db.models.fields.CharField', [], {'max_length': '128'}),
171+ 'user_permissions': ('django.db.models.fields.related.ManyToManyField', [], {'to': u"orm['auth.Permission']", 'symmetrical': 'False', 'blank': 'True'}),
172+ 'username': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '30'})
173+ },
174+ u'common.artifact': {
175+ 'Meta': {'object_name': 'Artifact', 'db_table': "'common_artifacts'"},
176+ 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
177+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
178+ 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
179+ 'jenkins_build': ('django.db.models.fields.related.ForeignKey', [], {'to': u"orm['common.JenkinsBuild']"}),
180+ 'name': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'db_index': 'True'}),
181+ 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
182+ 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'}),
183+ 'url': ('django.db.models.fields.URLField', [], {'max_length': '4096', 'db_index': 'True'})
184+ },
185+ u'common.bug': {
186+ 'Meta': {'object_name': 'Bug', 'db_table': "'bugs'"},
187+ 'assignee': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'null': 'True'}),
188+ 'bug_no': ('django.db.models.fields.CharField', [], {'max_length': '4096'}),
189+ 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
190+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
191+ 'importance': ('django.db.models.fields.CharField', [], {'default': "u'unknown'", 'max_length': '4096'}),
192+ 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
193+ 'project': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'null': 'True'}),
194+ 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
195+ 'status': ('django.db.models.fields.CharField', [], {'default': "u'unknown'", 'max_length': '4096'}),
196+ 'title': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'null': 'True'}),
197+ 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'})
198+ },
199+ u'common.jenkinsbuild': {
200+ 'Meta': {'unique_together': "(('job', 'build_number'),)", 'object_name': 'JenkinsBuild', 'db_table': "'jenkins_builds'"},
201+ 'bugs': ('django.db.models.fields.related.ManyToManyField', [], {'related_name': "'build_bugs'", 'symmetrical': 'False', 'to': u"orm['common.Bug']"}),
202+ 'build_description': ('django.db.models.fields.CharField', [], {'default': "''", 'max_length': '4096'}),
203+ 'build_number': ('django.db.models.fields.CharField', [], {'max_length': '4096'}),
204+ 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
205+ 'failed': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
206+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
207+ 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
208+ 'job': ('django.db.models.fields.related.ForeignKey', [], {'to': u"orm['common.JenkinsJob']"}),
209+ 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
210+ 'ran_at': ('django.db.models.fields.DateTimeField', [], {}),
211+ 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'})
212+ },
213+ u'common.jenkinsjob': {
214+ 'Meta': {'object_name': 'JenkinsJob', 'db_table': "'jenkins_jobs'"},
215+ 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
216+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
217+ 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
218+ 'name': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '4096'}),
219+ 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
220+ 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'}),
221+ 'url': ('django.db.models.fields.URLField', [], {'max_length': '4096'})
222+ },
223+ u'common.userprofile': {
224+ 'Meta': {'object_name': 'UserProfile', 'db_table': "'custom_user_profile'"},
225+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
226+ 'use_private_url': ('django.db.models.fields.BooleanField', [], {'default': 'False'}),
227+ 'user': ('django.db.models.fields.related.OneToOneField', [], {'to': u"orm['auth.User']", 'unique': 'True'})
228+ },
229+ u'contenttypes.contenttype': {
230+ 'Meta': {'ordering': "('name',)", 'unique_together': "(('app_label', 'model'),)", 'object_name': 'ContentType', 'db_table': "'django_content_type'"},
231+ 'app_label': ('django.db.models.fields.CharField', [], {'max_length': '100'}),
232+ u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
233+ 'model': ('django.db.models.fields.CharField', [], {'max_length': '100'}),
234+ 'name': ('django.db.models.fields.CharField', [], {'max_length': '100'})
235+ }
236+ }
237+
238+ complete_apps = ['common']
239\ No newline at end of file
240
241=== modified file 'common/models.py'
242--- common/models.py 2013-10-24 13:47:27 +0000
243+++ common/models.py 2014-05-01 15:18:22 +0000
244@@ -159,8 +159,8 @@
245 class Meta:
246 db_table = "common_artifacts"
247
248- url = models.URLField(max_length=4096)
249- name = models.CharField(max_length=4096)
250+ url = models.URLField(max_length=4096, db_index=True)
251+ name = models.CharField(max_length=4096, db_index=True)
252 jenkins_build = models.ForeignKey(JenkinsBuild)
253
254
255
256=== modified file 'smokeng/views.py'
257--- smokeng/views.py 2014-02-13 20:51:20 +0000
258+++ smokeng/views.py 2014-05-01 15:18:22 +0000
259@@ -90,7 +90,7 @@
260 passrates = {}
261
262 bugs = _get_bugs(release)
263- crashes = _get_crashes()
264+ crashes = _get_crashes(release)
265 bug_data = {}
266 for result in results:
267 _append_totals(totals, result, crashes)
268@@ -124,12 +124,14 @@
269 return totals, bug_data
270
271
272-def _get_crashes():
273+def _get_crashes(release):
274 crashes = SmokeResult.objects.filter(
275 publish=True,
276 jenkins_build__artifact__name__endswith='.crash',
277+ image__release=release,
278 ).distinct(
279 'image__id',
280+ 'jenkins_build__id',
281 'jenkins_build__artifact__url',
282 ).values(
283 'image__id',

Subscribers

People subscribed via source and target branches