Mysql Question posted in
The official MySQL documentation can be found here.

MySQL table size: Do I need to adjust something?

I have an InnoDB table which looks like this right now:

MySQL Table Header

Table Size

The table is around 126GB big, but I have read that InnoDB’s default can hold up to 64 TB of data. Is this correct?

I ask because of the column "Data Free" which is very small compared to "Data Length" and soon will be 0 I guess.

Do I need to do something here? I mean 126GB is nothing compared to 64 TB.

Does MySQL manage this all by itself?

Thank you very much!

Tags:

2

Answers


  1. 0

    This was to long for comment:

    Yes, there are big problems with performance if you only have one file. In the low level, MySQl uses the file handles from the system and only one process can write to a file. If you have a file for each table, mysql occupies 3 or 4 handles per table. This makes it much easier (for the system) to expand a table. There is also a file handle cache that needs to be adjusted. If there are no free file handles left (because they are not defined enough), MySQL has to close another table when reading or writing a table, open the new one and can then work with it. However, if the old table needs to be used again in the next query, the same thing happens in reverse. And these system calls, like open and close file, take a very long time to access.

    Furthermore, MySQL NEVER releases disk space once it has been used. Although it is used again internally, everything on the disk remains the same.

    Furthermore, if an error occurs, it is easier to recover a table than a monster file with all tables.

    Since you have a lot of disk space, you can adjust the options in the ini and then copy the tables and copy them back with CREATE TABEL, then the new tables will be created as individual files.

    You will be amazed at the speed gain you can achieve.

    Login or Signup to reply.
  2. 0

    By default, Data_free is not a finite resource that will run out. It’s only a count of the extents (1MB contiguous space) that has already been allocated in the physical file, but which contains no data.

    InnoDB tablespaces usually are configured to auto-expand. The default in current versions of MySQL is to store data in a file-per-table manner, and all such tablespaces are auto-expanding. That means if data_free runs out, InnoDB increases the size of the file incrementally until your storage runs out of space.

    When a file-per-table or general tablespace expands, it increases by small amounts initially, then by 4MB increments.

    Also as you UPDATE and DELETE data, it can leave gaps in pages of your tablespace. Once these gaps cover whole extents, they are counted in the Data_free figure for that tablespace. So the Data_free can increase and decrease from time to time, even if the physical file isn’t growing.

    InnoDB can address up to 64TB in a tablespace, but at least with current-generation servers, your storage is probably a lot smaller than that. You’ll fill your storage much sooner than you will exceed InnoDB’s addressable space.

    There is one way that Data_free can be like a countdown to running out of space: if you store data in the system tablespace (ibdata1) by setting innodb_file_per_table=OFF, AND you have configured a system tablespace with a max size. See https://dev.mysql.com/doc/refman/8.0/en/innodb-init-startup-configuration.html#innodb-startup-data-file-configuration for details on that. If that tablespace fills up, then you can’t store any more data. But neither of these configurations I mention are the default.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search