Open Stack

On 08/13/2012 06:02 PM, Michael Still wrote:
> On 14/08/12 01:24, Jay Pipes wrote:
> 
>> Or just set the column to the LONGTEXT type and both MySQL and
>> PostgreSQL will be just as happy.
> 
> This is what I was originally aiming at -- will large deployers be angry
> if I change this column to longtext? Will the migration be a significant
> problem for them?

>From the MySQL standpoint, the migration impact is neglible. It's
essentially changing the row pointer size from 2 bytes to 4 bytes and
rewriting data pages. For InnoDB tables, it's unlikely many rows would
even be moved, as InnoDB stores a good chunk of these types of rows in
its main data pages -- I think up to 4KB if I remember correctly -- so
unless the user data exceeded that size, I don't think the rows would
even need to move data pages...

I would guess that an ALTER TABLE that changes the column from a TEXT to
a LONGTEXT would likely take less than a minute for even a pretty big
(millions of rows in the instances table) database.

I was *going* to create a random-data table with the same average row
size as the instances table in Nova to see how long the migration would
take, and then I realized something... The user_data column is already
of column type MEDIUMTEXT, not TEXT:

jpipes at uberbox:~$ mysql -uroot nova -e "DESC instances" | grep user_data
user_data	mediumtext	YES		NULL	

So the column can already store data up to 2^24 bytes long, or 16MB of
data. So this might be a moot issue already? Do we expect user data to
be more than 16MB?

-jay