| Summary: | Missing index on Name field in Submission table makes cumin-web underperform | ||
|---|---|---|---|
| Product: | Red Hat Enterprise MRG | Reporter: | Trevor McKay <tmckay> |
| Component: | cumin | Assignee: | Trevor McKay <tmckay> |
| Status: | CLOSED NEXTRELEASE | QA Contact: | MRG Quality Engineering <mrgqe-bugs> |
| Severity: | medium | Docs Contact: | |
| Priority: | unspecified | ||
| Version: | 2.0 | CC: | matt |
| Target Milestone: | 2.1 | ||
| Target Release: | --- | ||
| Hardware: | Unspecified | ||
| OS: | Unspecified | ||
| Whiteboard: | |||
| Fixed In Version: | Doc Type: | Bug Fix | |
| Doc Text: |
Cause
The database schema did not include an index on the Name field in the Submission table.
Consequence
Sorting submissions by name was inefficient. As the number of submissions increased, display times increased for certain Cumin pages.
Fix
An index on Name in the Submission table has been added to the schema. A schema upgrade script has been included for existing databases which can be applied with the 'cumin-admin upgrade-schema' command.
Result
Cumin pages containing submission tables sorted by name should maintain performance as the number of submissions grows.
|
Story Points: | --- |
| Clone Of: | Environment: | ||
| Last Closed: | 2011-09-29 15:06:16 UTC | Type: | --- |
| Regression: | --- | Mount Type: | --- |
| Documentation: | --- | CRM: | |
| Verified Versions: | Category: | --- | |
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |
| Cloudforms Team: | --- | Target Upstream Version: | |
Fixed in revision 5021. Change the schema version in admin.py to 1.1 Make the Name field an index on the Submission table in the new schema. Add a schema upgrade script to model/updates to create the index for existing cumin DBs with schema version 1.0
Technical note added. If any revisions are required, please edit the "Technical Notes" field
accordingly. All revisions will be proofread by the Engineering Content Services team.
New Contents:
Cause
The database schema did not include an index on the Name field in the Submission table.
Consequence
Sorting submissions by name was inefficient. As the number of submissions increased, display times increased for certain Cumin pages.
Fix
An index on Name in the Submission table has been added to the schema. A schema upgrade script has been included for existing databases which can be applied with the 'cumin-admin upgrade-schema' command.
Result
Cumin pages containing submission tables sorted by name should maintain performance as the number of submissions grows.
|
Description of problem: Recent scale tests revealed that with ~150k submissions cumin-web took about 4 seconds to display pages containing tables of submissions. The problem was found to be a missing index in the database schema for the Submission table. With the index added, the time dropped to about 1/3 second. Version-Release number of selected component (if applicable): 2.0 (and all previous I believe) How reproducible: 100% Steps to Reproduce: The behavior (the slowness) can be observed onscreen with lots of submissions created, but there is an easier way to observe the problem and verify the fix using psql commands. 1. Enter postgres shell with "psql -d cumin -U cumin -h localhost" 2. cumin=# \d "com.redhat.grid"."Submission"; (will show the submission table, note no Submission_Name_idx under indices) 3. cumin=# explain analyze select * from "com.redhat.grid"."Submission" order by "Name" asc; (this will show the query plan and the timing) QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Sort (cost=66790.17..67161.61 rows=148577 width=217) (actual time=3027.033..3057.679 rows=148577 loops=1) Sort Key: "Name" -> Seq Scan on "Submission" (cost=0.00..10586.77 rows=148577 width=217) (actual time=7.024..73.440 rows=148577 loops=1) Total runtime: 3089.437 ms 4. Add the index, this can easily be done with the schema upgrade script /usr/share/cumin/model/upgrades/1.0_to_1.1 5. Run #2 above from the postgres shell to see the index 6. cumin=# explain analyze select * from "com.redhat.grid"."Submission" order by "Name" asc; (note the index scan, time will be reduced for lots of submissions) QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using "Submission_Name_idx" on "Submission" (cost=0.00..26711.66 rows=148577 width=217) (actual time=0.059..95.462 rows=148577 loops=1) Total runtime: 112.460 ms Additional info: The schema upgrade script above (/usr/share/cumin/model/upgrades/1.0_to_1.1 can also be applied with the "cumin-admin upgrade-schema" command from BZ741729 cumin packages going forward.