I have application which has the backend db as Mysql(InnoDb engine), and currently my client wants to decrease the size of the ibdata file to some fixed size. I found the three below solutions.

Solution 1: innodb_file_per_table

Solution 2: innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

Solution 3: innodb_data_file_path=ibdata1:10M:autoextend:max:50M

And I really dont have knowledge about the ibadata1 file, can anybody help me on this and I want to know whether is it safe to fix the ibdata size(solution 3) and what does the ibdata exactly for?

closed as off topic by John Gardeniers, Khaled, Chris S May 7 '12 at 18:20

Questions on Server Fault are expected to relate to server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here. If this question can be reworded to fit the rules in the help center, please edit the question.


You really should take a thorough look at the documentation when dealing with a DBMS of this complexity. http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html states:

Two important disk-based resources managed by the InnoDB storage engine are its tablespace data files and its log files. If you specify no InnoDB configuration options, MySQL creates an auto-extending 10MB data file named ibdata1 and two 5MB log files named ib_logfile0 and ib_logfile1 in the MySQL data directory.

Basically, the ibdata1 file contains the table data of your InnoDB tables. Specifying more than a single file would make sense in cases where you are using a different storage subsystem to store the second file for performance reasons or if you ran out of space for ibdata1 on its original partition and would want to have a simple add-on storage with a different storage file. Fixing the file size obviously has the effect of limiting the total amount of data which can be stored in InnoDB tables.

The ibdata1 file is probably auto-growing in your case, so it will inflate as more data is put into InnoDB tables. After records are deleted from InnoDB tables, the file will contain pages marked as "free" which could be used for future data, but the file itself is unshrinkable. If you have deleted larger amounts of data and need to shrink ibdata1, the only supported way goes through backing up the tables using mysqldump and re-initializing your database - see this StackOverflow answer for details.

  • @loganathan the first value is the initial file size which only applies at file creation. After the "max" size has been reached or exceeded, the file is not grown any more. The file will not shrink to any of the specified values. – the-wabbit May 7 '12 at 9:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.