0

I noticed that the data_free of the information_schema table in MySQL is getting huge, e.g., information_schema.PROCESSLIST.

Is it possible to resolve the fragmentation of this table? I could not run the optimize table because it said I did not have permission to do so.

4
  • What version of MySQL? How big is the free space now? How many tables do you have? How much data (GBs) is your entire dataset?
    – Rick James
    Mar 2, 2023 at 22:43
  • MySQL is 5.7.33. The total DB capacity is 8 TB, of which about 6 TB is used. The number of tables is about 150 tables. Because of frequent updates, data_free is increasing in the tables themselves, but data_free in the information_schema is also increasing a lot. For example SELECT table_name, data_free FROM information_schema.tables COLUMNS 3783262208
    – rihm
    Mar 3, 2023 at 0:17
  • What is the value of innodb_file_per_table? Do all the tables claim to have 3.7GB of Data_free?
    – Rick James
    Mar 3, 2023 at 2:21
  • I have innodb_file_per_table turned on. Not all tables have huge data_free. It seems that some tables in information_schema have data_free of 0. At least the following table had the same 3783262208. information_schema .COLUMNS .EVENTS .OPTIMIZER_TRACE .PARAMETERS .PARTITIONS .PLUGINS .PROCESSLIST .ROUTINES .TRIGGERS .VIEWS Can they be released by restarting the MySQL server?
    – rihm
    Mar 3, 2023 at 2:38

1 Answer 1

0

A guess...

information_schema is contained in a single "tablespace" with shared "data_free". That is, it is as if you had innodb_file_per_table=OFF, but only for those tables. A clue was "tables had the same 3783262208". This is what happens with OFF -- the free space is not for any individual table, but for all of them combined.

3GB out of 6TB is insignificant; don't worry about it. Don't bother rebooting.

On the other hand, I would worry about 6TB/8TB of disk being used. That could make it risky to ALTER a large table if it has to "copy".

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .