MySQL – Does MySQL Use Deleted Rows Space in InnoDB Table?

innodbMySQL

I am deleting a large data around 60GB from one of my innodb table which is around 70GB.

This is a rapidly growing table so i keep on inserting and deleting the records over a period of time. I cannot do DB optimization every time I delete.

So, does mysql use this deleted rows space (which is not given back to OS) for newly inserted rows?

update: innodb_file_per_table is ON already.

Best Answer

Here is something to consider: you need to determine how much fragmentation exists before deciding to defragment the table or not.

The most efficient way is to measure the space usage from the OS and from INFORMATION_SCHEMA

For this example, assume the following

datadir is /var/lib/mysql
database is mydb
table is mytable

OS

TABLE_SIZE_OS=`ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print $5}'`
echo ${TABLE_SIZE_OS}

INFORMATION_SCHEMA

SELECT (data_length+index_length) INTO @table_size_inf
FROM information_schema.tables
WHERE table_schema'mydb'
AND table_name='mytable';
SELECT @table_size_inf;

Simply subtract @table_size_inf from TABLE_SIZE_OS to get the number of bytes unused

Since you deleted 85.7142 % of the table (60GB out of 70GB), you could then run

OPTIMIZE TABLE mydb.mtable;

innodb_file_per_table is disabled

You could do this against an entire database

cd /var/lib/mysql
IBDATA_SIZE=0
for X in `ls -l ibdata* | awk '{print $5}'` ; do (( IBDATA_SIZE += X )) ; done
SQLSTMT="SELECT SUM(data_length+index_length)"
SQLSTMT="${SQLSTMT} FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE engine='InnoDB'"
INNODB_SIZE=`mysql -uroot -ppassword -ANe"${SQLSTMT}"`
(( IBDATA_FRAG = IBDATA_SIZE - INNODB_SIZE ))

I have suggested stuff like this before

Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?
Jul 03, 2013 : Information about Disk Storage MySQL

UPDATE 2017-01-31 07:40 EST

You just asked the following

so i am assuming , if i don't do optimization and keep on inserting data , newly inserted records will use the space which is left over by previously deleted records and table size won't grow. However it depends on the newly inserted data size and contiguous space left over by previously deleted data . Am i correct?

My answer to that is Yes. Deleted rows are simply marked and are available for reuse.

However, each 16K block would have its own internal fragmentation. Even my answer does not compute the granularity of fragmentation within 16K blocks. The more sparse a 16K block is due to deleted space, the more disk I/O InnoDB will experience in traversing 16K blocks in search of free space wide enough for row insertion.

If you are satisfied with the rate of your bulk insertions, then there is no need to OPTIMIZE TABLE that one table. If your index statistics against that table make SELECT queries bad, just run ANALYZE TABLE instead of OPTIMIZE TABLE.

SUMMARY

Even if you implement the InnoDB Cleanup (which separates all data and index pages from ibdata1), ibdata1 can still grow in a heavy-write, heavy transaction environment due to the 5 other classes of information (Data Dictionary, Double Write Buffer, Insert Buffer, Rollback Segments, Undo Logs).

If you want to know how much space is used by data and index pages in ibdata1, run this:

SELECT InnoDB_Bytes,InnoDB_Bytes/POWER(1024,3) InnoDB_GB
FROM (SELECT SUM(data_length+index_length) InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Now as for ibdata1, it still the Data Dictionary, Double Write Buffer, Insert Buffer, Rollback Segments, Undo Logs, and unused pages due to fragmentation. There is no really way to know:

the exact amount of fragmentation
how much space is used by the following (since they change rapidly):
- Data Dictionary
- Double Write Buffer
- Insert Buffer
- Rollback Segments
- Undo Logs

For the sake of simplicity, I will say this: Just subtract InnoDB_Bytes from the filesize of ibdata1.

After doing the InnoDB Cleanup, you should schedule running OPTIMIZE TABLE on every InnoDB table that is transaction-heavy. That will actually shrink the .ibd file for every InnoDB tables. The ibdata1 file will grow much slower thereafter. Yet, you will still have to live with some transactional growth.

If you implement InnoDB Cleanup but leave innodb_file_per_table disabled, that will shrink shrink ibdata1, but it will just climb back to 99G and beyond through normal application usage.

If you do not implement InnoDB Cleanup, no OPTIMIZE TABLE will ever shrink ibdata1.

RECOMMENDATION

Please implement InnoDB Cleanup with innodb_file_per_table enabled. Going forward, you should create a cronjob to run OPTIMIZE TABLE on all tables what experience mass INSERTs, mass UPDATEs, and mass DELETEs.

How Much Space is Needed to Reclaim Space from a Table in InnoDB

DISKSPACE FOR EVERYTHING INNODB

SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) InnoDB_DiskSpace
FROM information_schema.tables
WHERE engine='InnoDB';

DISKSPACE FOR DATABASE mydb BY TABLE

SELECT
    IFNULL(tbl,'Total') table_name,
    FORMAT(SUM(table_bytes)/POWER(1024,3),2) table_size
FROM
(
    SELECT table_name tbl,SUM(data_length+index_length)table_bytes
    FROM information_schema.tables WHERE table_schema='mydb
    GROUP BY table_name WITH ROLLUP
) A ORDER BY ISNULL(tbl) DESC,table_bytes;

DISKSPACE FOR INDIVIDUAL TABLE (mydb.mytable)

SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) Table_Diskspace
FROM information_schema.tables
WHERE table_schema='mydb;
AND table_name='mytable';

These queries will tell you how much space you need for any table, database, and engine.

SUGGESTION #1

If you would like to simulate OPTIMIZE TABLE just to see diskspace usage, you run the following steps on mydb.mytable

use mydb
CREATE TABLE mytable_new LIKE mytable;
INSERT INTO mytable_new SELECT * FROM mytable;
ANALYZE TABLE mytable_new;
SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) Table_Diskspace
FROM information_schema.tables
WHERE table_schema='mydb;
AND table_name='mytable_new';
#
# Stop Here. If you are happy with the size, do the last two commands
# If you prefer, drop table mytable_new and do do the online schema change
#
RENAME TABLE mytable TO mytable_old,mytable_newTO mytable;
DROP TABLE mytable_old;

Doing this can give an idea who big the .ibd file will be after running OPTIMIZE TABLE.

SUGGESTION #2

Please do not convert the table to MyISAM. It will be building two files (.MYD and .MYI). Then, you try to converting to InnoDB again. It is essentially the same as doing #SUGGESTION #1. MyISAM is also a little more prone to corruption with no recovery processes in place.

EPILOGUE

By all means, you can proceed with pt-online-schema-change if your data is live. My answer simply gives you queries to estimate sizes of the tables and approximate temp table sizes for OPTIMIZE TABLE.

If you are doing this during off hours, try my post Recover the disk space after deleting rows from table