Mercurial > hg > orthanc-databases

comparison NOTES @ 444:2ca939d02d39 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
cleanup
author Alain Mazy <am@osimis.io>
date Wed, 10 Jan 2024 15:22:40 +0100
parents 2a48f8fcec6e
children 9e039e65d68e
comparison
equal deleted inserted replaced
443:2a48f8fcec6e 444:2ca939d02d39
66 Sample SQL code that you can execute in DBeaver to test new functions/procedures: 66 Sample SQL code that you can execute in DBeaver to test new functions/procedures:
67 67
68 CreateInstance 68 CreateInstance
69 ************************************************************************ 69 ************************************************************************
70 70
71 CREATE OR replace FUNCTION CreateInstance( 71 CREATE OR REPLACE FUNCTION CreateInstance(
72 IN patient TEXT, 72 IN patient_public_id TEXT,
73 IN study TEXT, 73 IN study_public_id TEXT,
74 IN series TEXT, 74 IN series_public_id TEXT,
75 IN instance TEXT, 75 IN instance_public_id TEXT,
76 OUT isNewPatient BIGINT, 76 OUT is_new_patient BIGINT,
77 OUT isNewStudy BIGINT, 77 OUT is_new_study BIGINT,
78 OUT isNewSeries BIGINT, 78 OUT is_new_series BIGINT,
79 OUT isNewInstance BIGINT, 79 OUT is_new_instance BIGINT,
80 OUT patientKey BIGINT, 80 OUT patient_internal_id BIGINT,
81 OUT studyKey BIGINT, 81 OUT study_internal_id BIGINT,
82 OUT seriesKey BIGINT, 82 OUT series_internal_id BIGINT,
83 OUT instanceKey BIGINT) 83 OUT instance_internal_id BIGINT) AS $body$
84 AS $$ 84
85 begin 85 BEGIN
86 isNewPatient := 1; 86 is_new_patient := 1;
87 isNewStudy := 1; 87 is_new_study := 1;
88 isNewSeries := 1; 88 is_new_series := 1;
89 isNewInstance := 1; 89 is_new_instance := 1;
90 90
91
91 BEGIN 92 BEGIN
92 INSERT INTO "resources" VALUES (DEFAULT, 0, patient, NULL); 93 INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL);
93 exception 94 EXCEPTION
94 when unique_violation then 95 WHEN unique_violation THEN
95 isNewPatient := 0; 96 is_new_patient := 0;
96 end; 97 END;
97 select internalid into patientKey from "resources" where publicId=patient and resourcetype = 0; 98 SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id AND resourcetype = 0 FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction
98 99
99 BEGIN 100 BEGIN
100 INSERT INTO "resources" VALUES (DEFAULT, 1, study, patientKey); 101 INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id);
101 exception 102 EXCEPTION
102 when unique_violation then 103 WHEN unique_violation THEN
103 isNewStudy := 0; 104 is_new_study := 0;
104 end; 105 END;
105 select internalid into studyKey from "resources" where publicId=study and resourcetype = 1; 106 SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id AND resourcetype = 1 FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction
106 107
107 BEGIN 108 BEGIN
108 INSERT INTO "resources" VALUES (DEFAULT, 2, series, studyKey); 109 INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id);
109 exception 110 EXCEPTION
110 when unique_violation then 111 WHEN unique_violation THEN
111 isNewSeries := 0; 112 is_new_series := 0;
112 end; 113 END;
113 select internalid into seriesKey from "resources" where publicId=series and resourcetype = 2; 114 SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id AND resourcetype = 2 FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction
114 115
115 BEGIN 116 BEGIN
116 INSERT INTO "resources" VALUES (DEFAULT, 3, instance, seriesKey); 117 INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id);
117 exception 118 EXCEPTION
118 when unique_violation then 119 WHEN unique_violation THEN
119 isNewInstance := 0; 120 is_new_instance := 0;
120 end; 121 END;
121 select internalid into instanceKey from "resources" where publicId=instance and resourcetype = 3; 122 SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id AND resourcetype = 3 FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction
122 123
124 IF is_new_instance > 0 THEN
125 -- Move the patient to the end of the recycling order.
126 PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
127 END IF;
123 END; 128 END;
124 $$ LANGUAGE plpgsql; 129
130 $body$ LANGUAGE plpgsql;
131
125 132
126 DO $$ 133 DO $$
127 DECLARE 134 DECLARE
128 result record; 135 result record;
129 begin 136 begin
130 delete from "resources"; 137 delete from "resources";
131 138
132 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance1'); 139 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance1');
133 140
134 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient; 141 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patient_internal_id, result.is_new_patient;
135 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy; 142 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.study_internal_id, result.is_new_study;
136 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries; 143 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.series_internal_id, result.is_new_series;
137 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance; 144 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instance_internal_id, result.is_new_instance;
138 RAISE NOTICE '--------------'; 145 RAISE NOTICE '--------------';
139 146
140 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2'); 147 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2');
141 148
142 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient; 149 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patient_internal_id, result.is_new_patient;
143 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy; 150 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.study_internal_id, result.is_new_study;
144 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries; 151 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.series_internal_id, result.is_new_series;
145 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance; 152 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instance_internal_id, result.is_new_instance;
146 RAISE NOTICE '--------------'; 153 RAISE NOTICE '--------------';
147 154
148 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2'); 155 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2');
149 156
150 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient; 157 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patient_internal_id, result.is_new_patient;
151 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy; 158 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.study_internal_id, result.is_new_study;
152 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries; 159 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.series_internal_id, result.is_new_series;
153 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance; 160 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instance_internal_id, result.is_new_instance;
154 RAISE NOTICE '--------------'; 161 RAISE NOTICE '--------------';
155 162
156 END $$; 163 END $$;
164
157 165
158 -- \set patient_key 'patient_key' 166 -- \set patient_key 'patient_key'
159 -- SELECT CreateInstance('patient', 'study', 'series', 'instance', patient_key) ; 167 -- SELECT CreateInstance('patient', 'study', 'series', 'instance', patient_key) ;
160 168
161 -- drop function CreateInstance 169 -- drop function CreateInstance
171 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 43.957 s 179 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 43.957 s
172 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 15.744 s 180 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 15.744 s
173 test_concurrent_anonymize_same_study deletion took: 18.8 s 181 test_concurrent_anonymize_same_study deletion took: 18.8 s
174 182
175 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 21.214 s 183 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 21.214 s
176 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 6.57 s (with temporary tables) 184 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 6.57 s
177 185
178 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 23.016 s 186 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 23.016 s
179 Orthanc mainline + PG mainline (read-committed mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 7.129 s 187 Orthanc mainline + PG mainline (read-committed mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 7.129 s
188
189 Orthanc mainline + PG mainline (read-committed mode) : test_upload_multiple_studies_from_multiple_threads with 10 workers and 25 files and repeat 3x: 6.454 s
180 190
181 With Docker with 10 connections SQL: 191 With Docker with 10 connections SQL:
182 osimis/orthanc:23.11.0: TIMING test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 25.047 s 192 osimis/orthanc:23.11.0: TIMING test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 25.047 s
183 osimis/orthanc:current: TIMING test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 8.649 s 193 osimis/orthanc:current: TIMING test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 8.649 s
184 194
200 1 connection : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 11.172 s 210 1 connection : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 11.172 s
201 211
202 212
203 TODO: 213 TODO:
204 - have a separate "thread" to UpdateStatistics ? 214 - have a separate "thread" to UpdateStatistics ?
215 - perf tests: upload generated data (different studies)
205 - check https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor-when-using-mysql-mariadb/3820 216 - check https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor-when-using-mysql-mariadb/3820
206 - implement a downgrade script ? And test it in PotgresUpgrades integ tests 217 - implement a downgrade script ? And test it in PotgresUpgrades integ tests
207 - In Docker images, re-enable MySQL & ODBC plugins + tests 218 - In Docker images, re-enable MySQL & ODBC plugins + tests
208 219
209 - this error sometimes occur... 220 - this error sometimes occur...