MySQL Table Fragmentation: Beware of Bulk INSERT with FAILURE or ROLLBACK

MySQL Table Fragmentation InsertUsually, database people are familiar with table fragmentation with DELETE statements. Whenever doing a huge delete, in most cases, they are always rebuilding the table to reclaim the disk space. But, are you thinking only DELETEs can cause table fragmentation? (Answer: NO).

In this blog post, I am going to explain how table fragmentation is happening with the INSERT statement.

Before going into the topic, we need to know that with MySQL, there are two kinds of fragmentation:

  • Fragmentation where some of the InnoDB pages are completely free inside the table.
  • Fragmentation where some of the InnoDB pages are not completely filled (the page has some free space).

There are three major cases of table fragmentation with INSERTs :

  • INSERT with ROLLBACK
  • Failed INSERT statement
  • Fragmentation with page-splits

Test Environment

I have created my own test environment to experiment with those cases.

  • DB: percona
  • Tables : frag, ins_frag, frag_page_spl
  • Table Size: 2G

Case 1: INSERT with ROLLBACK

At first, I have created a new table “ins_frag”. Then I have created a transaction (with BEGIN) and started to copy the data from table “frag” to table “ins_frag” as shown below.

From the above, you can see the INSERT was executed, but still, I did not commit/rollback the INSERT. You can note that both tables have occupied 2 GB of disk space. 

Now, I am going to ROLLBACK the INSERT.

 

Yes, after rollback the INSERT, the table “ins_frag” is still occupying the same 2 GB of disk space. Let’s find the fragmented space through the MySQL client.

 

 

So, this clears the rolling back the INSERT will create the fragmentation.  We need to rebuild the table to reclaim the disk space.

 

 

Case 2: Failed INSERT Statement

To test this case, I have created two MySQL client sessions (session 1 and session 2). 

In session 1, I am running the same INSERT statement within the transaction. But this time I have interrupted and killed the INSERT query at session 2.

Session 1

Session 2

The INSERT is interrupted and failed.

Again, at Session 1: