Cannot increase the WOS memory.

  • KM03246652
  • 13-Sep-2018
  • 13-Sep-2018

This document has not been formally reviewed for accuracy and is provided "as is" for your convenience.

Summary

Even doing moveout manually still Vertica does not allow to increase memory.

Question

alter resource pool wosdata MEMORYSIZE '4G' MAXMEMORYSIZE '4G';


ERROR 3911: maxMemorySize for wosdata can be changed only when the user WOS is empty
HINT: Do a moveout and select * from wos_container_storage to confirm
ERROR 3911: maxMemorySize for wosdata can be changed only when the user WOS is empty
HINT: Do a moveout and select * from wos_container_storage to confirm
ERROR 3911: maxMemorySize for wosdata can be changed only when the user WOS is empty
HINT: Do a moveout and select * from wos_container_storage to confirm
 

Answer

--step 1: -- (save this value)
select GET_CONFIG_PARAMETER('MaxClientSessions') ;


--step 2:
select SET_CONFIG_PARAMETER('MaxClientSessions',0) ;


--step 3: -- (if there is no potential sessions/feeds running then proceed with next step)
select node_name ,user_name,client_pid,session_id,client_label,transaction_id ,statement_start ,statement_id from sessions where statement_id is not null;


--step 4:
Select close_all_sessions();


--step 5:
Select do_tm_task('moveout') ;


--step 6: --verify is there any tm operation still running
select session_id, operation_status, table_schema, projection_name, ros_count, plan_type, operation_start_timestamp from tuple_mover_operations where is_executing;


--step 7; verify the wos storage
select * from wos_container_storage ;


--step 8: --verify again for any active sessions.
select node_name ,user_name,client_pid,session_id,client_label,transaction_id ,statement_start ,statement_id from sessions where statement_id is not null;


--step 9: --if everything is fine, then execute the below command (Note that maxmemorysize should not be greater than 75% total memory)
alter resource pool wosdata MEMORYSIZE '4G' MAXMEMORYSIZE '4G';


--step 10: --Set back the Maxclient value taken in step 1
select SET_CONFIG_PARAMETER('MaxClientSessions',<original max session value>) ;