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
=== added file 'common/migrations/0012_add_index_Artifact_name.py'
--- common/migrations/0012_add_index_Artifact_name.py 1970-01-01 00:00:00 +0000
+++ common/migrations/0012_add_index_Artifact_name.py 2014-05-01 15:18:22 +0000
@@ -0,0 +1,114 @@
1# -*- coding: utf-8 -*-
2import datetime
3from south.db import db
4from south.v2 import SchemaMigration
5from django.db import models
6
7
8class Migration(SchemaMigration):
9
10 def forwards(self, orm):
11 # Adding index on 'Artifact', fields ['name']
12 db.create_index('common_artifacts', ['name'])
13
14
15 def backwards(self, orm):
16 # Removing index on 'Artifact', fields ['name']
17 db.delete_index('common_artifacts', ['name'])
18
19
20 models = {
21 u'auth.group': {
22 'Meta': {'object_name': 'Group'},
23 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
24 'name': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '80'}),
25 'permissions': ('django.db.models.fields.related.ManyToManyField', [], {'to': u"orm['auth.Permission']", 'symmetrical': 'False', 'blank': 'True'})
26 },
27 u'auth.permission': {
28 'Meta': {'ordering': "(u'content_type__app_label', u'content_type__model', u'codename')", 'unique_together': "((u'content_type', u'codename'),)", 'object_name': 'Permission'},
29 'codename': ('django.db.models.fields.CharField', [], {'max_length': '100'}),
30 'content_type': ('django.db.models.fields.related.ForeignKey', [], {'to': u"orm['contenttypes.ContentType']"}),
31 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
32 'name': ('django.db.models.fields.CharField', [], {'max_length': '50'})
33 },
34 u'auth.user': {
35 'Meta': {'object_name': 'User'},
36 'date_joined': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}),
37 'email': ('django.db.models.fields.EmailField', [], {'max_length': '75', 'blank': 'True'}),
38 'first_name': ('django.db.models.fields.CharField', [], {'max_length': '30', 'blank': 'True'}),
39 'groups': ('django.db.models.fields.related.ManyToManyField', [], {'to': u"orm['auth.Group']", 'symmetrical': 'False', 'blank': 'True'}),
40 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
41 'is_active': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
42 'is_staff': ('django.db.models.fields.BooleanField', [], {'default': 'False'}),
43 'is_superuser': ('django.db.models.fields.BooleanField', [], {'default': 'False'}),
44 'last_login': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}),
45 'last_name': ('django.db.models.fields.CharField', [], {'max_length': '30', 'blank': 'True'}),
46 'password': ('django.db.models.fields.CharField', [], {'max_length': '128'}),
47 'user_permissions': ('django.db.models.fields.related.ManyToManyField', [], {'to': u"orm['auth.Permission']", 'symmetrical': 'False', 'blank': 'True'}),
48 'username': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '30'})
49 },
50 u'common.artifact': {
51 'Meta': {'object_name': 'Artifact', 'db_table': "'common_artifacts'"},
52 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
53 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
54 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
55 'jenkins_build': ('django.db.models.fields.related.ForeignKey', [], {'to': u"orm['common.JenkinsBuild']"}),
56 'name': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'db_index': 'True'}),
57 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
58 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'}),
59 'url': ('django.db.models.fields.URLField', [], {'max_length': '4096', 'db_index': 'True'})
60 },
61 u'common.bug': {
62 'Meta': {'object_name': 'Bug', 'db_table': "'bugs'"},
63 'assignee': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'null': 'True'}),
64 'bug_no': ('django.db.models.fields.CharField', [], {'max_length': '4096'}),
65 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
66 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
67 'importance': ('django.db.models.fields.CharField', [], {'default': "u'unknown'", 'max_length': '4096'}),
68 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
69 'project': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'null': 'True'}),
70 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
71 'status': ('django.db.models.fields.CharField', [], {'default': "u'unknown'", 'max_length': '4096'}),
72 'title': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'null': 'True'}),
73 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'})
74 },
75 u'common.jenkinsbuild': {
76 'Meta': {'unique_together': "(('job', 'build_number'),)", 'object_name': 'JenkinsBuild', 'db_table': "'jenkins_builds'"},
77 'bugs': ('django.db.models.fields.related.ManyToManyField', [], {'related_name': "'build_bugs'", 'symmetrical': 'False', 'to': u"orm['common.Bug']"}),
78 'build_description': ('django.db.models.fields.CharField', [], {'default': "''", 'max_length': '4096'}),
79 'build_number': ('django.db.models.fields.CharField', [], {'max_length': '4096'}),
80 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
81 'failed': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
82 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
83 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
84 'job': ('django.db.models.fields.related.ForeignKey', [], {'to': u"orm['common.JenkinsJob']"}),
85 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
86 'ran_at': ('django.db.models.fields.DateTimeField', [], {}),
87 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'})
88 },
89 u'common.jenkinsjob': {
90 'Meta': {'object_name': 'JenkinsJob', 'db_table': "'jenkins_jobs'"},
91 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
92 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
93 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
94 'name': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '4096'}),
95 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
96 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'}),
97 'url': ('django.db.models.fields.URLField', [], {'max_length': '4096'})
98 },
99 u'common.userprofile': {
100 'Meta': {'object_name': 'UserProfile', 'db_table': "'custom_user_profile'"},
101 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
102 'use_private_url': ('django.db.models.fields.BooleanField', [], {'default': 'False'}),
103 'user': ('django.db.models.fields.related.OneToOneField', [], {'to': u"orm['auth.User']", 'unique': 'True'})
104 },
105 u'contenttypes.contenttype': {
106 'Meta': {'ordering': "('name',)", 'unique_together': "(('app_label', 'model'),)", 'object_name': 'ContentType', 'db_table': "'django_content_type'"},
107 'app_label': ('django.db.models.fields.CharField', [], {'max_length': '100'}),
108 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
109 'model': ('django.db.models.fields.CharField', [], {'max_length': '100'}),
110 'name': ('django.db.models.fields.CharField', [], {'max_length': '100'})
111 }
112 }
113
114 complete_apps = ['common']
0\ No newline at end of file115\ No newline at end of file
1116
=== added file 'common/migrations/0013_add_index_Artifact_url.py'
--- common/migrations/0013_add_index_Artifact_url.py 1970-01-01 00:00:00 +0000
+++ common/migrations/0013_add_index_Artifact_url.py 2014-05-01 15:18:22 +0000
@@ -0,0 +1,114 @@
1# -*- coding: utf-8 -*-
2import datetime
3from south.db import db
4from south.v2 import SchemaMigration
5from django.db import models
6
7
8class Migration(SchemaMigration):
9
10 def forwards(self, orm):
11 # Adding index on 'Artifact', fields ['url']
12 db.create_index('common_artifacts', ['url'])
13
14
15 def backwards(self, orm):
16 # Removing index on 'Artifact', fields ['url']
17 db.delete_index('common_artifacts', ['url'])
18
19
20 models = {
21 u'auth.group': {
22 'Meta': {'object_name': 'Group'},
23 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
24 'name': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '80'}),
25 'permissions': ('django.db.models.fields.related.ManyToManyField', [], {'to': u"orm['auth.Permission']", 'symmetrical': 'False', 'blank': 'True'})
26 },
27 u'auth.permission': {
28 'Meta': {'ordering': "(u'content_type__app_label', u'content_type__model', u'codename')", 'unique_together': "((u'content_type', u'codename'),)", 'object_name': 'Permission'},
29 'codename': ('django.db.models.fields.CharField', [], {'max_length': '100'}),
30 'content_type': ('django.db.models.fields.related.ForeignKey', [], {'to': u"orm['contenttypes.ContentType']"}),
31 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
32 'name': ('django.db.models.fields.CharField', [], {'max_length': '50'})
33 },
34 u'auth.user': {
35 'Meta': {'object_name': 'User'},
36 'date_joined': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}),
37 'email': ('django.db.models.fields.EmailField', [], {'max_length': '75', 'blank': 'True'}),
38 'first_name': ('django.db.models.fields.CharField', [], {'max_length': '30', 'blank': 'True'}),
39 'groups': ('django.db.models.fields.related.ManyToManyField', [], {'to': u"orm['auth.Group']", 'symmetrical': 'False', 'blank': 'True'}),
40 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
41 'is_active': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
42 'is_staff': ('django.db.models.fields.BooleanField', [], {'default': 'False'}),
43 'is_superuser': ('django.db.models.fields.BooleanField', [], {'default': 'False'}),
44 'last_login': ('django.db.models.fields.DateTimeField', [], {'default': 'datetime.datetime.now'}),
45 'last_name': ('django.db.models.fields.CharField', [], {'max_length': '30', 'blank': 'True'}),
46 'password': ('django.db.models.fields.CharField', [], {'max_length': '128'}),
47 'user_permissions': ('django.db.models.fields.related.ManyToManyField', [], {'to': u"orm['auth.Permission']", 'symmetrical': 'False', 'blank': 'True'}),
48 'username': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '30'})
49 },
50 u'common.artifact': {
51 'Meta': {'object_name': 'Artifact', 'db_table': "'common_artifacts'"},
52 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
53 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
54 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
55 'jenkins_build': ('django.db.models.fields.related.ForeignKey', [], {'to': u"orm['common.JenkinsBuild']"}),
56 'name': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'db_index': 'True'}),
57 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
58 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'}),
59 'url': ('django.db.models.fields.URLField', [], {'max_length': '4096', 'db_index': 'True'})
60 },
61 u'common.bug': {
62 'Meta': {'object_name': 'Bug', 'db_table': "'bugs'"},
63 'assignee': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'null': 'True'}),
64 'bug_no': ('django.db.models.fields.CharField', [], {'max_length': '4096'}),
65 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
66 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
67 'importance': ('django.db.models.fields.CharField', [], {'default': "u'unknown'", 'max_length': '4096'}),
68 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
69 'project': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'null': 'True'}),
70 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
71 'status': ('django.db.models.fields.CharField', [], {'default': "u'unknown'", 'max_length': '4096'}),
72 'title': ('django.db.models.fields.CharField', [], {'max_length': '4096', 'null': 'True'}),
73 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'})
74 },
75 u'common.jenkinsbuild': {
76 'Meta': {'unique_together': "(('job', 'build_number'),)", 'object_name': 'JenkinsBuild', 'db_table': "'jenkins_builds'"},
77 'bugs': ('django.db.models.fields.related.ManyToManyField', [], {'related_name': "'build_bugs'", 'symmetrical': 'False', 'to': u"orm['common.Bug']"}),
78 'build_description': ('django.db.models.fields.CharField', [], {'default': "''", 'max_length': '4096'}),
79 'build_number': ('django.db.models.fields.CharField', [], {'max_length': '4096'}),
80 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
81 'failed': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
82 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
83 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
84 'job': ('django.db.models.fields.related.ForeignKey', [], {'to': u"orm['common.JenkinsJob']"}),
85 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
86 'ran_at': ('django.db.models.fields.DateTimeField', [], {}),
87 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'})
88 },
89 u'common.jenkinsjob': {
90 'Meta': {'object_name': 'JenkinsJob', 'db_table': "'jenkins_jobs'"},
91 'created_at': ('django.db.models.fields.DateTimeField', [], {'auto_now_add': 'True', 'blank': 'True'}),
92 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
93 'internal': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
94 'name': ('django.db.models.fields.CharField', [], {'unique': 'True', 'max_length': '4096'}),
95 'publish': ('django.db.models.fields.BooleanField', [], {'default': 'True'}),
96 'updated_at': ('django.db.models.fields.DateTimeField', [], {'auto_now': 'True', 'blank': 'True'}),
97 'url': ('django.db.models.fields.URLField', [], {'max_length': '4096'})
98 },
99 u'common.userprofile': {
100 'Meta': {'object_name': 'UserProfile', 'db_table': "'custom_user_profile'"},
101 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
102 'use_private_url': ('django.db.models.fields.BooleanField', [], {'default': 'False'}),
103 'user': ('django.db.models.fields.related.OneToOneField', [], {'to': u"orm['auth.User']", 'unique': 'True'})
104 },
105 u'contenttypes.contenttype': {
106 'Meta': {'ordering': "('name',)", 'unique_together': "(('app_label', 'model'),)", 'object_name': 'ContentType', 'db_table': "'django_content_type'"},
107 'app_label': ('django.db.models.fields.CharField', [], {'max_length': '100'}),
108 u'id': ('django.db.models.fields.AutoField', [], {'primary_key': 'True'}),
109 'model': ('django.db.models.fields.CharField', [], {'max_length': '100'}),
110 'name': ('django.db.models.fields.CharField', [], {'max_length': '100'})
111 }
112 }
113
114 complete_apps = ['common']
0\ No newline at end of file115\ No newline at end of file
1116
=== modified file 'common/models.py'
--- common/models.py 2013-10-24 13:47:27 +0000
+++ common/models.py 2014-05-01 15:18:22 +0000
@@ -159,8 +159,8 @@
159 class Meta:159 class Meta:
160 db_table = "common_artifacts"160 db_table = "common_artifacts"
161161
162 url = models.URLField(max_length=4096)162 url = models.URLField(max_length=4096, db_index=True)
163 name = models.CharField(max_length=4096)163 name = models.CharField(max_length=4096, db_index=True)
164 jenkins_build = models.ForeignKey(JenkinsBuild)164 jenkins_build = models.ForeignKey(JenkinsBuild)
165165
166166
167167
=== modified file 'smokeng/views.py'
--- smokeng/views.py 2014-02-13 20:51:20 +0000
+++ smokeng/views.py 2014-05-01 15:18:22 +0000
@@ -90,7 +90,7 @@
90 passrates = {}90 passrates = {}
9191
92 bugs = _get_bugs(release)92 bugs = _get_bugs(release)
93 crashes = _get_crashes()93 crashes = _get_crashes(release)
94 bug_data = {}94 bug_data = {}
95 for result in results:95 for result in results:
96 _append_totals(totals, result, crashes)96 _append_totals(totals, result, crashes)
@@ -124,12 +124,14 @@
124 return totals, bug_data124 return totals, bug_data
125125
126126
127def _get_crashes():127def _get_crashes(release):
128 crashes = SmokeResult.objects.filter(128 crashes = SmokeResult.objects.filter(
129 publish=True,129 publish=True,
130 jenkins_build__artifact__name__endswith='.crash',130 jenkins_build__artifact__name__endswith='.crash',
131 image__release=release,
131 ).distinct(132 ).distinct(
132 'image__id',133 'image__id',
134 'jenkins_build__id',
133 'jenkins_build__artifact__url',135 'jenkins_build__artifact__url',
134 ).values(136 ).values(
135 'image__id',137 'image__id',

Subscribers

People subscribed via source and target branches