507969 - Speed up checksetup for cases when it's doing nothing

Status: RESOLVED FIXED

(Bugzilla :: Installation & Upgrading, enhancement)

Description

[Max Kanat-Alexander]

There are a few queries in checksetup.pl that run every time you run it, whether you're upgrading or not, that are really slow.

There are workarounds that can speed them up.

Comment 1

[Max Kanat-Alexander]

Attachment: v1

This fixes the two major speed issues remaining in checksetup. I'm asking LpSolit for review because I touched the status_workflow initialization code slightly. The query that I had to fix there was the "SELECT DISTNICT bug_status FROM bugs" which can be very slow if you have 500,000 bugs, so I didn't want to run it if it wasn't needed.

Comment 2

[Frédéric Buclin]

Comment on attachment 392212 [details] [diff] [review]
v1

>+    # Populate the status_workflow table. We do nothing if the table already
>+    # has entries. If all bug status transitions have been deleted, the
>+    # workflow will be restored to its default schema.
>+    my $count = $dbh->selectrow_array('SELECT COUNT(*) FROM status_workflow');
>+    return if $count;

Do not return now, because you miss the check about @closed_statuses below. My suggestion below will be safer.


>     # expected to be open statuses. Bug statuses we have no information about
>     # are left as 'open'.

This last comment has been truncated.


>     my @closed_statuses =
>       @{$dbh->selectcol_arrayref('SELECT DISTINCT bug_status FROM bugs

As this query may be slow, move it into the |if (!$num_closed_states)| block. This way it's only executed when needed, without any perf impact (because the query to get $num_closed_states is fast, and we will enter this block only once, when upgrading from < 3.2 to 3.2+).

Comment 3

[Frédéric Buclin]

Note that with my suggestion in comment 2, you can safely take it for 3.4, and even for 3.2.

Comment 4

[Max Kanat-Alexander]

FWIW, I think the @closed_statuses bit is more likely to result in errors from when we could have "NEW RESOLVED" than to help people with their customizations. (This is what happened during the GNOME Bugzilla upgrade.)

Comment 5

[Max Kanat-Alexander]

Attachment: v2

Okay, here we go, I did what you suggested.

Also, I found a few more places that could use some optimization, thanks to Devel::NYTProf, and I optimized those. (Those don't need review, just the workflow part.)

Comment 6

[Frédéric Buclin]

Comment on attachment 393716 [details] [diff] [review]
v2

>Index: Bugzilla/DB/Mysql.pm

>+    if (grep($_ eq 'bugs', @tables) and !grep('bugs_fulltext', @tables)) {

I know this is not the part you asked me to review, but the 2nd grep() is wrong. It should be $_ eq 'bugs_fulltext'. The other grep() a few lines later is also wrong.



>Index: Bugzilla/Install/DB.pm

>+        my $have_events = $dbh->selectrow_arrayref(

Nit: selectrow_array() would make more sense as you just want a scalar.


Other than that, the part about the status workflow is fine. So you can r+ your updated patch.

Comment 7

[Max Kanat-Alexander]

Attachment: v3

Okay, fixed the issues LpSolit pointed out. Granting myself review.

Comment 8

[Max Kanat-Alexander]

It's a minor perf enhancement, so I only checked it in on tip:

Checking in Bugzilla/DB/Mysql.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Mysql.pm,v  <--  Mysql.pm
new revision: 1.77; previous revision: 1.76
done
Checking in Bugzilla/Install/DB.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Install/DB.pm,v  <--  DB.pm
new revision: 1.69; previous revision: 1.68
done

Comment 9

[Max Kanat-Alexander]

Added to the release notes in bug 547466.