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 a database that its entire size is ~44GB, out of it ibdata1 is ~35GB that doesnt make sense, the size of the data shouldnt be more then 10GB.

i use the following query to get an esstimation of the data size:

    SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  30;

any ideas how to clean ibdata1 and why its grown so much?

BTW, I use innodb_file_per_table

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Andreas got this answer first in terms of what to do. +1 for Andreas !!!

I would like clarify why that answer is the only way and how to do it.

By default ibdata1 normally houses four types of information

  • Table Data
  • Table Indexes
  • MVCC (Multiversioning Concurrency Control) Data
  • Table Metadata

Running OPTIMIZE TABLE against an InnoDB table stored ibdata1 will make things worse because here is what it does:

  • 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. That way, only MVCC and Table MetaData would reside in ibdata1.

If you already use it, then you must have a high-write environment that stores lots of MVCC to support transaction isolation. Once the transactions holding the MVCC is done, the space is simply abandoned for re-use.

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) service mysql stop

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) rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile

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

STEP 06) service mysql start

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 would like to suggest a formula for you to query on sizing your InnoDB Buffer Pool from my post on What are the main differences between InnoDB and MyISAM?

share|improve this answer
    
Rolando, you rock my world! –  Ran Apr 19 '12 at 14:24
add comment

My best guess is that it has at some point in time been that big.

ibdata1 will never shrink so it will keep at it's maximum size. Perhaps you've had a couple if tables in it before you did innodb_file_per_table that where big.

The only way I know of shrinking it is to do a dump, delete the files (ibdata, iblog and all .idb) and load the dump.

share|improve this answer
add comment

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.