QC10 Upgrade and ongoing Performance
There is alot of information in this post and I hope it can help other improve their systems. I have been administering QC for over 10 years and the latest version has really changed things. Unfortunately HP Support was little to NO help in all of the problems/solutions listed below.
We had 510 projects in QC 9.2 when we decided to upgrade to QC10 Patch 17. We elected to do an in-place upgrade. It took 55 hours to
upgrade using 9 machines running Site Administrator to upgrade multiple projects at the same time. We are using Oracle 10.2.0.4. We ran the Assessment tool prior to the upgrade on all projects.
The Performance Issues
Our initial performance issue was due to the large increase in the number of tables added during the upgrade. In QC 9.2 there are 75 tables per project. In QC10 there are 175 tables. So this means we added over 50,000 (500 projects * 100 tables) new tables during our upgrade. This caused Oracle to perform poorly. The reason is the database stats were stale. So we manually ran stats and it improved the performance. We are also running stats every 30 minutes because we are finding they are getting stale.
Next problem we noticed was how long it took to login to a project if you were the first user (over 1 minute). We determined much of this time is QC establishing the connection pool. The 2nd person logging into the project only takes 20 seconds. So we modified our connection pool timeouts from the default of 3 minutes to 30 minutes. This seems to have really helped as the connection pool stays around longer. To modify this you need to add two parameters to the Site Admin - Site Configuration.
DB_CONNECTION_POOL_TIMEOUT (default is 3, we set to 30)
DB_CONNECTION_TIMEOUT (default is 1, we set to 3)
Next problem was how long it was taking our users to launch QC. Basically how long it was taking to get to the login screen. We discovered that our virus scanner was consuming 50% cpu on the clients machine during the load. Our virus team has to 'exclude' the components HP is loading to improve the load times. It was taking over 1 minute to load before the exclusion.. and now takes 20 seconds.
We also noticed our SESSION_LICENSE_HISTORY tables had over 900,000 records in it. HP recommends keeping this to under 100,000 records. You control this by the Site Admin - Site Configuration parameter LICENSE_ARCHIVE_PERIOD. Initially we had it set to 365 days. We had to lower it to 30 days to get under the 100,000 record limit. I am not sure of the performance impact of this tweak, but it does load the Site Analysis tab much quicker. The down side is loss of history of license useage beyond 30 days.
Using the new \qcbin\debug page in QC10 (you can enable with the option (DISABLE_CONSOLE_DEBUG_INFO with value of N) we are now able to see how long individual items are taking... as well as how our connection pool is being utilized. I would highly recommend enabling this if you have not done so yet. In our system we are seeing over 100,000 requests per day. So maybe some of our performance issues are simply related to load on the system.