I have giant tables with lots of VARCHAR, TINYTEXT, and TEXT fields. As I'm not an expert in MySQL DB I've recently came across the compression feature of MySQL database by seeing this page InnoDB Table Compression.
I've searched and read multiple pages and came across that I need to set the following parameters and apply these changes to my my.cnf:
innodb_file_per_table=1; (this is already set)
innodb_file_format=Barracuda;
and for table structure:
...
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8; (possibly I need to do a trial-and-error to find a
better key_block_size that fits better to my case)
So, I want to initially test and see how this compression would benefit me on table sizes and hopefully manage my space better. To do this test I'll generate a sample table with few hundreds or thousands rows of the original table and do the trials and performance measuring.
Since this would be a testing process and since I don't have any other server to do the test on, I don't want to risk anything.
Here is my question:
If I change my my.cnf file (by adding innodb_file_format=Barracuda;) on my current server and restart the server would it have any effect on my existing tables/data that have ROW_FORMAT=COMPACT?
I couldn't find any place answering/mentioning this, so thanks in advance.
innodb_file_format applies to newly created tables, not existing tables. I don't think you even need to do a restart.
The SET GLOBAL commands need root permission, but after that, you can run as a regular user.
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1; -- already done
SET GLOBAL innodb_large_prefix=1; -- optional
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=COMPRESSED; -- This assumes table exists
CREATE TABLE tbl ( ...
) ROW_FORMAT=COMPRESSED; -- This assumes creating