With a particularly large installation, like bmo, where we have many, many products all with many, many bugs loading editproducts.cgi takes a long time (I average just under 1 minute). 9 out of 10 times that I hit editproducts.cgi I'm just trying to get to a particular component to edit (http://bugzilla.mozilla.org/editproducts.cgi?action=edit&product=foo). I don't need the Status, Votes per user, Max Votes per bug, Votes to confirm, Bugs counts, or even the description. I'm not sure what part of the lookup causes it to take so long but I suspect it's the bug count. I can think of two ways to make this better: 1. don't do the expensive lookup (if one of them is most to blame) but provide a link "more info" or something on editproducts.cgi that will trigger the lookup and provide that extra data, or only do the lookup once a day. 2. drop the entire table and just give me an html select with all available products and an "add additional product" link. The delete action could be moved to within the actual product (http://bugzilla.mozilla.org/editproducts.cgi?action=edit&product=foo).

.

Creating the count does require you to examine each bug individually. I wonder if the main problem is lock contention - editproducts has to wait for all the write locks to be removed, and this script has to run on the main db, rather than the shadow db.

I did some tests, with a dummy db made up of 1 user, 10 products, and 150000 bugs. The bugs are randomly assigned to one of 9 products I wanted one product with 0 bugs to test that this still works. select products.product,COUNT(bugs.bug_id) from products LEFT JOIN bugs ON bugs.product = products.product GROUP BY products.product ORDER BY products.product; takes 5-10 seconds. Creating an index on products.product takes this down to 4 seconds. This still doesn't explain the one minute rate. myk, can you remove the count stuff temporarily, and see if it gets better? If not, then its almost certainly a lock contention issue.

With the following changes the page just took 1.343 seconds to load for me while the original version took over 30 seconds, so it's definitely a count issue. 209,212c209,210 < votesperuser,maxvotesperbug,votestoconfirm,COUNT(bug_id) < FROM products LEFT JOIN bugs < ON products.product=bugs.product < GROUP BY products.product --- > votesperuser,maxvotesperbug,votestoconfirm > FROM products

Actually, its probably the bugs table being involved more than the actual count.

I see minor improvements (15 vs. 17.5 seconds on average on my test install) with the following change: 209c209 < votesperuser,maxvotesperbug,votestoconfirm,COUNT(bug_id) --- > votesperuser,maxvotesperbug,votestoconfirm,COUNT(bugs.product) EXPLAIN reports that MySQL uses the "product" index on the "bugs" table in the latter case. The query still blocks other queries from running, though.

Yeah, I think this is basically unfixable without mvcc or some other (better) form of locking. Also see bug 148093

Does MySQL replication help this at all, nowadays? In my local Bugzilla, we have a link called "Display Bug Counts" that displays them only if you click on it. I think that's the best way to solve this.

Reassigning bugs that I'm not actively working on to the default component owner in order to try to make some sanity out of my personal buglist. This doesn't mean the bug isn't being dealt with, just that I'm not the one doing it. If you are dealing with this bug, please assign it to yourself.

This will be straightforward to fix as soon as bug 299753 gets fixed.

Only display the number of bugs per product when asked.

Comment on attachment 199865 [details] [diff] [review] patch, v1 Need to update template interface comment And a nit: The showbugcounts variable is in the 'often used variable ssection', but it isn't

Comment on attachment 201060 [details] [diff] [review] patch, v2 by inspection

Checking in editproducts.cgi; /cvsroot/mozilla/webtools/bugzilla/editproducts.cgi,v <-- editproducts.cgi new revision: 1.107; previous revision: 1.106 done Checking in template/en/default/admin/products/list.html.tmpl; /cvsroot/mozilla/webtools/bugzilla/template/en/default/admin/products/list.html.tmpl,v <-- list.html.tmpl new revision: 1.3; previous revision: 1.2 done