percona mysql add column to a large table

#PERCONA MYSQL ADD COLUMN TO A LARGE TABLE HOW TO#

Now the size of the table is reduced by 60%. Query OK, 0 rows affected (7 min 21.10 sec) mysql> alter table compress_test1 row_format=compressed We will change the row format to compressed in table compress_test1. Ok, now we will start compressing the tables by using different methods and to analyse the best one. 1 mysql mysql 8.9G Sep 24 10:02 compress_test1. 1 mysql mysql 8.9G Sep 24 09:40 compress_test2. 1 mysql mysql 8.9G Sep 24 09:23 compress_test3. I have created three tables with 2M records and the size of the tables are around 9GB test]# ls -lrth compress_test *.ibd `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.

#PERCONA MYSQL ADD COLUMN TO A LARGE TABLE HOW TO#

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.

Percona mysql add column to a large table