Improve performance when altering huge tables

[Davide Cicuta]

Hello,
I'm writing to have some clarifications about the process of altering an InnoDB table. My target is MySQL 5.5. I have to add columns to a "huge" table and I'm looking for a way to perform it in the most optimized way as possible.
So first of all I've seen that grouping all modifications in one statement is more efficient than using separate statements, since the table must be processed only once, I suppose.

TEST 1:
That said, first I processed a 4GB (more than double that size including indexes) table of slightly less than 20 million rows with the default memory settings and it took roughly an hour. The O.S. was Ubuntu Server.

TEST 2:
Then I built a similar table on Windows, ran the statement again and got similar times.
Then I tried to modify parameters
SET GLOBAL max_heap_table_size=1024000000; -- Roughly 1GB
SET GLOBAL tmp_table_size=1024000000; -- Roughly 1GB
and reran the statement. The time was reduced to 16 minutes.

TEST 3:
Then I went back to Linux, CentOS in particular, to upgrade the same table, of 22GB (without indexes) and slightly more than 100 million rows. I specified
SET GLOBAL max_heap_table_size=700000000; -- Roughly 1GB
SET GLOBAL tmp_table_size=700000000; -- Roughly 1GB
because the free memory of this machine was not as much as the other, but I noticed that MySQL always used up to 162MB, ignoring the increase in memory. The effect is that the script takes so long to execute that I always stop it when I get fed up...

My question is: since the long time was due to writing a temporary table to disk (if I understand correctly that's MyISAM), how can I get to use more memory even in the last test? Reading the documentation, it seems that the parameters I changed are used only if the table can stay in memory fully, but from the behaviour under Windows, it seems there's an improvement also if the table is bigger than max_heap_table_size.
I'm guessing that in the CentOS installation there's some default parameter which limits the memory to an overall 162MB, but I can't find it.

Do you have any ideas on how to improve the performance of adding columns to huge tables? Or at least some parameters to try to alter...

Thank you in advance for your help!



PS: I know there are commercial products which perform faster, but they're currently not an option.