Archive for the ‘Databases’ Category

Amazon Aurora Row size limit.

October 31, 2018

We started using Aurora as soon as it was released. With the obvious pros over using MySQL. There’s never been an issue running our production workloads.

Recently we encountered an issue that one would have assumed would be a non-issue in Aurora. The row size of a column is limited to a little less than 8KB. Our application had a description text area that needed to accept a little more than 8KB of data. [1]

“Amazon Aurora MySQL does not support compressed tables that is tables created with ROW_FORMAT=COMPRESSED, hence we request you to change the row_format to DYNAMIC in BARRACUDA file format” [5]

What I found on the forums wasn’t helpful. The solutions there were a no go, use Oracle lol. [6]

Background:

The issue occurs when the file format chosen by InnoDB is ANTELOPE. (Please note that ANTELOPE is the original InnoDB file format and currently this file format supports COMPACT and REDUNDANT row formats for the InnoDB tables and this currently is the default file format in MySQL 5.6. This is kept as default to ensure that the earlier MySQL versions have maximum compatibility with 5.6 MySQL. Please note that this is the case with MySQL also which leverages ANTELOPE as default file format.)

Currently InnoDB has another file format called BARRACUDA which is the newest file format for InnoDB and this currently supports all InnoDB row formats including the newer COMPRESSED and DYNAMIC besides the COMPACT and REDUNDANT row format from ANTELOPE file format. The COMPRESSED and DYNAMIC row formats include compressed tables, efficient storage of off-page columns and index key prefixes up to 3072 bytes [4]. So when you create a table with row format in DYNAMIC or COMPRESSED, InnoDB can store long variable-length column values for VARCHAR, BLOB and TEXT data types fully off-page. [4]

Having said this please kindly note that both DYNAMIC and COMPRESSED row formats support index key prefixes up to 3072 bytes, to get the prefix of more than 768 bytes we will have to use turn the innodb_large_prefix parameter to ON that can give key prefixes up to 3072 bytes. DYNAMIC row format can maintain the efficiency of storing the entire row in the index node, however, this format avoids the problem of filling B-tree nodes with a large number of data bytes of long columns.

Solution:

1. By default innodb_file_per_table parameter is set to ON in Cluster Parameter group, you can have a different custom cluster parameter group just in case if would like to change the collation and character sets in future, for now even with your default cluster parameter group the innodb_file_per_table parameter is set to ON.

  • The default values are innodb_file_format = Antelope [2] and innodb_large_prefix = 0 [3]

2. Tweak these respective parameters in a custom DB parameter group called ‘aurora56’

  • innodb_file_format value to BARRACUDA
  • innodb_large_prefix value to 1 so that you can get up to 3072 bytes for key prefixes.

Assign the custom DB parameter group to you Aurora instance.

3. Perform the ALTER TABLE after changing the values of parameter groups as suggested earlier and tag the custom parameter groups to your instance and after ensuring that the parameter groups have taken into effect you may perform the below.

mysql> ALTER TABLE <table_name> ENGINE=INNODB ROW_FORMAT = DYNAMIC;

Conclusion:

This solution was made available by AWS Support. And I thought it sounded pretty smart for a workaround. Credit to Ashika that picked up my ticket.

References:

[1] Limits on InnoDB Tables: https://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html

[2] innodb_file_format: https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_file_format

[3]innodb_large_prefix: https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

[4] InnoDB File-Format Management: https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html

[5] Compressed tables not supported: https://docs.aws.amazon.com/dms/latest/sbs/CHAP_MySQL2Aurora.RDSMySQL.html#CHAP_MySQL2Aurora.RDSMySQL.Snapshot.PreImport

[6] Forums. https://forums.aws.amazon.com/thread.jspa?threadID=283983

Advertisements