Inefficiency in the applier-get queries was bringing down patchew
completely.  The problem is that filtering the MessageResults for a
project is extremely inefficient: it requires going from Result (where
name and status are stored) to MessageResult to Message for each Result.
If for some reason even a single applier is down, and Result records pile
up with name = 'git' and status = 'pending', this becomes a linear scan
to find the needle with the right project_id in the haystack of records.

To fix this, add a project field to all results which, for MessageResults,
is a denormalized form of Message.project.  The new index on (name,
status, project_id) makes the query for unapplied series very fast.

Most of the complication lies in using the field also to replace the
project field of the ProjectResults.  This has to be done in several
steps, because Django does not like to have a "project" field in both
the Result superclass and the ProjectResult subclass.  The steps are
as follows:

- create a new NULLable field as project_denorm

- populate it and make it non-NULLable

- remove the old field and rename the new one (in this order!)

- introduce the new index and optimize the query.

In order to make the migration reversible, the first patch also makes
ProjectResult.project_id nullable.  Then the reverse steps will first
create the field and populate it while it is nullable, and finally the
reverse application of migration 0066 makes ProjectResult.project_id
*not* nullable.

Paolo



Paolo Bonzini (4):
  models: add denormalized Project link to Result
  models: populate denormalized project field
  models: final step in introducing Result.project field
  git: use denormalized project to query unapplied messages

 api/migrations/0066_auto_20220919_1004.py | 24 +++++++++++++++++
 api/migrations/0067_result_denormalize.py | 32 +++++++++++++++++++++++
 api/migrations/0068_auto_20220919_1007.py | 19 ++++++++++++++
 api/migrations/0069_auto_20220919_1008.py | 22 ++++++++++++++++
 api/migrations/0070_auto_20220919_1016.py | 17 ++++++++++++
 api/models.py                             | 22 +++++++++++-----
 mods/git.py                               |  6 ++---
 7 files changed, 132 insertions(+), 10 deletions(-)
 create mode 100644 api/migrations/0066_auto_20220919_1004.py
 create mode 100644 api/migrations/0067_result_denormalize.py
 create mode 100644 api/migrations/0068_auto_20220919_1007.py
 create mode 100644 api/migrations/0069_auto_20220919_1008.py
 create mode 100644 api/migrations/0070_auto_20220919_1016.py

-- 
2.37.2

_______________________________________________
Patchew-devel mailing list
Patchew-devel@redhat.com
https://listman.redhat.com/mailman/listinfo/patchew-devel