Mysql – Using all the CPU cores by NDBD and MySQL

MySQLndbcluster

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.

Best Answer

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 !!!

Related Question