MySQL Forums
Forum List  »  InnoDB

Re: Data file size
Posted by: Aftab Khan
Date: June 07, 2010 04:59AM

InnoDB extends the data file if it runs out of free space in the tablespace. The increment is 8MB at a time by default. In your example actual data file size is less than 60G. If you want to know how much free space is available in this file then run this command on any innodb table.

SHOW TABLE STATUS LIKE 'table-name';

and look for "Comment" field that will tell you how about innodb free space.

if your hard disk gets full then Innodb wont be able to extend this data file. If this happens then you have following options

1. Add another physical disk; Edit your my.cnf file to add 2nd file data file.
[mysqld]
..
innodb_data_home_dir =
innodb_data_file_path = /path/to/current_data_file/ibdata1:10M:autoextend;/drive2/ibdata/ibdata2:2000M:autoextend

Restart the server.

http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

2. Shrink some of the tables ( if possible)

3. Alternatively if you want to shrink ibdata1 file then

1) You would have to enable innodb_file_per_table. Restart mysql server
2) Backup your database.
3) Stop mysql server
4) delete ibdata file
5) restore database from backup

Options: ReplyQuote


Subject
Views
Written By
Posted
5929
June 02, 2010 07:24AM
1836
June 03, 2010 09:54AM
1468
June 04, 2010 05:37AM
Re: Data file size
1874
June 07, 2010 04:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.