4

I got the free space (fragmentation issues) in my information_Schema database.

Alert shows that there are 1500% free space in some tables like COLUMNS , ROUTINES.

I am worried how this is possible because i don't have any routines in my database and how i can optimize the information_schema because its memory based database and created on the starting of mysql service.

Also when i query "SHOW CREATE TABLE" on any of the information_schema table it gives me innodb as engine of these table, but i think it should be memory.

Any idea to optimize these tables without restart?

Thanks

Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81
  • Start by showing us some specifics. It may be a non-issue, or something that cannot be fixed. From there, we can discuss possible actions. For example, if it is 1500% of 1KB, I would consider that too small to be an issue. But if that is 1GB, we should dig into the details. – Rick James Jul 10 '16 at 13:44
  • i am searching for the reason for this. because i never created routines on this instance so why there is any of the free space. – Aman Aggarwal Jul 11 '16 at 05:48
  • InnoDB preallocates space in anticipation of more inserts/updates/etc. This is a performance optimization. The numbers you get from `information_schema` is only part of the "free" space that exists. There is no way to get rid of such "wasted" space. – Rick James Jul 11 '16 at 21:27
  • What version of MySQL are you running? What ENGINE are those tables? – Rick James Jul 11 '16 at 21:29

1 Answers1

0

When you have innodb_file_per_table = OFF, InnoDB tables are created in the system 'tablespace', ibdata1. It could be that you have created and manipulated a lot of tables there.

Data_free is a confusing term in SHOW CREATE TABLE and certain tables in information_schema...

  • For MyISAM tables, it is an accurate amount of the space that could be recovered from the .MYD file (but not the .MYI file).
  • For InnoDB it means one of 2 things...
    • If the table you are looking at was created with innodb_file_per_table = ON, then Data_free is some of the unused space. Often, not all of it can be recovered by any means.
    • If the table you are looking at was created with innodb_file_per_table = OFF, then Data_free is the free space in ibdata1. That free space will be used for new inserts and new tables, thereby decreasing Data_free. However, the size of ibdata1 cannot be shrunk, at least not without a lot of effort (dump everything, remove, reload).
Rick James
  • 135,179
  • 13
  • 127
  • 222