Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Tuple Mover fails out of memory

After 1024 Insert operations I invoke the Tuple Mover. It aborts. How do I resolve this ?

Snippets shown below, full code and log attached..

--
drop schema if exists "YADAMU_TEST" cascade;
--
create schema if not exists "YADAMU_TEST";
--
create table if not exists "YADAMU_TEST"."People"(
  "PersonID" decimal(10),
  "FullName" varchar(75),
  "PreferredName" varchar(75),
  "SearchName" varchar(152),
  "IsPermittedToLogon" boolean,
  "LogonName" varchar(75),
  "IsExternalLogonProvider" boolean,
  "HashedPassword" long varbinary(6553402),
  "IsSystemUser" boolean,
  "IsEmployee" boolean,
  "IsSalesperson" boolean,
  "UserPreferences" long varchar(6553402),
  "PhoneNumber" varchar(30),
  "FaxNumber" varchar(30),
  "EmailAddress" varchar(384),
  "Photo" long varbinary(6553402),
  "CustomFields" long varchar(6553402),
  "OtherLanguages" long varchar(6553402),
  "LastEditedBy" decimal(10),
  "ValidFrom" datetime,
  "ValidTo" datetime
);
SET SESSION AUTOCOMMIT TO OFF;
--
savepoint my_savepoint;
--
insert into "YADAMU_TEST"."People" ("PersonID","FullName","PreferredName","SearchName","IsPermittedToLogon","LogonName","IsExternalLogonProvider","HashedPassword","IsSystemUser","IsEmployee","IsSalesperson","UserPreferences","PhoneNumber","FaxNumber","EmailAddress","Photo","CustomFields","OtherLanguages","LastEditedBy","ValidFrom","ValidTo") 
values   ('17','Piper Koch','Piper','Piper Piper Koch',true,'[email protected]',false,
X'61d8d0828e55e8895e216b88fe98cba6b5940992279d74992aaa2563f4e66715'::LONG VARBINARY(32000000),
true,true,false,'{"theme":"dark-hive","dateFormat":"mm/dd/yy","timeZone": "PST","table":{"pagingType":"full","pageLength": 50},"favoritesOnDashboard":true}','(415) 555-0102','(415) 555-0103','[email protected]',null,'{ "OtherLanguages": ["Romanian","Portuguese"] ,"HireDate":"2011-10-15T00:00:00","Title":"Manager"}','["Romanian","Portuguese"]','1','2016-05-31T23:14:00.000000Z','9999-12-31T23:59:59.999999Z');
--
release savepoint my_savepoint;
--
savepoint my_savepoint;
--
insert into "YADAMU_TEST"."People" ("PersonID","FullName","PreferredName","SearchName","IsPermittedToLogon","LogonName","IsExternalLogonProvider","HashedPassword","IsSystemUser","IsEmployee","IsSalesperson","UserPreferences","PhoneNumber","FaxNumber","EmailAddress","Photo","CustomFields","OtherLanguages","LastEditedBy","ValidFrom","ValidTo") 

.....

SELECT COUNT(DISTINCT storage_oid) ros_cnt FROM storage_containers WHERE schema_name = 'YADAMU_TEST' and projection_name = 'People_super';
--
SELECT OPERATION_START_TIMESTAMP, node_name, operation_status, table_schema, table_name, projection_name, plan_type FROM TUPLE_MOVER_OPERATIONS where table_schema = 'YADAMU_TEST' and table_name = 'People' order by OPERATION_START_TIMESTAMP desc;
--
select do_tm_task('mergeout','YADAMU_TEST.People');
--
SELECT COUNT(DISTINCT storage_oid) ros_cnt FROM storage_containers WHERE schema_name = 'YADAMU_TEST' and projection_name = 'People_super';
--
SELECT OPERATION_START_TIMESTAMP, node_name, operation_status, table_schema, table_name, projection_name, plan_type FROM TUPLE_MOVER_OPERATIONS where table_schema = 'YADAMU_TEST' and table_name = 'People' order by OPERATION_START_TIMESTAMP desc;
--
savepoint my_savepoint;
--
insert into "YADAMU_TEST"."People" ("PersonID","FullName","PreferredName","SearchName","IsPermittedToLogon","LogonName","IsExternalLogonProvider","HashedPassword","IsSystemUser","IsEmployee","IsSalesperson","UserPreferences","PhoneNumber","FaxNumber","EmailAddress","Photo","CustomFields","OtherLanguages","LastEditedBy","ValidFrom","ValidTo") 
values   ('3158','Valentin Tirlea','Valentin','Valentin Valentin Tirlea',false,'NO LOGON',false,null,false,false,false,null,'(210) 555-0100','(210) 555-0101','[email protected]',null,null,null,'1','2013-01-01T00:00:00.000000Z','9999-12-31T23:59:59.999999Z');
rollback to savepoint my_savepoint;
savepoint my_savepoint;
--

Generates the folllowing output after 1024 iinserts

 ros_cnt 
---------
    1024
(1 row)

   OPERATION_START_TIMESTAMP   |     node_name     | operation_status | table_schema | table_name | projection_name | plan_type 
-------------------------------+-------------------+------------------+--------------+------------+-----------------+-----------
 2021-04-25 00:04:04.863375+00 | v_docker_node0001 | Abort            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:04:04.855188+00 | v_docker_node0001 | Start            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:04:04.739887+00 | v_docker_node0001 | Abort            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:04:04.727826+00 | v_docker_node0001 | Start            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:03:29.625978+00 | v_docker_node0001 | Abort            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:03:29.583162+00 | v_docker_node0001 | Start            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:00:06.358307+00 | v_docker_node0001 | Abort            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:00:06.343703+00 | v_docker_node0001 | Start            | YADAMU_TEST  | People     | People_super    | Mergeout
(8 rows)

                                                                                                                                           do_tm_task                                                                                                                                            
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Task: mergeout
(Table: YADAMU_TEST.People) (Projection: YADAMU_TEST.People_super)
On node v_docker_node0001:
  ERROR 3587:  Insufficient resources to execute plan on pool general [Request Too Large:Memory(KB) Exceeded: Requested = 93875058, Free = 54918215 (Limit = 54918215, Used = 0)]

(1 row)

 ros_cnt 
---------
    1024
(1 row)

   OPERATION_START_TIMESTAMP   |     node_name     | operation_status | table_schema | table_name | projection_name | plan_type 
-------------------------------+-------------------+------------------+--------------+------------+-----------------+-----------
 2021-04-25 00:13:23.800791+00 | v_docker_node0001 | Abort            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:13:23.791727+00 | v_docker_node0001 | Start            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:04:04.863375+00 | v_docker_node0001 | Abort            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:04:04.855188+00 | v_docker_node0001 | Start            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:04:04.739887+00 | v_docker_node0001 | Abort            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:04:04.727826+00 | v_docker_node0001 | Start            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:03:29.625978+00 | v_docker_node0001 | Abort            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:03:29.583162+00 | v_docker_node0001 | Start            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:00:06.358307+00 | v_docker_node0001 | Abort            | YADAMU_TEST  | People     | People_super    | Mergeout
 2021-04-25 00:00:06.343703+00 | v_docker_node0001 | Start            | YADAMU_TEST  | People     | People_super    | Mergeout
(10 rows)

Answers

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.