System and method for scheduling and running interactive database queries with service level agreements in a multi-tenant processing system
The invention is directed to systems and methods for scheduling interactive database queries from multiple tenants onto distributed query processing clusters with service level agreements (SLAs). SLAs may be provided through a combination of estimation of resources per query followed by scheduling of that query onto a cluster if enough resources are available or triggering proactive autoscaling to spawn new clusters if they are not. In some embodiments systems may include a workflow manager; a resource estimator cluster; one or more execution clusters; and one or more metastores. A workflow manager may include an active node and a passive node configured to send a query to the resource estimator cluster and receive a resource estimate. A resource estimator cluster may be in communication with the workflow manager. One or more execution clusters may be scaled by the workflow manager as part of a schedule or autoscale based on workload.
1. A system for scheduling interactive database queries from multiple tenants onto a distributed query processing system, while providing service level agreements and assurances, the system comprising:
a workflow manager;
a resource estimator cluster;
one or more execution clusters providing a heartbeat comprising resource usage information of the one or more execution clusters to the workflow manager; and
one or more metastores;
the workflow manager in communication with the resource estimator cluster and the one or more execution clusters.
2. The system of claim 1 , wherein the one or more execution clusters are scaled by the workflow manager as part of a schedule or autoscaled based on workload.
3. The system of claim 1 , wherein the workflow manager is configured to send a query to the resource estimator cluster and receive a resource estimate.
4. The system of claim 1 , wherein the resource estimator cluster is in communication with the one or more metastores.
5. The system of claim 1 , wherein the workflow manager comprises an active node and a passive node.
6. The system of claim 1 , further comprising a data store of historical resource consumption, comprising query and resource history, and wherein the resource estimator cluster is in communication with the datastore of query and resource history.
7. The system of claim 1 , wherein the workflow manager is configured for interaction with a user.
8. A system for scheduling interactive database queries from multiple tenants onto a distributed query processing system, while providing service level agreements and assurances, the system comprising:
a workflow manager comprising an active node and a passive node, configured to send a query to a resource estimator cluster and receive a resource estimate;
the resource estimator cluster in communication with the workflow manager;
one or more execution clusters scaled by the workflow manager as part of a schedule or autoscaled based on workload, the one or more execution clusters providing a heartbeat comprising resource usage information of the one or more execution clusters to the workflow manager; and
one or more metastores.
9. The system of claim 8 , wherein the resource estimator cluster is in communication with the one or more metastores.
10. The system of claim 8 , further comprising a data store of historical resource consumption, comprising query and resource history, and wherein the resource estimator cluster is in communication with the datastore of query and resource history.
11. The system of claim 8 , wherein the workflow manager is configured for interaction with a user and provides to the user service level assurances.
12. A method of scheduling and handling interactive database queries with service level assurances (SLAs) in a multi-tenant system, the method comprising:
when there is not enough spare capacity in the multi-tenant system to schedule higher SLA queries, preempting one or more lower SLA queries to make space for and schedule the higher SLA queries;
delay scheduling the preempted lower SLA queries up to a predefined period of time, until a required capacity become available;
autoscaling the multi-tenant system, either through an addition of one or more nodes to an existing cluster, or by launching a new cluster based at least in part on a SLA level of a query, expiry of wait times for deferred queries, and/or existing spare capacity;
assigning a default SLA level and default resource quota to queries for which a resource usage may not be predicted, and retrying such queries for which a resource may not be predicted if they fail;
ensuring multi-tenancy and isolation by providing each user with access to the user's separate metastores using credentials;
maintaining spare capacity based on observations of resources to reduce wait time for scheduling of incoming queries; and
protecting against underestimation of resources required by a cost-based optimizer (CBO) avoiding scheduling queries on clusters which report high resource utilization.