CODEWITHSUNDEEP
mysqldatabase
knowledgeseeker
knowledgeseeker

Reputation: 1203

Mysql Innodb Optimize table

I have a 100Gb innodb table on C:\ drive and the C:\ drive is out of space now.I deleted many rows but the the ibd file did not shrink. I have more than 200Gb available on D:\drive and subsequently configured tmpdir to be in d:\drive and tried to optimize table .But when I optimize table it is taking up whatever space is left on c:\ drive and not using d:\

What parameter should I change?

Upvotes: 2

Views: 1161

Answers (2)

Cedric Simon
Cedric Simon

Reputation: 4659

You cannot shrink the innodb data file (default name is ibdata1) without deleting it :S .

Using innodb engine you should use innodb_file_per_table=1

To solve you space issue you should:

1) Execute a full database backup: mysqldump -u root -pMyPassword -R --all-databases > full.sql

2) Stop mysql service

3) Add (or change value of) the parameter innodb_file_per_table=1

4) Drop the existing innodb data file (/$mysqldir/ibdata1)

5) Start mysql service

6) Restore the backup: mylsql -u root -pMyPassword mysql < full.sql

Now the innodb data file will not growth too much anymore, and an optimize table will allow you to shrink the involve table data file since now each table will amnage it's own data file.

Using symbolic links (at least under Linux), it allows you to locate databases on different disks.

Upvotes: 1

cool
cool

Reputation: 1786

There is tmpdir parameter for that. And I think you can set that with

SET GLOBAL tmpdir = D:/temp

before any optimization operation or add following under mysld

set-variable = tmpdir=/var/tmp

As a bonus I strongly suggest you to use 

innodb_file_per_table=1

to have separate file for each table if of course you have innodb tables. And also using compressed data will reduce your disk usage.

Upvotes: 0

Related Questions