Database Optimization (5)

Hey guys,

So I got lazy and let my DB get pretty huge, over 50 gigs already..I deleted a month's worth of stats in FF, but when I checked the DB, the size was the same..I was told that I have to optimize the DB to get the space back.. does anyone know the query to do that when it relates to FunnelFlux?

Thanks!

Let me ping the funnelflux guys to chime in and help you if possible.

The command is:

Optimize table [[tablename]]

Where [[tablename]] is the table you deleted from.

Be aware that unless you are on MySQL 5.7.4, running the command will most likely lock up the table during the entire process. Which means nothing would be saved to it etc. For 50 gig db, not sure the size of the table, that could take a while.

To do this with less downtime you would need to copy all the data to a new table, and switch the table names when complete. This is only viable if you have enough space to have both tables at the same time.

Thanks a lot @Nana!

I actually already replied in the support chat this morning, but I'm going to post it here for reference - my reply below includes compressing the table.

it's done in 3 steps and you need to have at least 50% free space on your disk.

1st step:
In your MySQL configuration file, check if you have the option innodb_file_per_table set to 1
Otherwise the compression won't work
If it is not set to 1, you cannot simply turn it to 1 now - there is a procedure to follow to turn it "on" on existing databases:
http://dba.stackexchange.com/questio...-for-a-live-db

2nd step:
Stop all traffic then run the following MySQL commands one by one:

create table `compressed_stats_root_4` like `stats_root`;
alter table `compressed_stats_root_4` key_block_size=4 row_format=compressed;
insert into `compressed_stats_root_4` select * from `stats_root`;
rename table `stats_root` TO `stats_root_old`;
rename table `compressed_stats_root_4` TO `stats_root`;

drop table `stats_root_old`;

Thanks everyone!!