Throttling of OQL queries

Since this RFC relates long query times to GC time, I was wondering if any testing has been done with newer garbage collectors such as ZGC. ZGC looks very  promising since the current version promises pause times of less than 10ms and future releases will have pause times of <1ms!

This is an interesting feature, if I understand it correctly, we want to be able to continue running queries but throttle them so that they don't take up a large amount of resources at one time.Is there a way to hook into the resource manager, and add a new threshold or two? Let's say a query throttle and query cancel threshold.
If the query throttle threshold is crossed, we could block query execution until the pressure goes below throttle threshold.

If the query cancel threshold is crossed, we could then cancel queries and reject them until pressure goes below cancel threshold?

Another option might be to limit the number of queries that can be executed in total across the system with some semaphore.

Nice!  Resource management in databases is an important and well-studied topic.  I think adding a capability like this to Geode would be a good addition.  However I think I would go in a slightly different direction.  In other database systems you have something like the following:

1. A query planner that can estimate of the cost of a query
2. A Resource Manager that knows the capacity of the system (cpu, memory, disk) and can arbitrate access to those resources
3. A query execution system that coordinates with the Resource Manager

I would like to see abstractions that introduce these ideas into the OQL engine.  Even we start with simplistic implementations it gives us a foundation for building more useful behaviors.

I would argue the injection of "sleep" statements in the execution path is problematic for a few reasons:

1. Uncontended queries would be slowed down unnecessarily.
2. Tuning would be quite challenging as it depends on both load and capacity.  Values would be different for dev vs production.  And results on heterogeneous clusters would be quite variable.
3. Small changes to the query code path (such as a refactoring) could alter the throttling behavior in unpredictable ways.

Alberto Gomez

Thanks for bringing this topic. Managing/monitoring resource consumption by a query is a long debated and a hard issue to tackle in Data systems. With the data as a service becoming more and more prominent this issue has become critical in providing accepted SLAs for multi users/tenants of the SaaS system.

Small request, the RFC topic name needs to be changed to appropriately reflect the problem to solve  rather than throttling query.

While implementing query feature in Geode; first we try to look at how other databases have tried to address/implemented it; you may have already done that, thought of pointing it for general purpose.

Looking through how other databases have managing this; mostly I come across resource monitoring articles/solution than any system providing resource allocation at query level; except this:

https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-ver15

Please take a look at it. 

As others have chimed in; it will be nice to have a well thought solution for this issue that could be implemented in steps; including resource monitor service, allocation service.

The Geode query service provides Query Monitor capability; which currently supports:

• Manage long running queries
• Low memory situation (hooked into resource manager)

Adding some hooks to get wait/notify with memory availability (resource manager) may be a way to address this (than using sleep) and expand in the future for other resource constraints.

As you can see this is a hard issue to tackle; considering query prioritization and other...And many times this issues is specific to certain use-cases/scenarios. Other solutions adopted to tackle these are: 

• Separate out the resource hungry queries and run them on dedicated systems/clusters (with replicated data)
• Run it during non critical system time (if they are not critical)

If you are looking to control a query without any product changes; geode provides query observer hook, which can be used to intercept a query...Currently this is internal, but you could try to see if this will help to manage resource utilization.  

Here's an example of a db engine that provides a three-level approach, see section 6 (page 16):

https://arxiv.org/pdf/2103.11080.pdf

It's not a perfect solution, but you can actually implement this sort of throttling without product changes using method invocation in the query as well.

I really wish OQL had a way to invoke static  methods, but you can invoke pretty much anything you want by passing an instance of a helper class as a query parameter.

Here's an example where you call a method in the WHERE clause that just returns true. It will get called for every entry, so you can count the number of invocations, measure time, do whatever you want in the throttle method.

cache.newQuery("select count(*) from /Portfolios p where ($1).throttle(2)")
.execute(new TimeBasedThrottle())

public static class TimeBasedThrottle implements Serializable {
  int count = 0;

  public boolean throttle(float ratio) {
    count++;
    // Do whatever throttling you want here based on count, elapsed time, etc.
  }
}

Alberto Gomez,

I cannot speak to the implementation of the existing query engine or even comment on the different approaches we could investigate on how to solve this problem. What I do know is:

1. The proposed solution you describing (the throttling) is not one that I want Geode to have by default (or at all if possible)
2. I think that we are looking to solve the problem in the wrong place
3. There is possibly a different approach to resolve the problem
4. There is no right solution

1.

To me the core behavior of the system is for it to complete an action as soon as it can, as quickly as it can. Whereas the proposed behavior is that we introduce a synthetic query processor limiter. Which goes against the "as fast as possible" mantra.

Can I request that whilst you investigate this feature, that the investigation includes more thought into HOW we make this pluggable. So that the core system can still perform each query "as fast as possible", but with a simple configuration change, the system loads the "throttled" behavior. 

Now, I'm not suggesting including branching logic in the code that follows the pattern of "if (throttling_enabled){} else{}". This would be completely counter productive, but rather something more modular. Like some of the work that had to be done to include Classloader isolation, where there had to be some extension points, some abstractions introduced, that allowed the system to load one implementation over another, without breaking the core system.

What the modular approach allowed for is: to easily trial/implement different approaches to reducing the impact that large queries have on the core system, by toggling a configuration. It also allows for the extendibility of Geode on case by case basis or specifically make it behave differently and specifically in a way that would solve the particular problem you are trying to resolve without having to be build into the core system.

2.

From the little I do understand of the query engine is that, there will be garbage generation regardless of the throttling. So, regardless of region type, there will always be a node the will have to take the query result(s), possibly aggregate all results from all nodes (in the case of partitioned regions), serialize it and send it. Which in its own right is memory and CPU intensive. The generated serialized result will take up memory and depending on JVM settings, either kept in ParNew memory and the GC cycle is "cheap", or be dropped into the old memory and then GC cycle becomes more expensive.

This is also describing the "good" case. Where these is no overflow/eviction involved. If you take the case where overflow is enabled, a query might actually cause the system to load a whole bunch of "cold" data into memory from disk. Possibly causing "hot" data to be overflowed to disk, to make space for the data loaded. This load/overflow cycle could then be a cause for another GC-cycle. Then you run the query, serialize and send data. (another possible GC-cycle) and then finally all the cold data will be overflowed to disk again, when the more "hot" data has to be loaded to be able to process new incoming data. (another GC-cycle). To make matters worse, all of this is running in real time and concurrently.

So, there are multiple places where garbage will be generated regardless of the query throttling.

3.

I think the problem that is trying to be solved, is not unique. Trying to shield the core system from large GC's caused by possibly large queries. In practice the simplest would be to have two clusters, uni-directional WAN between them. One being the main ingestion cluster, working as fast as it can in consume/process income events and the second being a query cluster. BOTH are tuned differently for the different workloads they would experience.

4.

Both Anthony Bakerand Anilkumar Gingade have provided possible solutions to the problem. The main flaw I see in all of them, is that the proposed solutions are both on top of systems that are written in languages where garbage collection is a manual event and memory usage has to be explicitly controlled. JAVA, for all its "good" - "you don't have to care about memory" approach is also the main hinderance here. As whatever we do, we have no control over how much memory is used and when the GC kicks in. i.e yes, we could impose a synthetic limit of memory used by each query or query engine, but how the JVM behaves on when/how GC cycles are activated, we in reality have no control over. The only thing we can do is:

• reduce when/how we generate garbage
• tune the JVM to handle short-lived data (more OLTP) or longer/query/analysis