Hi
We are developing analytic platform on top of Vertica , one of the challenges we face is to be able to run our solution on development / QA environment which very small data set , the environment are mostly run on top VM’s ,which include limited resource like 4 core’s and 4G -8G ram .
We recently face many issues which are related to “java.sql.SQLException: [Vertica][VJDBC](3587) ERROR: Insufficient resources to execute plan on pool general [Request Too Large:Memory(KB) Exceeded: Requested = 3149852, Free = 2983279 (Limit = 2983279, Used = 0)]”
First think I do is to try to run in my test environment , a machine with 32G RAM and 24Core , I copy the table from the source ( Very small table - 1 records , some of the cases the table is empty ) and execution the query (not include any join – flat execution with aggregation) , I see that query successfully executed but allocate the same size as in the exception above.
I gather statistics on the table , I do not define any user define resources pool , so my query use the general pool , the general pool include the default setup values , setting plannedconcurrency to very small value (1 -10) cause extra memory allocation (more the 3G) , value bigger than 10 do not affect my query at all . I do found out that plannedconcurrency with high value have good impact for most of our query’s (less memory allocation ) , the common denominator of this queries are the fact that this query do not have ORDER BY clause inside the analytic function .
We have a cases that we have query’s with 60 and more columns on the select-list each column is a results of analytic function with ORDER BY , the underline table include no data and Vertica try to allocate 3G memory (profile command show it) in spite of the value of the plannedconcurrency is very high (100) .
This lead to the understating that the resource limit can’t be a tool for reducing my memory allocation for this type of query’s as Vertica ignore . I will expect Vertica to allocate the memory base on my resource limit and my data set , looks like this is not the case , and we are in problem and can’t use VM’s for our Dev environment .
I can send you the SQL and the table for internal testing if you want , but I need to understand how Memory being allocated by this type query’s and whet we can do ?
Part of my query select list :
select
MAX (moving_agg_query.cpu_idle_time) over (partition BY moving_agg_query.host_name order by cpu_idle_time) as cpu_idle_time,
MAX (moving_agg_query.cpu_user_mode_util) over (partition BY moving_agg_query.host_name order by cpu_user_mode_util) as cpu_user_mode_util,
MAX (moving_agg_query.mem_pageout_rate) over (partition BY moving_agg_query.host_name order by mem_pageout_rate) as mem_pageout_rate,
MAX (moving_agg_query.process_started) over (partition BY moving_agg_query.host_name order by process_started) as process_started,
MAX (moving_agg_query.mem_util) over (partition BY moving_agg_query.host_name order by mem_util) as mem_util,
MAX (moving_agg_query.disk_write_byte_rate) over (partition BY moving_agg_query.host_name order by disk_write_byte_rate) as disk_write_byte_rate,
