Mercurial > hg > orthanc-databases
comparison NOTES @ 444:2ca939d02d39 pg-transactions
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... |