282199 - Bugzilla::User is too slow for general use

Status: RESOLVED WORKSFORME

(Bugzilla :: User Accounts, defect, P4)

Description

[Dave Miller [:justdave]]

Offshoot from bug 282145 comment 4.

When we moved processmail into Bugzilla::BugMail we got about a 175% speed boost
on processing mail.

We lost most of that speed boost when Bugzilla::BugMail was taught how to use
Bugzilla::User instead of directly grabbing the relevant info from the database
itself.  This suggests that Bugzilla::User needs some heavy optimization.

Comment 1

[Max Kanat-Alexander]

OK, I'll take a look at it if nobody else gets to it, first.

Comment 2

[Max Kanat-Alexander]

I've been looking at it, and so far, all I can see is that:

(1) derive_groups is *clearly* a slow code path, but we only call it if we need
to, so it's not a problem. However, it does lock the tables. Perhaps it would be
nice to have a 'maintenance mode' of checksetup where it rederived everybody's
groups, to speed up things. (derive_groups takes a WRITE lock on the tables its
using, one of which is profiles, which could slow down an installation quite a bit).

(2) If I just make a lot of new users, the profiler says we spend most of our
time inside the two selectrow_array statements in _create. That's how it should
be -- those two SELECT statements are both required, and can't get any faster.

(3) Something might be wrong with can_see_bug:

%Time ExclSec CumulS #Calls sec/call Csec/c  Name
 4.80   0.738  9.709   4000   0.0002 0.0024  Bugzilla::User::can_see_bug

  We only call it 4000 times (for 4000 users that I'm testing), as opposed to,
but the CumulS that it takes is WAY too long.

(4) We also spend quite a bit of time in prepare:

%Time ExclSec CumulS #Calls sec/call Csec/c  Name
 3.37   0.518  6.912  16001   0.0000 0.0004  DBI::db::prepare

I think that perhaps this has something to do with the strange optmization that
we do in can_see_bug. I'm going to investigate more.

Comment 3

[Max Kanat-Alexander]

OK, I think there's actually nothing inherently wrong with Bugzilla::User.
Here's a profile of process_bug.cgi sending 1000 emails with just a comment,
with enableSendMail turned off:

time elapsed (wall):   51.6210
time running program:  43.9857  (85.21%)
time profiling (est.): 7.6353  (14.79%)
number of calls:       163184

%Time    Sec.     #calls   sec/call  F  name
14.88    6.5471    13958   0.000469     Bugzilla::BugMail::FormatDouble
12.36    5.4382      997   0.005455     Bugzilla::BugMail::NewProcessOnePerson
10.12    4.4533     5107   0.000872     DBI::st::execute
 6.47    2.8442     4995   0.000569     Bugzilla::Util::lsearch
 4.84    2.1272     2001   0.001063     DBI::db::selectrow_array
 2.96    1.3037     5115   0.000255     DBI::_new_handle

What I *think* is happening in real production environments is that people are
updating their groups so much that ->derive_groups is being called all the time,
which locks high-contention tables on a WRITE lock. Which then, of course,
causes a lot of slowness in the system in general.

I'll have to look at Bonsai a bit, but I'm almost certain that derive_groups
(the only slow code in Bugzilla::User) is somehow responsible for the slowdown.

Comment 4

[Max Kanat-Alexander]

Yeah, it doesn't seem to be a problem at all, now. Particularly after we fixed
the blocker.