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…