From: Kenzaburo I. <ke...@30...> - 2003-05-16 15:40:46
|
Recently, our work install of Mantis (0.17.5) decided to slow down, significantly. Vewing a bug was taking 7 seconds or more to load up and similar slowdowns in the summary and view all bugs pages. Som characteristics of the installation: about 5,500 issues across 10 projects and a DB size of over 110MBs (we store files in the DB). So I started poking around with timings and discovered the culprits. Basically, we're massively decificient in having database indices. After adding the indices times were reduced to more than acceptable levels. This is without all the nice query and caching improvements that were put in for 0.18.x I've been looking through the recent stuff and I'll be adding various indices. How are we handling the upgrade path for users from 0.17.x versus those on the 0.18.x alphas? I guess I'm looking for some guidance from Julian on this matter. Thanks, -Ken |
From: Julian F. <ju...@be...> - 2003-05-16 17:02:52
|
Kenzaburo Ito wrote: > Recently, our work install of Mantis (0.17.5) decided to slow down, > significantly. Vewing a bug was taking 7 seconds or more to load up and > similar slowdowns in the summary and view all bugs pages. > > Som characteristics of the installation: about 5,500 issues across 10 projects > and a DB size of over 110MBs (we store files in the DB). > > So I started poking around with timings and discovered the culprits. > Basically, we're massively decificient in having database indices. After > adding the indices times were reduced to more than acceptable levels. This is > without all the nice query and caching improvements that were put in for > 0.18.x > > I've been looking through the recent stuff and I'll be adding various indices. > How are we handling the upgrade path for users from 0.17.x versus those on > the 0.18.x alphas? I guess I'm looking for some guidance from Julian on this > matter. As long as you add entries to the upgrade new upgrade system, alpha users and 0.17 users should both be able to just run the files to pick up any new updates. The trick is, once you've added something to the files that you think anyone may have run, you can't change them - people who already have them applied will get screwed because they won't have the new version. So, once you've committed changes to the file, if you screwed up, you need to add another entry afterwards that fixes the problem. If the fix can be conditional (ie. if the broken upgrade doesn't need to have been applied first) you can remove the broken upgrade from the list so nobody else gets it. Basically you just need to think of it as a stream that can't be rewound, because SQL commands are not programaticaly reversible. Julian |
From: Julian F. <ju...@be...> - 2003-05-16 17:05:47
|
Just realized that you probably meant that you wanted 0.17 users to get the upgrades now, rather than when they upgrade to 0.18. This is where we should be using CVS branches, so we don't have to maintain "branches" within our upgrade code. Then you could add upgrades to the 0.17 upgrade branch with the same names as the ones you add to the 0.18 branch, and when the users later upgrade to 0.18, those updates wouldn't be applied because they'd already have them. though, actually, I can't remember for sure if we account for some upgrades in the middle of a stream having already been applied. But if we don't then we should. Julian |
From: Kenzaburo I. <ke...@30...> - 2003-05-28 20:27:26
|
I finished some cursory timing. Here are some results from my testing: OLD is without indices, NEW is with. I've addeda about 25 or so throught the various tables. All times in seconds. 600Mhz P3, 512MB (128MB used), Win2K ------------- OLD -- NEW --- DIFF News Page 0.407 0.401 -0.006 (-1.4%) View All 3.487 2.417 -1.070 (-44% ) View Bug 0.322 0.318 -0.004 (-1.2%) Summary 1.405 1.348 -0.057 (-4.2%) Manage Users 2.870 2.890 +0.020 (+0.7%) First off, this is more eyeballing things than being scientific. I took about 20-30 samples of each page and took what looked like the mean (the number that came up most often). I'm going to investigate the Manage Users page more carefully as it didn't exhibit any improvement. People storing BLOBS in the database will see a large improvement in View Bug times. I'm also investigating the Summary pages further. Thanks, -Ken |
From: Russ T. <ru...@i2...> - 2003-05-28 21:21:29
Attachments:
indexes
|
Kenzaburo Ito wrote: >I finished some cursory timing. Here are some results from my testing: > >OLD is without indices, NEW is with. I've addeda about 25 or so throught the >various tables. > >All times in seconds. >600Mhz P3, 512MB (128MB used), Win2K >------------- OLD -- NEW --- DIFF >News Page 0.407 0.401 -0.006 (-1.4%) >View All 3.487 2.417 -1.070 (-44% ) >View Bug 0.322 0.318 -0.004 (-1.2%) >Summary 1.405 1.348 -0.057 (-4.2%) >Manage Users 2.870 2.890 +0.020 (+0.7%) > >First off, this is more eyeballing things than being scientific. I took about >20-30 samples of each page and took what looked like the mean (the number that >came up most often). > >I'm going to investigate the Manage Users page more carefully as it didn't >exhibit any improvement. People storing BLOBS in the database will see a >large improvement in View Bug times. > >I'm also investigating the Summary pages further. > >Thanks, >-Ken > > > Attached are some of the indexes we added a while back. Searching for bugs was taking an order of magnitude or two longer without the indexes. I haven't looked at the queries that mantis uses, but on mysql it seems that putting indexes on columns used in joins regardless of cardinality can make a big difference in the time it takes to execute. Anyway, I'd be interested in the list of indexes you have added so far. Thanks. -- Russ Tennant ru...@i2... |
From: Kenzaburo I. <ke...@30...> - 2003-05-29 19:09:26
|
We also experienced a huge speedup in search after adding indices. I'll get my list of indices out tonight. I want to do some more comparisons between the dev version and my work version. Thanks, -Ken > Attached are some of the indexes we added a while back. Searching for > bugs was taking an order of magnitude or two longer without the > indexes. I haven't looked at the queries that mantis uses, but on mysql > it seems that putting indexes on columns used in joins regardless of > cardinality can make a big difference in the time it takes to execute. > Anyway, I'd be interested in the list of indexes you have added so far. > Thanks. > > > -- > Russ Tennant > ru...@i2... |
From: Kenzaburo I. <ke...@30...> - 2003-06-09 18:24:42
|
Here's what I came up with. I'd probably add more in the bug_table. ALTER TABLE mantis_project_file_table ADD INDEX(`project_id`); ALTER TABLE mantis_bug_file_table ADD INDEX(`date_added`); ALTER TABLE mantis_bug_file_table ADD INDEX(`title`); ALTER TABLE mantis_bug_file_table ADD INDEX(`bug_id`); ALTER TABLE mantis_bug_history_table ADD INDEX(`bug_id`); ALTER TABLE mantis_bug_history_table ADD INDEX(`user_id`); ALTER TABLE mantis_bug_relationship_table ADD INDEX(`source_bug_id`); ALTER TABLE mantis_bug_relationship_table ADD INDEX(`destination_bug_id`); ALTER TABLE mantis_project_file_table ADD INDEX(`project_id`); ALTER TABLE mantis_project_file_table ADD INDEX(`filename`); ALTER TABLE mantis_project_file_table ADD INDEX(`date_added`); ALTER TABLE mantis_bug_table ADD INDEX(`priority`); ALTER TABLE mantis_bug_table ADD INDEX(`project_id`); ALTER TABLE mantis_bug_table ADD INDEX(`reporter_id`); ALTER TABLE mantis_bug_table ADD INDEX(`handler_id`); ALTER TABLE mantis_bug_table ADD INDEX(`severity`); ALTER TABLE mantis_bug_table ADD INDEX(`reproducibility`); ALTER TABLE mantis_bug_table ADD INDEX(`status`); ALTER TABLE mantis_bug_table ADD INDEX(`resolution`); ALTER TABLE mantis_bug_table ADD INDEX(`projection`); ALTER TABLE mantis_bug_table ADD INDEX(`category`); ALTER TABLE mantis_bug_table ADD INDEX(`last_updated`); ALTER TABLE mantis_bugnote_table ADD INDEX(`date_submitted`); ALTER TABLE mantis_bugnote_table ADD INDEX(`view_state`); ALTER TABLE mantis_bugnote_table ADD INDEX(`bug_id`); ALTER TABLE mantis_news_table ADD INDEX(`headline`); ALTER TABLE mantis_news_table ADD INDEX(`announcement`); ALTER TABLE mantis_news_table ADD INDEX(`project_id`); ALTER TABLE mantis_news_table ADD INDEX(`date_posted`); ALTER TABLE mantis_project_table ADD INDEX(`enabled`); ALTER TABLE mantis_project_table ADD INDEX(`view_state`); ALTER TABLE mantis_project_table ADD INDEX(`access_min`); ALTER TABLE mantis_project_user_list_table ADD INDEX(`user_id`); ALTER TABLE mantis_project_user_list_table ADD INDEX(`access_level`); ALTER TABLE mantis_project_user_list_table ADD INDEX(`project_id`); ALTER TABLE mantis_user_table ADD INDEX(`username`); ALTER TABLE mantis_user_table ADD INDEX(`date_created`); ALTER TABLE mantis_user_table ADD INDEX(`last_visit`); ALTER TABLE mantis_user_table ADD INDEX(`enabled`); ALTER TABLE mantis_user_table ADD INDEX(`access_level`); |