I thought about covering an issue that many people will come across when implementing MySQL Cluster if they are not aware of it. That is of bandwidth and latency in TCP/IP when implementing a transport over ethernet cabling.

The MySQL Cluster is always passing information between the different nodes that it contains. This is obvious for synchronisation and also for retrieval of data through the MySQL api nodes. When people first setup the cluster they often go for a default install on a higher speed network such as gigabit ethernet (GigE). This will probably work fine for very small data sets or initial testing data, but when the data becomes larger in size, the user realises very quickly that there is a lot of traffic passing through the network on the cluster.

So what can be done to help in reducing the load on the network, or in improving the performance of the cluster's network? Well, the first and most simplest step is to make sure that you have the new feature of engine-condition-pushdown turned on in the configuration. This can be done as a mysqld parameter (--engine-condition-pushdown) or as an entry for the configuration file.

Without this option, when a query is made on a node, all the data is transferred between the nodes without considering the where condition which could limit the number of rows dramatically. When the engine-condition-pushdown option is enabled, it allows the cluster to filter the data based on the where condition before passing the result set around the cluster. This means much less data is passing around the cluster and therefore less bandwidth is being used.

The second change to the cluster that can be implemented to improve an ethernet-based MySQL Cluster is to implement Jumbo frames for TCP/IP. Jumbo frames were introduced to accommodate for the higher speed capabilities in newer networks like gigabit ethernet. The existing default on Linux (and most systems) for ethernet is an MTU of 1500 - which was fine for earlier network speeds, but it under-utilises the ability of the gigabit networks.

So how do you enable these jumbo frames under Linux? The first step is to simply enlarge the size of the MTU being used on the interfaces. This can be done on the command line using:

shell#> ip link set eth0 mtu 9000

To make this a permanent setting under Linux, it will be dependent upon what distribution is being used. For a Red Hat system, it is defined under the sysconfig settings. In /etc/sysconfig/network-scripts/ifcfg-eth0 (or appropriate interface file), add the following line:

MTU=9000

Under Debian-based system, the configuration will be added to the /etc/network/interfaces file. Add the following line to the interfaces file:

MTU 9000

Note: There is no equal sign in the Debian configuration file.

You can also increase the number of transmit buffers to help performance. This can be done from the command line using:

shell#> ip link set eth0 txqueuelen 1000

When you get these setup you will notice an improvement in the response on your cluster that would otherwise have gone unnoticed. Remember if ethernet is still not fast enough, you can opt for other transport methods such as Dolphin SCI. *grin*