How to Enable Compression in MariaDB

Among the many things MariaDB has, it provides data compression. Yes, that means data is zipped before it is stored. This will give us the following benefits:

• If you are in a Gallera cluster, synchronization will be faster (less information to be transmitted), and
• with the same amount of disk space, you will be able to store more records.

Requirements for Compressing your Database

You must fulfil the following requirements:

• MariaDB >= 10.1.0
• Your configuration file must have innodb_file_per_table=1 and databases must be built with this parameter. If you do not have it, you will need to enable it and then rebuild the database.

Enabling Compression in the Database

1. Add the following lines in your MariaDB configuration file:
   innodb_compression_level=9
   innodb_file_format = barracuda
   innodb_file_format_max = barracuda
   
   By default, MariaDB will use the zlib compression algorithm. You may want to recompile manually if you want to enable others.
   
   
2. If you do not want to restart your server, you can type these commands:
   SET GLOBAL innodb_compression_level = 9;
   SET GLOBAL innodb_compression_algorithm=zlib;
   SET GLOBAL innodb_file_format = barracuda;
   SET GLOBAL innodb_file_format_max = barracuda;
   
   
3. You may want to verify the settings by typing SHOW VARIABLES WHERE Variable_name LIKE "have_%" OR Variable_name LIKE "%_compression_%". You will get an output like this:
   

   +------------------------------------------+----------+ 
   | Variable_name                            | Value    | 
   +------------------------------------------+----------+ 
   | have_compress                            | YES      | 
   | have_crypt                               | YES      | 
   | have_dynamic_loading                     | YES      | 
   | have_geometry                            | YES      | 
   | have_openssl                             | YES      | 
   | have_profiling                           | YES      | 
   | have_query_cache                         | YES      | 
   | have_rtree_keys                          | YES      | 
   | have_ssl                                 | DISABLED | 
   | have_symlink                             | YES      | 
   | innodb_compression_algorithm             | zlib     | 
   | innodb_compression_failure_threshold_pct | 5        | 
   | innodb_compression_level                 | 9        | 
   | innodb_compression_pad_pct_max           | 50       | 
   +------------------------------------------+----------+

4. At this point the MariaDB compression is enabled, the next database you created will have compression by default. In order to compress your tables, you will need to alter the table with the following command: ALTER TABLE `table_name` ENGINE=InnoDB PAGE_COMPRESSED=1. This command is slow and heavy, do not execute it if you have a database server with high queries per second.

What if I have a Gallera Cluster?

Happy for us, there is no an extra implication. You just need to active the parameters on your nodes and running the ALTER TABLE command in just one will force the other nodes to get the compressed format.

Good luck!