Monitoring NDBCluster Copying Alter Progress

MySQL NDB Cluster has great support for online (inplace) schema changes, but it is still sometimes necessary to perform an offline (copying) ALTER TABLE. These are relatively expensive to make as the entire table is copied into a new table which eventually replace the old table.

One example where a copying ALTER TABLE is required is when upgrading from MySQL NDB Cluster 7.2 or earlier to MySQL NDB Cluster 7.3 or later. The format used for temporal columns changed between these version (corresponding to MySQL Server 5.5 to 5.6). In order to take advantage of the new temporal format, a table rebuild is required.

Note

Support for the old temporal format has been removed in MySQL 8.0. So, you must upgrade your tables before an upgrade is possible. There is at the time of writing no MySQL NDB Cluster releases based on MySQL Server 8.0.

Schematic representation of a copying ALTER TABLE

For long running operations, it can be useful to monitor the progress. There is no built-in way to do this like there is for InnoDB in MySQL 5.7 and later (see the blog InnoDB Progress Information), however the ndbinfo schema can give some information about the progress.

The ndbinfo schema is a virtual schema with views that show information from the data nodes. You can argue it is MySQL NDB Cluster's answer to the Performance Schema. The ndbinfo schema was introduced in MySQL NDB Cluster 7.1 more than eight years ago and has steadily seen more and more information becoming available.

One of these changes arrived in MySQL NDB Cluster 7.4 where the memory_per_fragment view was added. This view shows detailed information about the memory used per fragment (in most cases the same as partitions). This can also be used to get an estimate of the progress of a copying ALTER TABLE.

As mentioned, a copying ALTER TABLE is similar to creating a new table with the new schema (which may potential be the same as the old schema), then inserting all of the data from the old table to the new. At the end, the two tables are swapped and the old table dropped.

Important

Remember that a copying ALTER TABLE is an offline operation. Any changes made to the table during the operation may be lost! Make sure the table is read-only while the ALTER TABLE is executing.

The temporary table (that later become the real table) is an NDBCluster table like other user created tables. This means the table will show up in ndbinfo.memory_per_fragment as a normal table, just with a special table name.

Temporary tables are named like #sql-7f4b_4 where the part after the – is generated based on the operating system process ID of the mysqld process and the connection id of the connection executing the ALTER TABLE. The schema name for the temporary table is the same as for the original table. In the example the process ID is 32587 or 7f4b in hexadecimal notation and the connection ID is 4.

As an example consider a rebuild of the db1.t1 table. In this case the fully qualified name (the name used by NDB Cluster instead of the normal table name) is db1/def/t1, i.e. the schema name and table name with /def/ between them. You can choose to create the fully qualified name for the temporary table as described above. An alternative, if you just have one concurrent table rebuild in the schema is to just look for the fully qualified name matching db1/def/#sql-%.

So, you can use the ndbinfo.memory_per_fragment table to see how much memory is allocated per fragment of the temporary table compared to the original table. For example:

mysql> SELECT fq_name, parent_fq_name, type, table_id,
              (fixed_elem_alloc_bytes-fixed_elem_free_bytes) AS FixedBytes,
              (var_elem_alloc_bytes-var_elem_free_bytes) AS VarBytes,
              hash_index_alloc_bytes
         FROM ndbinfo.memory_per_fragment
        WHERE fq_name = 'db1/def/t1' OR fq_name LIKE 'db1/def/#sql-%'
              OR parent_fq_name = 'db1/def/t1' OR parent_fq_name LIKE 'db1/def/#sql-%';
+------------------------+---------------------+-------------------+----------+------------+----------+------------------------+
| fq_name                | parent_fq_name      | type              | table_id | FixedBytes | VarBytes | hash_index_alloc_bytes |
+------------------------+---------------------+-------------------+----------+------------+----------+------------------------+
| db1/def/NDB$BLOB_45_3  | db1/def/t1          | User table        |       46 |     100580 |  1038088 |                  40960 |
| db1/def/NDB$BLOB_45_3  | db1/def/t1          | User table        |       46 |      99320 |  1056380 |                  40960 |
| db1/def/NDB$BLOB_45_3  | db1/def/t1          | User table        |       46 |     100580 |  1038088 |                  40960 |
| db1/def/NDB$BLOB_45_3  | db1/def/t1          | User table        |       46 |      99320 |  1056380 |                  40960 |
| sys/def/45/val1$unique | db1/def/t1          | Unique hash index |       49 |      77640 |        0 |                  40960 |
| sys/def/45/val1$unique | db1/def/t1          | Unique hash index |       49 |      76184 |        0 |                  40960 |
| sys/def/45/val1$unique | db1/def/t1          | Unique hash index |       49 |      77640 |        0 |                  40960 |
| sys/def/45/val1$unique | db1/def/t1          | Unique hash index |       49 |      76184 |        0 |                  40960 |
| sys/def/45/val1        | db1/def/t1          | Ordered index     |       48 |      39424 |        0 |                      0 |
| sys/def/45/val1        | db1/def/t1          | Ordered index     |       48 |      37792 |        0 |                      0 |
| sys/def/45/val1        | db1/def/t1          | Ordered index     |       48 |      39424 |        0 |                      0 |
| sys/def/45/val1        | db1/def/t1          | Ordered index     |       48 |      37792 |        0 |                      0 |
| sys/def/45/PRIMARY     | db1/def/t1          | Ordered index     |       47 |      39424 |        0 |                      0 |
| sys/def/45/PRIMARY     | db1/def/t1          | Ordered index     |       47 |      37792 |        0 |                      0 |
| sys/def/45/PRIMARY     | db1/def/t1          | Ordered index     |       47 |      39424 |        0 |                      0 |
| sys/def/45/PRIMARY     | db1/def/t1          | Ordered index     |       47 |      37792 |        0 |                      0 |
| db1/def/NDB$BLOB_14_3  | db1/def/#sql-7f4b_4 | User table        |       15 |      43180 |   446148 |                  24576 |
| db1/def/NDB$BLOB_14_3  | db1/def/#sql-7f4b_4 | User table        |       15 |      44404 |   471920 |                  24576 |
| db1/def/NDB$BLOB_14_3  | db1/def/#sql-7f4b_4 | User table        |       15 |      43360 |   450184 |                  24576 |
| db1/def/NDB$BLOB_14_3  | db1/def/#sql-7f4b_4 | User table        |       15 |      44404 |   471920 |                  24576 |
| sys/def/14/val1$unique | db1/def/#sql-7f4b_4 | Unique hash index |       44 |      33448 |        0 |                  24576 |
| sys/def/14/val1$unique | db1/def/#sql-7f4b_4 | Unique hash index |       44 |      34176 |        0 |                  24576 |
| sys/def/14/val1$unique | db1/def/#sql-7f4b_4 | Unique hash index |       44 |      33532 |        0 |                  24576 |
| sys/def/14/val1$unique | db1/def/#sql-7f4b_4 | Unique hash index |       44 |      34176 |        0 |                  24576 |
| sys/def/14/PRIMARY     | db1/def/#sql-7f4b_4 | Ordered index     |       42 |      15904 |        0 |                      0 |
| sys/def/14/PRIMARY     | db1/def/#sql-7f4b_4 | Ordered index     |       42 |      16992 |        0 |                      0 |
| sys/def/14/PRIMARY     | db1/def/#sql-7f4b_4 | Ordered index     |       42 |      15904 |        0 |                      0 |
| sys/def/14/PRIMARY     | db1/def/#sql-7f4b_4 | Ordered index     |       42 |      16992 |        0 |                      0 |
| sys/def/14/val1        | db1/def/#sql-7f4b_4 | Ordered index     |       43 |      15904 |        0 |                      0 |
| sys/def/14/val1        | db1/def/#sql-7f4b_4 | Ordered index     |       43 |      16992 |        0 |                      0 |
| sys/def/14/val1        | db1/def/#sql-7f4b_4 | Ordered index     |       43 |      15904 |        0 |                      0 |
| sys/def/14/val1        | db1/def/#sql-7f4b_4 | Ordered index     |       43 |      16992 |        0 |                      0 |
| db1/def/t1             | NULL                | User table        |       45 |     110792 |   775260 |                  40960 |
| db1/def/t1             | NULL                | User table        |       45 |     108712 |   760568 |                  40960 |
| db1/def/t1             | NULL                | User table        |       45 |     110792 |   775260 |                  40960 |
| db1/def/t1             | NULL                | User table        |       45 |     108712 |   760568 |                  40960 |
| db1/def/#sql-7f4b_4    | NULL                | User table        |       14 |      47536 |   332412 |                  24576 |
| db1/def/#sql-7f4b_4    | NULL                | User table        |       14 |      48656 |   340252 |                  24576 |
| db1/def/#sql-7f4b_4    | NULL                | User table        |       14 |      47696 |   333532 |                  24576 |
| db1/def/#sql-7f4b_4    | NULL                | User table        |       14 |      48656 |   340252 |                  24576 |
+------------------------+---------------------+-------------------+----------+------------+----------+------------------------+
40 rows in set (0.86 sec)

The columns with information about the node ID, block instance, and fragment number have been left out. This is why it looks like there are duplicate rows. It is also worth noticing that there are several “child tables” for the indexes and a blob column.

There are three memory columns. The first is for the fixed size column format, the second for the variable width columns format, and the last for hash indexes.

MySQL NDB Cluster supports two storage formats for the columns. The fixed format uses less memory for columns that are fixed width in nature (such as integers), however variable (called DYNAMIC in CREATE TABLE and ALTER TABLE statements) is more flexible. The variable/dynamic column format is also the only one supported when adding a column inplace (online). See also the manual page for CREATE TABLE for more information about the column format.

The hash memory is the memory used by hash indexes (for the primary key and unique indexes).

For the fixed and variable element memory usages there is both allocated and free bytes. Here the free bytes is used as a measure of the amount of fragmentation. A copying ALTER TABLE defragments the table, so it is necessary to the the fragmentation into consideration when estimating the progress. In reality it is more complicated than the query suggest, so the memory values in the query result will not end up matching 100%, however in most cases it should be a reasonable estimate.

You can also choose to aggregate the memory, for example:

mysql> SELECT IF(fq_name LIKE 'db1/def/%'
                    AND fq_name NOT LIKE 'db1/def/NDB$BLOB%',
                 fq_name,
                 parent_fq_name
              ) AS FqName,
              sys.format_bytes(
                 SUM(fixed_elem_alloc_bytes-fixed_elem_free_bytes)
              ) AS FixedBytes,
              sys.format_bytes(
                 SUM(var_elem_alloc_bytes-var_elem_free_bytes)
              ) AS VarBytes,
              sys.format_bytes(
                 SUM(hash_index_alloc_bytes)
              ) AS HashBytes
         FROM ndbinfo.memory_per_fragment
        WHERE fq_name = 'db1/def/t1' OR fq_name LIKE 'db1/def/#sql-%'
              OR parent_fq_name = 'db1/def/t1' OR parent_fq_name LIKE 'db1/def/#sql-%'
        GROUP BY FqName;
+---------------------+------------+----------+------------+
| FqName              | FixedBytes | VarBytes | HashBytes  |
+---------------------+------------+----------+------------+
| db1/def/#sql-7f4b_4 | 629.20 KiB | 3.08 MiB | 288.00 KiB |
| db1/def/t1          | 1.39 MiB   | 6.92 MiB | 480.00 KiB |
+---------------------+------------+----------+------------+
2 rows in set (0.69 sec)

This aggregate query also uses the sys schema function format_bytes() to convert the number of bytes into human readable numbers. The sys schema is installed by default for MySQL NDB Cluster 7.5 and later and is available from MySQL's repository on GitHub for MySQL NDB Cluster 7.3 and 7.4.

This way of estimating the progress of a copying ALTER TABLE is not perfect, but at least it can give an idea of how the operation progresses.

Monitoring NDBCluster Copying Alter Progress

Related