0

I have a big (500GB) innodb table that I'm performing many inserts as well as deletes on. innodb_file_per_table is set in my var configuration. I know it might contain a lot of 'wasted' disk space, due to the nature of innodb, question is how much is expected and if there is a way to find out that number in my specific table. From what I understand it re-uses the not-used space that is created during delete so if I keep doing inserts I shouldn't experience any major diff when performing a re-claim procedure? As a thumb rule, what are the high and low limits for the disk space waste and what usage patterns define it?

Community
  • 1
  • 1
Noam
  • 3,341
  • 4
  • 35
  • 64

2 Answers2

2

If you have innodb_file_per_table enabled, I have some good news. You can do a lot more than defrag the table. You can shrink the table. In order to find out how much space you will reclaim follow this procedure:

For these steps, let's use the InnoDB table mydb.mytable

GIVEN_DB=mydb
GIVEN_TB=mytable
MYSQL_CONN="-root -p...."
SQLSTMT="SELECT data_length+index_length FROM information_schema.tables WHERE"
SQLSTMT="${SQLSTMT} table_schema='${GIVEN_DB}' AND table_name='${GIVEN_TB}'"
IBDSIZE=`ls -l /var/lib/mysql/${GIVEN_DB}/${GIVEN_TB}.ibd | awk '{print $5}'`
TBLSIZE=`mysql ${MYSQL_CONN} --skip-column-names -Ae"${SQLSTMT}"`
(( FRGSIZE = IBDSIZE - TBLSIZE ))
FRAGPCT=`echo ${FRGSIZE}00/${IBDSIZE}|bc`
echo ${IBDSIZE} ${TBLSIZE} ${FRGSIZE} ${FRAGPCT}

This will print 4 numbers

  • IBDSIZE : Size of InnoDB Individual Tablespace from the OS Viewpoint
  • TBLSIZE : Size of InnoDB Table from the INFORMATION_SCHEMA Database
  • FRGSIZE : Difference of IBDSIZE and TBLSIZE (in Bytes)
  • FRAGPCT : Percentage Difference of IBDSIZE and TBLSIZE

If you do not like how high FRAGPCT is, there are three ways to shrink mydb.mytable:

OPTION 1 : OPTIMIZE TABLE mydb.mytable;

OPTION 2 : ALTER TABLE mydb.mytable ENGINE=InnoDB;

OPTION 3 : Run the Compression in Stages

CREATE TABLE mydb.mytabletmp LIKE mydb.mytable;
INSERT INTO mydb.mytabletmp SELECT * FROM mydb.mytable;
DROP TABLE mydb.mytable;
ALTER TABLE mydb.mytabletmp RENAME mydb.mytable;

Give it a Try !!!

RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
  • Lost you a bit. How/where should I run the first chunk of code you wrote (starts with "GIVEN_DB"). Sraight from bash? – Noam Apr 23 '13 at 15:54
0

MySQL / InnoDB provides no function to 'free up' space, but it will re-use the space from the deleted rows for future insert statements.

Running OPTIMIZE TABLE (more information) periodically will help to maximize the amount you can re-use as well as increase I/O performance (it's basically like defrag for your database).

Glitch Desire
  • 14,632
  • 7
  • 43
  • 55