Setting the Max Memory Available by Query

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
edited August 21 in Vertica Tips

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**
Sign In or Register to comment.