Following my previous two posts on concurrency, I’d like to explain why “too much” concurrency always hurts database performance (in any database), and discuss a couple of common database features that were designed to manage it, including an example from Oracle and Greenplum.
What do I mean by “too much” concurrency? Let’s play with an example. Let’s assume a small, simple database system where each SQL is processed by a single-threaded, CPU-bound process (maybe it is an in-memory database or all the relevant data is cached). Let’s further assume that each SQL takes 10 seconds to execute, and that the system can only efficiently execute four parallel SQLs. So, if we fire up four SQLs at a time every 10 seconds, we will get a throughput of 24 queries/minutes and average response time of 10 seconds. So far, life is good.
But what happens if we fire up 24 queries simultaneously once a minute? Let’s assume no interference between the SQLs and a fair scheduler that cycles between the processes many times per second. In that case, we will still get 24 queries per minute, but all queries will finish about 59-60 seconds, so the average response time will be almost 60 seconds – or six times slower with the same throughput. So, scheduling too many SQLs at once just drove response time through the roof without improving throughput!
Another way that “too much” concurrency hurts performance is that it forces the database resources to be split over a lot of SQLs, leading to inefficient processing. For example, running a SQL usually requires the database to allocate some working memory to the process(es) that execute a SQL. The memory is especially important for steps that sort data or build a transient in-memory hash tables (for joins or aggregations). When there are many active SQLs that require working memory, each will get a smaller piece of the memory (or else the system will start swapping). So, a SQL that could typically join or sort in a single memory pass might start spilling temporary results to disk during high concurrency, dramatically affecting its run time and its resource usage (extra I/O to write and later read temporary data). So, another effect of “too much” concurrency is potentially making each SQL work a lot harder (reducing overall throughput).
So, how can databases manage this? One possible way is to add more cores to the system so it could handle more concurrent sessions. This is is mostly relevant only for sequential OLTP / “short-circuit” processing. In a data warehouse context, when using a parallel (MPP) database, each SQL is parallelized across the entire cluster, utilizing as many cores and disks as possible to minimize its run time. This methodology actually reduces concurrency, as I noted before. So, adding more nodes will improve query run time and reduce the concurrency of your workload, but will not help handling peaks of more simultaneous SQLs.
So, what are the alternatives? The first common tool is Query Prioritization. Typically, sessions are divided into groups by function (ETL, online users, background reports, data mining jobs etc). Each group is given a different priority. So, for example, if online users get higher priority, they can have more or less the same response time even if there is a peak of background activity. While most vendors offer some sort of in-database query prioritization, I should note that Greenplum recently bought MoreVRP to add a sophisticated, dynamic, rule-based prioritization engine that takes query prioritization to the next level. This technology deserve its own post one day…
The second tool to handle concurrency is Query Queuing. The idea is simple – instead of starting too many SQLs, the database delays the beginning of the SQL execution until there are enough resources available. This helps handling both the negative latency effect and the spilling-to-disk effect. Let’s investigate a couple of implementation examples.
Oracle introduced statement queuing in version 11g Release 2 (and later enhanced it in 22.214.171.124). However, in their case, it is bundled with a bunch of other new parallelism features (automatic DOP and in-memory parallel execution), so it is unfortunately more complex than necessary. In Oracle, the system-wide number of parallel process slaves is fixed and the engine tries to automatically find the optimal per-SQL parallelism based on the current system load before each execution. The DBA controls various parameters (globally and per resource group) to try to tame the beast.
In Greenplum Database there is a different model. The degree of per-SQL parallelism is fixed. The administrator simply chooses how many active SQLs are allowed per resource queue (group) – if more SQLs are submitted to a queue, they will wait until a slot is available. The administrator can also specify a minimal cost threshold (per resource queue) – to allow quick queries to bypass the queuing mechanism (and of course prioritize between queues).
So, to sum it up, “too much” concurrency does hurt database performance. Luckily, it can be handled by proper setup in many modern databases – using statement prioritization and statement queuing.