2

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.

rubayeet
  • 123
  • 5

2 Answers2

1

You should see what is inside ibdata1 from an artist's conception (brought to you by Percona)

InnoDB Architecture

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 "The table ... is full" with "innodb_file_per_table"?)

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

Google searches can give you much more InnoDB Internals Documentation

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

I would begin with the following query:

select 
  s.schema_name,t.table_name, 
  concat(ifnull(round(sum(t.data_length)/1024/1024,2),0.00),"mb") data_size,
  concat(ifnull(round(sum(t.index_length)/1024/1024,2),0.00),"mb") index_size
from 
  information_schema.schemata s 
left join 
  information_schema.tables t 
on 
  s.schema_name = t.table_schema 
where 
  s.schema_name not in ('mysql','information_schema', 'performance_schema')
  and  t.engine = 'InnoDB'
group by 
  s.schema_name,
  t.table_name 
order by 
  s.schema_name, 
  data_size desc,
  index_size desc;

Check if the sum of the size of the tables justifies the size of ibdata1. Note that to eventually reclaim space after removing some data, you'll need to dump all the databases, recreate the system databases and reload the previously created dump.

Consider setting the parameter *innodb_file_per_table* as well (set to ON by default in MySQL 5.6).

Dimitre Radoulov
  • 799
  • 1
  • 7
  • 14