Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.
    Tweeted twitter.com/StackDBAs/status/773899305535012864
3 added 777 characters in body
source | link

I have a 700GB InnoDB table which I'm not writing any more data to (only reading). I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete part is pretty easy, because I have an auto-inc primary index so I can just iterate in chunks using it, and delete the rows, but that won't bring me back the space. I assume OPTIMIZE TABLE will but that might take forever on a 700GB table, so is there another option I'm overlooking?

Edit by RolandoMySQLDBA

Assuming your table is mydb.mytable, please run the following query and post it here so you can determine diskspace needed for the table's shrinkage:

SELECT
    FORMAT(dat/POWER(1024,3),2) datsize,
    FORMAT(ndx/POWER(1024,3),2) ndxsize,
    FORMAT((dat+ndx)/POWER(1024,3),2) tblsize
FROM (SELECT data_length dat,index_length ndx
FROM information_schema.tables WHERE
table_schema='mydb' AND table_name='mytable') A;

We also need to see the table structure, if allowed.

Edit by Noam

This is the output of the query:

datsize ndxsize tblsize
682.51 47.57 730.08

This is the table structure (SHOW CREATE TABLE)

`CREATE TABLE `mybigtable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,  
  `created_at` datetime NOT NULL,  
  `tid` bigint(20) NOT NULL,  
  `text` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
  `ft` tinyint(1) NOT NULL,  
  `irtsd` bigint(20) NOT NULL,  
  `irtuid` int(11) NOT NULL,  
  `rc` int(11) NOT NULL,  
  `r` tinyint(1) NOT NULL,  
  `e` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,  `timezone` varchar(5) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `uid_tid` (`uid`,`tid`)) ENGINE=InnoDB AUTO_INCREMENT=2006963844 DEFAULT CHARSET=utf8`

I have a 700GB InnoDB table which I'm not writing any more data to (only reading). I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete part is pretty easy, because I have an auto-inc primary index so I can just iterate in chunks using it, and delete the rows, but that won't bring me back the space. I assume OPTIMIZE TABLE will but that might take forever on a 700GB table, so is there another option I'm overlooking?

Edit by RolandoMySQLDBA

Assuming your table is mydb.mytable, please run the following query and post it here so you can determine diskspace needed for the table's shrinkage:

SELECT
    FORMAT(dat/POWER(1024,3),2) datsize,
    FORMAT(ndx/POWER(1024,3),2) ndxsize,
    FORMAT((dat+ndx)/POWER(1024,3),2) tblsize
FROM (SELECT data_length dat,index_length ndx
FROM information_schema.tables WHERE
table_schema='mydb' AND table_name='mytable') A;

We also need to see the table structure, if allowed.

I have a 700GB InnoDB table which I'm not writing any more data to (only reading). I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete part is pretty easy, because I have an auto-inc primary index so I can just iterate in chunks using it, and delete the rows, but that won't bring me back the space. I assume OPTIMIZE TABLE will but that might take forever on a 700GB table, so is there another option I'm overlooking?

Edit by RolandoMySQLDBA

Assuming your table is mydb.mytable, please run the following query and post it here so you can determine diskspace needed for the table's shrinkage:

SELECT
    FORMAT(dat/POWER(1024,3),2) datsize,
    FORMAT(ndx/POWER(1024,3),2) ndxsize,
    FORMAT((dat+ndx)/POWER(1024,3),2) tblsize
FROM (SELECT data_length dat,index_length ndx
FROM information_schema.tables WHERE
table_schema='mydb' AND table_name='mytable') A;

We also need to see the table structure, if allowed.

Edit by Noam

This is the output of the query:

datsize ndxsize tblsize
682.51 47.57 730.08

This is the table structure (SHOW CREATE TABLE)

`CREATE TABLE `mybigtable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,  
  `created_at` datetime NOT NULL,  
  `tid` bigint(20) NOT NULL,  
  `text` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
  `ft` tinyint(1) NOT NULL,  
  `irtsd` bigint(20) NOT NULL,  
  `irtuid` int(11) NOT NULL,  
  `rc` int(11) NOT NULL,  
  `r` tinyint(1) NOT NULL,  
  `e` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,  `timezone` varchar(5) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `uid_tid` (`uid`,`tid`)) ENGINE=InnoDB AUTO_INCREMENT=2006963844 DEFAULT CHARSET=utf8`
2 added 544 characters in body
source | link

I have a 700GB InnoDB table which I'm not writing any more data to (only reading). I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete part is pretty easy, because I have an auto-inc primary index so I can just iterate in chunks using it, and delete the rows, but that won't bring me back the space. I assume OPTIMIZE TABLE will but that might take forever on a 700GB table, so is there another option I'm overlooking?

Edit by RolandoMySQLDBA

Assuming your table is mydb.mytable, please run the following query and post it here so you can determine diskspace needed for the table's shrinkage:

SELECT
    FORMAT(dat/POWER(1024,3),2) datsize,
    FORMAT(ndx/POWER(1024,3),2) ndxsize,
    FORMAT((dat+ndx)/POWER(1024,3),2) tblsize
FROM (SELECT data_length dat,index_length ndx
FROM information_schema.tables WHERE
table_schema='mydb' AND table_name='mytable') A;

We also need to see the table structure, if allowed.

I have a 700GB InnoDB table which I'm not writing any more data to (only reading). I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete part is pretty easy, because I have an auto-inc primary index so I can just iterate in chunks using it, and delete the rows, but that won't bring me back the space. I assume OPTIMIZE TABLE will but that might take forever on a 700GB table, so is there another option I'm overlooking?

I have a 700GB InnoDB table which I'm not writing any more data to (only reading). I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete part is pretty easy, because I have an auto-inc primary index so I can just iterate in chunks using it, and delete the rows, but that won't bring me back the space. I assume OPTIMIZE TABLE will but that might take forever on a 700GB table, so is there another option I'm overlooking?

Edit by RolandoMySQLDBA

Assuming your table is mydb.mytable, please run the following query and post it here so you can determine diskspace needed for the table's shrinkage:

SELECT
    FORMAT(dat/POWER(1024,3),2) datsize,
    FORMAT(ndx/POWER(1024,3),2) ndxsize,
    FORMAT((dat+ndx)/POWER(1024,3),2) tblsize
FROM (SELECT data_length dat,index_length ndx
FROM information_schema.tables WHERE
table_schema='mydb' AND table_name='mytable') A;

We also need to see the table structure, if allowed.

1
source | link

Deleting & Reclaiming space from InnoDB table

I have a 700GB InnoDB table which I'm not writing any more data to (only reading). I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete part is pretty easy, because I have an auto-inc primary index so I can just iterate in chunks using it, and delete the rows, but that won't bring me back the space. I assume OPTIMIZE TABLE will but that might take forever on a 700GB table, so is there another option I'm overlooking?