Vertica Tip: Predicting the Resources a Statement Needs

Posted February 5, 2018 by Soniya Shah, Information Developer

High angle view of Beijing Guomao.
This blog post was authored by Eugenia Moreno.

You may find you want to set up resource pools before running queries to know how many resources a particular query needs. One way to do this is to create a small resource pool, profile the query, and note when the query is rejected. When the query is rejected, Vertica prints the resources that the query needs.

Let’s take a look at an example.

First, you must create the resource pool: dbadmin=> create resource pool test memorysize '1M' MAXMEMORYSIZE '1M' ; CREATE RESOURCE POOL Then, assign the resource pool: dbadmin=> set resource_pool=test; SET Next, profile the query to determine how many resources it needs. The queries will be rejected to determine this. In each query, we see the number of threads, file handles, and memory requested versus the amount that is free. These areas are highlighted in each code snippet.

The following is an example of a query with a join: dbadmin=> profile select i.*, d.* from inventory_fact i, date_dimension d where i.date_key = d.date_key; NOTICE 4788: Statement is being profiled HINT: Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273877623 and statement_id=1; NOTICE 3557: Initiator memory for query: [on pool test: 94169 KB, minimum: 94169 KB] ERROR 3587: Insufficient resources to execute plan on pool test [Request Too Large:Threads Exceeded: Requested = 18, Free = 0 (Limit = 0, Used = 0); File Handles Exceeded: Requested = 64, Free = 8 (Limit = 8, Used = 0); Memory(KB) Exceeded: Requested = 94169, Free = 1024 (Limit = 1024, Used = 0)] The following is an example of a query using GROUP BY: dbadmin=> profile select count(*) from date_dimension ; NOTICE 4788: Statement is being profiled HINT: Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273877623 and statement_id=2; NOTICE 3557: Initiator memory for query: [on pool test: 19881 KB, minimum: 19881 KB] ERROR 3587: Insufficient resources to execute plan on pool test [Request Too Large:Threads Exceeded: Requested = 10, Free = 0 (Limit = 0, Used = 0); Memory(KB) Exceeded: Requested = 19881, Free = 1024 (Limit = 1024, Used = 0)] The following is an example of a SELECT statement that requests a large number of rows: dbadmin=> profile select * from inventory_fact ; NOTICE 4788: Statement is being profiled HINT: Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273877623 and statement_id=3; NOTICE 3557: Initiator memory for query: [on pool test: 25126 KB, minimum: 25126 KB] ERROR 3587: Insufficient resources to execute plan on pool test [Request Too Large:Threads Exceeded: Requested = 10, Free = 0 (Limit = 0, Used = 0); File Handles Exceeded: Requested = 15, Free = 8 (Limit = 8, Used = 0); Memory(KB) Exceeded: Requested = 25126, Free = 1024 (Limit = 1024, Used = 0)] The following is an example of a query that uses the export command: dbadmin=> profile export to parquet ( directory = '/tmp/dimension') as select * from store.store_dimension limit 20; NOTICE 4788: Statement is being profiled HINT: Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273877623 and statement_id=5; NOTICE 3557: Initiator memory for query: [on pool test: 72927 KB, minimum: 72927 KB] ERROR 3587: Insufficient resources to execute plan on pool test [Request Too Large:Threads Exceeded: Requested = 10, Free = 0 (Limit = 0, Used = 0); File Handles Exceeded: Requested = 241, Free = 8 (Limit = 8, Used = 0); Memory(KB) Exceeded: Requested = 72927, Free = 1024 (Limit = 1024, Used = 0)] As you can see, different statements that have different operators and columns need different resource amounts.