Reporting DB: auto increment primary key fields type is too small

[brlindblom]

At least for Reporting_interaction_groups & Reporting_interaction_packages, its fairly easy to run out of id's with a 1k node environment within just a few weeks. Converting the schema to use bigint for auto_incremented primary keys would solve this issue (for now).

IRC chat log:

(10:31:46 AM) lindbr: got a reporting db question, if anyone has any insight... we're on 1.3.3.  django uses signed int for its primary keys.  we hit the limit on this periodically and have tried various work-arounds.  anyone ever deal with this before?
(10:34:16 AM) solj: lindbr: can't say i've ever encountered that issue
(10:34:47 AM) lindbr: we hit it every month like clock work, on Reporting_interaction_packages
(10:35:34 AM) mfenn: do you purge your database with bcfg2-admin?
(10:36:42 AM) lindbr: yes, but the table's auto_increment value always increases.  i should tell you that its on innodb
(10:37:43 AM) lindbr: we don't end up actually having 2 billion records (more like 10m), but 'id' keeps on incrementing as new records come in and and old ones are purged
(10:39:21 AM) solj: so changing to bigint would only delay the problem
(10:40:27 AM) mfenn: but bigint is 2 billion times bigger
(10:40:28 AM) lindbr: it would delay it a few billion years, sure.  I think that would be ok :)  I have a procedure ready to convert to bigint, just wanted to see if anyone dealt with this already
(10:40:32 AM) mfenn: so it will delay it by quite a while
(10:41:01 AM) mfenn: well 4 billion times more space, but since they're signed, the max value is "only" 2 billion times bigger
(10:41:29 AM) solj: lindbr: if you can modify the model and create a migration for that, we could apply that upstream
(10:41:36 AM) mfenn: lindbr: I wonder how that interacts with the db schema migrations that south does
(10:41:42 AM) mfenn: changing it in place I mean
(10:42:16 AM) lindbr: I've looked into modifying the model and south migrations.  its non-trivial and doesn't get fixed in upstream django until 1.7
(10:42:38 AM) lindbr: there's an 8 year old bug on this.  i'll see if i can dig it up
(10:42:41 AM) mfenn: I'm using myisam, and my max(id) in that dable is at max_int
(10:42:50 AM) mfenn: but everything is working with no problems
(10:43:22 AM) lindbr: sorry, 4 year old: https://code.djangoproject.com/ticket/14286
(10:44:02 AM) lindbr: mfenn: interesting... how are new records being added?
(10:45:23 AM) solj: lindbr: what behavior are you seeing?
(10:45:34 AM) ***solj has ~30m rows in that table
(10:47:32 AM) mfenn: lindbr: ah, it reports the number of modified packages but doesn't say what they were
(10:48:01 AM) lindbr: since all of the record inserts are "autocommit=0", I get an "invalid value" error on insert into the table, which drops the whole transaction, hence no reporting period
(10:48:43 AM) lindbr: i'd have to dig for the exact error message, but under the hood, insert is trying to write an id that is bigger than int(11), and innodb wont have none of that
(10:49:00 AM) solj: interesting
(10:49:20 AM) lindbr: setting the table's auto_increment to 1 simply sets it to MAX(id)
(10:49:24 AM) lindbr: +1
(10:49:44 AM) lindbr: even if the 1st record is, say, id=1.5 billion
(10:53:41 AM) solj: my ids are still in the 40 million range
(10:53:51 AM) solj: so i guess i have a while to go before i hit that issue :-/
(10:54:34 AM) solj: i would think mfenn of all people would have hit that bug
(10:55:08 AM) mfenn: I apparently have
(10:55:11 AM) solj: lindbr: is moving to django 1.7 an option?
(10:55:19 AM) mfenn: but I'm using myisam
(10:55:22 AM) mfenn: and myisam don't care
(10:55:25 AM) solj: ha
(10:55:29 AM) mfenn: about trivial things like integrity
(10:55:58 AM) lindbr: oh dear :/
(10:56:10 AM) solj: lindbr: could we conditionally create the table with BigAutoField for 1.7?
(10:56:13 AM) lindbr: solj: it would be a bit of an undertaking for us
(10:56:18 AM) solj: ah
(10:56:19 AM) solj: damn
(10:57:02 AM) lindbr: so, what i have is this: dump the schema and use sed to convert all /*_id/ fields to bigint(20), dump the data separately.  then, reload everything
(10:57:09 AM) afaris [~Adium@dagmar1.corp.linkedin.com] entered the room.
(10:58:24 AM) jbanier: can't you do an alter table ...?
(10:58:50 AM) lindbr: jbanier: i thought of that... our reporting db sits on a galera cluster (don't ask me why) and alter tables are super expensive
(11:00:31 AM) jbanier: the percona guys had a pt-table-online-schema-something to work around that I beleive
(11:00:34 AM) kincl [~kincl@unaffiliated/kincl] entered the room.
(11:01:57 AM) jbanier: http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html
(11:02:19 AM) jbanier: I've not used that specific tool but the others ones work flawlessly
(11:02:37 AM) jbanier: or dump the schema for that table, sed it and restore it :)
(11:03:13 AM) solj: lindbr: something like https://gist.github.com/zorainc/5883779 wouldn't work?
(11:04:19 AM) solj: also, is that even in 1.7?
(11:04:35 AM) solj: the ticket seems to indicate it will be available in 1.8 at the earliest
(11:06:47 AM) jbanier left the room (quit: Quit: Lingo - http://www.lingoirc.com).
(11:07:17 AM) lindbr: jbanier: that looks like it might choke on the foreign key references, unfortunately.  I've implemented a similar procedure before, and the foreign keys will reference the original table, which then gets dropped, which then breaks the reference
(11:08:10 AM) lindbr: solj: it might, but i honestly don't know enough about the underlying django to say
(11:09:03 AM) solj: yeah, i don't see the new data type in 1.7 https://github.com/django/django/blob/stable/1.7.x/django/db/backends/mysql/creation.py
(11:09:26 AM) solj: so that wouldn't work anyway
(11:09:54 AM) lindbr: jbanier: oh... looks like it has support to update the foreign key references
(11:10:05 AM) lindbr: too bad you left, but thanks!  it looks promising