I have a Production server

On November 5,2011 the ibdata size was 100G. In Approximate 3 months it is increased to 200G.So it has just doubled its size.So it is a Huge Data.Currently lvm is 251G.

I Have approximately all tables with Innodb.I am not using innodb per table. I have my idbata on lvm.So How much i should increase it for future use..?

Or any other Best way to Handle the Scenario.


You need to stop using InnoDB (with innodb_file_per_table off) and LVM snapshots as soon as possible. Here is why:

I have a monitoring system which uses MySQL as its database. It has innodb_file_per_table off as well. In a space of 6 months, it grew to 1.3TB.

My employer's company cannot have downtime for this monitoring system.

I attempted to create a Slave for this monster database. I ran an rsync of /var/lib/mysql to the Slave server. Its first pass took 42 hours (That's not a typo, 1 day 18 hours). The second pass took 84 hours (3 days 12 hours) and was only 15% done (220GB copied). As you can see, I abandoned the second rsync.

The problem stemmed from ibdata1 being 1.3TB.

What is inside ibdata1 when innodb_file_per_table is OFF?

  • Table Data Pages
  • Table Index Pages
  • Table Metadata (Tablespace ID Management)
  • MVCC Data (for Transaction Isolation, ACID Compliance)

In a write-heavy environment, all records of these four types are being read and/or written. The rsync experienced a nightmare trying to get the ibdata1 coalesced enough to write changes to the Slave server. According to my Senior Linux Engineer counterparts, an LVM snapshot would not fare any better.

Your only recourse is to start using innodb_file_per_table.

For you to see this need further, please run this query (should take 5-10 min for you)

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

Using the totals for InnoDB, compare it to the size of ibdata1. You will find that there may be some a difference of 30GB of less. I ran than query against ibdata1 in my monitoring system and had only 27GB for table metadata and new MVCC info. Steady growth would always occur on this file.

Here is what you can do: Convert all your InnoDB to use innodb_file_per_table. I wrote a post in StackOverflow on how to do that. Not only will you be able to have easch table exist in a separate file, you will be able to keep ibdata1 rate of growth down to a bare minimum.

UPDATE 2012-02-22 13:00 EST

When executing the CleanUp of InnoDB, make sure you eliminate the making of multiple ibdata files. your setting should be the default:

  • I have shared the data of one of my slave server.I have 3 slaves with exactly same configuration and the ibdata is increasing on all of them. And all are the dedicated MySQL production servers so what should i do in this case.Should i have to create the another slave for performing the above task for all the slave. – Abdul Manaf Feb 22 '12 at 14:42
  • Nice answer (as usual). It's going to take some finagling to convert to innodb_file_per_table without having any downtime at all on the monitoring system, which seems to be important to the OP. – Derek Downey Feb 22 '12 at 14:44
  • @Abdul Yes you should. Perform this on the Slave. You should then shomewho setup circular replication between that Slave and Master. Failover everything to that Slave,. Then perform the same operation on all other servers. Make sure you have good mysqldumps to reload each Slave. – RolandoMySQLDBA Feb 22 '12 at 15:17
  • @RolandoMySQLDBA: in your update part why we should have default setting for ibdata file. – Abdul Manaf Feb 23 '12 at 3:29
  • @Abdul because metadata and MVCC do not rapidly grow. You will only need ibdata1. NEVER, NEVER, NEVER EVER AGAIN should you be using multiple ibdata files. – RolandoMySQLDBA Feb 23 '12 at 3:43

In addition to what Rolando said, switching to the per file setup may allow you regain some disk space through periodic maintenance.

Optimize table doesn't do much for you with everything in a single ibdata file. However in a per file setup you can often regain some disk space space by doing this. Optimize table will effectively rebuild the entire table, eliminating unused space left over from fragmentation during deletes.

You can see which tables will benefit from this by running show table status. Alternatively run

select table_name, data_length, data_free from information_schema.tables
where engine='innodb' order by 3

Optimizing tables with the most data_free will give you the biggest savings in disk space.

However there are some caveats:

  • Each table will be fully locked during the optimize. If an individual table is 10s to 100s of gigs this will not be quick
  • You should not expect the data_free to fall completely to 0 after an optimize. There will still be some unused space due to the way innodb allocates space in 16k pages.
  • Once innodb_file_per_table is enabled and ibdata1 is cleaned up (stackoverflow.com/questions/3927690/…), you could run OPTIMZE TABLE on any InnoDB table to shrink the .ibd file. You could also shrink it with ALTER TABLE tblname ENGINE=InnODB; At least ibdata1 would be spared any monolithic growth spurts. +1 for mentioning additional pros-and-cons of InnoDB with/without innodb_file_per_table. – RolandoMySQLDBA Feb 22 '12 at 17:56
  • alter table engine='innodb' is basically what optimize does for innodb. More a matter of syntax preference I believe. – atxdba Feb 24 '12 at 2:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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