SQL Nodes

SQL nodes can either be MySQL servers that connect to the clusterwhich most areor other cluster-specific binaries, such as the binaries described in Chapter3, "Backup and Recovery".

Parameters to Define for All SQL Nodes (MYSQLD_DEFAULT)

We mention the following parameters for reference. It is unlikely that you are going to want to change them from the defaults. In addition, these only apply to SQL nodes that are MySQL servers connecting to the clusternot to other binaries that may connect to the cluster.

ArbitrationRank

See the parameter with the same name in the section "Parameters to Define for All Management Nodes (NDB_MGMD_DEFAULT)." This parameter is identical to that one except that its default value is 2 (low priority).

ArbitrationDelay

Setting the ArbitrationDelay parameter to any other value than 0 (the default) means that responses by the arbitrator to arbitration requests will be delayed by the stated number of milliseconds. It is usually not necessary to change this value.

BatchByteSize

For queries that are translated into full table scans or range scans on indexes, it is important for best performance to fetch records in sensibly sized batches. It is possible to set the proper size both in terms of number of records (BatchSize) and in terms of bytes (BatchByteSize). The actual batch size is limited by both parameters (that is, it makes the batch as large as it can to be below both parameters).

The speed at which queries are performed can vary by more than 40%, depending on how the BatchByteSize parameter is set. In future releases, the MySQL server will make educated guesses on how to set parameters relating to batch size, based on the query type.

This parameter is measured in bytes and by default is equal to 32KB. If you have very fast hardware and good networking between nodes, you might want to increase this.

BatchSize

The BatchSize parameter is measured in terms of the number of records and is by default set to 64. The maximum size is 992. If you have very fast hardware and good networking between nodes, you might want to increase this.

MaxScanBatchSize

The batch size is the size of each batch sent from each storage node. Most scans are performed in parallel on the storage nodes, so in order to protect the MySQL server from receiving too much data from many nodes in parallel, the MaxScanBatchSize parameter sets a limit on the total batch size over all nodes. If your SQL nodes are not overloaded, you might want to increase this value significantly.

The default value of this parameter is set to 256KB. Its maximum size is 16MB.

Parameters to Define for Each SQL Node

As with storage nodes, you can set all the parameters that we suggest you put in the DEFAULT section in each individual SQL node part of the configuration file. If you have different hardware for different SQL nodes, we suggest that you do this. However, if you have identical hardware for all SQL nodes, there seems little point.

This leaves the Id and Hostname parameters to set in each [NDBD] section. We suggest that at the bottom of the SQL nodes section of your config.ini file, you leave a few SQL nodes with no Hostname so that you can connect extra cluster-related daemons from any host within your network. (When giving this advice, we assume that you have taken earlier advice and that your cluster is on a private network so there is no chance of anyone evil connecting to your cluster.) You will probably find this useful later on.

A Final Note on SQL Nodes

You should remember that if you are going to attempt to recover a backup, you need one SQL node per storage node available for the ndb_restore command to connect to. If you have followed our advice and have a few spare [MYSQLD] sections free with no Hostname defined, you should be able to do this without any changes.

Remember that there is no problem at all with specifying two SQL nodes with the same IP address. The first node ID will go to the first one to connect, and so on (you can specify which node is which with connectstrings, but we do not really see the point in doing so).

You should by now be familiar enough with producing configuration files that we do not give an example of a storage node section of a configuration file here. There is, however, a full config.ini example in the next section, "Using Multiple Management Nodes."