Does mysql use deleted rows space in a table?

Question

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.

Answer 1

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.

• kasi: 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?
• kasi: i understand everything you said, except last line "If your index statistics against that table make SELECT queries bad, just run ANALYZE TABLE instead of OPTIMIZE TABLE". Can you please elaborate this line.

Answer 2

Yes, MySQL marks as deleted and it can later reuse the space, BUT the OS won't recover that space, so if your data grows to 200 GB at some point and then you delete all the rows, MySQL will recycle that space for you, but won't give it back unless you do some dump/recover.

Here's some post on how to recover that space, if you're interested someday in recovering it: https://www.percona.com/blog/2013/09/25/how-to-reclaim-space-in-innodb-when-innodb_file_per_table-is-on/

• kasi: yes i know about optimization. But my understanding is whether deleted space is going to be used by newly inserted records and table size won't grow . am i correct?