Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
which version of MySQL are you using ? –  altmannmarcelo Apr 30 '13 at 15:22
    
updated the question: mysql Ver 15.1 Distrib 5.5.30-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1 –  Joe Frambach Apr 30 '13 at 15:25
    
1) Please show me the exact ALTER TABLE command. 2) Does the table have foreign keys ? –  RolandoMySQLDBA Apr 30 '13 at 15:37
    
updated the question. Thanks. –  Joe Frambach Apr 30 '13 at 15:45
    
Is anonymized_table_name MyISAM or InnoDB ??? –  RolandoMySQLDBA Apr 30 '13 at 15:47

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer
    
This will show the size of the existing table before the alter command. I'd like to find the size of the tmptable that is created when the alter is run. –  Joe Frambach Apr 30 '13 at 15:35
    
I've updated my answer. –  altmannmarcelo Apr 30 '13 at 15:48
    
Thank you for the response. So the size of the tmptable will be the same size as the resulting table, then? I assumed the tmptable would be different for any reason. –  Joe Frambach Apr 30 '13 at 15:51
    
yes, the size of the temp table will be the same size as the resulting, as you can see in my update, MySQL does the same process. –  altmannmarcelo Apr 30 '13 at 15:57

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.