Mysql Data automatically gets deleted

How to fix the Overhead and Effective problem on InnoDB table?

How to fix the Overhead and Effective problem on InnoDB table?

up vote7down votefavorite 2

Questions : 1 - What is mean by Overhead? When I click "Optimize table" button on MyISAM table, Overhead and Effective data are gone. I wonder what it does to my table? 2 - Do I need to care of Overhead and Effective value actually? How to fix the Overhead and Effective problem on InnoDB table? mysql shareimprove this question edited Aug 15 '11 at 19:35 skaffman 241k53547603 asked Aug 15 '11 at 18:31 zac1987 83842343

add a comment

2 Answers

activeoldestvotes

up vote7down voteaccepted

Fixing InnoDB is not as trivial as a Click of a Button. MyISAM is. Under the hood, OPTIMIZE TABLE will do this to a MyISAM table called mytb: Create empty temp table with same structure as mytb Copy MyISAM data from mytb into the temp table Drop table mytb Rename temp table to mytb Run ANALYZE TABLE against mytb and store index statistics OPTMIZE TABLE does not work that way with InnoDB for two major reasons: REASON #1 : InnoDB Storage Layout By default, InnoDB has innodb_file_per_table disabled. Everything InnoDB and its grandmother lands in ibdata1. Running OPTIMIZE TABLE does the following to an InnoDB table called mytb: Create empty InnoDB temp table with same structure as mytb Copy InnoDB data from mytb into the temp table Drop table mytb Rename temp table to mytb Run ANALYZE TABLE against mytb and store index statistics Unfortunately, the temp table used for shrinking mytb is appended to ibdata1. INSTANT GROWTH FOR ibdata1 !!! In light of this, ibdata1 will never shrink. To matters worse, ANALYZE TABLE is useless (Explained in REASON #2) If you have innodb_file_per_table enabled, the first four(4) steps will work since the data is not stored in ibdata1, but in an external tablespace file called mytb.ibd. That can shrink. REASON #2 : Index Statistics are Always Recomputed InnoDB does not effectively store index statistics. In fact, if you run ANALYZE TABLE on mytb, statistics are created and stored. Unfortunately, by design, InnoDB will dive into the BTREE pages of its indexes, guessimate key cardinalities, and uses those numbers to prep the MySQL Query Optimizer. This is an on-going process. In effect, ANALYZE TABLE is useless because the index statistics calculated are overwritten with each query executed against that table. I wrote about this in the DBA StackExchange June 21, 2011. Percona explained this thoroughly in www.mysqlperformanceblog.com As for overhead in MyISAM, that number can be figured out. For a MyISAM table, the overhead represents internal fragmentation. This is quite common in a table that experiences, INSERT, UPDATEs, and DELETEs, especially if you have BLOB data or VARCHAR columns. Running OPTIMIZE TABLE make such fragmentation disappear by copying to a temp table (naturally not copying empty space). Going back to InnoDB, how do you effectively eliminate waste space ? You need to rearchitect ibdata1 to hold less info. Withing ibdata1 you have four types of data: Table Data Pages Index Data Pages Table MetaData MVCC Data for Transactions You can permamnently move Table and Indexes Out of ibdata1 forever. What about data and indexes already housed in ibdata1 ? Follow the InnoDB Cleanup Plan that I posted October 29, 2010 : Howto: Clean a mysql InnoDB storage engine?