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
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;
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: