- 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 .
SELECT NAME, TABLE_ROWS, format_bytes(data_length) DATA_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 !!