Merge lp:~james-w/python-oops-tools/recent-oopses into lp:python-oops-tools

Proposed by James Westby
Status: Needs review
Proposed branch: lp:~james-w/python-oops-tools/recent-oopses
Merge into: lp:python-oops-tools
Diff against target: 142 lines (+28/-58)
2 files modified
src/oopstools/oops/templates/report.html (+8/-23)
src/oopstools/oops/views.py (+20/-35)
To merge this branch: bzr merge lp:~james-w/python-oops-tools/recent-oopses
Reviewer Review Type Date Requested Status
Natalia Bidart (community) Approve
python-oops-tools reviewers Pending
Review via email: mp+201639@code.launchpad.net

Commit message

Modify the recent oopses query to perform better with lots of oopses.

When there is a report with many prefixes, and the most recent oopses are
not for any of those prefixes postgres will pick a query plan that performs
poorly (not using the most selective index, assuming that the prefix ids
are distributed fairly uniformly through the oopses.)

We instead trick postgres in to using the more selective index using a window
function. Thanks to stub for the query.

We do a second query to load all the infestations for these oopses, to prevent
the ORM from doing 50 queries as we iterate over the list of oopses.

We also drop the pagination because it is not as simple to implement with the
raw query. It could be re-instated if it was really needed, but usually
I find looking at the most recent 50 oopses is sufficient.

Lastly, I re-ordered the columns in the oops listing, to put the oops
first as that is usually what you want to click on. I also dropped the linking
of the URL, as the escaping meant that it never worked, and the URL is often
not something you can simply click on either. The fact that the URL was first
in the table and a link meant that I often clicked on that rather than the
oops, wasting time.

Description of the change

Hi,

This is a few changes to hopefully improve the performance of some recent
oops views. The commit message has all the details.

Thanks,

James

To post a comment you must log in.
Revision history for this message
Natalia Bidart (nataliabidart) wrote :

Looks good!

review: Approve
Revision history for this message
Launchpad PQM Bot (launchpad-pqm) wrote :
Download full text (84.9 KiB)

The attempt to merge lp:~james-w/python-oops-tools/recent-oopses into lp:python-oops-tools failed.Below is the output from the failed tests.

find . -type f -name '*.py[co]' -exec rm -f {} \;
rm -f bin/buildout
rm -f src/oopstools/settings.py
bzr clean-tree --unknown --force
bzr up download-cache
python bootstrap.py \
  --setup-source=download-cache/ez_setup.py \
  --download-base=download-cache/dist --eggs=eggs \
  --version=1.5.2
Generated script '/home/pqm/pqm-workdir/oops-tools/bin/buildout'.
bin/buildout configuration:db-port=5433
Develop: '/home/pqm/pqm-workdir/oops-tools/.'
Uninstalling filetemplates.
Updating scripts.
Installing filetemplates.
Updating django.
Updating docs.
Updating tags.
bin/test
Creating test database for alias 'default'...
Destroying test database for alias 'default'...

W: line 4 [buildbot-staging]: Deprecated key 'location' used
I: This option will be removed in the future; please update your configuration
W: line 2 [pqm]: Deprecated key 'location' used
I: This option will be removed in the future; please update your configuration
W: line 3 [pqm-oops-tools]: Deprecated key 'location' used
I: This option will be removed in the future; please update your configuration
W: line 1 [precise]: Deprecated key 'location' used
I: This option will be removed in the future; please update your configuration
I: [pqm-oops-tools chroot] Running command: "cd /home/pqm/pqm-workdir/oops-tools && make check"
Nothing to delete.
+N dist/bzr-2.6.0.tar.gz
+N dist/d2to1-0.2.10.tar.gz
+N dist/dkimpy-0.5.4.tar.gz
+N dist/keyring-1.2.2-notestrunner.zip
+N dist/mock-1.0.1.tar.gz
+N dist/oslo.config-1.1.1.tar.gz
+N dist/pbr-0.5.20.tar.gz
+N dist/pip-1.4.tar.gz
+N dist/prettytable-0.7.2.tar.bz2
+N dist/python-keystoneclient-0.3.1.tar.gz
+N dist/python-swiftclient-1.5.0.tar.gz
+N dist/python_keystoneclient-0.3.1-py2.6.egg
+N dist/python_keystoneclient-0.3.1-py2.7.egg
+N dist/python_swiftclient-1.5.0-py2.6.egg
+N dist/python_swiftclient-1.5.0-py2.7.egg
+N dist/requests-1.2.3.tar.gz
+N dist/s4-0.1.2.tar.gz
+N dist/setuptools-git-1.0.tar.gz
+N dist/six-1.3.0.tar.gz
+N dist/yui_3.10.3.zip
All changes applied successfully.
Updated to revision 568 of branch http://bazaar.launchpad.net/~launchpad/lp-source-dependencies/trunk
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/django/template/loaders/filesystem.py:58: DeprecationWarning: 'django.template.loaders.filesystem.load_template_source' is deprecated; use 'django.template.loaders.filesystem.Loader' instead.
  DeprecationWarning
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/django/template/loaders/app_directories.py:71: DeprecationWarning: 'django.template.loaders.app_directories.load_template_source' is deprecated; use 'django.template.loaders.app_directories.Loader' instead.
  DeprecationWarning
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/django/contrib/auth/__init__.py:26: DeprecationWarning: Authentication backends without a `supports_object_permissions` attribute are deprecated. Please define it in <class 'django_openid_auth.auth.OpenIDBackend'>.
  DeprecationWarning)
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/d...

Revision history for this message
Launchpad PQM Bot (launchpad-pqm) wrote :
Download full text (7.1 KiB)

The attempt to merge lp:~james-w/python-oops-tools/recent-oopses into lp:python-oops-tools failed.Below is the output from the failed tests.

find . -type f -name '*.py[co]' -exec rm -f {} \;
rm -f bin/buildout
rm -f src/oopstools/settings.py
bzr clean-tree --unknown --force
bzr up download-cache
python bootstrap.py \
  --setup-source=download-cache/ez_setup.py \
  --download-base=download-cache/dist --eggs=eggs \
  --version=1.5.2
Generated script '/home/pqm/pqm-workdir/oops-tools/bin/buildout'.
bin/buildout configuration:db-port=5433
Develop: '/home/pqm/pqm-workdir/oops-tools/.'
Uninstalling filetemplates.
Updating scripts.
Installing filetemplates.
Updating django.
Updating docs.
Updating tags.
bin/test
Creating test database for alias 'default'...
Destroying test database for alias 'default'...

W: line 4 [buildbot-staging]: Deprecated key 'location' used
I: This option will be removed in the future; please update your configuration
W: line 2 [pqm]: Deprecated key 'location' used
I: This option will be removed in the future; please update your configuration
W: line 3 [pqm-oops-tools]: Deprecated key 'location' used
I: This option will be removed in the future; please update your configuration
W: line 1 [precise]: Deprecated key 'location' used
I: This option will be removed in the future; please update your configuration
I: [pqm-oops-tools chroot] Running command: "cd /home/pqm/pqm-workdir/oops-tools && make check"
Nothing to delete.
+N dist/cssselect-0.9.1.tar.gz
+N dist/subvertpy-0.9.1.tar.gz
All changes applied successfully.
Updated to revision 569 of branch http://bazaar.launchpad.net/~launchpad/lp-source-dependencies/trunk
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/django/template/loaders/filesystem.py:58: DeprecationWarning: 'django.template.loaders.filesystem.load_template_source' is deprecated; use 'django.template.loaders.filesystem.Loader' instead.
  DeprecationWarning
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/django/template/loaders/app_directories.py:71: DeprecationWarning: 'django.template.loaders.app_directories.load_template_source' is deprecated; use 'django.template.loaders.app_directories.Loader' instead.
  DeprecationWarning
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/django/contrib/auth/__init__.py:26: DeprecationWarning: Authentication backends without a `supports_object_permissions` attribute are deprecated. Please define it in <class 'django_openid_auth.auth.OpenIDBackend'>.
  DeprecationWarning)
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/django/contrib/auth/__init__.py:31: DeprecationWarning: Authentication backends without a `supports_anonymous_user` attribute are deprecated. Please define it in <class 'django_openid_auth.auth.OpenIDBackend'>.
  DeprecationWarning)
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/django/contrib/auth/models.py:393: DeprecationWarning: The user messaging API is deprecated. Please update your code to use the new messages framework.
  category=DeprecationWarning)
........................................E.EE
======================================================================
ERROR: test_no_recent_o...

Read more...

Revision history for this message
Launchpad PQM Bot (launchpad-pqm) wrote :
Download full text (9.1 KiB)

The attempt to merge lp:~james-w/python-oops-tools/recent-oopses into lp:python-oops-tools failed.Below is the output from the failed tests.

find . -type f -name '*.py[co]' -exec rm -f {} \;
rm -f bin/buildout
rm -f src/oopstools/settings.py
bzr clean-tree --unknown --force
bzr up download-cache
python bootstrap.py \
  --setup-source=download-cache/ez_setup.py \
  --download-base=download-cache/dist --eggs=eggs \
  --version=1.5.2
Generated script '/home/pqm/pqm-workdir/oops-tools/bin/buildout'.
bin/buildout configuration:db-port=5433
Develop: '/home/pqm/pqm-workdir/oops-tools/.'
Uninstalling filetemplates.
Updating scripts.
Installing filetemplates.
Updating django.
Updating docs.
Updating tags.
bin/test
Creating test database for alias 'default'...
Destroying test database for alias 'default'...

W: line 4 [buildbot-staging]: Deprecated key 'location' used
I: This option will be removed in the future; please update your configuration
W: line 2 [pqm]: Deprecated key 'location' used
I: This option will be removed in the future; please update your configuration
W: line 3 [pqm-oops-tools]: Deprecated key 'location' used
I: This option will be removed in the future; please update your configuration
W: line 1 [precise]: Deprecated key 'location' used
I: This option will be removed in the future; please update your configuration
I: [pqm-oops-tools chroot] Running command: "cd /home/pqm/pqm-workdir/oops-tools && make check"
Nothing to delete.
Tree is up to date at revision 569 of branch http://bazaar.launchpad.net/~launchpad/lp-source-dependencies/trunk
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/django/template/loaders/filesystem.py:58: DeprecationWarning: 'django.template.loaders.filesystem.load_template_source' is deprecated; use 'django.template.loaders.filesystem.Loader' instead.
  DeprecationWarning
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/django/template/loaders/app_directories.py:71: DeprecationWarning: 'django.template.loaders.app_directories.load_template_source' is deprecated; use 'django.template.loaders.app_directories.Loader' instead.
  DeprecationWarning
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/django/contrib/auth/__init__.py:26: DeprecationWarning: Authentication backends without a `supports_object_permissions` attribute are deprecated. Please define it in <class 'django_openid_auth.auth.OpenIDBackend'>.
  DeprecationWarning)
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/django/contrib/auth/__init__.py:31: DeprecationWarning: Authentication backends without a `supports_anonymous_user` attribute are deprecated. Please define it in <class 'django_openid_auth.auth.OpenIDBackend'>.
  DeprecationWarning)
/home/pqm/pqm-workdir/oops-tools/eggs/Django-1.3.3-py2.6.egg/django/contrib/auth/models.py:393: DeprecationWarning: The user messaging API is deprecated. Please update your code to use the new messages framework.
  category=DeprecationWarning)
........................................E.EE
======================================================================
ERROR: test_no_recent_oopses (test_report.ReportTests)
-------------------------------------------------------...

Read more...

Unmerged revisions

59. By James Westby

Correct the variable name.

58. By James Westby

Revert to old python syntax until tests are run on a modern python.

57. By James Westby

Use more expressions. Thanks nessita.

56. By James Westby

Modify the recent oopses query to perform better with lots of oopses.

When there is a report with many prefixes, and the most recent oopses are
not for any of those prefixes postgres will pick a query plan that performs
poorly (not using the most selective index, assuming that the prefix ids
are distributed fairly uniformly through the oopses.)

We instead trick postgres in to using the more selective index using a window
function. Thanks to stub for the query.

We do a second query to load all the infestations for these oopses, to prevent
the ORM from doing 50 queries as we iterate over the list of oopses.

We also drop the pagination because it is not as simple to implement with the
raw query. It could be re-instated if it was really needed, but usually
I find looking at the most recent 50 oopses is sufficient.

Lastly, I re-ordered the columns in the oops listing, to put the oops
first as that is usually what you want to click on. I also dropped the linking
of the URL, as the escaping meant that it never worked, and the URL is often
not something you can simply click on either. The fact that the URL was first
in the table and a link meant that I often clicked on that rather than the
oops, wasting time.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== modified file 'src/oopstools/oops/templates/report.html'
--- src/oopstools/oops/templates/report.html 2012-11-29 21:33:52 +0000
+++ src/oopstools/oops/templates/report.html 2014-01-31 16:38:48 +0000
@@ -17,37 +17,22 @@
17 <table>17 <table>
18 <th><tr>18 <th><tr>
19 <td>Date/Time</td>19 <td>Date/Time</td>
20 <td>Oops</td>
21 <td>Exception Type</td>
22 <td>Duration (ms)</td>
20 <td>HTTP Method</td>23 <td>HTTP Method</td>
21 <td>URL</td>24 <td>URL</td>
22 <td>Duration (ms)</td>
23 <td>Exception Type</td>
24 <td>Oops</td>
25 </tr></th>25 </tr></th>
26 {% for oops in recent.object_list %}26 {% for oops, exception_type in recent %}
27 <tr>27 <tr>
28 <td>{{oops.date|date:"Y-m-d H:i:s"}}</td>28 <td>{{oops.date|date:"Y-m-d H:i:s"}}</td>
29 <td><a href="{% url oopstools.oops.views.index %}?oopsid={{oops.oopsid}}">{{oops.oopsid}}</a></td>
30 <td>{{exception_type}}</td>
31 <td>{{oops.total_time}}</td>
29 <td>{{oops.http_method}}</td>32 <td>{{oops.http_method}}</td>
30 <td>{% if oops.url %}<a href="{{oops.url}}">{{oops.url}}</a>{% endif %}</td>33 <td>{% if oops.url %}{{oops.url}}{% endif %}</td>
31 <td>{{oops.total_time}}</td>
32 <td>{{oops.exception_type}}</td>
33 <td><a href="{% url oopstools.oops.views.index %}?oopsid={{oops.oopsid}}">{{oops.oopsid}}</a></td>
34 </tr>34 </tr>
35 {% endfor %}35 {% endfor %}
36 </table>36 </table>
37 <div class="pagination">
38 <span class="step-links">
39 {% if recent.has_previous %}
40 <a href="?page={{ recent.previous_page_number }}">newer</a>
41 {% endif %}
42
43 <span class="current">
44 Page {{ recent.number }} of {{ recent.paginator.num_pages }}.
45 </span>
46
47 {% if recent.has_next %}
48 <a href="?page={{ recent.next_page_number }}">older</a>
49 {% endif %}
50 </span>
51 </div>
52 </body>37 </body>
53</html>38</html>
5439
=== modified file 'src/oopstools/oops/views.py'
--- src/oopstools/oops/views.py 2012-12-06 01:07:17 +0000
+++ src/oopstools/oops/views.py 2014-01-31 16:38:48 +0000
@@ -18,13 +18,12 @@
18from datetime import datetime, timedelta18from datetime import datetime, timedelta
1919
20from django.conf import settings20from django.conf import settings
21from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
22from django.http import Http404, HttpResponseRedirect21from django.http import Http404, HttpResponseRedirect
23from django.shortcuts import render_to_response22from django.shortcuts import render_to_response
2423
25from oopstools.oops import dbsummaries24from oopstools.oops import dbsummaries
26from oopstools.oops.helpers import parsedate25from oopstools.oops.helpers import parsedate
27from oopstools.oops.models import Oops, Report26from oopstools.oops.models import Infestation, Oops, Report
28from oopstools.oops.prefixloader import PrefixLoader27from oopstools.oops.prefixloader import PrefixLoader
29from oopstools.oops.oopsstore import OopsStore, OopsNotFound28from oopstools.oops.oopsstore import OopsStore, OopsNotFound
3029
@@ -97,30 +96,23 @@
97 "prefixes": prefixes})96 "prefixes": prefixes})
9897
9998
100def get_page_from_query_args(paginator, query_args):99def get_recent_oops_info(report):
101 """Get the page from ``paginator`` specified in the query params.100 prefix_ids = list(report.prefixes.values_list('pk', flat=True))
102101 recent_oopses = list(Oops.objects.raw("""
103 If the request specified ``page`` in the query params, then that102 SELECT whatever.*, pos FROM (
104 page of the paginator will be retrieved.103 select *, rank() OVER w AS pos
105104 FROM oops_oops
106 This function aims to return a page regardless of the page requested,105 WHERE "oops_oops"."prefix_id" = ANY(%s)
107 e.g. requesting a page beyond the last page will just return the106 WINDOW w AS (PARTITION BY prefix_id ORDER BY date DESC)
108 last page.107 ) AS whatever
109108 WHERE pos <= 50
110 :param paginator: a ``Paginator`` object.109 ORDER BY date DESC
111 :param query_args: a ``dict`` of query params.110 LIMIT 50;
112 :return: a ``Page`` from the ``Paginator``.111 """, [prefix_ids]))
113 """112 infestation_pks = set([oops.oopsinfestation_id for oops in recent_oopses])
114 page_num = query_args.get('page', 1)113 infestations = Infestation.objects.filter(id__in=infestation_pks).all()
115 try:114 exception_types = dict([(i.id, i.exception_type) for i in infestations])
116 page = paginator.page(page_num)115 return [(oops, exception_types[oops.oopsinfestation_id]) for oops in recent_oopses]
117 except PageNotAnInteger:
118 # If page is not an integer, deliver first page.
119 page = paginator.page(1)
120 except EmptyPage:
121 # If page is out of range (e.g. 9999), deliver last page of results.
122 page = paginator.page(paginator.num_pages)
123 return page
124116
125117
126def report(request, report_name):118def report(request, report_name):
@@ -135,17 +127,12 @@
135 dates = []127 dates = []
136 for day in range(1, 11):128 for day in range(1, 11):
137 dates.append(now - timedelta(day))129 dates.append(now - timedelta(day))
138 prefix_ids = list(r.prefixes.values_list('pk', flat=True))130 recent = get_recent_oops_info(r)
139 oopses = Oops.objects.filter(
140 prefix__in=prefix_ids).select_related(
141 'oopsinfestation').order_by('-date')
142 paginator = Paginator(oopses, 50)
143 recent_oopses = get_page_from_query_args(paginator, request.GET)
144 data = {131 data = {
145 'report': r,132 'report': r,
146 'dates': dates,133 'dates': dates,
147 'SUMMARY_URI': settings.SUMMARY_URI,134 'SUMMARY_URI': settings.SUMMARY_URI,
148 'recent': recent_oopses,135 'recent': recent,
149 }136 }
150 return render_to_response("report.html", dictionary=data)137 return render_to_response("report.html", dictionary=data)
151138
@@ -184,5 +171,3 @@
184 }171 }
185 return render_to_response("summary.html", dictionary=data)172 return render_to_response("summary.html", dictionary=data)
186173
187
188

Subscribers

People subscribed via source and target branches

to all changes: