innoDB disk file size

  • Filter
  • Time
  • Show
Clear All
new posts

  • innoDB disk file size

    I am setting up a solution that will insert around 100 million rows in a table per month and every row will have a timestamp that will be indexed. The idea is to only store data regarding the last 3 months in this table, so the total row count will be approximately 300 million rows. Therefore every night an event will delete the rows that are older that 3 months.

    I understand that the innodb files can never be reduced in size after deletes, but in this case will the innodb file size grow infinitely for every month when new data is inserted? Or will the new data replace the deleted data in the file storage?

  • #2
    You can use a single ibdata file which will autoextend and will remain allocated in size permanently. It will never get any smaller from OS perspective. However internally the deleted space will be seen as space which can be reused by innodb. So new data will be placed in unused blocks and innodb will extend the size of the ibdata file once these unused blocks are exhausted.

    You can also choose to have multiple tablespaces in which you can actually let the database recreate the .ibd file and freeing up physical disk space. Please remember though that although you will have multiple tablespaces, you will still have an general ibdata file in your main data directory for the internal data dictionary and the undo logs.

    Some more information on the pro's and contra's of multiple tablespaces can be found at the linked page.


    • #3
      Have you considered paritioning the table by date and creating new partition for each month? This way you could just drop oldest partitions easily if needed.