0

I have a question about the way MySQL with MyISAM engine handles the free/empty space after deletions and how it behaves when new records are inserted.

For example, think about a new and empty DB (let's assume is 10 kb, just for example).

After inserting 10000 records (let's say each record occupies 1 kb), its total size on the filesystem becomes 10010 kb.

Then, after deleting 5000 records, its size still remains 10010 kb on the filesystem even if the number of records has halved (nothing strange here, it's the normal behaviour).

The OPTIMIZE command could then come in hand to "defragment" the database and reclaim the free space by reorganizing its data.

BUT, if I totally ignore the OPTIMIZE command and insert 2000 new records (so, 5000 still there + 2000 new records = 7000 records in total), does MySQL reuse the free space left by the previous deletions for the new records (meaning that the DB size of 10010 kb won't change) or does it "append" the new records to the DB (meaning that the DB size on the filesystem will grow to, let's say, 12010 kb)?

Thanks! :)

Riccardo M.
  • 150
  • 1
  • 9
  • You could have tested it for yourself in less time than it took you to write that question... – womble Jul 08 '16 at 10:49

1 Answers1

2

Typically if a MyISAM table has free space in the middle of the datafile after DELETE operations, it will insert the rows in that free space. However, due the way MyISAM implements concurrency, it depends on the workload what happens.

From MySQL manual

For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.

And even that after fine-tuning that concurrent_insert parameter.

Janne Pikkarainen
  • 31,852
  • 4
  • 58
  • 81