What is the size of currently-existing tmp tables in innodb?

Question

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;

Answer 1

OPTION 1:

To find the size of this table you can use the follow query:

SELECT DATA_LENGTH+INDEX_LENGTH AStotalTable, TABLE_ROWS from information_schema.TABLES WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_NAME = 'TABLE_NAME';

Now you have to calculate the difference from the actual size totalTable + what you will add, see the list on [MySQL Documentation] about the size of each field.

Let's say the first query give you the follow result:

mysql> SELECT DATA_LENGTH+INDEX_LENGTH AS `totalTable`, TABLE_ROWS from information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'bigTable';
+------------+------------+
| totalTable | TABLE_ROWS |
+------------+------------+
|    3014740 |         10 |
+------------+------------+
1 row in set (0.00 sec)

And you are going to add an BIGINT(8 bytes) field, you will have to multiply the new field size * number of rows + actual size in this case will be: 8 * 10 + 3014740 which will give us 3014820

OPTION 2

Create a new table before changing the live one

CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;
ALTER TALBE new_table .... #you changes here
SELECT DATA_LENGTH+INDEX_LENGTH AS `totalTable` from information_schema.TABLES WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_NAME = 'new_table';

According to Documentation MySQL does this process.

In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one.

Then change the size of tmp_table_size and also max_heap_table_size

Have in mind, that other parts of MySQL may using the same shared memory space, then will be save to put a number higher then you receive as result from SELECT

OPTION 3:

On MySQL 5.6 if you table is innoDB you can explore the new Online DDL for InnoDB Tables

Answer 2

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.