ERROR: Insufficient resources to execute plan on pool...

Moderator: NorbertKrupa

Post Reply
User avatar
Josh
Intermediate
Intermediate
Posts: 106
Joined: Thu Jan 26, 2012 9:38 pm

ERROR: Insufficient resources to execute plan on pool...

Post by Josh » Thu Jan 26, 2012 9:57 pm

Hi,

I keep getting errors when I try and execute a query. For instance: ERROR: Insufficient resources to execute plan on pool general [Request exceeds session memory cap: 2537432KB > 972800KB].

What does this error mean and how do I get rid of it?

Thanks
Thank you!
Joshua

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1817
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ERROR: Insufficient resources to execute plan on pool..

Post by JimKnicely » Fri Jan 27, 2012 4:55 am

This error indicates that the user executing the query has a memory cap setting that is insufficient for this query. The query will need about 2.5GB and the user has a max memory cap of 1GB.

You can check the user's memory cap (MEMORYCAP) setting by issuing the SHOW ALL command in vSQL.

Code: Select all

dbadmin=> show all;
            name             |                      setting
-----------------------------+---------------------------------------------------
 locale                      | en_US@collation=binary (LEN_KBINARY)
 standard_conforming_strings | on
 escape_string_warning       | on
 datestyle                   | ISO, MDY
 intervalstyle               | plain
 timezone                    | US/Eastern
 search_path                 | "$user", public, v_catalog, v_monitor, v_internal
 transaction_isolation       | READ COMMITTED
 transaction_read_only       | false
 resource_pool               | general
 memorycap                   | UNLIMITED
 tempspacecap                | UNLIMITED
 runtimecap                  | UNLIMITED
 enabled roles               | dbadmin, pseudosuperuser
 available roles             | dbadmin, pseudosuperuser
In my case I have unlimited resources. The DBA can change the resource cap for your user with the ALTER USER command.

Example:

Code: Select all

ALTER USER xxx MEMORYCAP '2.5G';
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1817
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ERROR: Insufficient resources to execute plan on pool..

Post by JimKnicely » Wed May 02, 2012 9:39 pm

Update on my previous post... it seems that you have to use integer values when defining the MEMORYCAP. The 2.5G I specified won't work:

Code: Select all

dbadmin=> ALTER USER mark MEMORYCAP '2.5G';
ROLLBACK:  '2.5G' is not a valid size description
dbadmin=> ALTER USER mark MEMORYCAP '2.6G';
ROLLBACK:  '2.6G' is not a valid size description
dbadmin=> ALTER USER mark MEMORYCAP '2G';
ALTER USER
dbadmin=> ALTER USER mark MEMORYCAP '3G';
ALTER USER
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

DataWhisperer
Newbie
Newbie
Posts: 16
Joined: Fri May 11, 2012 12:25 pm

Re: ERROR: Insufficient resources to execute plan on pool..

Post by DataWhisperer » Fri May 11, 2012 12:29 pm

This can also be caused from having too many delete vectors in your projections.

Code: Select all

dbadmin=> select node_name, schema_name, projection_name, sum( deleted_row_count ) num_deleted_row_count, sum( used_bytes ) num_used_bytes from delete_vectors group by 1, 2, 3 order by 1, 2, 3;
If the num_deleted_row_count is over 10% of your total table size, then set the ahm and purge your tables/projections. It can take time to purge, so be careful to do it during non-loading times, and lower usage, it will lock the projections/tables.

We purge after every ETL.

Post Reply

Return to “New to Vertica Database Development”