Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

In My Opinion you should use your solution1.

I am not very clear with what you will accomplish with the solution2 and solution3. with innodb_file_per_table OFF because they will never shrink the ibdata size whenever there delete,drop or truncate on an InnoDB table so using these option your ibdata will grow endlessly.

For your information have a look at this way to reduce the size of ibdata in mysql.?

without using innodb_file_per_table, the ibdata file never compresses or shrinks, Not when you delete a row, drop a table, or a database. In no time, 3GB of data can become a 20GB file if you have an active queuing system.

Have a look why to use innodb_file_per_table : Reasons to use innodb_file_per_table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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