Configuring MySQL Cluster Data Nodes
- by Mat Keep
0
0
1
692
3948
Homework
32
9
4631
14.0
Normal
0
false
false
false
EN-US
JA
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:12.0pt;
font-family:Cambria;
mso-ascii-font-family:Cambria;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Cambria;
mso-hansi-theme-font:minor-latin;
mso-ansi-language:EN-US;}
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
