Options
Tuple Mover fails out of memory
mark_d_drake
Community Edition User ✭
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,'piperk@wideworldimporters.com',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','piperk@wideworldimporters.com',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','valentin@example.com',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)
0
Answers
Test case attached
If I invoke the Tuple mover each 128 rows it works, but at 256 rows it reports out of memory