422785 - Re-evaluate the use of various INT types in the schema

Status: NEW

(Bugzilla :: Database, enhancement)

Description

[Max Kanat-Alexander]

Right now, Bugzilla's database uses tinyint, smallint, and mediumint in MySQL for nearly all integer fields.

smallint is only a 16-bit integer, and is used for things like components. You could definitely have more than 32,000 components in a single Bugzilla.

A signed mediumint maxes out at around 8,000,000. Theoretically, someday somebody could have more bugs than that (Yahoo could get there in maybe 5 years?), but that's the type used in the bugs table.

It would probably also be good to provide a BIGSERIAL or BIGINT type that can hold a 64-bit integer, for counters or values that could get super-high.

Comment 1

[Greg Hendricks]

/me looks at the Testopia patch file and coughs

Comment 2

[Greg Hendricks]

Ok, in all seriousness. I raised this issue at the beginning of Testopia development. The test_case_runs table for example, which maps each test case to each run for each build and environment is one that grows very rapidly by definition. 
We are already up to 1 million records there in just over a year of use, and growing at an increased rate. I think this discussion is long overdue. Our approach was to define a BIGSERIAL definition (bigint unsigned) which is admitidly overkill, but as I understand it, Mysql makes bigint fairly efficient. Not sure how Postgres approaches it though.

Comment 3

[Max Kanat-Alexander]

UNSIGNED is a non-standard extension, as I understand it, so we wouldn't be using it. However, a signed 64-bit integer (bigint) goes up to something like 9 septillion, so I think that would be fine, unless you really are worried about having more than 9 septillion rows in the database, in which case I hope that you have hard drives in outer space, since I think that would take more storage than there is on Earth.

Comment 4

[David Marshall]

(In reply to comment #0)
> smallint is only a 16-bit integer, and is used for things like components. You
> could definitely have more than 32,000 components in a single Bugzilla.
> 

Yahoo! reached that point on 2010-05-04!  We bumped up components.id to mediumint unsigned

> A signed mediumint maxes out at around 8,000,000. Theoretically, someday
> somebody could have more bugs than that (Yahoo could get there in maybe 5
> years?), but that's the type used in the bugs table.


At our present rate, that's probably about right.  When we merge another internal ticketing system into our Bugzilla, that rate is going to increase significantly.

When we actually migrate from our severely-hacked 2.22 to 3.6, I anticipate that we'll raise anything that isn't an INT1 to at least INT4, perhaps even to bigint so that it's just never an issue ever again.

Going from mediumint on bugs to bigint (for our 3M rows in bugs) would add 15 megabytes directly, no more than 100 megabytes over all?  That's about a nickel's worth of hard drive at April 2010 prices!