Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

mysql

enter image description here

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?

like image 322
zac1987 Avatar asked Aug 15 '11 18:08

zac1987


People also ask

How do I optimize InnoDB tables?

OPTIMIZE TABLE for an InnoDB table is mapped to an ALTER TABLE operation to rebuild the table and update index statistics and free unused space in the clustered index. This operation does not use fast index creation.

Does optimize table work on InnoDB?

OPTIMIZE TABLE works for InnoDB , MyISAM , and ARCHIVE tables. OPTIMIZE TABLE is also supported for dynamic columns of in-memory NDB tables. It does not work for fixed-width columns of in-memory tables, nor does it work for Disk Data tables.

What is overhead in phpMyAdmin?

If you are talking about the thing that phpMyAdmin calls overhead , then it's actual size of a table datafile relative to the ideal size of the same datafile (as if when just restored from backup). For performance reasons, MySQL does not compact the datafiles after it deletes or updates rows.


2 Answers

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?

like image 102
RolandoMySQLDBA Avatar answered Oct 30 '22 17:10

RolandoMySQLDBA


In fact "OPTIMIZE TABLE" is a useless waste of time on MyISAM, because if you have to do it, your database is toast already.

It takes a very long time on large tables, and blocks write-access to the table while it does so. Moreover, it has very nasty effects on the myisam keycache etc.

So in summary

  • small tables never need "optimize table"
  • large tables can never use "optimize table" (or indeed MyISAM)

It is possible to achieve (roughly) the same thing in InnoDB simply using an ALTER TABLE statement which makes no schema changes (normally ALTER TABLE t ENGINE=InnoDB). It's not as quick as MyISAM, because it doesn't do the various small-table-which-fits-in-memory optimisations.

MyISAM also uses a bunch of index optimisations to compress index pages, which generally cause quite small indexes. InnoDB also doesn't have these.

If your database is small, you don't need it. If it's big, you can't really use MyISAM anyway (because an unplanned shutdown makes the table to need rebuilding, which takes too long on large tables). Just don't use MyISAM if you need durability, reliability, transactions, any level of concurrency, or generally care about robustness in any way.

like image 34
MarkR Avatar answered Oct 30 '22 18:10

MarkR