Project

General

Profile

action #94667

coordination #80142: [saga][epic] Scale out: Redundant/load-balancing deployments of openQA, easy containers, containers on kubernetes

coordination #92854: [epic] limit overload of openQA webUI by heavy requests

Optimize products/machines/test_suites API calls size:M

Added by tinita over 1 year ago. Updated 3 days ago.

Status:
Resolved
Priority:
Low
Assignee:
Category:
Feature requests
Target version:
Start date:
2021-06-24
Due date:
% Done:

0%

Estimated time:
Difficulty:

Description

Motivation

The SQL to fetch the data is inefficient. For each item in the result set another SELECT FROM job_settings is executed. For https://openqa.suse.de/admin/test_suites that means about 2500 SELECTs currently.
Also the costly DateTime columns are fetched, although they are not needed.

Acceptance criteria

  • AC1: Significant performance improvements

Suggestions

  • The number of individual SQL queries doesn't go up with the number of items in the table anymore
  • Less columns are fetched
  • All three /api/v1/products, /api/v1/machines, /api/v1/test_suites are handled by the same code in lib/OpenQA/WebAPI/Controller/API/V1/Table.pm
  • Enable DBIC_TRACE=1 environment variable to see all SQL
  • https://github.com/os-autoinst/openQA/pull/3969 has a similar optimizations
  • Look at the existing patch from #94667#note-7

History

#1 Updated by tinita over 1 year ago

  • Description updated (diff)

#2 Updated by tinita over 1 year ago

  • Description updated (diff)

#3 Updated by tinita over 1 year ago

  • Description updated (diff)

#4 Updated by tinita over 1 year ago

  • Description updated (diff)

#5 Updated by tinita about 1 year ago

  • Status changed from Workable to New

Set to New because not estimated yet

#6 Updated by okurz about 1 year ago

  • Subject changed from Optimize products/machines/test_suites API calls to [easy][beginner] Optimize products/machines/test_suites API calls

#7 Updated by tinita about 1 year ago

  • Subject changed from [easy][beginner] Optimize products/machines/test_suites API calls to Optimize products/machines/test_suites API calls

I had a look into this on the weekend, since the actual change is simple. But then a test failed unexpectedly:
t/40-script_load_dump_templates.t
So I don't consider it a beginner task anymore.

The order of the products/machines seems to be different than before, and this is also visible in the WebUI.
So the join with the *_settings table results in a different order (which means so far we simply relied on the default postgresql order).

Since I will be on vacation I'll post the actual patch for prefetching the settings here:

diff --git a/lib/OpenQA/WebAPI/Controller/API/V1/Table.pm b/lib/OpenQA/WebAPI/Controller/API/V1/Table.pm
index a8591f721..f86e9116c 100644
--- a/lib/OpenQA/WebAPI/Controller/API/V1/Table.pm
+++ b/lib/OpenQA/WebAPI/Controller/API/V1/Table.pm
@@ -108,15 +108,30 @@ sub list {
         }
         if ($have) {
             for my $par (@$key) {
-                $search{$par} = $self->param($par);
+                $search{"me.$par"} = $self->param($par);
             }
         }
     }

     my @result;
+    my $sql_field = {
+        Machines   => 'machine',
+        TestSuites => 'test_suite',
+        Products   => 'product',
+    }->{$table};
     eval {
         my $rs = $self->schema->resultset($table);
-        @result = %search ? $rs->search(\%search) : $rs->all;
+        @result = $rs->search(
+            \%search,
+            {
+                # 'prefetch' would select too much unneeded columns
+                # https://metacpan.org/pod/DBIx::Class::ResultSet#collapse
+                join      => 'settings',
+                collapse  => 1,
+                '+select' => [
+                    qw(settings.id settings.key settings.value),
+                    "settings.${sql_field}_id"
+                ]});
     };
     my $error = $@;
     if ($error) {

#8 Updated by okurz about 2 months ago

  • Target version changed from future to Ready

#10 Updated by cdywan about 1 month ago

  • Subject changed from Optimize products/machines/test_suites API calls to Optimize products/machines/test_suites API calls size:M
  • Description updated (diff)
  • Status changed from New to Workable

#11 Updated by kraih 10 days ago

  • Assignee set to kraih

This looks fun. :)

#12 Updated by kraih 6 days ago

  • Status changed from Workable to In Progress

#13 Updated by kraih 4 days ago

Yes, not a beginner task. Tina's approach was pretty close to optimal already though. I did some benchmarking with different approaches on the O3 database and was a little surprised how little impact prefetching the settings timestamps actually has.

# current version
@result = %search ? $rs->search(\%search) : $rs->all;

# prefetch version
@result = $rs->search(keys %search ? \%search : undef, {prefetch => 'settings', order_by => 'me.id'});

# join/collapse version
@result = $rs->search(keys %search ? \%search : undef,
  {
    join => 'settings',
    '+select' => [qw(settings.id settings.key settings.value), "settings.${sql_field}_id"],
    collapse => 1,
    order_by => 'me.id'
  });

All three of these work with all tests passing. The baseline for performance is 5.03 requests per second with the current version. With the optimized versions it is 14.85 requests per second for the prefetch version. And 15.58 requests per second with the join/collapse version. Out of these the prefetch version probably has the best balance between performance and maintainability. But given that we are all becoming quite proficient with DBIx::Class optimizations, i'll open a PR for the join/collapse version first.

#15 Updated by kraih 3 days ago

  • Status changed from In Progress to Resolved

Also available in: Atom PDF