Score:0

MySQL information_schema table fragmentation

de flag

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.

ua flag
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?
de flag
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
ua flag
What is the value of `innodb_file_per_table`? Do all the tables claim to have 3.7GB of Data_free?
de flag
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?
Score:0
ua flag

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".

I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.