CODEWITHSUNDEEP
mysql
Michael
Michael

Reputation: 2867

MySQL: reducing ibdata file size for MyISAM tables

My Question is actually very similar to this one and also includes a good answer for a 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 have noticed that drop schema do not shrink ibdata files , so i have looked for a methods to configure the DB so that the size will be reduced after deleting a schema.

i have found many links talking about InnoDB and the way to save table per file so that the .frm file it self will contain the table data and it will be reduced.

But what happens with MyISAM tables (with more than 5G table size).

Upvotes: 4

Views: 5470

Answers (2)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

ibdata1 and MyISAM are mutually exclusive.

First thing you should do is count 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:

Perform my CleanUp of InnoDB

If you have only MyISAM tables and no InnoDB tables:

First, eliminate any traces of InnoDB Do the following:

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 perform a Compression of Each MyISAM tables like this:

For the table mydb.mytable that is MyISAM, just run one of the following:

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

If you want to defrag all your MyISAM tables, here is a shell script to do so...

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 suggestions for compression of MyISAM

I said earlier

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

These commands are mechanically identical. OPTIMIZE TABLE performs a defrag of the MyISAM table and then runs ANALYZE TABLE to compute fresh 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;

Upvotes: 4

ferensick
ferensick

Reputation: 21

As mentioned MyISAM shouldn't be using ibdata. What are your innodb settings in your 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 the ibdata and ib_logfile(s) and restart MySQL. Normally though removing these without dropping the tables first will cause issues. See How to shrink/purge ibdata1 file in MySQL

If you do have a MyISAM table over 5Gigs, it's recommended that you use INNODB anyway. (Anything over 4 Gigs). To troubleshoot you could try and add

    skip-innodb

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

Upvotes: 2

Related Questions