![]() `created_user` varchar(100) DEFAULT NULL, `health_record_type_id` int(11) DEFAULT NULL, I have created a test database named “compression” and table with below structure. So, we should be more careful in selecting the column for compression. In the first example we observed that compressed data size is more than the original because of the compression header and small amount of data with less repetitive patterns. | length('w….testededed') | length(compress('w….testededed')) | | length('mydbops') | length(compress('mydbops'))|ġ row in set (0.00 sec) mysql> select length('w….testededed'),length(compress('w….testededed')) mysql> select length('mydbops'),length(compress('mydbops')) We need to select right column for compression which can provide good compression ratio else compression will be an additional overhead. ![]() It provides a better compression ratio with text data having large number of predefined words by using dictionaries. It is using zlib library for compression. The data will be compressed while writing on disk and decompressed while reading. Per-Column compression is a feature which helps us to store the data of columns in compressed format. I have used this tool mysql_random_data_load to load random data. To test the same, I have installed Percona server for MySQL 5.7 in a machine with 4 cores of CPU and 8GB of RAM. In this blog post, we will look on this feature and how to effectively use it. We explored this feature and got major improvements in size reduction. Later I checked on other possibilities to compress the text columns further, At that time, then I came across per-column compression feature in Percona MySQL server (From 5.7.17-11) which features individual column compression and we were using Percona XtraDB cluster servers in that environment. At preliminary check, I have validated the table size and its row format, as it was in compressed format already. Recently, One of our client reached our Remote DBA team with a requirement to reduce the size of the table as it is having many text columns with huge number of records.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |