/var/lib/mysql/mysql.ibd big after disabling performance_schema

Question

What I think is the reason for this issue

Recently, I was trying to reduce the memory usage by disabling the performance_schema (I read a tutorial that disabling it will reduce the memory usage.)

So, I basically added these lines to /etc/mysql/my.cnf.

performance_schema      = 0
key_buffer_size         = 8M 
max_connections         = 30 # Limit connections
thread_stack            = 128K

Memory usage was reduced. Everything was fine. Until I noticed my droplet was down due to 100% Disk usage (I eventually got it down to around 95% by clearing some logs, etc.)

What happened as a result

Long story short, I found that /var/lib/mysql/mysql.ibd was taking up like 8 GB and the undo_001 in the same directory, like 2 GB which is not normal for the small amount of database and size I have.

I read a lot about how to resolve this issue. And according to many articles and discussions, this file is not shrinkable, and deleting it will result in things to break. As per those articles I read, the only way is to drop all tables, create a new namespace or something, and reload all the tables again. All of these are beyond my understanding.

So, I wanted to know if you have any ideas/guides on how to solve this issue without having to go through all those work? Is there any simple solution to reclaim this space and solve this issue?

(I have found similar questions on here, but all of them are talking about the "specific tables or databases" inside folders. But I am facing this issue on the "mysql.ibd" file itself. The individual databases seem to have very less size. The innodb_file_per_table is turned ON.)

Answer 1

ibdata1 is used for a variety of things. Since, presumably, all the tables are each in their own .ibd file, what's left is mostly transient things. one quick way to bloat ibdata1 is to accidentally do a "cross join" -- where you get a much bigger resultset (even a temp table that is later shrunk via LIMIT or GROUP BY).

It sounds like you will have to "start over".

1. Dump all the data; be sure to include any stored routines. (This step was missing from your steps.)
2. Start over. [see below]
3. Load the data.

May I suggest that step 2 is done by getting another Droplet, especially if it comes with a fresh copy of MySQL. This may cost an extra day's rental, but may be well worth it.

Note that this technique allows you to do step 4 (Verify that the new installation has all the data.)