Bug 1257011 - Investigate why some custom field types are slow to create under PostgreSQL
Summary: Investigate why some custom field types are slow to create under PostgreSQL
Keywords:
Status: CLOSED CANTFIX
Alias: None
Product: Bugzilla
Classification: Community
Component: Database
Version: 4.4
Hardware: Unspecified
OS: Unspecified
medium
medium
Target Milestone: 4.4
Assignee: Jeff Fearn 🐞
QA Contact: Rony Gong 🔥
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2015-08-26 05:07 UTC by Jason McDonald
Modified: 2018-12-09 06:29 UTC (History)
3 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2015-09-01 22:33:33 UTC
Embargoed:


Attachments (Terms of Use)

Description Jason McDonald 2015-08-26 05:07:58 UTC
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):
4.4.9039-3

How reproducible:
Always

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:
none

Comment 1 Jeff Fearn 🐞 2015-09-01 06:18:51 UTC
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;
ALTER TABLE
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);
ALTER TABLE
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.