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 some Production servers whose ibdata files increases in size day by day.

It has already consumed 290GB of space.

The tables in the servers are mostly InnoDB and there are high read and write requests.

The log file size also increasing. There is a huge amount of Data in the Tables.

How can I control the growing size of both?

Its really a very hectic for me to control there sizes..

Thanks...!!

share|improve this question
    
Are you using innodb_file_per_table ??? –  RolandoMySQLDBA Dec 9 '11 at 6:19
    
No i am not using innodb_file_per_table... –  Abdul Manaf Dec 9 '11 at 6:23
    
Can put this as a comment? @RolandoMySQLDBA OS: Windows 7 Professional 64 bits, MySQL: '5.5.24-log' 120822 9:41:45 InnoDB: Unrecognized value O_DIRECT for innodb_flush_method 120822 9:41:45 [ERROR] Plugin 'InnoDB' init function returned error. 120822 9:41:45 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 120822 9:41:45 [ERROR] Unknown/unsupported storage engine: INNODB 120822 9:41:45 [ERROR] Aborting –  Jose Nobile Aug 22 '12 at 14:46

1 Answer 1

up vote 25 down vote accepted

Keep in mind the busiest file in the InnoDB infrastructure is /var/lib/mysql/ibdata1

This file normally houses many classes of information (when innodb_file_per_table is 0)

  • Table Data
  • Table Indexes
  • MVCC (Multiversioning Concurrency Control) Data
    • Rollbacks Segments
    • Undo Tablespace
  • Table Metadata
  • See Pictorial Representation

Many people create multiple ibdata files hoping for better diskspace management and performance. It does not help.

Unfortunately, OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things:

  • Makes the table's data and indexes contiguous inside ibdata1
  • It makes ibdata1 grow because the contiguous data is appended to ibdata1

You can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table. To shrink ibdata1 once and for all you must do the following

Step 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

Step 02) Drop all databases (except mysql schema)

Step 03) Shutdown mysql

Step 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

Step 05) Delete ibdata1, ib_logfile0 and ib_logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

Step 06) Restart mysql

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

Step 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

  • mytable.frm (Storage Engine Header)
  • mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many times in my career as a MySQL DBA

In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!

If you would like to see how much actual data is stored in MyISAM and InnoDB, please run this query:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;
share|improve this answer
    
Its really a good answer can u give me your email address.. –  Abdul Manaf Dec 9 '11 at 6:40
1  
It's in my profile, redwards@logicworks.net –  RolandoMySQLDBA Dec 9 '11 at 6:46
    
Awesome answer, thanks! Just adding a note that on 32-bit systems, innodb_buffer_pool_size must be less than 4G - I used 3.2G –  nathan.f77 May 24 '13 at 0:57
    
Thank you for going into such detail! This was very helpful! –  TJ Biddle Sep 16 '13 at 22:02
    
See also How to shrink/purge ibdata1 file in MySQL on Stack Overflow. –  colan Nov 15 '13 at 21:28

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.