Configuring MySQL Cluster Data Nodes

In my previous blog post, I discussed the enhanced performance and scalability delivered by extensions to the multi-threaded data nodes in MySQL Cluster 7.2. In this post, I’ll share best practices on the configuration of data nodes to achieve optimum performance on the latest generations of multi-core, multi-thread CPU designs.

Configuring the Data Nodes

The configuration of data node threads can be managed in two ways via the config.ini file:

- Simply set MaxNoOfExecutionThreads to the appropriate number of threads to be run in the data node, based on the number of threads presented by the processors used in the host or VM.

- Use the new ThreadConfig variable that enables users to configure both the number of each thread type to use and also which CPUs to bind them too.

The flexible configuration afforded by the multi-threaded data node enhancements means that it is possible to optimise data nodes to use anything from a single CPU/thread up to a 48 CPU/thread server. Co-locating the MySQL Server with a single data node can fully utilize servers with 64 – 80 CPU/threads. It is also possible to co-locate multiple data nodes per server, but this is now only required for very large servers with 4+ CPU sockets dense multi-core processors.

24 Threads and Beyond!

An example of how to make best use of a 24 CPU/thread server box is to configure the following:

- 8 ldm threads

- 4 tc threads

- 3 recv threads

- 3 send threads

- 1 rep thread for asynchronous replication.

Each of those threads should be bound to a CPU. It is possible to bind the main thread (schema management domain) and the IO threads to the same CPU in most installations.

In the configuration above, we have bound threads to 20 different CPUs. We should also protect these 20 CPUs from interrupts by using the IRQBALANCE_BANNED_CPUS configuration variable in /etc/sysconfig/irqbalance and setting it to 0x0FFFFF.

The reason for doing this is that MySQL Cluster generates a lot of interrupt and OS kernel processing, and so it is recommended to separate activity across CPUs to ensure conflicts with the MySQL Cluster threads are eliminated.

When booting a Linux kernel it is also possible to provide an option isolcpus=0-19 in grub.conf. The result is that the Linux scheduler won't use these CPUs for any task. Only by using CPU affinity syscalls can a process be made to run on those CPUs.

By using this approach, together with binding MySQL Cluster threads to specific CPUs and banning CPUs IRQ processing on these tasks, a very stable performance environment is created for a MySQL Cluster data node.

On a 32 CPU/Thread server:

- Increase the number of ldm threads to 12

- Increase tc threads to 6

- Provide 2 more CPUs for the OS and interrupts.

- The number of send and receive threads should, in most cases, still be sufficient.

On a 40 CPU/Thread server, increase ldm threads to 16, tc threads to 8 and increment send and receive threads to 4.

On a 48 CPU/Thread server it is possible to optimize further by using:

- 12 tc threads

- 2 more CPUs for the OS and interrupts

- Avoid using IO threads and main thread on same CPU

- Add 1 more receive thread.

Summary

As both this and the previous post seek to demonstrate, the multi-threaded data node extensions not only serve to increase performance of MySQL Cluster, they also enable users to achieve significantly improved levels of utilization from current and future generations of massively multi-core, multi-thread processor designs.

A big thanks to Mikael Ronstrom, Senior MySQL Architect at Oracle, for his work in developing these enhancements and best practices.

You can download MySQL Cluster 7.2 today and try out all of these enhancements. The Getting Started guides are an invaluable aid to quickly building a Proof of Concept

Don’t forget to check out the MySQL Cluster 7.2 New Features whitepaper to discover everything that is new in the latest GA release

Comments:

Great post!

I'm having one problem that you might be able to help me resolve. I'm using the latest release, 7.2.6. When I try to use more than 4 ldm threads, I get the following error (even when starting ndbmtd with --initial):

2012-06-05 16:37:36 [ndbd] INFO -- Trying to start 8 LQH workers with only 4 log parts, try initial node restart to be able to use more LQH workers.
2012-06-05 16:37:36 [ndbd] INFO -- DBLQH (Line: 1242) 0x00000006
2012-06-05 16:37:36 [ndbd] INFO -- Error handler shutting down system
2012-06-05 16:37:36 [ndbd] INFO -- Error handler shutdown completed - exiting
2012-06-05 16:37:36 [ndbd] ALERT -- Node 11: Forced node shutdown completed. Occured during startphase 0. Caused by error 2350: 'Invalid configuration received from Management Server(Configuration error). Permanent error, external action needed'.

My understanding is that prior to 7.2.5, the maximum number of log parts was 4. Is there a new configuration option that will allow me to specify the number of log parts, or is it supposed to be calculated automatically?

My use case for MySQL Cluster includes several tables that have many billions of records, and the ability to fully utilize my hardware is essential. Thanks!

Lucas

Posted by guest on June 05, 2012 at 08:45 PM GMT #

Hi Lucas
You should set NoFragmentLogParts to at least the value of LDM
threads. So in this case you need to set NoFragmentLogParts to 8.

We'll ensure the docs are updated to reflect this

Posted by Mat Keep on June 06, 2012 at 11:01 PM GMT #

Post a Comment:
Comments are closed for this entry.
About

Get the latest updates on products, technology, news, events, webcasts, customers and more.

Twitter


Facebook

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
5
6
9
10
11
12
13
15
16
17
18
19
20
23
24
25
26
27
28
29
30
   
       
Today