What is the size of currently-existing tmp tables in innodb? This is yet another "how to add a column to a gigantic table" question. The table on disk takes 2.5GB, and I have around 10GB of ram I can use. I have a feeling I can crank up the tmptablesize option so my add column tmptable resides completely in memory so disk i/o isn't a bottleneck. How can I get a sense of what value I should set tmptablesize to?

I have a second vm provisioned for a dry run. I'm running my alter table command and it is chugging along, writing to a temp table. How can I tell how large this temp table is, exactly?

edit for comments

mysql --version
mysql  Ver 15.1 Distrib 5.5.30-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1


The table has 5.5 million rows: 10 decimal columns, 17 integer columns, and 2 datetime columns. No foreign keys. 12 of the integer columns have indexes.

The command:

alter table anonymized_table_name ADD COLUMN `anonymized_column_name` VARCHAR(100) NULL;
The only way to see the temp table needed would be to micromanage the process.

Here is how:

USE mydb
CREATE TABLE anonymized_table_name_new LIKE anonymized_table_name;
ALTER TABLE anonymized_table_name_new ADD COLUMN `anonymized_column_name` VARCHAR(100) NULL;
INSERT INTO anonymized_table_name_new (list of all columns except anonymized_column_name)
SELECT * FROM anonymized_table_name;
ALTER TABLE anonymized_table_name RENAME anonymized_table_name_old;
ALTER TABLE anonymized_table_name_new RENAME anonymized_table_name;
DROP TABLE anonymized_table_name_old;


After the third command you can run

SELECT (data_length+index_length)/power(1024,3) tmptablesize_GB
FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='anonymized_table_name_new';


This may not be all that useful because anonymized_table_name_new may be about the same size as anonymized_table_name since anonymized_column_name has no value. Notwithstanding, you would run that query to see the temp table size in gigabytes.