ibdata1 file very large

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bwv2
Forum Commoner
Posts: 83
Joined: Fri Jun 10, 2005 11:50 am
Location: AZ

ibdata1 file very large

Post by bwv2 »

My ibdata1 file seems to get larger and larger as I change around/add to my databases. However, it never seems to get any smaller when I delete things from databases. I'm currently developing a site on my local machine to get ready to load it to a host, so my current ibdata1 file size of 14.7gB is starting to take a toll on my disk space on my local machine. Any idea how to trim the fat on the ibdata1 file?
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

The ibdata1 file is where the INNODB database data is being stored. In otherwords your databases are using INNODB instead of MyIsam.

You could convert the tables to MyIsam. That would split the big file into multiple big files and still not reduce the size of the ibdata1 file.

The ibdata1 file will never get smaller. When data is deleted from innodb database tables it frees up space inside the ibdata1 file. New entries will be stored in the free space before the ibdata1 file is expanded upon.

In otherwords even if you did convert the database type to MyIsam it would probably still use the same drive space.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

AKA Panama Jack wrote:The ibdata1 file is where the INNODB database data is being stored. In otherwords your databases are using INNODB instead of MyIsam.

You could convert the tables to MyIsam. That would split the big file into multiple big files and still not reduce the size of the ibdata1 file.

The ibdata1 file will never get smaller. When data is deleted from innodb database tables it frees up space inside the ibdata1 file. New entries will be stored in the free space before the ibdata1 file is expanded upon.

In otherwords even if you did convert the database type to MyIsam it would probably still use the same drive space.
I didn't know that... interesting. You learn something new every day :)
Post Reply