Setting the Max Memory Available by Query
Jim_Knicely
- Select Field - Administrator
Vertica 9.1.1 introduces the new Resource Pool parameter MAXQUERYMEMORYSIZE. Its value represents the maximum amount of memory the pool can allocate at runtime to process a query. If the query requires more memory than this setting, Vertica stops execution and returns an error.
Example:
**dbadmin=> CREATE USER test;
CREATE USER
dbadmin=> CREATE RESOURCE POOL limited_by_query MAXQUERYMEMORYSIZE '1K';
CREATE RESOURCE POOL
dbadmin=> GRANT USAGE ON RESOURCE POOL limited_by_query TO test;
GRANT PRIVILEGE
dbadmin=> GRANT USAGE ON SCHEMA public TO test;
GRANT PRIVILEGE
dbadmin=> GRANT SELECT ON public.big_table TO test;
GRANT PRIVILEGE
dbadmin=> \c - test
You are now connected as user "test".
dbadmin=> SHOW resource_pool;
name | setting
---------------+------------------
resource_pool | limited_by_query
(1 row)
dbadmin=> SELECT * FROM public.big_table;
ERROR 8722: The minimal memory required by the query [9224 KB] exceeds the query cap size [1 KB]
HINT: Increase MAXQUERYMEMORYSIZE parameter of the 'limited_by_query' resource pool**
0