MySQL Cluster Slow Queries
Hello,
First time poster here, so be gentle. I wonder if anyone has any advice regarding a situation I'm experiencing with MySQL Cluster 7.3.7. I'm using the cluster as the backend of a discussion forum. I just changed the architecture to what I thought was an improved and recommended set-up, but the results were discouraging. Please bare with me while I provide background:
The original setup was two servers, each running Apache, Tomcat, MySQL, and ndbd. I had to other computers set up as management nodes.
Even though we are advised to not combine things like this, the set-up worked fine for almost two years. But, it had started to slow down and I decided to separate the ndbd nodes. Therefore, I purchased two new servers and set things up like this:
Front end node 1: Apache, Tomcat, MySQL
Front end node 2: Apache, Tomcat, MySQL
Back end node 1: ndbmtd
Back end node 2: ndbmtd
All of these are connected by a copper gig switch. In this set up, the discussion forum app simply would not run. The queries were much too slow. Even very basic select statements. The front end nodes were not memory or cpu bound and had very light loads.
I then changed to this set-up:
Front end node 1: Apache, Tomcat
Front end node 2: Apache, Tomcat
Back end node 1: MySQL, ndbmtd
Back end node 2: MySQL, ndbmtd
This works very fast. To give a comparison, this query:
SELECT COUNT(1) FROM posts WHERE forum_id = 2
takes 129 seconds when run from MySQL on a front end node compared to 1.2 seconds from MySQL on a data node. That is an astronomical difference. I don't see any network-related explanation as all servers have their NICs configured correctly and there are good ping times between the servers. Anyway, Tomcat is now using the same connection to connect to MySQL with no problem.
Any idea what is going on here and what I can do about it?