Search code examples
singlestore

singlestore ( formerly memsql) Too many queries are queued for execution, exceeding the limit of 100

I am using singleStore with 4 pretty strong leaf node machine but keep getting this error when serving a lot of traffic

enter image description here

this is the current resource pool config

[
  {
    "Pool_Name": "default_pool",
    "Memory_Percentage": 100,
    "Query_Timeout": null,
    "Max_Concurrency": null,
    "Soft_CPU_Limit_Percentage": 100,
    "Max_Queue_Depth": null
  }
]

this Is the current workload management status

[
  {
    "Stat": "QueuedQueries",
    "Value": 0
  },
  {
    "Stat": "Running Queries (from local queue)",
    "Value": 0
  },
  {
    "Stat": "Running Queries (from global queue)",
    "Value": 0
  },
  {
    "Stat": "Running Memory (MB) On Leaves (from local queue)",
    "Value": 0
  },
  {
    "Stat": "Running Memory (MB) On Leaves (from global queue)",
    "Value": 0
  },
  {
    "Stat": "Running Threads Per Leaf (from local queue)",
    "Value": 0
  },
  {
    "Stat": "Running Connections Per Leaf (from local queue)",
    "Value": 0
  },
  {
    "Stat": "Memory Threshold (MB) to Queue Locally",
    "Value": 561
  },
  {
    "Stat": "Memory Threshold (MB) to Queue Globally",
    "Value": 28082
  },
  {
    "Stat": "Connections Threshold to Queue Globally",
    "Value": 2500
  }
]

"Error: UNKNOWN_CODE_PLEASE_REPORT: Too many queries are queued for execution, exceeding the limit of 100. You may try running the query again later. Consider reducing cluster load.The limit can be configured with the workload_management_max_queue_depth variable or max_queue_depth property of resource pool.

"Error: UNKNOWN_CODE_PLEASE_REPORT: Too many queries are queued for execution, exceeding the limit of 100. You may try running the query again later.
Consider reducing cluster load.The limit can be configured with the workload_management_max_queue_depth variable or max_queue_depth property of resource pool.
at Query.Sequence._packetToError(/usr/local/platform - js/node_modules/mysql/lib/protocol/sequences/Sequence.js: 47: 14)
at Query.ErrorPacket(/usr/local/platform - js/node_modules/mysql/lib/protocol/sequences/Query.js: 79: 18)
at Protocol._parsePacket(/usr/local/platform - js/node_modules/mysql/lib/protocol/Protocol.js: 291: 23)
at Parser._parsePacket(/usr/local/platform - js/node_modules/mysql/lib/protocol/Parser.js: 433: 10)
at Parser.write(/usr/local/platform - js/node_modules/mysql/lib/protocol/Parser.js: 43: 10)
at Protocol.write(/usr/local/platform - js/node_modules/mysql/lib/protocol/Protocol.js: 38: 16)
at Socket.< anonymous > (/usr/local/platform - js/node_modules/mysql/lib/Connection.js: 88: 28)
at Socket.< anonymous > (/usr/local/platform - js/node_modules/mysql/lib/Connection.js: 526: 10)
at Socket.emit(events.js: 315: 20)
at Socket.EventEmitter.emit(domain.js: 467: 12)
at addChunk(internal/streams/readable.js: 309: 12)
at readableAddChunk(internal/streams/readable.js: 284: 9)
at Socket.Readable.push(internal/streams/readable.js: 223: 10)
at TCP.onStreamRead(internal/stream_base_commons.js: 188: 23)
at TCP.callbackTrampoline(internal/async_hooks.js: 131: 14)
--------------------
    at Protocol._enqueue(/usr/local/platform - js/node_modules/mysql/lib/protocol/Protocol.js: 144: 48)
at PoolConnection.query(/usr/local/platform - js/node_modules/mysql/lib/Connection.js: 198: 25)
at/usr/local/platform - js/node_modules /@mobile-demand/memsql/dist/index.js: 112: 32
at Ping.onOperationComplete(/usr/local/platform - js/node_modules/mysql/lib/Pool.js: 110: 5)
at Ping.< anonymous > (/usr/local/platform - js/node_modules/mysql/lib/Connection.js: 526: 10)
at Ping._callback(/usr/local/platform - js/node_modules/mysql/lib/Connection.js: 488: 16)
at Ping.Sequence.end(/usr/local/platform - js/node_modules/mysql/lib/protocol/sequences/Sequence.js: 83: 24)
at Ping.Sequence.OkPacket(/usr/local/platform - js/node_modules/mysql/lib/protocol/sequences/Sequence.js: 92: 8)
at Protocol._parsePacket(/usr/local/platform - js/node_modules/mysql/lib/protocol/Protocol.js: 291: 23)
at Parser._parsePacket(/usr/local/platform - js/node_modules/mysql/lib/protocol/Parser.js: 433: 10)
at Parser.write(/usr/local/platform - js/node_modules/mysql/lib/protocol/Parser.js: 43: 10)
at Protocol.write(/usr/local/platform - js/node_modules/mysql/lib/protocol/Protocol.js: 38: 16)
at Socket.< anonymous > (/usr/local/platform - js/node_modules/mysql/lib/Connection.js: 88: 28)
at Socket.< anonymous > (/usr/local/platform - js/node_modules/mysql/lib/Connection.js: 526: 10)
at Socket.emit(events.js: 315: 20)
at Socket.EventEmitter.emit(domain.js: 467: 12)"

my question is what is the best practice to configure the resource pool?

Solution

  • On SingleStore DB, you can CREATE RESOURCE POOL for new queries in addition to Setting Resource Limits both on a Cluster and/or user level.

    SHOW RESOURCE POOL let's you see available resource pools from your SQL Editor.

    ALTER RESOURCE POOL let's you make changes to a specific resource pool.

    Resource pools can be adjusted on a per user basis with the ALTER USER command.