Merge ~bjornt/maas:2.6-bug-1835316-add-event-index into maas:2.6

[Björn Tillenius]

Backport b745a2765caa132eb76a542435d0b1116f420c82.

LP: #1835316 - Requesting machine listing results in 100% postgresql CPU
usage

Add index to the event table to speed up machine listing.
The index speeds up getting the latest event for each node that is
displayed on the machine listing.

[Björn Tillenius (bjornt)]

Self-approve backport.

review: Approve

[MAAS Lander (maas-lander)]

LANDING
-b 2.6-bug-1835316-add-event-index lp:~bjornt/maas/+git/maas into -b 2.6 lp:~maas-committers/maas

STATUS: FAILED BUILD
LOG: http://maas-ci-jenkins.internal:8080/job/maas/job/branch-tester/6312/consoleText

[MAAS Lander (maas-lander)]

UNIT TESTS
-b 2.6-bug-1835316-add-event-index lp:~bjornt/maas/+git/maas into -b 2.6 lp:~maas-committers/maas

STATUS: FAILED
LOG: http://maas-ci-jenkins.internal:8080/job/maas/job/branch-tester/6314/console
COMMIT: a1ca33a891f35c6e6d75d9f2e7f9e00fb6721c18

review: Needs Fixing

58158cf... by Björn Tillenius on 2019-09-03

Fix test failures.

diff --git a/src/maasserver/migrations/maasserver/0194_machine_listing_event_index.py b/src/maasserver/migrations/maasserver/0194_machine_listing_event_index.py
 new file mode 100644
index 0000000..b82a8ea
--- /dev/null
+++ b/src/maasserver/migrations/maasserver/0194_machine_listing_event_index.py
@@ -0,0 +1,24 @@
+# -*- coding: utf-8 -*-
+# Generated by Django 1.11.11 on 2019-08-28 15:07
+from __future__ import unicode_literals
+
+from django.db import (
+    migrations,
+    models,
+)
+
+
+class Migration(migrations.Migration):
+
+    # This depends on the latest (at time of writing) patch in 2.6,
+    # since this patch is to be backported there.
+    dependencies = [
+        ('maasserver', '0192_event_node_no_set_null'),
+    ]
+
+    operations = [
+        migrations.AddIndex(
+            model_name='event',
+            index=models.Index(fields=['node', '-created', '-id'], name='maasserver__node_id_e4a8dd_idx'),
+        ),
+    ]
diff --git a/src/maasserver/models/event.py b/src/maasserver/models/event.py
index c49628a..f64526e 100644
--- a/src/maasserver/models/event.py
+++ b/src/maasserver/models/event.py
@@ -12,6 +12,7 @@ import logging
 from django.db.models import (
     CharField,
     ForeignKey,
+    Index,
     IntegerField,
     Manager,
     PROTECT,
@@ -145,6 +146,11 @@ class Event(CleanSave, TimestampedModel):
         index_together = (
             ("node", "id"),
         )
+        indexes = [
+            # Needed to get the latest event for each node on the
+            # machine listing page.
+            Index(fields=['node', '-created', '-id']),
+        ]
 
     @property
     def endpoint_name(self):
diff --git a/src/maasserver/models/tests/test_node.py b/src/maasserver/models/tests/test_node.py
index 593fd5d..a1739bd 100644
--- a/src/maasserver/models/tests/test_node.py
+++ b/src/maasserver/models/tests/test_node.py
@@ -1974,7 +1974,7 @@ class TestNode(MAASServerTestCase):
         self.patch(Node, "_set_status")
         with post_commit_hooks:
             node.abort_disk_erasing(owner)
-        events = Event.objects.filter(node=node)
+        events = Event.objects.filter(node=node).order_by('id')
         self.assertEqual(
             events[0].type.name, EVENT_TYPES.REQUEST_NODE_ABORT_ERASE_DISK)
         self.assertEqual(
@@ -2105,7 +2105,7 @@ class TestNode(MAASServerTestCase):
         self.patch(Node, "_stop").return_value = None
         with post_commit_hooks:
             node.abort_deploying(admin)
-        events = Event.objects.filter(node=node)
+        events = Event.objects.filter(node=node).order_by('id')
         self.assertEqual(
             events[0].type.name, EVENT_TYPES.REQUEST_NODE_ABORT_DEPLOYMENT)
         self.assertEqual(
@@ -2162,7 +2162,7 @@ class TestNode(MAASServerTestCase):
         self.patch(Node, "_stop").return_value = None
         with post_commit_hooks:
             node.abort_testing(admin)
-        events = Event.objects.filter(node=node)
+        events = Event.objects.filter(node=node).order_by('id')
         self.assertEqual(
             events[0].type.name, EVENT_TYPES.REQUEST_NODE_ABORT_TESTING)
         self.assertEqual(
@@ -2331,7 +2331,7 @@ class TestNode(MAASServerTestCase):
         node.power_state = POWER_STATE.OFF
         with post_commit_hooks:
             node.release()
-        events = Event.objects.filter(node=node)
+        events = Event.objects.filter(node=node).order_by('id')
         self.expectThat(events[1].type.name, Equals(EVENT_TYPES.RELEASING))
         self.expectThat(events[2].type.name, Equals(EVENT_TYPES.RELEASED))
         self.expectThat(node._stop, MockNotCalled())
@@ -2534,7 +2534,7 @@ class TestNode(MAASServerTestCase):
         self.patch(node, '_set_status')
         with post_commit_hooks:
             node.release(owner)
-        events = Event.objects.filter(node=node)
+        events = Event.objects.filter(node=node).order_by('id')
         self.assertEqual(events[0].type.name, EVENT_TYPES.REQUEST_NODE_RELEASE)
         self.assertEqual(events[1].type.name, EVENT_TYPES.RELEASING)
 
@@ -3071,7 +3071,7 @@ class TestNode(MAASServerTestCase):
         node.start_commissioning(admin)
         post_commit_hooks.reset()  # Ignore these for now.
         node = reload_object(node)
-        events = Event.objects.filter(node=node)
+        events = Event.objects.filter(node=node).order_by('id')
         self.assertEqual(
             events[0].type.name, EVENT_TYPES.REQUEST_NODE_START_COMMISSIONING)
         self.assertEqual(events[1].type.name, EVENT_TYPES.COMMISSIONING)
@@ -3147,7 +3147,7 @@ class TestNode(MAASServerTestCase):
         self.patch(Node, "_stop").return_value = None
         with post_commit_hooks:
             node.abort_commissioning(admin)
-        events = Event.objects.filter(node=node)
+        events = Event.objects.filter(node=node).order_by('id')
         self.assertEqual(
             events[0].type.name, EVENT_TYPES.REQUEST_NODE_ABORT_COMMISSIONING)
         self.assertEqual(
@@ -3281,7 +3281,7 @@ class TestNode(MAASServerTestCase):
         self.patch(node, '_power_cycle').return_value = None
         admin = factory.make_admin()
         node.start_testing(admin, testing_scripts=[script.name])
-        events = Event.objects.filter(node=node)
+        events = Event.objects.filter(node=node).order_by('id')
         post_commit_hooks.reset()  # Ignore these for now.
         node = reload_object(node)
         self.assertEqual(
@@ -4619,7 +4619,7 @@ class TestNode(MAASServerTestCase):
         node.start_rescue_mode(admin)
         post_commit_hooks.reset()  # Ignore these for now.
         node = reload_object(node)
-        events = Event.objects.filter(node=node)
+        events = Event.objects.filter(node=node).order_by('id')
         self.assertEqual(
             events[0].type.name, EVENT_TYPES.REQUEST_NODE_START_RESCUE_MODE)
         self.assertEqual(events[1].type.name, EVENT_TYPES.ENTERING_RESCUE_MODE)
diff --git a/src/maasserver/rpc/tests/test_boot.py b/src/maasserver/rpc/tests/test_boot.py
index a0bfd50..859b752 100644
--- a/src/maasserver/rpc/tests/test_boot.py
+++ b/src/maasserver/rpc/tests/test_boot.py
@@ -769,7 +769,7 @@ class TestGetConfig(MAASServerTestCase):
         for purpose, description in purposes:
             node = self.make_node()
             event_log_pxe_request(node, purpose)
-            events = Event.objects.filter(node=node)
+            events = Event.objects.filter(node=node).order_by('id')
             self.assertEqual(description, events[0].description)
             self.assertEqual(events[1].type.description, EVENT_DETAILS[
                 EVENT_TYPES.PERFORMING_PXE_BOOT].description)
diff --git a/src/metadataserver/tests/test_api.py b/src/metadataserver/tests/test_api.py
index e9f4b25..5f43121 100644
--- a/src/metadataserver/tests/test_api.py
+++ b/src/metadataserver/tests/test_api.py
@@ -2166,7 +2166,7 @@ class TestCommissioningAPI(MAASServerTestCase):
         self.assertThat(
             Node.set_default_storage_layout,
             MockCalledOnceWith(node))
-        events = Event.objects.filter(node=node)
+        events = Event.objects.filter(node=node).order_by('id')
         self.assertThat(
             events[0].description,
             DocTestMatches("Failed to set default storage layout."))
@@ -2188,7 +2188,7 @@ class TestCommissioningAPI(MAASServerTestCase):
         self.assertThat(
             Node.set_initial_networking_configuration,
             MockCalledOnceWith(node))
-        events = Event.objects.filter(node=node)
+        events = Event.objects.filter(node=node).order_by('id')
         self.assertThat(
             events[0].description,
             DocTestMatches("Failed to set default networking configuration."))