Modern analytic databases such as Vertica often need to process a myriad of workloads ranging from the simplest primary-key lookup to complex analytical queries that include dozens of large tables and joins between them. Different types of load jobs (such as batch type ETL jobs and near real-time trickle loads) keep the data up-to-date in an enterprise data warehouse (EDW). Therefore, an enterprise class database like Vertica must have a robust yet easy-to-use mixed-workload management capability.
The Resource Manager
Vertica manages complex workloads using the Resource Manager. With the Resource Manager, you manage resource pools, which are pre-defined subsets of system resources with an associated queue. Vertica is preconfigured with a set of built-in resource pools that allocate resources to different request types. The General pool allows for a certain concurrency level based on the RAM and cores in the machines.
Vertica provides a sophisticated resource management scheme that allows diverse, concurrent workloads to run efficiently on the database. For basic operations, the built-in general pool is usually sufficient. However, you can customize this pool to handle specific workload requirements.
In more complex situations, you can also define new resource pools that can be configured to limit memory usage, concurrency, and query priority. You can even optionally restrict each database user to use a specific resource pool to control memory consumption of their requests.
Understanding and Classifying Workloads
Before you start thinking about resource pools and workload optimization in Vertica, you must first develop a solid understanding of the customer’s workloads and know how to properly classify them.
What should you use for classification criterion? You could pick apart a complex query, studying its structure in detail, counting and adding up the number of tables, joins and aggregate functions, number and types of derived tables and analytical functions to come up with some weighted score. However this kind of approach is extremely tedious and subjective and, as a result is not a practical option.
What if we use the standalone runtime of a query as the criterion? This method is also problematic because a query that runs in one minute while using up 80% of a systems resources should obviously not be in the same category as another query that runs in the same amount of time (one minute) but uses < 0.1% of the resources.
In Vertica, the best proxy for query complexity is its memory usage. Being a modern MPP columnar database, Vertica is rarely, if ever, I/O bound. Vertica is also less likely to hit the CPU bottleneck because of the tremendous power and speed of modern multi-core CPUs. Therefore, the most common resource bottleneck in a production Vertica cluster running a complex mixed-workload is often memory. Because of this, the Vertica Resource Manager focuses on establishing equitable memory allocation among different workloads or pools. This ensures that no resource pool is starved out of memory in the worst-case scenario—under full system load.
Determining Memory Requirements for a Query
If you can somehow determine quickly how much memory a query requires per node, then you can use that value to classify an Vertica query (or any other job). Based on extensive real-world performance tuning experience gained from working with some of Vertica’s biggest and most demanding customers, I have found the following classification rules to be very easy to use and effective:
Large: >800 MB
How can you quickly determine a query’s memory requirement? It turns out that Vertica has a convenient profiling option (similar to EXPLAIN.) You can use the PROFILE statement to get the total memory required for the query (among other things). As a best practice, you should set up a small and dedicated profiling pool for this purpose, as shown in the following example:
CREATE RESOURCE POOL p_pool MEMORYSIZE '1K' PRIORITY 80 PLANNEDCONCURRENCY 4 MAXCONCURRENCY 4 EXECUTIONPARALLELISM 16;
Creating a specific profiling pool forces your query to borrow from the default general pool for any extra memory that it needs to execute. If you use the general pool (a common mistake), depending on the detailed pool settings, the reserved memory may be more than a query actually needs. Vertica could be fooled to report on reserved memory as opposed to the actual allocated/used memory under certain circumstances and this would skew your result.
For more information on the Resource Manager, see Managing Worloads in the Vertica Documentation set.
Po Hong is a senior pre-sales engineer in Vertica’s Corporate Systems Engineering (CSE) group with a broad range of experience in various relational databases such as Vertica, Neoview, Teradata and Oracle.
About the Author
Po Hong
