Resource Tracking improvements #1307
Assignees
Milestone
Comments
Closed
Performance AnalysisPreparationCheck queries used for testing
INSERT INTO resource_tracker_service_runs
(product_name, service_run_id, wallet_id, wallet_name, pricing_plan_id, simcore_user_agent, user_id, user_email, project_id, project_name, node_id, node_name, service_key, service_version, service_type, service_resources, service_additional_metadata, started_at, stopped_at, service_run_status, modified, last_heartbeat_at, pricing_unit_id, pricing_unit_cost_id, pricing_unit_cost, service_run_status_msg, missed_heartbeat_counter)
select 's4l', concat('test_', s), 4, 'Matus stress testing', NULL, 'undefined', 146457, '[email protected]', '52f4a2c4-36cb-11ee-a51f-02420ac12330', 'Stress testing', '52f4a2c4-36cb-11ee-a51f-02420ac12330', 'sleeper', 'simcore/services/comp/itis/sleeper', '2.1.6', 'DYNAMIC_SERVICE', '{}', '{}', now(), now(), 'SUCCESS', now(), now(), NULL, NULL, 0, 'testing', 0
FROM generate_series(1, 1000000) as s;
INSERT INTO resource_tracker_credit_transactions
(product_name, wallet_id, wallet_name, pricing_plan_id, user_id, user_email, osparc_credits, transaction_status, transaction_classification, service_run_id, payment_transaction_id, created, last_heartbeat_at, modified, pricing_unit_id, pricing_unit_cost_id)
SELECT 's4l', CASE WHEN random() < 0.05 THEN 0 ELSE 4 END, 'Matus stress testing', NULL, 146457, '[email protected]', -random(), CASE WHEN random() < 0.9999 THEN 'BILLED'::credittransactionstatus ELSE 'PENDING'::credittransactionstatus end, 'DEDUCT_SERVICE_RUN', service_run_id, '', now(), now(), now(), NULL, NULL
FROM resource_tracker_service_runs where wallet_name = 'Matus stress testing' and service_run_id LIKE 'test_%';
explain ANALYSE SELECT sum(resource_tracker_credit_transactions.osparc_credits) AS sum_1
FROM resource_tracker_credit_transactions
WHERE resource_tracker_credit_transactions.product_name = 's4l' AND resource_tracker_credit_transactions.wallet_id = 4 AND resource_tracker_credit_transactions.transaction_status IN ('PENDING', 'BILLED');
explain ANALYSE SELECT resource_tracker_service_runs.product_name, resource_tracker_service_runs.service_run_id, resource_tracker_service_runs.wallet_id, resource_tracker_service_runs.wallet_name, resource_tracker_service_runs.pricing_plan_id, resource_tracker_service_runs.pricing_unit_id, resource_tracker_service_runs.pricing_unit_cost_id, resource_tracker_service_runs.pricing_unit_cost, resource_tracker_service_runs.user_id, resource_tracker_service_runs.user_email, resource_tracker_service_runs.project_id, resource_tracker_service_runs.project_name, resource_tracker_service_runs.node_id, resource_tracker_service_runs.node_name, resource_tracker_service_runs.service_key, resource_tracker_service_runs.service_version, resource_tracker_service_runs.service_type, resource_tracker_service_runs.service_resources, resource_tracker_service_runs.started_at, resource_tracker_service_runs.stopped_at, resource_tracker_service_runs.service_run_status, resource_tracker_service_runs.modified, resource_tracker_service_runs.last_heartbeat_at, resource_tracker_service_runs.service_run_status_msg, resource_tracker_service_runs.missed_heartbeat_counter, resource_tracker_credit_transactions.osparc_credits, resource_tracker_credit_transactions.transaction_status
FROM resource_tracker_service_runs LEFT OUTER JOIN resource_tracker_credit_transactions ON resource_tracker_service_runs.service_run_id = resource_tracker_credit_transactions.service_run_id
WHERE resource_tracker_service_runs.product_name = 's4l' AND resource_tracker_service_runs.user_id = 146457 ORDER BY resource_tracker_service_runs.started_at DESC
LIMIT 20 OFFSET 0;
explain analyse SELECT * from aws_s3.query_export_to_s3('SELECT resource_tracker_service_runs.product_name, resource_tracker_service_runs.service_run_id, resource_tracker_service_runs.wallet_name, resource_tracker_service_runs.user_email, resource_tracker_service_runs.project_name, resource_tracker_service_runs.node_name, resource_tracker_service_runs.service_key, resource_tracker_service_runs.service_version, resource_tracker_service_runs.service_type, resource_tracker_service_runs.started_at, resource_tracker_service_runs.stopped_at, resource_tracker_credit_transactions.osparc_credits, resource_tracker_credit_transactions.transaction_status FROM resource_tracker_service_runs LEFT OUTER JOIN resource_tracker_credit_transactions ON resource_tracker_service_runs.service_run_id = resource_tracker_credit_transactions.service_run_id WHERE resource_tracker_service_runs.product_name = ''s4l'' AND resource_tracker_service_runs.user_id = 146457 ORDER BY resource_tracker_service_runs.started_at DESC',
aws_commons.create_s3_uri('matus-testing', 'resource-usage-tracker-service-runs/testing/test9.csv', 'us-east-1'), 'format csv, HEADER true');Results
Conclusion
|
|
some questions from our test together:
|
1 task
Yes, when the frontend lists usage endpoint, we join 2 tables in the database and filter/sort based on user requests. When this was called multiple times, it caused the database to be overloaded, resulting in timeouts of requests as the database was unable to process all the queries at once.
|
1 task
|
after talking to @matusdrobuliak66 we can close this parent case, keeping the linked issue around. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Tasks
The text was updated successfully, but these errors were encountered: