How to interpret the information spit out by innochecksum? MySQL is using up a lot of the disk space. Running du -sh on the data directory shows a humongous ibdata1 file (around 1.7 terabytes).

I found a very helpful article online and ran a modified version of the innochecksum program on the file as it instructed. Here's the (partial) output of the program:


  0      bad checksum
  
  96334276        FIL_PAGE_INDEX 3173387 FIL_PAGE_UNDO_LOG 
  
  19932  FIL_PAGE_INODE
  
  45494  FIL_PAGE_IBUF_FREE_LIST
  
  11692831        FIL_PAGE_TYPE_ALLOCATED
  
  0      FIL_PAGE_IBUF_BITMAP
  
  0      FIL_PAGE_TYPE_SYS 
  
  0      FIL_PAGE_TYPE_TRX_SYS
  
  0      FIL_PAGE_TYPE_FSP_HDR
  
  0      FIL_PAGE_TYPE_XDES
  
  0      FIL_PAGE_TYPE_BLOB
  
  0      FIL_PAGE_TYPE_ZBLOB
  
  0      other
  
  49572348        max index_id
  
  undo type: 331 insert, 1761203 update, 1411853 other
  
  undo state: 0 active, 297 cached, 0 to_free, 1475925 to_purge, 0
  prepared, 1697165 other


I don't have much knowledge of the INNODB internals, nor I could find any documentation that will help me interpret this information.

Could anyone help me understand what these information signify? Particularly, I want to know what is taking up so much space in the ibdata1 file.
You should see what is inside ibdata1 from an artist's conception (brought to you by Percona)



Before you decide to shrink ibdata1, you need to know the following:

From the size of the ibdata1 file (1.7TB), I can safely assume you are using innodb_file_per_table=0. Thus, data and index pages for all InnoDB tables sits inside ibdata1. You need to full summary of what is inside ibdata1:

SET @pw = 3;
SET @bu = power(1024,@pw);
SELECT
    FORMAT(SUM(DL/@bu),2) DataStorage,
    FORMAT(SUM(DL),2) DataStorageBytes,
    FORMAT(SUM(IL/@by),2) IndexStorage,
    FORMAT(SUM(IL),2) IndexStorageBytes,
    FORMAT(SUM(DL+IL),2), InnoDBStorage
FROM
(
    SELECT data_length DL,index_length IL
    FROM information_schema.tables WHERE ENGINE='InnoDB'
    AND information
) A;


Take the InnoDBStorage and subtract it from the filesize of ibdata1

The difference, which I will call Wiggle Room, is left for all other logical structures inside ibdata1:


Data Dictionary
Rollback Segments
Undo Logs
Double Write buffer
Insert Buffer


It is possible to get table full errors if the Wiggle Room (difference of ibdata filesize - InnoDBStorage) cannot accommodate the two busiest logical structures that can change size quickly: 1) Rollback Segments, 2) Undo Logs. I have seen this happen as recently as 6 months ago (I wrote about this before : How to solve &quot;The table ... is full&quot; with &quot;innodb_file_per_table&quot;?)

Here are you options

OPTION #1

If you decide to shrink ibdata1, make sure you have enough diskspace for doing a mysqldump and gzip of all the data. If none of the InnoDB table have foreign keys constraints, you could convert all InnoDB tables to MyISAM, shutdown mysql, delete ibdata1, delete ib_logfile0, delete ib_logfile1, set innodb_data_file_path=ibdata1:10M:autoextend in /etc/my.cnf, start mysql, and convert all those tables back to InnoDB. I just recently learned that technique for Shlomi Noach.

OPTION #2

If you decide to enable innodb_file_per_table, ibdata1 should still incredibly small for life. However, accessing metadata about InnoDB tables will be a lot slower.

OPTION #3

If you want to leave things the way they are, please add ibdata2 within /etc/my.cnf:

STEP01 : service mysql stop

STEP02 : Get filesize of ibdata1

cd /var/lib/mysql
IBDATA1_FILESIZE=`ls -l ibdata1 | awk '{print $5}'`
echo ${IBDATA1_FILESIZE}


Let's suppose IBDATA1_FILESIZE is 2000000000000.

STEP03 : Add ibdata2 to InnoDB Data File Path in /etc/my.cnf

innodb_data_file_path=ibdata1:2000000000000;ibdata2:10M:autoextend


STEP04 : service mysql start

Give it a Try !!!

CAVEAT Here are some nice links for InnoDB Internals


http://dev.mysql.com/doc/internals/en/innodb.html
http://blog.jcole.us/innodb/
http://www.percona.com/files/percona-live/justin-innodb-internals.pdf


Google searches can give you much more InnoDB Internals Documentation