Open Bug 824701 Opened 11 years ago Updated 10 years ago

DB columns of type MEDIUMTEXT can contain 16 Mb of data in MySQL (instead of the documented 4 Kb)

Categories

(Bugzilla :: Database, defect)

defect
Not set
normal

Tracking

()

People

(Reporter: LpSolit, Unassigned)

References

()

Details

(Keywords: perf)

There was an initial discussion from bbaetz about Bugzilla using inappropriate types for several DB columns, see bug 153129 comment 0. jake made a proposal to replace most of these types by varchar(255) as they often don't need to be larger than that, see bug 153129 comment 4. But mkanat and xiaoou totally diverged from the initial goal of the bug and managed to replace some fields which were of type TEXT (64 Kb) by MEDIUMTEXT (16 Mb), for instance most description fields. This is totally insane and has performance impact on MySQL. It doesn't make any sense to type a classification, product, component or group description which is 16 Mb long. The initial 64 Kb limit reported by bbaetz was already too high, which is why that bug has been filed. Such fields do not need to be larger than, say, 512 characters, which is supported by all DB servers, see e.g. bug 187753 comment 20.

The documentation in Bugzilla/DB/Schema.pm is misleading as it says that MEDIUMTEXT means a limit of 4 Kb:

"Variable length string of characters up to 4000 characters wide. May be longer on some databases."

On MySQL, it's *much* longer!!

I think the main bug is in Bugzilla/DB/Schema/Mysql.pm itself:

        MEDIUMTEXT =>   'mediumtext',
        LONGTEXT =>     'mediumtext',

The definition for MEDIUMTEXT should be 'text', which means 64Kb, see http://dev.mysql.com/doc/refman/5.5/en/string-type-overview.html.

I don't know what to do with existing DB columns of type MEDIUMTEXT. It would probably take several hours to reformat them, but on the other hand, it's ridiculous to accept such large data. It's hard to determine how much this impacts performance on MySQL.
A good example of why storing too large columns is bad for performance:

  http://dev.mysql.com/tech-resources/articles/partitioning.html

The interesting part of the article is the section whose title is "A Quick Side Note on Vertical Partitioning". Dropping a varchar(30) column improves performance by a factor of 10 (probably because the other 2 columns are small)! I think the same would be true in our case, where a MEDIUMTEXT column replaced by e.g. a varchar(255) column should improve performance (though I doubt the win will be as large as in the article above).
Keywords: perf
Target Milestone: --- → Bugzilla 5.0
Target Milestone: Bugzilla 5.0 → ---
You need to log in before you can comment on or make changes to this bug.