Mysql – Ibdata usage and Recommendations

ibdatainnodbMySQL

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.

Best Answer

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
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:

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend

Related Solutions

Mysql – the best way to reduce the size of ibdata in thesql

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) (More details here)

Step 02) Drop all databases (except mysql, performance_schema, and information_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;

Mysql – Restore the data from Snapshot Backup when the data spans over multiple directories

To begin with, storing ibdata on a separate volume from the datadir is a bad idea because of

MetaData Issues : I wrote about this as well
Performance Issues : I wrote about that back in August 2011
- MySQL InnoDB data file directly on a disk slice (fixed-size)?

Getting back to your problem, the reason LVM for ibdata1 is not good has to do with the metadata in ibdata1 at any given point-in-time. If you were to LVM Snapshot ibdata1, then create new tables, and drop some tables, somne of the corresponding .frm files within datadir would not match. The time needed to coalesce that large an ibdata would be astronomical (unless 21 hours is acceptable to you). I tried using rsync as opposed to LVM and I fared no better.

The safest and most sensible way to clear the space with innodb_file_per_table disabled is to do the following on the Slave

Step 01) mysqldump all databases
Step 02) shutdown mysql
Step 03) delete ibdata1
Step 04) startup mysql
Step 05) reload the mysqldump

Without this method, ibdata1 can never be shrunk otherwise. Even in my post in StackOverflow on InnoDB Cleanup (using innodb_file_per_table), you have to do those same 5 steps.

Best Answer

UPDATE 2012-02-22 13:00 EST

Related Solutions

Mysql – the best way to reduce the size of ibdata in thesql

Mysql – Restore the data from Snapshot Backup when the data spans over multiple directories

Related Question