Bug 1257011 - Investigate why some custom field types are slow to create under PostgreSQL
Investigate why some custom field types are slow to create under PostgreSQL
Product: Bugzilla
Classification: Community
Component: Database (Show other bugs)
Unspecified Unspecified
medium Severity medium (vote)
: ---
: ---
Assigned To: Jeff Fearn
Rony Gong
Depends On:
  Show dependency treegraph
Reported: 2015-08-26 01:07 EDT by Jason McDonald
Modified: 2015-09-06 22:40 EDT (History)
3 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed: 2015-09-01 18:33:33 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)

  None (edit)
Description Jason McDonald 2015-08-26 01:07:58 EDT
Recent performance testing of Bugzilla under PostgreSQL showed that certain custom field types take approximately 75 times longer to create than other custom field types.

Results copied from https://projects.engineering.redhat.com/browse/BUGZILLA-393:

Free Text: 144.4s
Drop Down: 145.7s
Large Text Box: 143.9s
Multiple-Selection Box: 1.9s
Date/Time: 1.9s
Bug ID: 1.8s
Integer: 1.8s
Number: 1.9s
Multiple Value Text Box (Integer): 1.9s

The length of time to create the first three types, and the fact that the bugs table appears to be locked for all of that time, means that we can only create those custom fields during an outage.

We need to investigate why the first three field types take so much longer to create than the others and determine whether any improvement is possible.

Version-Release number of selected component (if applicable):

How reproducible:

Steps to Reproduce:
1. Go to Administration->Custom Fields
2. Create new custom fields of the above types and measure the time for the operation to complete.

Actual results:
Times for each type similar to those above.  Bugs cannot be created or modified while custom fields are being created.

Expected results:
Similar time for all custom field types

Additional info:
Comment 1 Jeff Fearn 2015-09-01 02:18:51 EDT
I don't think this is related to Bugzilla code, it's just how Postgres does it's thing.

e.g. code BZ generates run by hand in Pg:

bugs=# ALTER TABLE bugs ADD COLUMN cf_ft_test_21 varchar(255) DEFAULT '' NOT NULL;
Time: 194470.637 ms

You could save some time in creation by dropping the constraints, but it'd break the code in a lot of places, and still isn't fast enough for production use.

bugs=# ALTER TABLE bugs ADD COLUMN cf_ft_test_22 varchar(255);
Time: 84641.213 ms

There is nothing fancy or complex in the BZ code, it's just a DBMS limitation on updating huge tables.

Is there anything else you'd like covered?

Note You need to log in before you can comment on or make changes to this bug.