grahamc/hydra2 - Change ADIHLAZIPFFSWEBJV4NWFP7S5QN4BLQFHLDPUZ436GP7W7NKQ7WAC

search: limit queries to 20s

Even 20s is really long, but it cuts off queries which are today running for 500+s.

Created by Graham Christensen on January 30, 2021

ADIHLAZIPFFSWEBJV4NWFP7S5QN4BLQFHLDPUZ436GP7W7NKQ7WAC

Dependencies

In channels

main

Change contents

Replacement in src/lib/Hydra/Controller/Root.pm at line 445 [5.804]

B:BD[4.295] → [4.295:360]

B:BD[4.360] → [6.0:214]

∅:D[6.214] → [4.496:534]

B:BD[4.496] → [4.496:534]

    $c->stash->{projects} = [ $c->model('DB::Projects')->search(
        { -and =>
            [ { -or => [ name => { ilike => "%$query%" }, displayName => { ilike => "%$query%" }, description => { ilike => "%$query%" } ] }
            , { hidden => 0 }
            ]
        },
        { order_by => ["name"] } ) ];

[4.295]

[4.534]

    $c->model('DB')->schema->txn_do(sub {
        $c->model('DB')->schema->storage->dbh->do("SET LOCAL statement_timeout = 20000");
        $c->stash->{projects} = [ $c->model('DB::Projects')->search(
            { -and =>
                [ { -or => [ name => { ilike => "%$query%" }, displayName => { ilike => "%$query%" }, description => { ilike => "%$query%" } ] }
                , { hidden => 0 }
                ]
            },
            { order_by => ["name"] } ) ];

Replacement in src/lib/Hydra/Controller/Root.pm at line 455 [5.804]

B:BD[4.535] → [4.535:598]

B:BD[4.598] → [6.215:497]

    $c->stash->{jobsets} = [ $c->model('DB::Jobsets')->search(
        { -and =>
            [ { -or => [ "me.name" => { ilike => "%$query%" }, "me.description" => { ilike => "%$query%" } ] }
            , { "project.hidden" => 0, "me.hidden" => 0 }
            ]
        },
        { order_by => ["project", "name"], join => ["project"] } ) ];

[4.535]

[4.743]

        $c->stash->{jobsets} = [ $c->model('DB::Jobsets')->search(
            { -and =>
                [ { -or => [ "me.name" => { ilike => "%$query%" }, "me.description" => { ilike => "%$query%" } ] }
                , { "project.hidden" => 0, "me.hidden" => 0 }
                ]
            },
            { order_by => ["project", "name"], join => ["project"] } ) ];

Replacement in src/lib/Hydra/Controller/Root.pm at line 463 [5.804]

B:BD[4.744] → [3.1850:1952]

∅:D[3.1952] → [6.545:608]

B:BD[6.545] → [6.545:608]

B:BD[6.608] → [3.1953:1978]

∅:D[3.1978] → [6.608:619]

B:BD[6.608] → [6.608:619]

B:BD[6.619] → [3.1979:2061]

∅:D[3.2061] → [7.32:73]

B:BD[8.338] → [7.32:73]

B:BD[7.73] → [3.2062:2081]

    $c->stash->{jobs} = [ $c->model('DB::Builds')->search(
        { "job" => { ilike => "%$query%" }
        , "project.hidden" => 0
        , "jobset.hidden" => 0
        , iscurrent => 1
        },
        { order_by => ["project", "jobset", "job"], join => ["project", "jobset"]
        , rows => $c->stash->{limit} + 1
        } )
    ];

[4.744]

[9.50]

        $c->stash->{jobs} = [ $c->model('DB::Builds')->search(
            { "job" => { ilike => "%$query%" }
            , "project.hidden" => 0
            , "jobset.hidden" => 0
            , iscurrent => 1
            },
            { order_by => ["project", "jobset", "job"], join => ["project", "jobset"]
            , rows => $c->stash->{limit} + 1
            } )
        ];

Replacement in src/lib/Hydra/Controller/Root.pm at line 474 [5.804]

B:BD[9.51] → [10.0:90]

∅:D[10.90] → [9.51:112]

B:BD[9.51] → [9.51:112]

B:BD[9.112] → [11.0:60]

B:BD[11.60] → [2.0:112]

    # Perform build search in separate queries to prevent seq scan on buildoutputs table.
    $c->stash->{builds} = [ $c->model('DB::Builds')->search(
        { "buildoutputs.path" => { ilike => "%$query%" } },
        { order_by => ["id desc"], join => ["buildoutputs"]
        , rows => $c->stash->{limit}
        } ) ];

[9.51]

[9.265]

        # Perform build search in separate queries to prevent seq scan on buildoutputs table.
        $c->stash->{builds} = [ $c->model('DB::Builds')->search(
            { "buildoutputs.path" => { ilike => "%$query%" } },
            { order_by => ["id desc"], join => ["buildoutputs"]
            , rows => $c->stash->{limit}
            } ) ];

Replacement in src/lib/Hydra/Controller/Root.pm at line 481 [5.804]

B:BD[9.266] → [10.141:205]

B:BD[10.205] → [11.61:111]

B:BD[11.111] → [2.113:199]

    $c->stash->{buildsdrv} = [ $c->model('DB::Builds')->search(
        { "drvpath" => { ilike => "%$query%" } },
        { order_by => ["id desc"]
        , rows => $c->stash->{limit}
        } ) ];

[9.266]

[12.0]

        $c->stash->{buildsdrv} = [ $c->model('DB::Builds')->search(
            { "drvpath" => { ilike => "%$query%" } },
            { order_by => ["id desc"]
            , rows => $c->stash->{limit}
            } ) ];

Replacement in src/lib/Hydra/Controller/Root.pm at line 487 [5.804]

B:BD[12.1] → [12.1:262]

    $c->stash->{resource} = { projects => $c->stash->{projects},
                              jobsets  => $c->stash->{jobsets},
                              builds  => $c->stash->{builds},
                              buildsdrv  => $c->stash->{buildsdrv} };

[12.1]

[13.639]

        $c->stash->{resource} = { projects => $c->stash->{projects},
                                jobsets  => $c->stash->{jobsets},
                                builds  => $c->stash->{builds},
                                buildsdrv  => $c->stash->{buildsdrv} };
    });