Test status: COMPLETED WITH ERRORS

...

Running FYR on eureka completed with errors. Tests failed multiple times on overall order rollover with 504 Gateway Time-out issue.
The problem confirmed on okapi based environment. In scope of investigation of the problem the Time-out was solved changing parameter max_locks_per_transaction in DB configuration from 64 to 1024.
Eureka based environment (after changes were applied):
- #1 Phase Test FYR took 3 hours 35 minutes, completed successfully.
- #2 Phase actual FYR took 8 hours and completed with errors* (Order rollover Error).
Okapi based environment (after changes were applied, testing is carried out in scope of investigation to check functionality of FYR):
- #1 Phase Test FYR took 3 hours 20 minutes. Completed successfully.
- #2 Phase actual FYR took 9 hours 7 minutes. Completed successfully.
Service CPU utilization for pre-strop to Fiscal Year Rollover (FYR) - Test FYR was less than 5% for all the modules, except a spike at the beginning of test rollover (mod-invoice reached 10%, mod-invoice-storage - 8%). Some modules were steady - mod-inventory - 11%, mod-pubsub - 8%.
DB CPU usage was on level of 9% during the test rollover and there was growing trend from 9% to 15% during the rollover itself.
No memory leaks were found.

Recommendations & Jiras

Set max_locks_per_transaction parameter in DB cluster and instance configurations from default 64 to 1024 to avoid problems with ordersRollover.
Investigation of issues on Eureka based environment
Jira Legacy
server System Jira
serverId 01505d01-b853-3c2e-90f1-ee9b165564fc
key MODFISTO-495
- Time out issue.

Errors

127.0.0.1:43264 POST /finance/ledger-rollovers null HTTP_1_1 500 265 400027 tid=[tenant] Internal Server Error. This error happens when we try FYR with more than 10k open orders. The last attempt to run FYR with 50k open orders after changed recommended parameter max_locks_per_transaction failed. A new observation here is that budgets became unavailable with this amount of data. Probably it may cause fail of FYR.
* Error message: io.vertx.core.impl.NoStackTraceThrowable: Response status code 401 is not between 200 and 299,qelc2/mod-finance-storage/9044d47f773b48a99cc6c8420864b724 which indicate possible RTR issue.
WARN [org.fol.sid.ser.rou.RequestMatchingService] (vert.x-eventloop-thread-4) Egress routing entry was not found for the request's path. Forwarding request to the Gateway:moduleId = sc-mod-finance-storage, path = /orders/rollover, destination = https://kong-qelc2.int.aws.folio.org, x-okapi-module-id = null",qelc2/sidecar-mod-finance-storage/9044d47f773b48a99cc6c8420864b724,054267740449:qelc2-folio-eis
ERROR edgerRolloverService handleOrderRolloverError:: Orders rollover failed for ledger f38a29f5-feaa-4363-98a1-1eba79557c9c

...

Spikes are connected to fqm manager queries (as example - REFRESH MATERIALIZED VIEW CONCURRENTLY [tenant]_0001_mod_fqm_manager.drv_langu...). Do not affect duration of FYR.

DB Connections

Test FY rollover (qelc2)

...

PTF -environment qelc2

10 m6i.2xlarge EC2 instances located in US East (N. Virginia)us-east-1
1 database instance, writer
Name Memory GIB vCPUs max_connections
db.r6g.4xlarge
32 GiB 4 vCPUs -
MSK fse-tenant
- 4 m5.2xlarge brokers in 2 zones
- Apache Kafka version 3.7.x
- EBS storage volume per broker 300 GiB
- auto.create.topics.enable=true
- log.retention.minutes=480
- default.replication.factor=3

PTF -environment qcon

10 m6i.2xlarge EC2 instances located in US East (N. Virginia)us-east-1
1 database instance, writer
Name Memory GIB vCPUs max_connections
db.r6g.xlarge
32 GiB 4 vCPUs -

...

Replace FYR id, access provider, fund id, fund name in accordance with FYR ledger info. Also check all instance related fields which should be replaced with actual data. The script prepared to generate needed amount of organizations, orders, po lines, invoices.

Expand

title	Queries to generate data

Code Block

language	sql
title	Generate

CREATE OR REPLACE FUNCTION public.generate_0062_data_for_edifact_export(
scenario_prefix TEXT,
organizations_amount INTEGER,
orders_per_vendor INTEGER,
polines_per_order INTEGER,
pieces_per_order INTEGER
) RETURNS VOID AS
$$
DECLARE
orgName TEXT DEFAULT 'perf_test_vendor';
orgCodePrefix TEXT DEFAULT 'PERF_TEST_ORG';
vendor_id TEXT;
BEGIN
FOR org_counter IN 1..organizations_amount
LOOP
INSERT INTO [tenant]_mod_organizations_storage.organizations (id, jsonb)
VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'code', concat(orgCodePrefix, '_', scenario_prefix, '_', org_counter),
'erpCode', '12345',
'isVendor', true,
'name', concat(orgCodePrefix, '_', scenario_prefix, '_', org_counter),
'status', 'Active',
'metadata', jsonb_build_object(
'createdDate', '2023-02-08T00:00:00.000+0000',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedDate', '2023-02-08T00:00:00.000+0000',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
)
)
)
RETURNING id INTO vendor_id;

PERFORM public.generate_0062_orders(orders_per_vendor, polines_per_order, pieces_per_order, vendor_id,1,2);
END LOOP;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.generate_0062_orders(
orders_per_vendor INTEGER,
polines_per_order INTEGER,
pieces_per_order INTEGER,
vendor_id TEXT,
invoices_per_organization INTEGER,
invoicelines_per_invoice INTEGER
) RETURNS VOID AS
$$
DECLARE
order_id TEXT;
poline_id TEXT;
newPoNumber INTEGER;
BEGIN
FOR order_counter IN 1..orders_per_vendor
LOOP
SELECT nextval('[tenant]_mod_orders_storage.po_number') INTO newPoNumber;

INSERT INTO [tenant]_mod_orders_storage.purchase_order (id, jsonb)
VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'id', public.uuid_generate_v4(),
'notes', jsonb_build_array(),
'acqUnitIds', jsonb_build_array(),
'approved', true,
'reEncumber', true,
'nextPolNumber', 2,
'workflowStatus', 'Pending',
'poNumber', newPoNumber,
'vendor', vendor_id,
'orderType', 'One-Time',
'metadata', jsonb_build_object(
'createdDate', '2024-10-06T00:00:00.000+0000',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedDate', '2024-10-06T00:00:00.000+0000',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
)
)
)
RETURNING id INTO order_id;

PERFORM public.generate_0062_polines(order_id, polines_per_order, pieces_per_order, newPoNumber, vendor_id);
PERFORM public.generate_0062_invoices(invoices_per_organization, invoicelines_per_invoice, vendor_id, poline_id);
END LOOP;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.generate_0062_polines(
order_id TEXT,
polines_per_order INTEGER,
pieces_per_order INTEGER,
ponumber INTEGER,
vendor_id TEXT
) RETURNS VOID AS
$$
DECLARE
polineNumber TEXT;
holding_id UUID;
currPoNumber INTEGER;
iteration_counter INT := 1;

BEGIN
SELECT id INTO holding_id
FROM [tenant]_mod_inventory_storage.holdings_record 
WHERE permanentlocationid = '9ffb23a9-0e83-4d8e-a70d-ddf44ab67c84' 
AND instanceid = 'b94d2138-bdfc-5dea-bab9-ac99e21b7970'
LIMIT 1;

FOR line_counter IN 1..polines_per_order
LOOP
SELECT currval('[tenant]_mod_orders_storage.po_number') INTO currPoNumber;

INSERT INTO [tenant]_mod_orders_storage.po_line (id, jsonb)
VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'id', public.uuid_generate_v4(),
'cost', jsonb_build_object(
'currency', 'USD',
'discountType', 'percentage',
'quantityElectronic', 2,
'poLineEstimatedPrice', 4,
'listUnitPriceElectronic', 2
),
'rush', false,
'alerts', jsonb_build_array(),
'claims', jsonb_build_array(),
'source', 'User',
'details', jsonb_build_object(
'productIds', jsonb_build_array(
jsonb_build_object(
'productId', '73121721',
'productIdType', 'c858e4f2-2b6b-4385-842b-60732ee14abb'
)
),
'isAcknowledged', false
),
'edition', '',
'metadata', jsonb_build_object(
'createdDate', '2024-10-06T11:09:59.181Z',
'updatedDate', '2024-10-06T11:10:48.652Z',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
),
'eresource', jsonb_build_object(
'trial', false,
'activated', false,
'accessProvider', 'c88742c3-dc2f-4589-a7cf-a90694c2a296',
'createInventory', 'Instance, Holding'
),
'isPackage', false,
'locations', jsonb_build_array(
jsonb_build_object(
'quantity', 2,
'locationId', '9ffb23a9-0e83-4d8e-a70d-ddf44ab67c84',
'quantityElectronic', 2
)
),
'publisher', 'Praeger Publishers',
'collection', false,
'instanceId', 'b94d2138-bdfc-5dea-bab9-ac99e21b7970',
'orderFormat', 'Electronic Resource',
'checkinItems', false,
'contributors', jsonb_build_array(
jsonb_build_object(
'contributor', 'Yurchenco, Henrietta',
'contributorNameTypeId', '2b94c631-fca9-4892-a730-03ee529ffe2a'
)
),
'poLineNumber', currPoNumber || '-' || iteration_counter,
'vendorDetail', jsonb_build_object(
'instructions', '',
'vendorAccount', '',
'referenceNumbers', jsonb_build_array()
),
'paymentStatus', 'Pending',
'receiptStatus', 'Pending',
'claimingActive', false,
'reportingCodes', jsonb_build_array(),
'titleOrPackage', '!Hablamos! Puerto Ricans speak / Photos. by Julia Singer.',
'automaticExport', false,
'publicationDate', '[1971]',
'purchaseOrderId', order_id,
'fundDistribution', jsonb_build_array(
jsonb_build_object(
'code', 'FYND',
'value', 100,
'fundId', '8003d427-34a3-4ad4-97cd-c1aae19f2bfc',
'distributionType', 'percentage'
)
),
'acquisitionMethod', '796596c4-62b5-4b64-a2ce-524c747afaa2',
'searchLocationIds', jsonb_build_array(
'9ffb23a9-0e83-4d8e-a70d-ddf44ab67c84'
),
'donorOrganizationIds', jsonb_build_array(),
'checkinItems', true
)
)
RETURNING id INTO polineNumber;
iteration_counter := iteration_counter + 1;

-- Call the function to generate data for titles table
PERFORM public.generate_0062_titles_data(polineNumber, concat(ponumber, '-', line_counter), pieces_per_order);
END LOOP;
END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION public.generate_0062_titles_data(
po_line_id TEXT,
po_line_number TEXT,
pieces_per_order INTEGER
) RETURNS VOID AS
$$
DECLARE
title_id UUID;
BEGIN
-- Insert data into titles table
INSERT INTO [tenant]_mod_orders_storage.titles (id, jsonb, creation_date, created_by, polineid)
VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'id', '4945c26f-6cf7-4050-93c1-0a8a25a2f4d7',
'title', '!Hablamos! Puerto Ricans speak / Photos. by Julia Singer.',
'edition', '',
'metadata', jsonb_build_object(
'createdDate', '2024-10-06T11:09:59.247Z',
'updatedDate', '2024-10-06T11:09:59.247Z',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
),
'poLineId', po_line_id,
'publisher', 'Praeger Publishers',
'acqUnitIds', jsonb_build_array(),
'instanceId', 'b94d2138-bdfc-5dea-bab9-ac99e21b7970',
'productIds', jsonb_build_array(
jsonb_build_object(
'productId', '73121721',
'productIdType', 'c858e4f2-2b6b-4385-842b-60732ee14abb'
)
),
'contributors', jsonb_build_array(
jsonb_build_object(
'contributor', 'Yurchenco, Henrietta',
'contributorNameTypeId', '2b94c631-fca9-4892-a730-03ee529ffe2a'
)
),
'poLineNumber', po_line_number,
'publishedDate', '[1971]',
'claimingActive', false,
'isAcknowledged', false
),
current_timestamp, -- Assuming creation date is the current timestamp
'created_by_user_id', -- Replace with the actual user ID
po_line_id::uuid
)
RETURNING id INTO title_id;

-- Call the function to generate data for pieces table
PERFORM public.generate_0062_pieces_data(title_id, po_line_id, pieces_per_order);
END
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION public.generate_0062_pieces_data(
title_id UUID,
polineNumber TEXT,
pieces_per_order INTEGER
) RETURNS VOID AS
$$
DECLARE
piece_counter INTEGER;
holding_id UUID;
BEGIN
SELECT id INTO holding_id
FROM [tenant]_mod_inventory_storage.holdings_record 
WHERE permanentlocationid = '9ffb23a9-0e83-4d8e-a70d-ddf44ab67c84' 
AND instanceid = 'b94d2138-bdfc-5dea-bab9-ac99e21b7970'
LIMIT 1;

FOR piece_counter IN 1..pieces_per_order
LOOP
-- Replace the following line with your actual poLineId
INSERT INTO [tenant]_mod_orders_storage.pieces VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'id', public.uuid_generate_v4(),
'format', 'Electronic',
'titleId', title_id,
'metadata', jsonb_build_object(
'createdDate', '2024-10-06T11:43:04.384Z',
'updatedDate', '2024-10-06T11:43:04.384Z',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
),
'poLineId', polineNumber,
'holdingId', 'aefd322b-9a32-4cd9-80e0-a5a3f882f2b1',
'copyNumber', piece_counter,
'supplement', true,
'displaySummary', piece_counter,
'receivingStatus', 'Expected',
'displayOnHolding', false,
'discoverySuppress', false,
'statusUpdatedDate', '2024-06-05T11:43:04.385+00:00'
),
NULL,
NULL,
'eb79cb72-0fb5-4773-a580-b1428fa724e9'::UUID, -- Replace with the actual poLineId
title_id
);
END LOOP;
END
$$ LANGUAGE plpgsql;






CREATE OR REPLACE FUNCTION public.generate_0062_invoices(
invoices_per_organization INTEGER,
invoicelines_per_invoice INTEGER,
vendor_id TEXT,
order_id TEXT
) RETURNS VOID AS
$$
DECLARE
invoice_id TEXT;
newInvoiceNumber INTEGER;
BEGIN
FOR invoice_counter IN 1..invoices_per_organization
LOOP
SELECT nextval('[tenant]_mod_invoice_storage.invoice_number') INTO newInvoiceNumber;

INSERT INTO [tenant]_mod_invoice_storage.invoices (id, jsonb)
VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'id', public.uuid_generate_v4(),
'total', 0.0,
'source', 'User',
'status', 'Approved',
'currency', 'USD',
'metadata', jsonb_build_object(
'createdDate', '2024-10-06T09:07:45.366Z',
'updatedDate', '2024-10-06T09:08:47.954Z',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
),
'subTotal', 0.0,
'vendorId', vendor_id,
'poNumbers', ARRAY[newInvoiceNumber]::TEXT[],
'acqUnitIds', ARRAY[]::UUID[],
'adjustments', ARRAY[]::JSONB[],
'invoiceDate', '2024-02-27T00:00:00.000+00:00',
'batchGroupId', '2a2cb998-1437-41d1-88ad-01930aaeadd5',
'fiscalYearId', '562bf586-11e9-4c36-98d7-3341042c856c',
'paymentMethod', 'Cash',
'accountingCode', '12345',
'folioInvoiceNo', newInvoiceNumber::TEXT,
'enclosureNeeded', false,
'vendorInvoiceNo', invoice_counter::TEXT,
'adjustmentsTotal', 0.0,
'exportToAccounting', false,
'nextInvoiceLineNumber', 2,
'chkSubscriptionOverlap', true
)
) RETURNING id INTO invoice_id;

PERFORM public.generate_0062_invoicelines(
invoicelines_per_invoice,
invoice_id,
newInvoiceNumber,
vendor_id,
order_id
);
END LOOP;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.generate_0062_invoicelines(
invoicelines_per_invoice INTEGER,
invoice_id TEXT,
newInvoiceNumber INTEGER,
vendor_id TEXT,
order_id TEXT
) RETURNS VOID AS
$$
DECLARE
invoiceline_id TEXT;
BEGIN
FOR invoiceline_counter IN 1..invoicelines_per_invoice
LOOP
INSERT INTO [tenant]_mod_invoice_storage.invoice_lines (id, jsonb)
VALUES (
public.uuid_generate_v4(),
jsonb_build_object(
'id', public.uuid_generate_v4(),
'total', 0.0,
'metadata', jsonb_build_object(
'createdDate', '2024-10-06T09:08:47.393Z',
'updatedDate', '2024-10-06T09:08:47.393Z',
'createdByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42',
'updatedByUserId', '9f9d1c46-52e1-4bb7-9c6c-56e6bb945c42'
),
'poLineId', order_id,
'quantity', 2,
'subTotal', 0.0,
'invoiceId', invoice_id,
'adjustments', ARRAY[]::JSONB[],
'description', 'Its awesome if its working',
'accountNumber', 'libraryorders@library.tam',
'accountingCode', '12345',
'adjustmentsTotal', 0.0,
'referenceNumbers', ARRAY[]::JSONB[],
'fundDistributions', jsonb_build_array(
jsonb_build_object(
'code', 'FYND',
'value', 100.0,
'fundId', '8003d427-34a3-4ad4-97cd-c1aae19f2bfc',
'distributionType', 'percentage'
)
),
'invoiceLineNumber', invoiceline_counter,
'invoiceLineStatus', 'Approved',
'releaseEncumbrance', false
)
) RETURNING id INTO invoiceline_id;

-- You can perform additional actions if needed
END LOOP;
END
$$ LANGUAGE plpgsql;


-- CREATE sample data
-- 1 - amount of organizations to be created
-- 2 - amount of orders per organization
-- 3 - amount of polines per order
-- 4 - amount of pieces per order

SELECT public.generate_0062_data_for_edifact_export('FYNG',1,1,1,1);



-- CLEANUP

To create encumbrances linked to the current budget orders should be with open status. To change the status from pending to open select needed data for the jmeter script. Save to use in the PUT request orders/composite-orders/${po_id}. Call needed number of requests to prepare open orders.

...

Before each fiscal year rollover data should be prepared. Common information about data preparation instructions can be found at the link: Steps for testing process#Fiscalyearrollover

Set max_locks_per_transaction parameter in DB cluster and instance configurations from default 64 to 1024 to avoid problems with ordersRollover.
Additional info for testing (scripts, queries) saved in S3 bucket - Buckets/fse-ptf/FYR/

Version	Old Version 16	New Version 17
Changes made by	Leonid Kolesnykov	Leonid Kolesnykov
Saved on	Nov 14, 2024	Nov 14, 2024

Versions Compared

Key

Test status: COMPLETED WITH ERRORS

Recommendations & Jiras

Errors

DB Connections

Test FY rollover (qelc2)

Name	Memory GIB	vCPUs	max_connections
db.r6g.4xlarge	32 GiB	4 vCPUs	-

Page Comparison

Versions Compared

Key

Recommendations & Jiras

Errors

DB Connections

Test FY rollover (qelc2)