Mysql – How much disk space does innodb need to ALTER TABLE

That is a nice number of tables, but the thing to remember here is that not all of them are going to be open at once - most times you'll set the MySQL parameter for open files to around 300-500, and MySQL itself will keep the ones most active on your system in the memory table pool (cache, if you will) much like how the kernel keeps memory pages for applications around.

I personally am a firm believer in the file per table as a systems management tool; while it does have it's tradeoffs as you rightly bring up, the ability to keep each client's tables distinct (you could even spread them out to different disks - RAID, SAN, DAS, etc. - by customer database!), back the raw ones up by hand if needed, optimize them if needed and so forth goes a lot longer at the end of the day. The main price you pay is more fsync() calls during writes which can degrade performance if you have a lot of tables being written too, but in general it's not too bad.

I periodically need to make changes to tables in mysql 5.1, mostly adding columns.

Don't. No really. Just don't. It should be a very rare occasion when this is ever necessary.

Assuming your data really is normalized to start with, the right way to solve the problem is to add a new table with a 1:1 relationship to the base table (non-obligatory on the new table).

Having to add columns regularly is usually an indicator of a database which is not normalized - if your schema is not normalized then that's the problem you need to fix.

Finally, if your schema really, really is normalized and you really, really must keep adding columns then:

1. Ensure you've got a timestamp column on the database or that it is generating replication logs
2. Create a copy (B) of the table (A)
3. add the new columns to B (this will still block with myisam)
4. disable transactions
5. rename the original table (A) as something else (backup)
6. rename the new table (B) with the name of the original table (A)
7. replay the transactions from the start of the operation from the replication log or from the backup table
8. enable transactions.