MySQL Table can have the WASTED/FRAGMENTED space without data deletion ( DELETE ) ?

  • Are thinking the table fragmentation will be happened with only DELETE’s ?
  • Do you believe the INSERT’s will also cause the table fragmentation ?

Yes, INSERT’s with the ROLLBACK can also create the table fragmentation . In this blog I am going to explain how the INSERT is causing the table fragmentation .

How the table fragmentations is happening with INSERT and ROLLBACK ?

When you triggered the INSERT, it will start to write the data into it’s table space file ( .ibd ) . Right ?

From the snap, I have created the table and started the transaction ( BEGIN ) with INSERT . Within the transaction the INSERT has been completed but still I did not commit / rollback the transaction .

You can check the table space file growth to 1.2 GB .

Now, I am going to rollback the transaction .

After the ROLLBACK completed, still the table space file size is 1.2 GB ( disk space is not reduced ). You can see the table don’t have any records at COUNT(*) section .

Make sense ?

Now, I wanted to check the table fragmentation . The usual query with INFORMATION_SCHEMA.TABLES will not give the exact fragmentation size . There is a nice article by Mr. Frederic Descamps to calculate the fragmented / wasted disk space . The query which used in the article will additionally use the IINFORMATION_SCHEMA.NNODB_TABLESPACES table .

Query :

SELECT NAME, TABLE_ROWS, format_bytes(data_length) DATA_SIZE,
format_bytes(index_length) INDEX_SIZE,
format_bytes(data_length+index_length) TOTAL_SIZE,
format_bytes(data_free) DATA_FREE,
format_bytes(FILE_SIZE) FILE_SIZE,
format_bytes((FILE_SIZE/10 – (data_length/10 + index_length/10))*10) WASTED_SIZE
FROM information_schema.TABLES as t
JOIN information_schema.INNODB_TABLESPACES as it
ON it.name = concat(table_schema,”/”,table_name) WHERE t.table_schema=’jc’ and t.table_name=’test_frag_Ins’;

So, now I need to rebuilt the table for recover the disk space ?

Yes, recovered the disk space . 🙂

Is the fragmented disk space is re-usable ?

Yes, it is re-usable , the below example will explain it .

From the above snap, first I have created the fragmented space ( 1.2 GB ) with INSERT/ROLLBACK . Then again I ran the same INSERT but this time committed the transaction . Still the disk space is same 1.2 GB .

Make sense ? 🙂

Hope this blog gives some additional information and it helps to understand the table fragmentation in MySQL !!

Thanks !!!

One thought on “MySQL Table can have the WASTED/FRAGMENTED space without data deletion ( DELETE ) ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: