Geek Answers Handbook

MySQL: ibdata file size reduction for MyISAM tables

My question is actually very similar to this one and also contains a good answer for the case with InnoDB Engine tables:

https://dba.stackexchange.com/questions/8982/is-there-any-best-way-to-reduce-the-size-of-ibdata-in-mysql/8983#8983 ,

I noticed that the output scheme does not shrink ibdata files, so I was looking for methods to tune the database to reduce the size after deleting the scheme.

I found a lot of links talking about InnoDB and a way to save the table to a file so that the .frm file itself contains the table data and it will be reduced.

But what happens to MyISAM tables (with table sizes over 5G).

+4
source share
2 answers

ibdata1 and MyISAM are mutually exclusive.

The first thing you need to do is to calculate how many tables use both storage engines:

SELECT COUNT(1) EngineCount,engine FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql') GROUP BY engine;

If SOME tables are InnoDB:

Run my InnoDB Cleaner

If you only have MyISAM tables and InnoDB tables:

First, repair any traces of InnoDB Follow these steps:

STEP01) Add this to my.cnf

 [mysqld] skip-innodb

STEP02) service mysql restart

STEP03) rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile*

After these steps, you can compress each MyISAM table as follows:

For the mydb.mytable table, which is MyISAM, simply run one of the following:

  • OPTIMIZE TABLE mydb.mytable;
  • ALTER TABLE mydb.mytable ENGINE=MyISAM; ANALYZE TABLE mydb.mytable;

If you want to defragment all your MyISAM tables, make a shell script for this ...

 MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';') " SQL="${SQL} FROM information_schema.tables " SQL="${SQL} WHERE engine='MyISAM' AND table_schema NOT IN " SQL="${SQL} ('information_schema','performance_schema','mysql')" mysql ${MYSQL_CONN} -ANe"${SQL}" > GlobalMyISAMOptmizeTable.sql less GlobalMyISAMOptmizeTable.sql

Once you trust the script visually, just run it

 mysql ${MYSQL_CONN} < GlobalMyISAMOptmizeTable.sql

Give it a try !!!

UPDATE 2012-07-25 09:52 EDT

I would like to clarify one of my MyISAM compression suggestions

I said earlier

  • OPTIMIZE TABLE mydb.mytable;
  • ALTER TABLE mydb.mytable ENGINE=MyISAM; ANALYZE TABLE mydb.mytable;

These commands are mechanically identical. OPTIMIZE TABLE defragments the MyISAM table and then runs ANALYZE TABLE to compute new index statistics.

Mechanically speaking, this is what ALTER TABLE mydb.mytable ENGINE=MyISAM; does:

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

As mentioned, MyISAM should not use ibdata. What are your innodb settings in my.cnf or in the MySQL shell type:

  SHOW VARIABLES LIKE "%innodb%";

Are these variables set?

  innodb_data_home_dir innodb_data_file_path

If you are not using INNODB at all, you should be able to safely remove ibdata and ib_logfile and restart MySQL. Usually, although deleting them without losing tables will cause problems first. See How to compress / clean ibdata1 file in MySQL

If you have a MyISAM table on top of 5Gigs, it recommends using INNODB anyway. (Something over 4 concerts). For troubleshooting you can try and add

  skip-innodb

in my.cnf if you are not using INNODB at all.

+2
source

More articles:


All Articles