Advanced Configuration of the MySQL Server

Now that we have covered everything except advanced thread configurations for the cluster configuration we will take a deeper look at what configurations that are useful when setting up a MySQL Server to use NDB as a storage engine.

Most of the configuration options to consider are RonDB specific, but I will mention a few general MySQL options as well.

Basic MySQL server options

Basic options are required whenever you set up a MySQL server independent of what storage engine you use. These options specify where to find the files, which port to use, where to place socket files, where to log errors, how many connections we will be able to handle in parallel and so forth. Most of those options can be set either at command line or in the MySQL server configuration file. I will not discuss details on exactly where they can be placed, this information is available in the MySQL manual.

I will not discuss any performance schema options. The performance schema have few advantages when using the MySQL server with NDB as storage engine.

--ndbcluster

To use the NDB storage engine with the MySQL server it is required to use the --ndbcluster option when starting the MySQL Server.

--datadir

--datadir specifies the data directory of the MySQL Server. This is where most files related to the MySQL Server will be stored. This includes error logs, data files, pid files and so forth.

In RonDB you will normally have many MySQL servers connected to the cluster. To make it easy to know which MySQL server you are examining it is a good idea to have the node id (or one of the node ids if there are multiple ones) as part of the directory name. This makes it easier to find your way about in the various logs produced by the nodes in the cluster.

--basedir

--basedir points to the installation directory of RonDB. It is used among other things to find the error messages for different languages.

--log-error

The --log-error option specifies that you want errors logged and where you want those errors to be written. A filename can be specified, the location of this file will be in the datadir. The default name of the error log is the hostname with .err as file extension.

--pid-file

A pid file contains the process id of the MySQL server process. This is a file located in the data directory. A filename is provided in the option.

--port

This parameter defaults to 3306. If another port is desired it should be provided in this option.

--socket

If the MySQL client is located on the same machine it can communicate to the MySQL server using Unix domain sockets. This is normally a bit faster compared to using a TCP/IP socket on the local machine. The default setting for this option is /tmp/mysql.sock. It can be set to point to a different socket file name in this option.

--bind-address

It is possible to specify the network interface that the MySQL server will accept connections from. This can be useful to avoid accepting connections from a public network and only accept connections from a specific private network.

One manner to specify the network interface is to provide the IP address of the desired network interface.

The default behaviour is to accept connections from any network interface.

Query logging

The MySQL server can set query logging on all queries, the log output is specified using the --log-output option and by setting --general-log all queries are logged and by setting --log-slow-queries one can set logs on only queries that are slower than a certain time.

--ndb-extra-logging

The NDB handler will log more or less dependent on the setting of the --ndb-extra-logging option. This defaults to 1. Setting it to 0 means that most logging is disabled, setting it to 20 means that all possible messages are printed in the error log.

Connection options

There are a few parameters that are important to configure properly when starting up a MySQL server to be part of a RonDB cluster. It is important to point the MySQL server to an RonDB management server (or a set of them) such that it can get the cluster configuration. There are parameter settings to ensure that we can decide how to handle cases where it takes time to connect to the cluster (it might be starting up for example).

It is important to decide how many API node slots this MySQL server will use and it is important to set the node ids of those API node slots.

--ndb-connectstring and --ndb-mgmd-host

This option points to the management server from where the cluster configuration is fetched. It is possible to specify several management servers in which case the configuration is fetched from any of those management servers.

The default setting for this parameter is localhost:1186. If only a hostname is provided the port is 1186 by default.

--ndb-mgmd-host is another name for --ndb-connectstring. Both set the same value.

--ndb-cluster-connection-pool

By default one cluster connection is used. In this case one can set the node id of the MySQL server using the option --ndb-nodeid. If more than one cluster connection it is still possible to set the node ids of those cluster connections (and recommended to do so in both cases). In this case one uses --ndb-cluster-connection-pool-nodeids to set the node ids.

This option specifies the number of cluster connections.

Normally one cluster connection scales to at least 8 CPUs and in many cases it can scale to around 16 CPUs. To scale to a higher number of CPUs for the MySQL server requires using more cluster connections.

In my experiments I have been able to scale the MySQL server to at least 32 CPUs using 4 cluster connections.

--ndb-nodeid

With one cluster connection this option can be used to set the node id of the MySQL Server. It is recommended that MySQL server have specific node ids that are set in the cluster configuration with a node id and a host name.

--ndb-cluster-connection-pool-nodeids

This option accepts a comma separated list of node ids when using multiple cluster connections.

--ndb-wait-connected

If a MySQL server is started it will first connect to the NDB management server and then it will perform various synchronisations with the existing cluster to ensure that the MySQL server knows about the tables that are present in the cluster and that it gets informed of any new tables added or tables being dropped or altered.

This parameter specifies the time that the MySQL server will wait for NDB to connect before it proceeds assuming that the NDB storage engine is unavailable. Defaults to 120 seconds.

--ndb-wait-setup

This option specifies the amount of time the MySQL server will wait for the NDB storage engine to perform its startup. Defaults to 120 seconds, 0 means that the MySQL server does not wait.

--ndb-tls-search-path and --ndb-mgm-tls

--ndb-tls-search-path gives the directories where the MySQL Server looks for its TLS private key and certificate. Defaults to $HOME/ndb-tls.

--ndb-mgm-tls sets whether the connection to the management server must use TLS. With relaxed, the default, a missing certificate or a management server that does not offer TLS gives a cleartext connection. With strict, any failure to set up TLS is an error.

Both options are described in TLS Between Cluster Nodes.

Defaults for table creation

Your MySQL server is up and running and you want to start creating the tables in your RonDB cluster. There is a number of options that have an effect on the tables that are created.

--ndb-read-backup

Before MySQL Cluster 7.5 NDB always read the primary replica. In 7.5 the option was introduced to create tables that can read any replica. See the chapter on table options for more descriptions of this.

By default the tables are created with the read backup feature.

A read backup table will have a slightly higher latency when updating the table to ensure that we can read our own updates.

--ndb-fully-replicated

If you are using NDB to build a cluster with read scaling, you want to connect up to hundreds of MySQL servers and many data nodes that all read the same data and still have the ability to update from anywhere such that all MySQL servers see your updates.

In this case it might make sense to make all tables use the fully replicated feature explained in the chapter on table options. In this case all data is replicated in all data nodes.

This option can be set to 1 will ensure that all tables are created as fully replicated tables.

--ndb-table-no-logging

Setting this option to 1 means that any table created will use the NOLOGGING feature explained in the chapter on table options. It is 0 by default and can also be set per session.

NOLOGGING tables will be restored as empty tables after a cluster crash.

--ndb-table-temporary

This option is OFF by default. Setting it to ON has the same effect as --ndb-table-no-logging: new tables are created without logging. The tables are not temporary tables. Use --ndb-table-no-logging instead.

--ndb-row-checksum

This option is 1 by default and can be set to 0 or 1. Tables created while it is 1 store a checksum in each row. The data nodes verify the checksum when they read the row. This detects rows that were damaged in memory, for example by a hardware fault, at some cost in performance. A row with a wrong checksum gives error 896, and with the data node parameter CrashOnCorruptedTuple at its default the data node stops.

--ndb-distribution

This option sets how a new table without a PARTITION BY clause is partitioned. It can be KEYHASH, the default, which gives PARTITION BY KEY(), or LINHASH, which gives PARTITION BY LINEAR KEY(). LINHASH should not be used, keep the default KEYHASH.

--ndb-default-column-format

By default column formats used are FIXED which means that they will use fixed size part of the row for fixed size columns and the variable sized part of the row for columns that are variable sized.

If set to DYNAMIC all columns will use the dynamic part of the row. Columns that are stored in the DYNAMIC section will use no memory to store a NULL value, but will use a bit more memory when not NULL.

All new fields added through an ALTER TABLE with algorithm set to inplace will be using the DYNAMIC part of the row.

Alter table options

There are options to provide control over ALTER TABLE as well.

--ndb-allow-copying-alter-table

This option is ON by default. If this option is set to 0 it means that copying ALTER TABLE isn’t allowed. It is recommended to set this option to 0 since copying ALTER TABLE should be avoided and not be used by mistake. Setting this to 0 means that it must be explicitly requested with ALGORITHM=COPY in the ALTER TABLE statement before a copying ALTER TABLE can be performed.

--ndb-use-copying-alter-table

This defaults to 0 and is recommended to stay that way.

Schema distribution options

A MySQL server that runs a DDL statement on a RonDB table distributes it to the other MySQL servers connected to the cluster, so that they update their data dictionaries. Tables can also be created or dropped in RonDB without a MySQL server, for example by ndb_restore or an NDB API program. The MySQL servers find such changes through a regular check.

--ndb-schema-dist-timeout

This option sets how many seconds a MySQL server waits for the other MySQL servers to complete a distributed DDL statement before it stops waiting and logs a timeout. It defaults to 120 seconds and can be set from 5 to 1200 seconds. It can only be set at startup.

--ndb-schema-dist-lock-wait-timeout

When a MySQL server applies a DDL statement that another MySQL server distributed, it may have to wait for metadata locks. This option sets how many seconds it waits for such a lock before it returns an error for that schema change. It defaults to 30 seconds and can be set from 0 to 1200 seconds. A short wait keeps the binlog thread, which applies the schema changes, from being blocked for long.

--ndb-schema-dist-upgrade-allowed

This option is ON by default. A MySQL server of a newer version may then upgrade the schema distribution tables mysql.ndb_schema and mysql.ndb_schema_result when it connects to the cluster. Setting it to OFF defers this upgrade until all MySQL servers connected to the cluster have been upgraded to the same version. It can only be set at startup.

--ndb-metadata-check and --ndb-metadata-check-interval

--ndb-metadata-check is ON by default. A background thread in the MySQL server then compares the tables, tablespaces and logfile groups in RonDB with those in its data dictionary every --ndb-metadata-check-interval seconds. Differences are synchronized: tables that exist only in RonDB are installed in the data dictionary, and tables that no longer exist in RonDB are removed from it.

--ndb-metadata-check-interval defaults to 60 seconds and can be set from 0 to 31536000 seconds (one year). Both options can be changed at runtime.

--ndb-metadata-sync

This option is OFF by default. Setting it to ON makes the MySQL server synchronize all differences between RonDB and its data dictionary at once, without regard to --ndb-metadata-check and --ndb-metadata-check-interval. The option is set back to OFF when the synchronization is complete.

Execution options

With some tables added into NDB, it is now time to start querying those tables. There is a fair amount of options that control execution behaviour.

--max-connections

The MySQL server caps the number of connections it will accept. By default it is set to 151 connections. In many applications it is desirable to use a lot more connections. In my benchmark runs I always set this variable to an appropriate value.

Important to consider if changing this parameter is that one also need to ensure that the process can open enough files, each socket uses a file descriptor and thus the OS limits must be set to accept the number of connections set here.

--max-prepared-stmt-count

The server caps the amount of prepared statements that a server will accept. It is set by default to 16382. A prepared statement is bound to a connection, so with many similar prepared statements the server can use more than 16382 prepared statements.

As an example running sysbench will prepare around 20 prepared statements per connection, running a benchmark with 1024 connections requires this parameter to be changed.

It is set a bit lower for security reasons. It cannot be set higher than 1048576.

--sort-buffer-size

The MySQL server limits the amount of memory a connection can use for sorting to 256 kBytes. To use more than that requires changing this configuration option.

--autocommit

autocommit is set to 1 by default. This means that every statement is a separate transaction and will be committed as part of the query execution. It is still possible to start a longer transaction using a BEGIN statement and end it with either COMMIT or ROLLBACK.

When this option is set to 0 every statement after a COMMIT or ROLLBACK will start a new transaction that have to be completed with a COMMIT or ROLLBACK.

There is not many reasons to change the default setting.

Meta data options

The MySQL server caches table metadata to speed up queries. Each connection uses one table object from the table open cache for each table it queries, in addition it uses a table objects from the table definition cache. To retrieve such objects there is a number of parallel hash tables to ensure that many connections can access these hash tables in parallel.

--table-open-cache

Each query needs one table object from the table open cache for each table it uses. This option sets the amount of table objects that can be in the cache before we start deleting table objects. There can be many table objects referring to the same table since every connection needs its own table objects.

The default is to allow for up to 2000 table objects. Increasing this also increases the number of file descriptors that the MySQL server uses.

--table-definition-cache

Each table object is read from an frm file normally stored in the datadir. The table definition cache specifies how many frm files are stored in memory such that we don’t have to open the frm file to get the meta data of the table.

The default value is calculated based on the setting of table open cache. If this is set to its default it will be set to 1400.

--table-open-cache-instances

This specifies how many hash tables are used to access table objects. It defaults to 16, this number makes the MySQL Server scale to a high number of CPUs. To improve scaling on very large servers one can increase this number, there is very little reason to decrease this number other than to save a bit of memory.

--memlock

By setting this option to 1 we ensure that the MySQL server locks all memory it uses, so it will fail allocating memory when there is no real memory available.

--lc-messages-dir

--lc-messages and --lc-messages-dir is used to set the error messages in a proper language. The default is english and a directory found through basedir.

--ndb-use-exact-count

--ndb-use-exact-count defaults to 0 and should normally stay that way. When it is set to 1, the MySQL server fetches the row count of a table from the data nodes each time the query optimizer asks for it, which costs a round trip to the data nodes. With the default the MySQL server uses a row count that it has cached.

--ndb-force-send

When a connection is ready to communicate with the data nodes it can either use force send or adaptive send. Force send is the default and works ok in most situations. In a highly concurrent environment the adaptive send might have some benefit, the difference between the two methods is usually very small, should only be changed after testing with your application.

--ndb-optimized-node-selection

Should never be changed, was introduced for backwards compatibility.

--ndb-data-node-neighbour

In most cases the MySQL server will be able to deduce which data node is the closest neighbour by comparing the hostname of the MySQL server and the hostname of the data nodes.

If they are running on the same machine and using different hostnames one can tell the MySQL server explicitly which data node is the closest neighbour.

The proximity of a data node will be used to select transaction coordinator when no specific hint has been provided.

If set it should be set to the node id of the data node which is the nearest neighbour.

--ndb-use-transactions

This option has a weird name. It is set by default to 1.

The meaning of setting it to 1 is that large insert statements will be split into several transactions. If set to 0 also large insert statements will be executed using normal transaction boundaries.

--transaction-allow-batching

When NDB discovers a large insert, delete or a large update, it will normally use batching. Batching here means that multiple row writes will be sent in parallel to the data node. This is the default setting and should normally be kept.

The only reason I can think of when it could be interesting to not set it is when it is required to avoid overloading the data nodes.

--ndb-batch-size

During large write operations we keep a memory buffer for retrieved values. By default this buffer is 32 kBytes in size. Increasing this can lead to a higher batch size for large write operations and thus higher throughput at the expense of using more memory per connection.

--ndb-autoincrement-prefetch-sz

The default value of this parameter is 512. This means that each MySQL server reserves 512 auto increment values at a time. Rows inserted through one MySQL server get the values of its range in order, with a step of auto_increment_increment (1 by default). The values are unique across the MySQL servers, but they are not consecutive between them.

Normally an autoincrement columns should use a BIGINT and thus have no problems that the autoincrement numbers have gaps and that they are not necessarily inserted in perfect order.

In this case this size can be set higher, setting it to 1024 will make it fairly safe that the autoincrement column will not cause scaling issues.

The maximum it can be set to is 65535.

--ndb-blob-read-batch-bytes and --ndb-blob-write-batch-bytes

These two options control the behaviour of accesses to BLOB tables. When this option is set to a non-zero value, the MySQL server will send reads and writes to the BLOB tables in batches of this size. It defaults to 64 kBytes.

By increasing this size we will perform more work in parallel when reading and updating BLOB tables. At the same time it increases the risk of overloading the cluster. The default setting is intended for a balanced setting.

--tmp-table-sizeand --max-heap-table-size

The maximum size of temporary tables created during query execution is limited by the minimum of these two options. Both are set to 16 MByte by default.

--ndb-deferred-constraints

Is not intended to be changed. It delays the check of constraints to the end of the transaction.

--ndbinfo-show-hidden

By default the MySQL client only shows the documented ndbinfo tables. The base tables are not documented since they can change from release to release. If one wants to also see the base tables one should set the option ndbinfo-show-hidden to 1.

--ndb-user-rate-limits

This option is OFF by default and can only be set at startup. When it is ON, each transaction that the MySQL server starts in RonDB carries the MySQL user name of the connection. The data nodes then apply the user rate limits set for that user, see Rate Limits and Quotas.

--ndb-ring-buffer-show-meta

This option is OFF by default and can also be set per session. A ring buffer table, created with MAX_ROWS_PER_PK, keeps one meta row, with ring index 0, for each primary key prefix. Reads do not return these rows. When this option is ON, SELECT statements also return the meta rows, which is useful when inspecting the table. Scans in UPDATE and DELETE statements still skip them. Ring buffer tables are available since RonDB 26.10.

Optimizer options

There are a number of options that directly impact the query optimisation.

--ndb-join-pushdown

Pushdown of joins is a key feature of NDB. It means that many complex queries can execute various row conditions in parallel. It uses a form of linked joins.

It is possible to set this option to not be used. This might be useful in some cases when testing have shown that a certain query is more efficient when executed using a different algorithm present in the MySQL server.

There should be no reason to reset it when starting the MySQL server.

--ndb-join-pushdown-aggregate

This option is OFF by default. When set to ON, a query with GROUP BY and the aggregate functions COUNT, SUM, MIN, MAX or AVG on a fully pushed join can have its aggregation performed in the data nodes. The data nodes then return one row per group instead of every joined row. ORDER BY is still handled by the MySQL server. Pushdown of aggregation is available from RonDB 26.10.

This option and the two below can also be set per session.

--ndb-join-pushdown-aggregate-outer-join

This option is OFF by default. When set to ON together with --ndb-join-pushdown-aggregate, aggregation is also pushed for pushed joins that contain outer joins.

--ndb-pushdown-aggregate

This option is OFF by default. When set to ON, the aggregation of a query with GROUP BY on a single table can be performed in the data nodes, using the same aggregate functions as --ndb-join-pushdown-aggregate.

--ndb-index-stat-enable

Index statistics is enabled by default for NDB. Enabling index statistics can at times provide major improvements to query execution time through a much better query plan.

--ndb-index-stat-option

There is a great number of options that can be influenced through this option. It is beyond the scope of this manual to go through all of those. The MySQL manual contains a list of the options and how to set them.

--ndb-optimization-delay

When executing OPTIMIZE TABLE we will have a delay between each batch of rows sent for optimisation. This delay defaults to 10 milliseconds.

--optimizer-switch

This variable is a massive switch that can be used to enable or disable 19 different algorithms.

--optimizer-trace

There is a set of options available to trace the workings of the optimizer. Many applications have problems in developing SQL queries that work well. The optimizer trace feature will give detailed information about what makes the query optimizer choose certain paths and avoid other paths.

During query development and query debugging this feature can be very useful. This option is only available in debug builds of the MySQL server.

Receive thread configuration

Each cluster connection have one receive thread. This thread or some execution thread that takes on the role of receiver executes all signals received from the data nodes. The behaviour of the receive logic is important to get optimal performance when running the MySQL server using NDB.

--ndb-recv-thread-activation-threshold

By default the user threads will take care of the receiver role of the cluster connection until at least 8 threads are active in waiting for response from the data node.

When more than 8 threads are waiting for response from the data nodes, the receive thread will take care of the receive role.

For the receive thread to use its own CPU requires that we have locked the MySQL server (and other processes running on the same machine) to other CPUs, thus ensuring that no other thread is using the CPU assigned to the receive thread.

To lock the MySQL server to certain CPUs I use the taskset program or the numactl program.

This parameter can change the number of threads we are waiting for a response from before we start using the receive thread.

--ndb-recv-thread-cpu-mask

By default this is not set and thus the receiver thread(s) can execute anywhere. When set, it should be a comma separated list of CPU ids. The number of CPUs in this list should be one for each cluster connection we use.

RonDB replication setup

There is a wide range of options to use for MySQL replication servers. Remember that in RonDB these are normally specialized MySQL servers used primarily to be replication sources or replication replicas.

Most of these options were explained in their context in the chapters on Global Replication.

--log-bin and --ndb-log-bin

In a replication source the binary log must be on. In the MySQL Server it is on by default, and --log-bin sets the basename of the binlog files (binlog by default). In addition --ndb-log-bin must be set to ON. It is OFF by default, and without it changes to RonDB tables are not written to the binary log.

In addition --ndb-log-binlog-index should be set when setting up a MySQL replication server for NDB. It fills the table mysql.ndb_binlog_index, which maps epochs to binlog positions. This is ON by default, no action is required for this to happen.

--server-id

Each MySQL replication server must have a unique server id.

--server-id-bits

This option defines how many bits of the 32 bit server id to actually use. It defaults to 32, but can be set as low as 7. The idea with this is to provide room for NDB API applications to use special NDB API options to insert changes into the binlog using special server ids.

--sync-binlog

By default the binlog is synched once per transaction. NDB writes one epoch as a transaction into the binlog, synching this is what we want. So sync-binlog should be set to 1 when replicating from a RonDB cluster. There is about 10 epochs per second generated, so there will be no performance impact from this.

--relay-log

When the MySQL replication server is executing as a replica it will use relay logs to store the binlog information retrieved from the primary cluster before the logs are applied to the backup cluster. This option can be used to set a non-standard name of the relay logs.

--replica-allow-batching

To achieve the desirable speed of the replica, this option should be set to 1. It ensures that the replica applier uses batching to improve the speed of applying the replication logs in the backup cluster.

--ndb-replica-batch-size

This option sets the batch size in bytes that the replica applier uses when it applies the replication logs. It defaults to 2 MBytes. As long as it is left at its default, the replica applier uses the larger of this value and --ndb-batch-size.

--ndb-replica-blob-write-batch-bytes

This option sets the size in bytes of the batches of BLOB writes in the replica applier, 0 means no limit. It defaults to 2 MBytes. As long as it is left at its default, the replica applier uses the larger of this value and --ndb-blob-write-batch-bytes.

--ndb-eventbuffer-max-alloc and --ndb-eventbuffer-free-percent

The --ndb-eventbuffer-max-alloc option can be used to set a maximum on the memory available to use for event buffers in the NDB API. By default there is no limit. If this option is set and we reach the maximum allocated size, the free percent option indicates how much memory must be free before we start receiving new events again. By default that is set to 20%, it can be set from 1% to 99%.

--ndb-report-thresh-binlog-epoch-slip

This option defaults to 10. It sets the number of epochs that the injector binlog thread will be behind before the MySQL server logs this lag to the error log.

The epochs are first received by the NDB API into a memory buffer. Next the injector thread injects them into the binlog. This reports when the injector thread cannot keep up with the NDB API.

--ndb-report-thresh-binlog-mem-usage

Sets the level when reports about memory usage are written in the error log.

--ndb-log-updated-only

By default this is set to ON. Thus only columns that are changed are logged and columns that are not changed are not part of the log records. This makes replication to other RonDB clusters more efficient.

The conflict detection functions NDB$OLD and the NDB$MAX functions need the full before and after image of the row. For them this option and --ndb-log-update-as-write are set to OFF, which is the same as binlog_type 7 in the ndb_replication table. NDB$EPOCH2 and NDB$EPOCH2_TRANS keep this option ON, with --ndb-log-update-as-write set to OFF and --ndb-log-update-minimal set to ON, which is the same as binlog_type 8.

--ndb-log-update-minimal

This option is used to minimize the amount of binlogging required when using conflict detection functions NDB$EPOCH2 and NDB$EPOCH2_TRANS. It should never be set in any other situation. It is OFF by default.

--ndb-log-update-as-write

By default RonDB logs updates as writes, this ensures that no before value image is required and thus higher efficiency. When replicating to another storage engine and when using any of the conflict detection functions this option should be set to OFF.

--log-replica-updates

In a circular setup where the replica cluster will replicate to yet another cluster (or another storage engine) we must enable the --log-replica-updates in the MySQL replication servers that act as binlog servers.

--ndb-log-orig

This option is off by default, it needs to be set to enable circular replication. It is necessary to know the origin cluster of the log records such that we can stop replicating when we are back at the originating cluster. It should be set on all binlog servers in the circular replication.

--ndb-log-empty-epochs

It is possible to log also empty epochs, by default this is disabled. It is not recommended to use this feature.

--ndb-log-transaction-compression and --ndb-log-transaction-compression-level-zstd

--ndb-log-transaction-compression is OFF by default. When set to ON the binlog server compresses the epoch transactions it writes into the binlog using ZSTD. The compression level is set with --ndb-log-transaction-compression-level-zstd, from 1 to 22 with 3 as default. Both options can be changed at runtime, a change applies from the next epoch transaction.

Setting --binlog-transaction-compression to ON when the MySQL server starts also turns on --ndb-log-transaction-compression. If the level is then left at 3, the level of --binlog-transaction-compression-level-zstd is used.

--ndb-log-cache-size

The binlog server collects each epoch transaction in a memory buffer before it writes it into the binlog. This option sets the size of that buffer in bytes. It defaults to 64 MBytes, the minimum is 4 kBytes, and it is rounded down to a multiple of 4 kBytes. It replaces --binlog-cache-size for RonDB changes. It can be changed at runtime, and a new value larger than --max-binlog-cache-size is then reduced to that size. See Internals of Global Replication.

--ndb-log-transaction-dependency

This option is OFF by default and can only be set at startup. When it is ON, the binlog server records in the binlog which epoch transactions depend on each other. A replica with several applier workers, set with --replica-parallel-workers, can then apply epoch transactions that do not depend on each other in parallel. See Internals of Global Replication.

--ndb-log-purge-rate

When a binlog file is purged, the MySQL server deletes the rows that refer to it in the table mysql.ndb_binlog_index. This option sets how many rows are deleted at a time, by default 8192.

--ndb-log-fail-terminate

This option is OFF by default. When set to ON, the MySQL server shuts down if it fails to set up binlogging of a table. This ensures that the binlog never misses changes to a table.

--ndb-clear-apply-status

By default the RESET REPLICA command will clear all entries from the ndb_apply_status table. By setting this option to OFF the table will not be cleared as the command RESET REPLICA is executed.

--ndb-log-apply-status

This option is OFF by default. When ON, the binlog server also writes into its binlog the changes that the replica appliers in its cluster make to the table mysql.ndb_apply_status. At runtime it can only be changed while --log-replica-updates is ON.

It is used in chain replication and circular replication to ensure that the replication chain can be discovered.

--ndb-log-transaction-id

This option is required to be set to ON when using the NDB_EPOCH2_TRANS method for conflict resolution. By default this option is OFF.

--ndb-log-exclusive-reads

This option can be used for the NDB_EPOCH2_TRANS method for conflict resolution. By default this option is OFF. If it is set the option --ndb-log-empty-update must also be set to ON.

--ndb-log-empty-update

This option is OFF by default. The binlog server then filters away updates that change no columns. --ndb-log-exclusive-reads needs it ON, since the reads it tracks are logged as such empty updates.

--ndb-applier-conflict-role

See the chapter on RonDB replication for a description of this. It is only related to replication with conflict detection functions. The older name --ndb-slave-conflict-role is deprecated.

--ndb-applier-allow-skip-epoch

This option is OFF by default. The replica applier then stops if it is asked to apply a new epoch while an earlier epoch has not been committed. When set to ON, it logs a warning and continues.

--ndb-show-foreign-key-mock-tables

Shows some mock tables that are used by NDB to handle the foreign_key_checks equal to 0. Adds a bit more descriptive logging in the error log.

Version information

The read-only system variables ndb_version, ndbinfo_version and ndb_version_string show the NDB version, the ndbinfo version and the NDB version in text format, for example with SELECT @@ndb_version_string. They cannot be set as options.

--core-file

If it is desirable to get a core file when the MySQL server crashes this option should be set.

MySQL Server Status variables

There are many different status variables available in the MySQL Server, both for the NDB storage engine and for general MySQL Server usage. We will not go through them at all in this book, we will instead refer to the MySQL manual that contains a description of each and every one of those status variables. They can be helpful in examining a MySQL server in production.