Configuring MySQL Cluster for High Performance

Now that you've learned how MySQL Cluster works, its key concepts, and scenarios in which it can be exploited to boost performance, it's time to review those settings and variables that can have the most impact on the speed at which your cluster operates.

This section begins by citing some clustering best practices that are independent of any other considerations. After exploring that topic, the chapter moves on to surveying the specific options for better networking, followed by how to configure performance-related transport options. Next, it proceeds on to considerations to help deploy a better management node before examining details about the data node. Finally, the section and chapter close with some ideas for the SQL node.

General Clustering Best Practices

No matter what clustering software version or topology you select, you can follow several best practices to get better performance from your MySQL cluster.

Employing Primary Keys

As you have seen throughout the book, a primary key makes it possible to find a record with a single read. Make sure that all of your tables have a primary key defined. If not, the NDB storage engine automatically creates one, but it's likely that this artificial key won't be meaningful for your application.

Grouping Insert Operations

If your cluster operations consist of many small data-altering transactions, the sheer amount of network traffic can bog down performance even if the network is fast. In cases in which you are inserting large amounts of data, try to batch the INSERT statements into big groups. If possible, try to use the LOAD DATA INFILE statement, which produces even better results.

Using Appropriate Indexes

Many portions of this book have cited the importance of indexes; clustering is no exception to this rule. In fact, it can be argued that the potential damage caused by a badly designed index strategy is multiplied by the number of nodes in a cluster. Remember to use the EXPLAIN command to get a better idea of how your queries are using indexes, if at all.

Spreading the Load Among SQL Nodes

Recall that from the perspective of the ultimate client (such as an application), there is no such thing as a cluster. Instead, the client simply connects to a MySQL server as always, and then issues requests against the database. In fact, the client is connecting to a node in the cluster the SQL node in this case.

The SQL node, in turn, employs the distributed processing capabilities of the cluster to access or alter data. Load balancing at this level is built in to MySQL Cluster. However, the same is not the case for clients connecting to the SQL node. In this situation, it's the responsibility of the administrator to employ load balancing, round-robin access, or other methods of spreading the work among multiple SQL nodes.

In the absence of a work distribution strategy, it's quite possible that a given SQL node might be overloaded with user requests while another SQL node sits idle. Fortunately, many open source and commercial solutions can solve this problem.

Protecting Your Data

In Chapter 13, "Improving Disk Speed," you saw how MySQL derives performance and availability benefits by leveraging any of the disk-based data-preserving technologies, such as RAID, mirroring, and so on. The same holds true for information stored in MySQL Cluster; even though data remains resident in memory, it is also periodically logged to disk during checkpoints. Any steps that you can take to increase the integrity of this disk storage will help improve your MySQL cluster's ability to survive a severe hardware failure.

MySQL Cluster also offers backup and restore; it is well worth your time to learn how to use these utilities to help safeguard your information.

Network Considerations

Because internode communications are so vital in a well-tuned MySQL Cluster, this section examines this important topic.

Running on a Dedicated Subnet

Don't let other, nonclustered computers clog your cluster communication network with irrelevant chatter. Instead, connect all nodes in your MySQL cluster onto their own, private subnet. Only the SQL node needs to be directly accessible by cluster clients from your broader network; all communication among cluster nodes should be shielded from interference.

Using Numeric IP Addresses

You are free to use traditional names for all of the hosts that run the nodes that make up your MySQL cluster, but why force an unnecessary DNS lookup on these hosts? Instead, consider referencing these nodes by their numeric IP addresses. If you must use names, at least create entries in the /etc/hosts file on each host to provide a faster way for the host to translate between the name and its associated IP address.

Being Security Conscious

Earlier in this chapter, you learned that when communicating among themselves, nodes do not consult the standard MySQL permissions. In addition, all traffic among these hosts is sent in the clear: There is no encryption for this type of communication.

Both of these facts mean that a MySQL Cluster administrator has extra security responsibility, especially as it relates to network access because that traffic can be easily intercepted and managed.

Transports

Recall from earlier in this chapter that the transport is the actual communication protocol used by all the nodes in the cluster to converse. This section looks at each of the transports to explore performance-specific settings. It pays particular attention to how SCI sockets can make a huge difference in communication speed.

TCP/IP

Even though this is the most commonly used protocol for clustering and the Internet at large, surprisingly, few configuration settings are available. For the book's purposes, two come to mind:

• SendBufferMemory If set too low, there is a risk that the TCP/IP buffer can overfill and fail. The primary potential consequence of setting it too high is memory wastage.

• Checksum By switching this parameter from its default of 0 (disabled) to 1 (enabled), MySQL computes a checksum for these internode messages, which adds overhead to the communication process. Generally, it can be left alone at its default (disabled) value, especially if all internode communication is happening on a closed, nearby subnet.

Shared Memory

Shared memory is a transport option for those computers that are hosting multiple nodes. As you saw earlier, running several nodes on one machine partially defeats the purpose of clustering. However, if you decide to follow this approach, you can control two key performance-related settings:

• ShmSize This parameter controls how much shared memory is allocated to each connection's shared memory segment. If set too low, there is a risk that this buffer might not have enough space to service the connection's request, whereas a too-high setting wastes memory. The default value of 1MB should suffice in most cases.

• Checksum By switching this parameter from its default of 0 (disabled) to 1 (enabled), MySQL computes a checksum for these in-memory messages. Given that in-memory corruption is an extremely rare event, it likely can be left alone at its default (disabled) value.

SCI Sockets

At the beginning of this chapter, you saw how the new SCI technologies can dramatically improve communication among nodes. You also read how this transport requires specialized hardware and considerable additional configuration compared to standard TCP/IP.

With that said, you can control two parameters to help this transport run as efficiently as possible:

• Checksum If you are concerned about potential message corruption, change this parameter from its default of 0 (disabled) to 1 (enabled). If enabled, MySQL computes a checksum for all messages sent between nodes using this protocol. However, the extra processing cost is probably not worth the derived benefits; LAN-resident message corruption is typically a very rare event.

• SharedBufferSize With a default value of 1MB, this parameter is used by MySQL to create a memory-based area for internode communication. Unless you are really pressed for memory, it's probably wisest to leave this setting untouched. On the other hand, if your processing environment sees substantial amounts of concurrent data insertion, and you have available RAM, consider slowly raising this amount for extra memory benefits.

Management Node Considerations

This section examines optimal configuration of your management node, along with ways to monitor its status.

Logging

Because all data modifications are logged, and disk access is typically one of the most severe performance impediments, it stands to reason that anything you can do to improve the speed at which logging operates should reflect in overall cluster response.

One key logging parameter is the LogDestination setting, which tunes a variety of aspects of this capability. Try using the FILE option and setting it to the fastest available file system.

Arbitration

When something goes wrong (such as one or more nodes dying) in a MySQL cluster, arbitration refers to the decisions made by a predesignated node that instruct the other nodes how to proceed. Two important parameters affect arbitration; they each have a peripheral impact on performance.

The first, ArbitrationRank, tells MySQL whether this node may never act as an arbitrator (value of 0); whether this node is a high-priority arbitrator (value of 1); or whether this node is a "last-choice" arbitrator (value of 2). Management nodes should have a setting of 1; optionally, you may elect to grant a SQL node a setting of 2, but this should be done sparingly and only for redundancy's sake. Otherwise, SQL nodes should have a value of 0.

The second, ArbitrationDelay, tells MySQL to wait a specified number of milliseconds before responding to an arbitration request. There is little reason to change this value from its default of 0 (that is, instantaneous response).

SQL Node Considerations

As the node that interfaces with client applications, it's important that your SQL node work as efficiently as possible. This section examines how to configure the relatively few number of SQL node parameters to realize the best performance.

Arbitration

As you saw in the previous section that discussed the management node, the ArbitrationRank and ArbitrationDelay settings control the location and behavior of potential arbitrators for the cluster. As stated earlier, your best bet is to set ArbitrationRank to 0, thereby leaving the SQL nodes out of the arbitration loop. Only when you absolutely need an additional potential arbitrator should you set it to 1 or 2.

Batching

In certain query conditions such as index range scans or table scans, MySQL attempts to optimize data access by retrieving large chunks of information from a given table. For these situations, MySQL Cluster provides administrators with tools to configure the size of these chunks (also known as batches).

The BatchSize setting spells out the number of records per batch. With a range zero to 992, there is a lot of room for you to experiment with different settings. Here is yet another example of how understanding the MySQL optimizer's output can help an administrator do a better job of configuring their server. The BatchByteSize setting provides an alternative method (that is, actual size in bytes) of tuning the batch buffer.

Finally, the MaxScanBatchSize value acts as a throttle to prevent all nodes from flooding the network with batched results from a particular query. You have great leeway in tuning this setting; potential values range from zero to the default of 256KB all the way up to 16MB. If your environment sees lots of table or index range scans, and your clustering network is private and offers ample bandwidth, consider raising this value to take advantage of this speed. Otherwise, leave it at a relatively low number if network capacity is a concern.

Data Node Considerations

Now that you have seen how to configure the management and SQL nodes, it's time to look at setting up and tuning the most complex component in a MySQL cluster: the data node. For consistency's sake, try to employ servers with equivalent processing, memory, and storage capabilities as data nodes: It's not a good idea to have wildly divergent computers serving as data nodes. In addition, keep these settings alike on all nodes.

In terms of providing sufficient memory for clustering, note that each data node needs enough capacity to hold the database size times the number of replicas, divided by the number of data nodes.

After your data nodes are in place, you can then shape your settings to meet your site-specific needs. The balance of this chapter reviews a number of these variables, paying particular attention to those that directly affect performance. For clarity, they are organized by major functional area.

Capacity

MySQL Cluster administrators have numerous settings at their disposal to specify the operational and metadata capacities of their environment. Some of the most important parameters include the following:

• MaxNoOfConcurrentScans

• MaxNoOfLocalScans

• BatchSizePerLocalScan

• MaxNoOfAttributes

• MaxNoOfTables

• MaxNoOfIndexes

• MaxNoOfOrderedIndexes

• MaxNoOfUniqueHashIndexes

• MaxNoOfTriggers

If any of these variables are set too low, there is a risk of resource contention. For this reason, it's wise to leave them at their defaults unless you are sure that an alteration makes sense in your environment.

Timing

Timing plays a vital role in keeping all nodes in a cluster in sync; numerous settings are available for administrators to tune their MySQL Cluster's site-specific behavior. In most cases, however, the default values are sufficient. This is true for many of the "heartbeat" and timeout settings, such as:

• ArbitrationTimeout

• TimeBetweenWatchDogCheck

• HeartBeatIntervalDbDb

• HeartBeatIntervalDbApi

• transactionInactiveTimeout

• TRansactionDeadlockDetectionTimeout

• TimeBetweenInactiveTransactionAbortCheck

• TimeBetweenLocalCheckpoints

• TimeBetweenGlobalCheckpoints

The same can be said for those parameters that help throttle the number of data or index pages written to disk during checkpoints or startup, including the following:

• NoOfDiskPagesToDiskAfterRestartTUP

• NoOfDiskPagesToDiskAfterRestartACC

• NoOfDiskPagesToDiskDuringRestartTUP

• NoOfDiskPagesToDiskDuringRestartACC

One parameter that can definitely benefit from alteration is StartFailureTimeout. This setting places an upper limit on how long MySQL Cluster waits for a data node to start before giving up. The default value of 60,000 milliseconds (that is, 60 seconds) is probably insufficient for a large site: It can take significantly longer for a data node to complete the startup operation. You might need to dramatically raise this value to meet your needs.

Memory

By storing all data within memory, MySQL Cluster is able to deliver substantially faster performance than a more traditional, disk-based database architecture. This section reviews those memory settings that can affect performance. As you saw earlier, the correct settings for these values are highly dependent on your own unique processing profile.

One initial decision you can make is to instruct MySQL Cluster not to register any data alterations to disk by enabling the Diskless setting. This in-memory clustering reduces overhead, but exposes you to complete data loss should your server go down unexpectedly.

During Chapter 10's discussion of general database settings, you saw how MySQL administrators may elect to force mysqld to remain in memory, generally immune to the swapping that other processes face. The same holds true on the data nodes: The LockPagesInMainMemory setting (disabled by default) instructs the operating system not to swap the data node process to disk. If you are confident that you have enough memory so that other processes won't unduly suffer, consider enabling this parameter.

The DataMemory and IndexMemory each control how much memory is allocated for data and indexes, respectively. In addition to the typical storage requirements for this information, there is also overhead ranging from 16 bytes all the way up to more than 50 bytes, depending on whether the information contains data or index content. To arrive at the ideal setting for your environment takes some calculation; you can also use the time-tested trial-and-error approach to arrive at the correct value. It's probably better to slightly overallocate memory than to deprive MySQL Cluster of the space it needs.

Memory allocation also plays a role in efficient online backups. Administrators can set the BackupDataBufferSize, BackupLogBufferSize, BackupMemory, and BackupWriteSize variables. The defaults for all of these settings are probably fine; if you have memory to burn you might try raising them.

Transactions

Transactions make it possible for MySQL Cluster to offer enhanced data reliability and consistency across multiple nodes. This section discusses performance-related transaction settings. Most of these settings are best left alone, unless your processing profile features many parallel query or data alteration operations. Settings that conform to this rule are MaxNoOfFiredTriggers, MaxNoOfConcurrentIndexOperations, and transactionBufferMemory.

On the other hand, if your environment sees relatively few transactions and those transactions are relatively small, you can safely lower the MaxNoOfConcurrentOperations setting. If the opposite is true, consider raising its value. As usual, if you are unsure, just leave it at its default.

An environment with copious nodes and periodic very large transactions benefits from an increase to the MaxNoOfLocalOperations setting. Otherwise, it should be left untouched.

Finally, the MaxNoOfConcurrentTransactions default value of 4096 is generally sufficient. To determine if this is so for your site, estimate the total number of transactions likely to be active across all data nodes at any one time. Should all of the data nodes crash but one, the remaining node would need this setting to be greater than or equal to the new transaction workload.

Logging

Transactions are the mechanism that MySQL Cluster uses to keep information synchronized; logging is the process that transactions themselves use to ensure consistency.

Because log records are first buffered in memory and then written to disk, several parameters are available to determine the amount of memory available for this caching. These settings include UndoDataBuffer, UndoIndexBuffer, and RedoBuffer. Their default values will likely suffice in most environments; setting them too low triggers MySQL Cluster error states, whereas setting them too high wastes memory.

One vital logging-related parameter is NoOfFragmentLogFiles, which is used to allocate space for the node's REDO logs. Allocated in batches of 64MB, these logs are used to restore data to a consistent state should a transaction abort. If set too low, there is a strong possibility that MySQL Cluster will be forced to automatically abort transactions that alter data. If set too high, disk space is wasted. The default value of 8 translates to 512MB of REDO log storage. Here again, the dangers of a too-high setting are far outweighed by the risks of a too-low setting.