Compress the MariaDB tables

       260 words, 2 minutes

This is just a quick note on how to enable MySQL / MariaDB compressed tables. As I plan to store lots of text, I’ll check later on if that’s usefull or not. But I guess it should.

Compressed tables will required the InnoDB storage engine and non-default parameters:

# pkg_info mariadb-server
Information for inst:mariadb-server-10.0.34v1
(...)

# vi /etc/my.cnf
(...)
# Compressed tables
innodb_file_per_table = 1
innodb_file_format = barracuda
innodb_strict_mode = 1
:wq

# rcctl restart mysqld
mysqld(ok)
mysqld(ok)

# tail cherie.tumfatig.net.err
180703 11:28:41 [Note] InnoDB: Compressed tables use zlib 1.2.3
(...)
180703 11:28:41 [Note] InnoDB: Highest supported file format is Barracuda.
(...)

Now, every tables will have to be created using the InnoDB engine and “COMPRESSED” row_format:

MariaDB [test]> CREATE TABLE test_unspec (i INT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE TABLE test_compressed (i INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> SELECT name,row_format FROM information_schema.innodb_sys_tables WHERE name LIKE "%test_%";
+----------------------+------------+
| name                 | row_format |
+----------------------+------------+
| logs/test_compressed | Compressed |
| logs/test_unspec     | Compact    |
+----------------------+------------+
2 rows in set (0.01 sec)

A way to compress already existing tables is to list uncompressed tables and write the alter command. Then select the tables to alter and run the command in MySQL / MariaDB.

# cat > create_alter_list.sql
USE INFORMATION_SCHEMA;
SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ROW_FORMAT=COMPRESSED;") AS UncompressedTables FROM TABLES WHERE ENGINE='innodb' AND ROW_FORMAT !='COMPRESSED';

# mysql -u root -p < create_alter_list.sql
UncompressedTables
ALTER TABLE `logs`.`test_unspec` ROW_FORMAT=COMPRESSED;
(...)

Now, time to work on the real thing…