On MySQL Cluster, a shared-nothing cluster solution, the user has access to the full power of the SQL language to configure their own tables through the MySQL frontend. Not all the features of the NDB storage engine are exposed via SQL, and thus available solely through the NDB API. It is one of those features, now available through SQL, that this blog post will focus on.

NDB can be configured with many parameters to tune the database performance with respect to the hardware resources available. Some of these parameters affect the data nodes as a whole, such as thread, memory, limits or transport configurations. Other parameters only affect tables and others affect only specific columns. In these series the BLOB column will be the subject matter and, in this Part 1, a deeper dive into the BLOB column in NDB will illustrate how table memory can be configured and monitored.

The BLOB column in NDB

The BLOB column type is well-known data type to store anything that is later transformed into a more humane representation; e.g. files and images. It is used as the underlying type for several more complex types (JSON, TEXT, coordinates, etc) due to its flexible nature.

In NDB, BLOB values are split between the column in the table and zero or more rows in a separate internal table. The part of the BLOB stored in the main table is henceforth referred to as inline, while the other(s) are rows in the internal table uniquely assigned to that BLOB column. The same applies to TEXT and JSON columns. Technically, TEXT is a BLOB with a character set and JSON just has different default inline and part sizes; this text refers to JSON as the binary-encoded type since it is concerned with storage optimization.

The inline and part sizes can be checked using MySQL NDB system tables on the ndbinfo schema or using the ndb_desc tool.

With ndbinfo, run the following to check the inline and part sizes:

mysql> SELECT database_name, table_name, column_name, inline_size, part_size, blob_table_name FROM ndbinfo.blobs;
+---------------+------------+-------------+-------------+-----------+-----------------+
| database_name | table_name | column_name | inline_size | part_size | blob_table_name |
+---------------+------------+-------------+-------------+-----------+-----------------+
| test          | t1         | b           |         256 |      2000 | NDB$BLOB_37_1   |
| test          | t1         | lb          |         256 |     13948 | NDB$BLOB_37_2   |
| mysql         | ndb_schema | query       |         256 |      2000 | NDB$BLOB_4_3    |
+---------------+------------+-------------+-------------+-----------+-----------------+

 

Here table t1 has two blob columns, one BLOB b data type and another a LONGBLOB lb, each respectively having 2000 and 13948 bytes of part size. This says that storing a value in b with slightly more than 2256 bytes of data (e.g. 2500 bytes) will require at two rows in the parts table (NDB$BLOB_37_1 in the example above) because the inline is filled with 256 bytes, one part row with 2000 bytes and another with the left over 244 bytes. On the other hand, if that same value is stored in lb instead then only one row in the parts table (NDB$BLOB_37_2) needs to be allocated. The LONGBLOB data type was chosen to better illustrate the maximum part size (String Type Storage Requirements).

To retrieve the same information with ndb_desc run the following and observe the values under parenthesis in the Attributes section (some fields removed for readability):

$> ndb_desc -d <database> <table-name>
-- Attributes --
a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
b Blob(256,2000,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_37_1
lb Blob(256,13948,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_37_2

Fitting data in NDB with BLOB columns

Each created BLOB and TEXT column have the default inline size set to 256 bytes. Only the JSON data type has a different default, 4000 bytes. This means that with the maximum size of 30000 bytes per row (23.2.7.5 Limits Associated with Database Objects in NDB Cluster), if a single BLOB column is used then the amount of bytes left for the other columns is given by the following calculation (the additional 16 bytes subtracted are the always present header that defines a BLOB column):

30000 - (16 header + inline) - key-size = S

Given that a non-BLOB primary key is always required (NDB cannot have a primary index over BLOB columns), then at least 4 bytes need to be accounted for the INTEGER primary key. Some examples follow:

  • BLOB with INT key: 30000 - (16 header + 256 inline + 4 key-size) = 29724 bytes

  • JSON with no key (implicit hidden BIGUINT key): 30000 - (16 header + 4000 inline + 8 key-size) = 25976 bytes

The following examples illustrate the above with two tables, created with an INTEGER primary key.

mysql> CREATE TABLE t1 (k INTEGER PRIMARY KEY, b BLOB) ENGINE=NDB;
|-------------+----------------------------|
| k (INTEGER) | b (BLOB) = header + inline |
|-------------+----------------------------|
| 4 bytes     | 16 bytes + 256 bytes       |
|-------------+----------------------------|
Max record in table = 276 byte
mysql> CREATE TABLE t2 (j JSON) ENGINE = NDB;
|-------------+----------------------------|
| k (BIGUINT) | j (JSON) = header + inline |
|-------------+----------------------------|
| 8 bytes     | 16 bytes + 4000 bytes      |
|-------------+----------------------------|
Max record in table = 4024 byte

 

If multiple BLOB columns are used, they end up filling the maximum record size with their inline size.

mysql> CREATE TABLE t3 (k INTEGER PRIMARY KEY, j0 JSON, j1 JSON, j2 JSON, j3 JSON, j4 JSON, j5 JSON, j6 JSON) ENGINE=NDB;
|-------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------|
| k (INTEGER) | j0 (JSON) | j1 (JSON) | j2 (JSON) | j3 (JSON) | j4 (JSON) | j5 (JSON) | j6 (JSON) |
|-------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------|
| 4 bytes     | 4k bytes  | 4k bytes  | 4k bytes  | 4k bytes  | 4k bytes  | 4k bytes  | 4k bytes  |
|-------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------|
Max record in table = 28004 bytes

 

And if another large column is added, the ‘Record Too Big’ error is thus imminent.

mysql> ALTER TABLE t3 ADD COLUMN j7 JSON;
ERROR 1005 (HY000): Can't create destination table for copying alter table (use SHOW WARNINGS for more info).
mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1296 | Got error 738 'Record too big' from NDB |
| Error   | 1005 | ...                                     |
+---------+------+-----------------------------------------+

Configuring BLOB inline size

Before MySQL NDB Cluster 8.0.30, the inline and part sizes for BLOB-like columns could only be configured using the NDB API (2.3.1 The Column Class – Column::setInlineSize). Starting with MySQL NDB Cluster 8.0.30, a new feature to control the inline sizes from the SQL-layer was introduced, called BLOB INLINE SIZE. It can be used to set the inline number of bytes of any given BLOB column, whether that be shrinking the existing defaults to fit more columns or to enlarge to improve data placement, and thus read and write performance. This feature is extensively documented in 13.1.20.12 Setting NDB Comment Options and an example is illustrated next:

mysql> CREATE TABLE t4 (k INTEGER PRIMARY KEY,
                        j0 JSON COMMENT "NDB_COLUMN=BLOB_INLINE_SIZE=2000",
                        j1 JSON COMMENT "NDB_COLUMN=BLOB_INLINE_SIZE=2000",
                        j2 JSON COMMENT "NDB_COLUMN=BLOB_INLINE_SIZE=2000",
                        j3 JSON COMMENT "NDB_COLUMN=BLOB_INLINE_SIZE=2000",
                        j4 JSON COMMENT "NDB_COLUMN=BLOB_INLINE_SIZE=2000",
                        j5 JSON COMMENT "NDB_COLUMN=BLOB_INLINE_SIZE=2000",
                        j6 JSON COMMENT "NDB_COLUMN=BLOB_INLINE_SIZE=2000") ENGINE = NDB;
|-------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------|
| k (INTEGER) | j0 (JSON) | j1 (JSON) | j2 (JSON) | j3 (JSON) | j4 (JSON) | j5 (JSON) | j6 (JSON) |
|-------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------|
| 4 bytes     | 2k bytes  | 2k bytes  | 2k bytes  | 2k bytes  | 2k bytes  | 2k bytes  | 2k bytes  |
|-------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------|
Max record in table = 14004 bytes

 

Now the following works:

mysql> ALTER TABLE t4 ADD COLUMN j7 JSON;

 

To use the new feature, the user needs only to specify the amount of bytes expected to be in the inline part of the table using the syntax for a column comment NDB_COLUMN=BLOB_INLINE_SIZE=<value>. As referred in the above section, BLOB writes occupy the inline part first and if more space is required then zero or more rows on the parts table will be allocated.

Since NDB is a distributed system, every single reduction in the number of distributed steps improves the response time and thus efficiency. Therefore, if all data can be fitted in the inline part, then less distribution of rows throughout the nodes will occur and the average response time can be reduced. To illustrate this, let’s consider a table that solely stores a BLOB:

mysql> CREATE TABLE t5 (k INTEGER PRIMARY KEY, b BLOB COMMENT "NDB_COLUMN=BLOB_INLINE_SIZE=29980") ENGINE = NDB;

This table has the largest possible BLOB in the main table part, considering the maximum row in NDB ( 23.2.7.5 Limits Associated with Database Objects in NDB Cluster), the 16 byte BLOB header and the 4 byte primary key.

How much does it fit?

Now let’s create another table with default inline size, and another with a slightly larger inline size, and measure how much memory is used. Consider the table t5, created above, t6 and t7 created below:

mysql> CREATE TABLE t6 (k INTEGER PRIMARY KEY, b BLOB) ENGINE = NDB;
mysql> CREATE TABLE t7 (k INTEGER PRIMARY KEY, b BLOB COMMENT "NDB_COLUMN=BLOB_INLINE_SIZE=2048") ENGINE = NDB;
mysql> SELECT table_id, table_name, column_name, inline_size, part_size, blob_table_name
    -> FROM ndbinfo.blobs WHERE table_name = "t5" or table_name = "t6" or table_name = "t7";
+----------+------------+-------------+-------------+-----------+-----------------+
| table_id | table_name | column_name | inline_size | part_size | blob_table_name |
+----------+------------+-------------+-------------+-----------+-----------------+
|       23 | t5         | b           |       29980 |      2000 | NDB$BLOB_23_1   |
|       26 | t6         | b           |         256 |      2000 | NDB$BLOB_26_1   |
|       29 | t7         | b           |        2048 |      2000 | NDB$BLOB_29_1   |
+----------+------------+-------------+-------------+-----------+-----------------+

 

Insert some data with a large BLOB value, so that it needs to be split between the main table and the parts table for tables t6 and t7. For this example, 32768 rows were inserted each with a 2256 BLOB value.

mysql> CALL insert_blob_into('t5', 32768, 2256);
mysql> CALL insert_blob_into('t6', 32768, 2256);
mysql> CALL insert_blob_into('t7', 32768, 2256);

 

Resorting to the ndbinfo table memory_per_fragment (ndbinfo: The NDB Cluster Information Database), the memory usage for each table can be observed. Depending on the attribute, the allocation type for its storage varies according to the data memory page type, fixed for fixed-size data and var for variable-sized data. Thus, using memory_per_fragment data the average memory space that each row occupies can be calculated, accounting fixed-size data (indexes, keys, etc) and variable-sized data. In the end, all data usage is summed up to see how much can be saved. Attribute allocation types can be checked using the ndb_desc tool (shown below for one of the tables).

$> ndb_desc -d test t5
-- Attributes --
k Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
b Blob(29980,2000,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_23_1
-- Indexes --
PRIMARY KEY(k) - UniqueHashIndex
PRIMARY(k) - OrderedIndex
$> ndb_desc -d mysql NDB\$BLOB_23_1
-- Attributes --
k Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
NDB$PART Unsigned PRIMARY KEY AT=FIXED ST=MEMORY
NDB$PKID Unsigned NOT NULL AT=FIXED ST=MEMORY
NDB$DATA Longvarbinary(2000) NOT NULL AT=MEDIUM_VAR ST=MEMORY
-- Indexes --
PRIMARY KEY(k, NDB$PART) - UniqueHashIndex

 

Looking at the key AT (Allocation Type) from the result above, it can be seen that the primary key will be stored on the main table in pages for fixed-sized data and the inline BLOB value on the main table on pages for var-sized data. If a row in the BLOB table needs to be allocated (NDB$BLOB_23_1) then NDB$PART and NDB$PKID will be stored on the BLOB table in pages for fixed-sized data (k is the same of the main table and just used for the composite primary key) and NDB$DATA in pages for var-sized data.

The output below illustrates the extraction of all this data, and then some math is done to calculate how much memory each table occupies.

# BLOB table usage
# t5
mysql> SELECT AVG((fixed_elem_alloc_bytes - fixed_elem_free_bytes) / fixed_elem_count) as avg_fixed,
    ->        AVG((var_elem_alloc_bytes - var_elem_free_bytes) / var_elem_count) as avg_var
    -> FROM ndbinfo.memory_per_fragment WHERE parent_fq_name LIKE '%def/t5%'
    -> AND fq_name LIKE '%BLOB%';
+-----------+---------+
| avg_fixed | avg_var |
+-----------+---------+
|      NULL |    NULL |
+-----------+---------+
# t6
mysql> SELECT AVG((fixed_elem_alloc_bytes - fixed_elem_free_bytes) / fixed_elem_count) as avg_fixed,
    ->        AVG((var_elem_alloc_bytes - var_elem_free_bytes) / var_elem_count) as avg_var
    -> FROM ndbinfo.memory_per_fragment WHERE parent_fq_name LIKE '%def/t6%'
    -> AND fq_name LIKE '%BLOB%';
+-------------+---------------+
| avg_fixed   | avg_var       |
+-------------+---------------+
| 36.19911061 | 2020.27821806 |
+-------------+---------------+
# t7
mysql> SELECT AVG((fixed_elem_alloc_bytes - fixed_elem_free_bytes) / fixed_elem_count) as avg_fixed,
    ->        AVG((var_elem_alloc_bytes - var_elem_free_bytes) / var_elem_count) as avg_var
    -> FROM ndbinfo.memory_per_fragment WHERE parent_fq_name LIKE '%def/t7%'
    -> AND fq_name LIKE '%BLOB%';
+-------------+--------------+
| avg_fixed   | avg_var      |
+-------------+--------------+
| 36.19911061 | 220.93460067 |
+-------------+--------------+
# MAIN table usage
# t5
mysql> SELECT AVG((fixed_elem_alloc_bytes - fixed_elem_free_bytes) / fixed_elem_count) as avg_fixed,
    ->        AVG((var_elem_alloc_bytes - var_elem_free_bytes) / var_elem_count) as avg_var
    -> FROM ndbinfo.memory_per_fragment WHERE fq_name LIKE '%def/t5%';
+-------------+---------------+
| avg_fixed   | avg_var       |
+-------------+---------------+
| 32.14067706 | 2289.45045945 |
+-------------+---------------+
# t6
mysql> SELECT AVG((fixed_elem_alloc_bytes - fixed_elem_free_bytes) / fixed_elem_count) as avg_fixed,
    ->        AVG((var_elem_alloc_bytes - var_elem_free_bytes) / var_elem_count) as avg_var
    -> FROM ndbinfo.memory_per_fragment WHERE fq_name LIKE '%def/t6%';
+-------------+--------------+
| avg_fixed   | avg_var      |
+-------------+--------------+
| 32.14067706 | 281.18842440 |
+-------------+--------------+
# t7

mysql> SELECT AVG((fixed_elem_alloc_bytes - fixed_elem_free_bytes) / fixed_elem_count) as avg_fixed,
    ->        AVG((var_elem_alloc_bytes - var_elem_free_bytes) / var_elem_count) as avg_var
    -> FROM ndbinfo.memory_per_fragment WHERE fq_name LIKE '%def/t7%';
+-------------+---------------+
| avg_fixed   | avg_var       |
+-------------+---------------+
| 32.14067706 | 2080.81799665 |
+-------------+---------------+

 

Let’s calculate how much memory space are the KEY and BLOB columns occupying with 32768 rows inserted on each table (all values rounded up):

  • t5:
    • BLOB fixed: 0 bytes * 32768= 0, variable: 0 bytes * 32768 = 0
    • MAIN fixed: ~33 bytes * 32768 = 1081344 bytes ; variable = 2290 bytes * 32768 = 75038720 bytes
    • TOTAL fixed: 1081344 bytes ~= 1.03MB ; variable: 75038720 bytes ~= 71.56MB ; ALL ~= 72.59MB
  • t6:
    • BLOB fixed: ~37 bytes * 32768 = 1212416 bytes ; variable = 2020 bytes * 32768 = 66191360 bytes
    • MAIN fixed: ~33 bytes * 32768 = 1081344 bytes ; variable = 282 bytes * 32768 = 9240576 bytes
    • TOTAL fixed: 2293760 bytes ~= 2.18MB ; variable: 75431936 bytes ~= 71.93MB ; ALL ~= 74.12MB
  • t7:
    • BLOB fixed: ~37 bytes * 32768 = 1212416 bytes ; variable = 221 bytes * 32768 = 7241728 bytes
    • MAIN fixed: ~33 bytes * 32768 = 1081344 bytes ; variable = 2081 bytes * 32768 = 68190208 bytes
    • TOTAL fixed: 2293760 bytes ~= 2.18MB ; variable: 75431936 bytes ~= 71.93MB ; ALL ~= 74.12MB

 

With this, it can be observed that some memory space can be saved if the whole BLOB value can fit inline. But, if some part of data must be stored in the BLOB table, we incur some cost due to the necessary index and row metadata on the separate BLOB table. Therefore, t6 and t7 have the same memory requirements even with t7 configured with a larger inline size. However, we can save some amount of memory (~32 bytes per row) if a carefully chosen inline size is set, as well as some round-trips between MySQL server and NDB data nodes. Further tuning to reduce round-trips can be done with the options ndb-blob-read-batch-bytes and ndb-blob-write-batch-bytes (respectively ndb_replica_blob_write_batch_bytes for replica MySQL servers), and that will be the subject of Part 2.

Conclusion

In this post, some BLOB internals were introduced as well as a new feature to configure these from SQL. Additionally, the memory footprint of a BLOB column data was explained with a small evaluation over different configured sizes. It was observed that if the BLOB column takes an important role in the application, then this feature is a suitable candidate to consider. As a recommendation, consider the storage requirements of your application before defining your DDL statements. Hopefully, BLOB inline size configuration can help you tune your tables to be more conservative.

On Part 2 a performance evaluation will be done, using the new feature just introduced and some MySQL options, to see how can the application be more performant with BLOB columns whilst being conservative.

This feature is available since MySQL NDB Cluster 8.0.30, which can be downloaded here https://dev.mysql.com/downloads/cluster/