In my previous post I pointed out that the existing ARCHIVE storage engine in MySQL may not be the one that will satisfy your needs when it comes to effectively storing large and/or old data. But are there any good alternatives? As the primary purpose of this engine is to store rarely accessed data in disk space efficient way, I will focus here on data compression abilities rather then on performance.
The InnoDB engine provides compressed row format, but is it’s efficiency even close to the one from that available in archive engine? You can also compress MyISAM tables by using myisampack tool, but that also means a table will be read only after such operation.
Moreover, I don’t trust MyISAM nor Archive when it comes to data durability. Fortunately along came a quite new (open source since April 2013) player into this field – TokuDB! It seems to provide an excellent compression ratios, but also it’s fully ACID compliant, and does not have any of the limitations present in Archive, so it’s functionality is much more like InnoDB! This may allow you also to store production data on SSD drives, which disk space cost is still higher then on traditional disks, where otherwise it could be too expensive.
To better illustrate what choice do we have, I made some very simple disk savings comparison of all the mentioned variants.
I have used an example table with some scientific data fetched from here (no indexes):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
CREATE TABLE `table1` ( `snp_id` int(11) DEFAULT NULL, `contig_acc` varchar(32) DEFAULT NULL, `contig_ver` tinyint(4) DEFAULT NULL, `asn_from` int(11) DEFAULT NULL, `asn_to` int(11) DEFAULT NULL, `locus_id` int(11) DEFAULT NULL, `locus_symbol` varchar(128) DEFAULT NULL, `mrna_acc` varchar(128) DEFAULT NULL, `mrna_ver` int(11) DEFAULT NULL, `protein_acc` varchar(128) DEFAULT NULL, `protein_ver` int(11) DEFAULT NULL, `fxn_class` int(11) DEFAULT NULL, `reading_frame` int(11) DEFAULT NULL, `allele` text, `residue` text, `aa_position` int(11) DEFAULT NULL, `build_id` varchar(4) NOT NULL, `ctg_id` int(11) DEFAULT NULL, `mrna_start` int(11) DEFAULT NULL, `mrna_stop` int(11) DEFAULT NULL, `codon` text, `protRes` char(3) DEFAULT NULL, `contig_gi` int(11) DEFAULT NULL, `mrna_gi` int(11) DEFAULT NULL, `mrna_orien` tinyint(4) DEFAULT NULL, `cp_mrna_ver` int(11) DEFAULT NULL, `cp_mrna_gi` int(11) DEFAULT NULL, `verComp` varchar(7) NOT NULL ) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql >show table status like 'table1'G *************************** 1. row *************************** Name: table1 Engine: ARCHIVE Version: 10 Row_format: Compressed Rows: 19829016 Avg_row_length: 11 Data_length: 221158267 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: 2013-12-22 23:58:51 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.28 sec) -rw-rw----. 1 przemek przemek 211M Dec 22 23:58 table1.ARZ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql >show table status like 'table1'G *************************** 1. row *************************** Name: table1 Engine: TokuDB Version: 10 Row_format: tokudb_zlib Rows: 19829016 Avg_row_length: 127 Data_length: 2518948412 Max_data_length: 9223372036854775807 Index_length: 0 Data_free: 6615040 Auto_increment: NULL Create_time: 2013-12-23 00:03:47 Update_time: 2013-12-23 00:12:14 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.13 sec) -rwxrwx--x. 1 przemek przemek 284M Dec 23 00:12 _b_tokudb_table1_main_32_1_18_B_0.tokudb |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql [localhost] {msandbox} (b_tokudb) > show table status like 'table1'G *************************** 1. row *************************** Name: table1 Engine: TokuDB Version: 10 Row_format: tokudb_lzma Rows: 19829016 Avg_row_length: 127 Data_length: 2518948412 Max_data_length: 9223372036854775807 Index_length: 0 Data_free: 6950912 Auto_increment: NULL Create_time: 2013-12-23 00:43:47 Update_time: 2013-12-23 00:49:14 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: row_format=TOKUDB_LZMA Comment: 1 row in set (0.01 sec) -rwxrwx--x. 1 przemek przemek 208M Dec 23 00:49 _b_tokudb_sql_980_2_main_1b92_2_18.tokudb |
(btw, did you notice how the file name changed after altering with different compression?
It’s no longer reflecting the real table name, so quite confusing 🙁 )
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql > show table status like 'table1'G *************************** 1. row *************************** Name: table1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 19898159 Avg_row_length: 117 Data_length: 2343567360 Max_data_length: 0 Index_length: 0 Data_free: 4194304 Auto_increment: NULL Create_time: 2014-01-01 16:47:03 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.42 sec) -rw-rw----. 1 przemek przemek 2.3G Jan 1 16:37 table1.ibd |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql > show table status like 'table1'G *************************** 1. row *************************** Name: table1 Engine: InnoDB Version: 10 Row_format: Compressed Rows: 19737546 Avg_row_length: 59 Data_length: 1171783680 Max_data_length: 0 Index_length: 0 Data_free: 5767168 Auto_increment: NULL Create_time: 2014-01-01 18:51:22 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: row_format=COMPRESSED Comment: 1 row in set (0.31 sec) -rw-rw----. 1 przemek przemek 1.2G Jan 1 18:51 table1.ibd |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql > show table status like 'table1'G *************************** 1. row *************************** Name: table1 Engine: InnoDB Version: 10 Row_format: Compressed Rows: 19724692 Avg_row_length: 30 Data_length: 592445440 Max_data_length: 0 Index_length: 0 Data_free: 3932160 Auto_increment: NULL Create_time: 2014-01-01 19:41:12 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=4 Comment: 1 row in set (0.03 sec) -rw-rw----. 1 przemek przemek 584M Jan 1 19:41 table1.ibd |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql > show table status like 'table1'G *************************** 1. row *************************** Name: table1 Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 19829016 Avg_row_length: 95 Data_length: 1898246492 Max_data_length: 281474976710655 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2013-12-23 11:02:28 Update_time: 2013-12-23 11:03:45 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec) -rw-rw----. 1 przemek przemek 1.8G Dec 23 11:03 table1.MYD |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql > show table status like 'table1'G *************************** 1. row *************************** Name: table1 Engine: MyISAM Version: 10 Row_format: Compressed Rows: 19829016 Avg_row_length: 42 Data_length: 848098828 Max_data_length: 281474976710655 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2013-12-23 11:02:28 Update_time: 2013-12-23 11:03:45 Check_time: NULL Collation: latin1_swedish_ci Checksum: 853535317 Create_options: Comment: 1 row in set (0.00 sec) -rw-rw----. 1 przemek przemek 809M Dec 23 11:03 table1.MYD |
Compression summary table
| Engine | Compression | Table size [MB] |
|---|---|---|
| InnoDB | none | 2272 |
| InnoDB | KEY_BLOCK_SIZE=8 | 1144 |
| InnoDB | KEY_BLOCK_SIZE=4 | 584 |
| MyISAM | none | 1810 |
| MyISAM | compressed with myisampack | 809 |
| Archive | default | 211 |
| TokuDB | ZLIB | 284 |
| TokuDB | LZMA | 208 |
So the clear winner is TokuDB, leaving InnoDB far behind. But this is just one test – the results may be very different for your specific data.
To get even better idea, let’s compare several crucial features available in mentioned storage engines
| Feature | Archive | MyISAM (compressed) | InnoDB | TokuDB |
| DML | only INSERTs | no | yes | yes |
| Transactions | no | no | yes | yes |
| ACID | no | no | yes | yes |
| Indexes | no | yes | yes | yes |
| Online DDL | no | no | yes * | yes ** |
* – since version 5.6, with some limitations
** – supports add/drop indexes, add/drop/rename columns and expand int, char, varchar and varbinary data types
TokuDB seems to be an excellent alternative when it comes to disk space usage efficiency, but this is not the only reason why you should try it perhaps.
You may want to check these articles too:
Resources
RELATED POSTS
