Table size not decreasing after deleting large amount of data in MySQL

Xu2 days ago

Hi Traccar team,

I am using MySQL with Traccar, and I recently deleted a large amount of data from one of my tables. However, I noticed that the physical size of the table did not decrease after the deletion.

Here are the details of the table(tc_positions):

  • Table size: 32.3 GiB
  • Row count: 165,432,147
  • Downtime is not acceptable for this operation

I am looking for guidance on the best strategy to reclaim disk space or optimize the table without causing downtime.

Specifically:

  1. Can I safely use OPTIMIZE TABLE or ALTER TABLE in this scenario?

Note:

  1. We have already implemented clearing database mentioned in https://www.traccar.org/clear-history/

Any advice or guidance would be greatly appreciated!

Thank you.

Anton Tananaev2 days ago

It is absolutely expected that the file size doesn't decrease when you delete the data. Why do you need to decrease it?

Xu2 days ago

otherwise the physical storage will increase linearly ?

Anton Tananaev2 days ago

Why would it increase? If you removed data, that space will be reused.