MySQL Assistance

nitrobass24

Moderator
Dec 26, 2010
1,087
131
63
TX
Hey Guys - not exactly a DBA, but i have setup a mysql instance on Ubuntu and loaded the data into the tables. After realizing that I can save significant space by using innodb compression I have altered a few tables to use compression.

Problem is its using the same amount of space...at least according to phpmyadmin. Also the "compression" process seems to have eaten up even more disk space and it hasnt been released. I assume it was tmp/scratch space to write out the contents temporarily.

Any ideas here?
 

dba

Moderator
Feb 20, 2012
1,478
183
63
San Francisco Bay Area, California, USA
Just guessing since I don't use mySQL much, but since you have turned on compression, any subsequent adds or changes will be compressed, but the old data may stay as is. Unless it's a huge table, just copy your data to some temporary table, truncate the old table, and then re-insert your data from the temporary table to your target table. Also, mess with KEY_BLOCK_SIZE a bit if you haven't already; using the wrong setting can prevent you getting any benefit from compression.
 
Last edited:

Salami

New Member
Oct 12, 2012
31
1
0
I also don't use MySQL much...

It is possible that MySQL did not release the free disk space you created, but will use it for new data. This is what SQL Server would do - the database file size doesn't decrease even if you delete a huge table.

If this is the case, you want to google "shrinking" the database.