Capacity Limit Errors

ERROR 2374: Leaf node could not be added because you are using the SingleStore free license which has a limit of 4 license units and after adding the node you would be using XX license units.

Leaf Error (HOST:PORT): Unable to connect to leaf @HOST:PORT with user USER, using password YES/NO: [1040] Too many connections.

Issue

As memory is maxed out, queries may hit the maximum_memory limit on the node and be killed automatically. And this error message is recorded in the logs.

Solution

As the CPU maxes out, queries will start taking longer to complete as the kernel queues them (load average will increase above 1 per cpu).Tuning max_connection_threads will allow you to find the correct balance between the count of concurrently running queries, and resource utilization. As you increase the max threads per aggregator, monitor the resource usage (CPU, load average, memory) during a typical workload.

WARN: The ready queue has not decreased (currently <num> elements, <num> pops) for <num> seconds. This workload needs more threads.

Issue

Each query that runs on a cluster (including internal queries used by the nodes to communicate with each other) requires exactly one thread on an aggregator. This warning appears when the max_connection_threads limit is reached on an aggregator and the aggregator stops responding. The following is a brief description of each warning parameter:

• elements refers to the number of queued queries (queries which are waiting for execution threads)

• pops refers to the cumulative number of queries that have been scheduled. If the value of pops remains same, it means that the scheduler is not making progress because queries are still running.

• This workload needs more threads refers to the kernel threads dedicated to SingleStore, specifically to the value of max_connection_threads.

Solution

• Increase the value of max_connection_threads on aggregator nodes (maximum 12192). The default value for aggregators is 192. Increasing the value of max_connection_threads on aggregator nodes will allow more queries to run at the same time; however, this can increase resource pressure on the cluster.

  Note: The value on leaf nodes does not need to be changed from the default of 8192, which is the maximum value.

• The warning may also occur due to execution of BACKUP. If BACKUP is executed when there are long-running queries executing in the cluster, the backup operation will have to wait for the long-running queries to complete. During this time new queries executed on the cluster will also have to wait for the long-running query to finish. If this warning appears in close proximity to BACKUP entries in the master aggregator’s memsql.log, check to make sure backups are not being executed when long-running queries are present in the cluster.

Monitor Thread Utilization

Use the SHOW STATUS EXTENDED command to check the current number of running threads (threads_running) and number of connections in queue currently waiting (ready_queue). If a workload runs enough long-running concurrent queries that reaches the max_connection_threads threshold, and the queries are sufficiently long-running so that none have finished for over few seconds, the aggregator’s memsql.log file will have the WARN: This workload needs more threads. entry.