Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

Is there any best way to reduce the size of ibdata in mysql.?,

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).

share|improve this question

2 Answers 2

up vote 0 down vote accepted

I already answered this in StackOverflow : http://stackoverflow.com/a/11636341/491757

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 !!!

share|improve this answer
    
Thanks for a good answer –  Michael Jul 24 '12 at 20:23

MyISAM tables do not use ibdata files, so they won't affect the size of ibdata files.

From the MySQL Docs, here is how MyISAM stores data on disk:

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

share|improve this answer
    
Maybe i am doing something wrong, and may be i have some bug, although my default engine is MyISAM and i do create all the tables as MyISAM tables and the ibdata is very high (~20G). I have also checked the actual data size in the DB and it is about 3-4G, –  Michael Jul 24 '12 at 16:47
    
@Michael Can you add the output of SELECT engine, COUNT(*) FROM information_schema.tables GROUP BY engine; to your question? –  Derek Downey Jul 24 '12 at 16:49
    
I will do so and update here as soon as i will return back to work tomorrow morning, thanks –  Michael Jul 24 '12 at 16:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.