[PATCH 00/11] Performance for ALL THE THINGS!

[Stephen Finucane]

On Fri, 2018-08-10 at 18:00 +1000, Stewart Smith wrote:
> This is an updated and extended patch series of the few patches I've
> sent out over the past few days.
> 
> I've had a pretty major attack at performance of as many patchwork pages
> and bits of functionality that I could find.
> 
> I've been using a mixture of test environments for this, initially a
> small dataset in MySQL, and later on, the data that's in
> patchwork.ozlabs.org in PostgreSQL.
> 
> I've done some testing with a warm cache and cold cache (standard drop
> caches trick) to benchmark what the difference could be for things that
> aren't too commonly viewed.
> 
> As of now, everything except adding patches to a bundle seems to be
> fewer than 20 SQL queries, and worst case for a cold cache seems to be a
> total of about a second in the database.
> 
> I think the next big wins are going to be:
> - Adding a 'date' column to patchwork_patch so we don't have to go to
>   patchwork_submission for it.
>   (and then bending Django to produce a decent query that doesn't go
>   there anyway)
> - Computing/caching patch counts for common queries.
>   e.g. right now, just viewing the /list/ page for qemu-devel takes 26ms
>   for the query to display the first page of patches, but 226ms to do the
>   COUNT(). Even on high page numbers, where the query to generate the
>   list could be 700ms on a cold cache, 226ms is a lot.
>   i.e. a cache of the matrix of action_required/archived would give us a
>   huge boost in page generation.
> - Redoing the create/add/remove from bundle logic to not do everything
>   patch-at-a-time.
> - Tackling the efficiency of the python code itself.
> 
> With this series, I'm struggling to find any query or page that takes
> more than a second of database time on my laptop, with the worst cases
> being around 400ms, and most being <40ms.
> 
> Let me know if there's still anything slow with this patch set :)

Thanks for this - I've applied the few fixes necessary that were
pointed out in the reviews and pushed to master. I'm still trying to
decide if I should backport some of this (namely, anything that doesn't
touch 'models.py' and therefore doesn't require a migration). I'll
likely attempt the backport later this week and see if it works out.

The only performance issues I'm currently seeing are with the admin
interface. This is very low priority though (which is why I haven't
personally touched it yet) so I wouldn't personally worry about it. I
would, however, appreciate your eyes on the "Convert Series-Patch
relationship to 1:N" series that I will be sending a v2 of later today.
This touches a few of the places you modified in this series so there's
a chance I might have broken some stuff.

Once again, thanks for undertaking this. Very much appreciated!
Stephen

> This series replaces:
> https://patchwork.ozlabs.org/patch/954921/
> https://patchwork.ozlabs.org/patch/955321/
> https://patchwork.ozlabs.org/patch/955322/
> https://patchwork.ozlabs.org/patch/955342/
> https://patchwork.ozlabs.org/patch/955347/
> https://patchwork.ozlabs.org/patch/955392/
> 
> Stewart Smith (11):
>   Improve patch listing performance (~3x)
>   4x performance improvement for viewing patch with many comments
>   Add index for patchwork_comment (submission_id,date)
>   Fetch all series for patch/cover viewing
>   Add covering index for /list/ query
>   check distinct(user) based on just user_id
>   Be particular over check_set and series prefetch for /list/
>   Add covering index to patchwork_submissions for /list/ queries
>   Optimise fetching checks when displaying a patch
>   Be sensible computing project patch counts
>   Fetch maintainer information in one query
> 
>  .../migrations/0027_add_comment_date_index.py | 23 +++++++++++++++++
>  .../0028_add_list_covering_index.py           | 19 ++++++++++++++
>  .../0029_add_submission_covering_index.py     | 19 ++++++++++++++
>  patchwork/models.py                           | 21 ++++++++++++++--
>  patchwork/templates/patchwork/submission.html | 16 ++++++------
>  patchwork/views/__init__.py                   |  8 +++++-
>  patchwork/views/cover.py                      |  5 ++++
>  patchwork/views/patch.py                      |  7 ++++++
>  patchwork/views/project.py                    | 25 ++++++++++++++++---
>  9 files changed, 128 insertions(+), 15 deletions(-)
>  create mode 100644 patchwork/migrations/0027_add_comment_date_index.py
>  create mode 100644 patchwork/migrations/0028_add_list_covering_index.py
>  create mode 100644 patchwork/migrations/0029_add_submission_covering_index.py
>