List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:August 6 2010 1:04am
Subject:Re: Possible tricks to ALTER on huge tables?
View as plain text  
On Thu, Aug 5, 2010 at 5:07 PM, Daevid Vincent <daevid@stripped> wrote:
> Driving to work today, I had an epiphany thought, but wanted to see if
> anyone could prove my theory or not.
>
> We currently have some tables that are approaching 1 BILLION rows (real
> Billion, with nine zeros, not that silly six zero version). Trying to do an
> "ALTER" on them to add a column can sometimes take hours.
>
> I'm wondering if we had the foresight to create the tables, and then tack
> on extra "dormant" columns of various common types, such as:
>
> future_uint int(10) unsigned null,
> future_int int(10) signed null,
> future_var varchar(255) null,
> Etc.
>
> So basically they'd be unused, then when we wanted a "new" column of that
> type, we would just "rename" the dormant one.
>
> I'm not sure if mySQL is smart enough to realize that if the schema
> definition for a column is identical, then it's just a simple rename, or if
> it treats any change the same and will still take hours to complete (if so,
> perhaps there's an optimization for you mysql developer team)
>
> Another option I considered, was is it possible to just go in with a hex
> editor and rename the field in the .frm file? Is there some kind of .frm
> editor available anywhere?
>
> root@mypse:/var/lib/mysql/agis_core# hexdump -C country.frm
> 00001000  01 01 00 00 0a 00 00 00  02 00 01 00 00 00 01 80
> |................|
> 00001010  02 00 00 12 00 02 00 ff  50 52 49 4d 41 52 59 ff
> |........PRIMARY.|
> 00001020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00
> |................|
> *
> 000013f0  00 00 00 00 00 00 00 00  00 00 00 00 06 00 49 6e
> |..............In|
> 00001400  6e 6f 44 42 00 00 00 00  00 00 00 00 00 00 00 00
> |noDB............|
> 00001410  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00
> |................|
> *
> 00002150  04 00 0b 69 64 5f 63 6f  75 6e 74 72 79 00 05 00
> |...id_country...|
> 00002160  05 69 73 6f 32 00 06 00  05 69 73 6f 33 00 07 00
> |.iso2....iso3...|
> 00002170  0d 63 6f 75 6e 74 72 79  5f 6e 61 6d 65 00 04 0b
> |.country_name...|
> 00002180  05 05 00 02 00 00 12 00  0f 00 00 02 c0 00 00 05
> |................|
> 00002190  05 06 06 00 04 00 00 00  80 00 00 00 fe c0 00 00
> |................|
> 000021a0  06 05 09 09 00 0a 00 00  00 80 00 00 00 fe c0 00
> |................|
> 000021b0  00 07 0d 42 fd 02 13 00  00 00 00 00 00 00 0f c0
> |...B............|
> 000021c0  00 00 ff 69 64 5f 63 6f  75 6e 74 72 79 ff 69 73
> |...future_var...|
> 000021d0  6f 32 ff 69 73 6f 33 ff  63 6f 75 6e 74 72 79 5f
> |...future_int...|

Having significant amount of overhead for unused columns will without
doubt harm performance significantly for certain operations.

Altering .frm files should is always be tried on a non-prod box before
even considering using it on prod. There are some well known use cases
(adding enums values, enlarging varchar columns) where altering a .frm
is useful, but it should always be considered very dangerous.

You could of course consider using PostgreSQL which would only need a
very brief exclusive lock for adding a default null column...

-- 
Rob Wultsch
wultsch@stripped
Thread
Possible tricks to ALTER on huge tables?Daevid Vincent6 Aug
  • Re: Possible tricks to ALTER on huge tables?Buford Tannen6 Aug
  • Re: Possible tricks to ALTER on huge tables?Rob Wultsch6 Aug
    • RE: Possible tricks to ALTER on huge tables?Daevid Vincent6 Aug
  • Re: Possible tricks to ALTER on huge tables?Jigal van Hemert6 Aug
    • Re: Possible tricks to ALTER on huge tables?Andy Wallace6 Aug