Setting the Max Memory Available by Query: Quick Tip

Jim Knicely authored this tip. 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 Have fun!