MySQL InnoDB does not free disk space after deleting data rows from table-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

MySQL InnoDB does not free disk space after deleting data rows from table

P粉148434742 2023-10-15 00:02:19

531

I have a MySQL table using the InnoDB storage engine; it contains about 2M data rows. When I delete data rows from the table, it does not free the allocated disk space. After running theoptimize tablecommand, the size of the ibdata1 file has not been reduced.

Is there any way to reclaim disk space from MySQL?

I'm in a bad situation; the application is running in about 50 different locations and now almost all of them are running out of disk space.

P粉148434742

reply all (2)

P粉1830770972023-10-15 17:09:49 2 floor

I encountered the same problem myself.

What happens is that even if you delete the database, innodb still does not free up the disk space. I have to export, stop mysql, delete the files manually, start mysql, create database and user, then import. Thank god I only have 200MB of rows but it saved 250GB of innodb file.

Design failed.

Like+0

Add Reply

P粉3409802432023-10-15 16:24:36 1 floor

MySQL does not reduce the size of ibdata1. once. Even if you useOptimize Tableto free up the space used by deleted records, it will reuse it later.

An alternative is to configure the server to useinnodb_file_per_table, but this requires backup, deletion of the database, and recovery. On the positive side, theoptimized tables.

Like+0

Add Reply