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
ERROR: Insufficient resources to execute plan on pool...
Moderator: NorbertKrupa
ERROR: Insufficient resources to execute plan on pool...
Thank you!
Joshua
Joshua
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: ERROR: Insufficient resources to execute plan on pool..
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.
In my case I have unlimited resources. The DBA can change the resource cap for your user with the ALTER USER command.
Example:
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
Example:
Code: Select all
ALTER USER xxx MEMORYCAP '2.5G';
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: ERROR: Insufficient resources to execute plan on pool..
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 16
- Joined: Fri May 11, 2012 12:25 pm
Re: ERROR: Insufficient resources to execute plan on pool..
This can also be caused from having too many delete vectors in your projections.
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.
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;
We purge after every ETL.