Skip to content

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#

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-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 30 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 30 seconds.

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-nologging#

Setting this option to 1 means that any table created will use the NOLOGGING feature explained in the chapter on table options.

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

--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#

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 enabled before a copying ALTER TABLE can be performed.

--ndb-use-copying-alter-table#

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

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 always stay that way. The alternative is to use a full table scan to get the number of rows. This is less exact than the default method, thus there are no benefits in changing this option.

--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 auto increments will use steps of 512 between rows inserted in using an auto increment column.

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 should be set much higher, setting it to 1024 will make it fairly safe that the autoincrement column will not cause scaling issues.

The default setting will for sure cause scalability settings with many parallel inserts into the autoincrement table.

The maximum it can be set to is 65536.

--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.

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-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 --log-bin must be set to the basename of the binlog files. By default it is OFF. In addition the --ndb-log-bin must also be set (is ON by default).

In addition the --ndb-log-bin-index should always be set when setting up a MySQL replication server for NDB. 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-eventbuffer-max-alloc and --ndbeventbuffer-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%.

--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.

It is required to set it to OFF also when using the conflict detection functions.

--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-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 it ensures that updates to the ndb_log_apply_status table is replicated to the replica cluster.

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-replica-conflict-role#

See the chapter on RonDB replication for a description of this. It is only related to replication with conflict detection functions.

--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#

It is possible to display information about the NDB version, the ndbinfo version and the NDB version in text format by using the following options --ndb-version, --ndbinfo-version, and --ndb-version-string.

--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.