When a distributed join occurs, the leaves within the cluster must reshuffle data amongst themselves, which requires the leaves to connect to one another. If the leaves are not able to communicate with one another, and a distributed join is touching those leaves, the distributed query will not run successfully. Use the following steps to troubleshoot this scenario:
- Confirmed we were able to access memsql from one leaf to another. By doing this step, we know the network is OK.
- Run SHOW LEAVES; on an affected leaf in the cluster. The Opened_Connections columns should reveal what leaves the affected leaf has open connections with. Verify that leaf Y is not in this list.
- When leaves connect to each other they cache connection information (leaf-1 lives at IP x, leaf-2 lives at IP y, etc.). If the IPs of these leaves ever change the cache will not automatically update. The result is that if a leaf's IP changes and another leaf is trying to communicate with it, the other leaf will be trying to reach it at it's old address and be unsuccessful. We need to flush the DNS cache and connection pools on all affected nodes. You can do so by running the following:
memsql> FLUSH HOSTS;
memsql> FLUSH CONNECTION POOLS;
Flush hosts clears the DNS cache on the node (and must be performed on a per node basis; you can't just run from the Master Aggregator).
Flush connection pools shuts down all existing connections and closes idle pooled connections.
In step 1 we are able to connect manually from 1 leaf to another because doing so does not utilize the DNS cache on the leaf. The inter-leaf communication that takes place when a query containing a distributed join relies on the DNS cache on each leaf. If this cache is out of sync with reality, the distributed join will fail.