ERROR 4305: Out of system WOS memory during catalog SELECT

kushal_alester

Dear Friends,

I am getting the error- "vsql:/tmp/vrt_temp_exec.sql.4326:24: ERROR 4305:  Out of system WOS memory during catalog SELECT
HINT:  Increase sysdata.maxmemorysize, current value is 1048576 KB"

The error is because of the select query in my sql script:
select now();

When i checked the resources allocated to resource_pools, i get the below information-

SELECT NAME,MEMORYSIZE,maxmemorysize FROM RESOURCE_POOLS;

NAME          MEMORYSIZE    maxmemorysize
general                                   Special: 95%
sysquery            64M   
sysdata              100M                 1G
wosdata             0%                     2G
tm                      200M   
refresh               0%   
recovery            0%   
dbd                    0%   
jvm                    0%                     2G
svcacct_pool    0%   


#My sample script look like;

select now();

insert  /*+direct*/ INTO table1
select * from table1_bkp;

select now();

Update ...

select now();

.....

....
# End of script

Please help me out how to resolve the issue.

Note: I just wanted to have the select now(); query in my script to check how much time it takes for each load data from one table to other.

Regards,
Kushal

Abhishek_Rana

Hi,

ERROR 4305: Out of system WOS memory during catalog SELECT HINT:Increase sysdata.maxmemorysize, current value is 1048576 KB

Answer:


Vertica uses the resource pool sysdata for processing queries against
system tables (all tables in schemas v_catalog, v_monitor, and
v_internal).  Exceeding the limit of this pool could indicate either
(a) many sessions are trying to query system tables at the same time,
and the resource pool is overutilized, or (b) the query is very
complex and requires too much memory to execute within the configured
memory resource limit.


You could try increasing the maxmemorysize for this pool somewhat
(say, 25-50%) to see if that lets the system ride through periods of
high demand.  For complex queries on system tables, you could
materialize the system tables into temporary database tables with
insert-select, then operate on the temporary tables instead, to avoid
the resource limit.

Regards'
Abhishek

colin_loghin

I am trying to wrap my head around this error message. To my knowledge WOS space is used from WOSDATA, and system tables queries from sysdata.  I can find any explanation for the word WOS in the error message. Is this an unrefined error message or am i reading it wrong? 

[Deleted User]

To check the timestamp, clock_timestamp function might be more helpful though it depends on the situation.

https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Date-Time/NO...

amarjeet

There is two things here:-

1) Remove maxmemorysize of sysdata for temporary....Now what this will do is..?

-->When your query will run it will take from general pool whatever memory it needed.But do not put other load in system, And after this query complete you can revert back the maxmemorysize of sysdata to normal. Every query don't want this much memory.

2) if you want to run concurrently then you have to go for increasing memory, so general pool can have enough memory to make that query run.

If You have anything please write to us.