Using all the CPU cores by NDBD and MySQL

Question

We are running MariaDB which is a fork of MySQL with NDB cluster engine. The cluster includes one mysql node and two ndb data nodes. While benchmarking the cluster, we are trying to make a bottleneck of CPU and the mysql nodes has 8 cores in total. But mysqld process uses only one to two cores and does not utilizing more than 2 cores. We tried increasing threads and warehouses but that did not help in utilizing all the CPU cores. Is there a way to make this possible. What system variables should I focus on to increase???

Any thoughts or suggestions are appreciated.

Answer 1

Solution is rather simple: Run mysqld multiple times on the same DB Server !!!.

Assign each mysqld to run on a different port.

The OS will spread the multple mysqld processes across the cores.

UPDATE

You may have to jerry rig some port forwarding using mysql-proxy

Assuming 10.240.35.180 is the SQL node

• startup mysql on port 3307 in the SQL node
• startup mysql on port 3308 in the SQL node
• startup mysql on port 3309 in the SQL node
• startup mysql on port 3310 in the SQL node

In the mgnt node, set up 4 instances of mysql-proxy

• mysql-proxy 10.1.1.6 to 10.240.35.180 port 3307
• mysql-proxy 10.1.1.7 to 10.240.35.180 port 3308
• mysql-proxy 10.1.1.8 to 10.240.35.180 port 3309
• mysql-proxy 10.1.1.9 to 10.240.35.180 port 3310

Now place IPs 10.1.1.6 - 10.1.1.9 in config.ini and startup mysql cluster. This sounds like a very major stretch, but it's your only shot at getting mysqld running across multiple cores.

BTW : back on August 10, 2011, someone asked a similar question in ServerFault and gave a detailed explanantion from the MySQL Cluster Team.

Give it a Try !!!

• RolandoMySQLDBA: No. At present, only InnoDB (since 5.1.38) has parameters capable of engaging multicore activity : dba.stackexchange.com/a/5968/877
• lampscholar: Is there a way to utilize all cores with one mysqld process? Another item to discuss is as mysql node is a part of cluster. All the nodes are managed by the config.ini file in the NDBD management node, where we list out the mysql nodes under [mysqld] section for each node. The mysql cluster have system parameter "hostname=ip/hostname" but it does not accept a "ip:port". Also could not find a system variable to mention the mysql port. So if I run multiple mysqld servers in the same machine with same ip but different port how do I add this to the config.ini to make the cluster understand this

Answer 2

I don't know which version of MySQL Cluster is distributed with MariaDB, which may affect the answer.

Generally, to get more throughput from a MySQLD instance connected to a Cluster, we recommend using multiple cluster connections from the MySQLD instance. This is described in this blog : http://johanandersson.blogspot.com/2008/02/mysql-cluster-features-what-they-are.html#multiconnect

This feature is added in mysql cluster 6.3, so if MariaDB distribute that version then you should be able to use it.