Mercurial > hg > orthanc-databases
annotate NOTES @ 443:2a48f8fcec6e pg-transactions
cleanup
author | Alain Mazy <am@osimis.io> |
---|---|
date | Wed, 10 Jan 2024 09:04:05 +0100 |
parents | d979f25e60cf |
children | 2ca939d02d39 |
rev | line source |
---|---|
431 | 1 Resources: |
2 ********* | |
3 - PG transaction modes explained: https://www.postgresql.org/files/developer/concurrency.pdf | |
433
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
4 - Isolation level explained (PG + MySQL): https://amirsoleimani.medium.com/understanding-database-isolation-level-via-examples-mysql-and-postgres-a86b5502d404 |
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
5 - Message queuing in PG: https://www.crunchydata.com/blog/message-queuing-using-native-postgresql |
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
6 |
431 | 7 |
432
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
8 Create and delete instances Internals: |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
9 ************************************* |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
10 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
11 isNewInstance = CreateInstance(...) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
12 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
13 if (!isNewInstance && overwriteInstances) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
14 DeleteResource(instance) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
15 -> ClearDeletedFiles(manager); |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
16 DELETE FROM DeletedFiles ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction -> it is empty when taking a snapshot of the DB in READ COMMITTED mode!!! |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
17 ClearDeletedResources(manager); |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
18 DELETE FROM DeletedResources ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction !!! |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
19 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
20 DELETE FROM RemainingAncestor ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction !!! |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
21 DELETE FROM Resources WHERE internalId=.. |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
22 -> cascades delete the MainDicomTags, the Metadata and the AttachedFiles |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
23 -> this triggers AttachedFileDeletedFunc |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
24 INSERT INTO DeletedFiles VALUES |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
25 (old.uuid, old.filetype, old.compressedSize, |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
26 old.uncompressedSize, old.compressionType, |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
27 old.uncompressedHash, old.compressedHash); |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
28 RETURN NULL; |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
29 -> this triggers a SQL trigger: ResourceDeletedFunc |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
30 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
31 IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
32 -- Signal that the deleted resource has a remaining parent |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
33 -- (a parent that must not be deleted but whose LastUpdate must be updated) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
34 INSERT INTO RemainingAncestor |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
35 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
36 ELSE |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
37 -- Delete a parent resource when its unique child is deleted |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
38 DELETE FROM Resources WHERE internalId = old.parentId; |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
39 END IF; |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
40 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
41 SELECT * FROM RemainingAncestor |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
42 -> SignalRemainingAncestor() // There is at most 1 remaining ancestor |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
43 -> ServerIndex::TransactionContext::SignalRemainingAncestor() |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
44 -> stores remainingType and remainingPublicId (this is used in StatelessDatabaseOperations::DeleteResource to build the Rest Response of /delete |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
45 and to update the LastUpdate of all parent (only when deleted from /delete)) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
46 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
47 SignalDeletedFiles(output, manager); |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
48 SELECT * FROM DeletedFiles |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
49 -> SignalDeletedAttachment() |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
50 -> ServerIndex::TransactionContext::SignalAttachmentDeleted() |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
51 -> pendingFilesToRemove_.push_back(FileToRemove(info)) (files are deleted in CommitFilesToRemove in the ServerIndex::TransactionContext::Commit) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
52 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
53 SignalDeletedResources(output, manager); |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
54 SELECT resourceType, publicId FROM DeletedResources |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
55 -> SignalDeletedResource() |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
56 -> Emit DeletedResource event (lua) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
57 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
58 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
59 if (!CreateInstance(...)) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
60 Error: "No new instance while overwriting; this should not happen" |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
61 |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
62 if isNewInstance -> LogChange |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
63 if isNewSeries -> LogChange |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
64 .... |
431 | 65 |
66 Sample SQL code that you can execute in DBeaver to test new functions/procedures: | |
67 | |
68 CreateInstance | |
69 ************************************************************************ | |
70 | |
71 CREATE OR replace FUNCTION CreateInstance( | |
72 IN patient TEXT, | |
73 IN study TEXT, | |
74 IN series TEXT, | |
75 IN instance TEXT, | |
76 OUT isNewPatient BIGINT, | |
77 OUT isNewStudy BIGINT, | |
78 OUT isNewSeries BIGINT, | |
79 OUT isNewInstance BIGINT, | |
80 OUT patientKey BIGINT, | |
81 OUT studyKey BIGINT, | |
82 OUT seriesKey BIGINT, | |
83 OUT instanceKey BIGINT) | |
84 AS $$ | |
85 begin | |
86 isNewPatient := 1; | |
87 isNewStudy := 1; | |
88 isNewSeries := 1; | |
89 isNewInstance := 1; | |
90 | |
91 BEGIN | |
92 INSERT INTO "resources" VALUES (DEFAULT, 0, patient, NULL); | |
93 exception | |
94 when unique_violation then | |
95 isNewPatient := 0; | |
96 end; | |
97 select internalid into patientKey from "resources" where publicId=patient and resourcetype = 0; | |
98 | |
99 BEGIN | |
100 INSERT INTO "resources" VALUES (DEFAULT, 1, study, patientKey); | |
101 exception | |
102 when unique_violation then | |
103 isNewStudy := 0; | |
104 end; | |
105 select internalid into studyKey from "resources" where publicId=study and resourcetype = 1; | |
106 | |
107 BEGIN | |
108 INSERT INTO "resources" VALUES (DEFAULT, 2, series, studyKey); | |
109 exception | |
110 when unique_violation then | |
111 isNewSeries := 0; | |
112 end; | |
113 select internalid into seriesKey from "resources" where publicId=series and resourcetype = 2; | |
114 | |
115 BEGIN | |
116 INSERT INTO "resources" VALUES (DEFAULT, 3, instance, seriesKey); | |
117 exception | |
118 when unique_violation then | |
119 isNewInstance := 0; | |
120 end; | |
121 select internalid into instanceKey from "resources" where publicId=instance and resourcetype = 3; | |
122 | |
123 END; | |
124 $$ LANGUAGE plpgsql; | |
125 | |
126 DO $$ | |
127 DECLARE | |
128 result record; | |
129 begin | |
130 delete from "resources"; | |
131 | |
132 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance1'); | |
133 | |
134 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient; | |
135 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy; | |
136 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries; | |
137 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance; | |
138 RAISE NOTICE '--------------'; | |
139 | |
140 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2'); | |
141 | |
142 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient; | |
143 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy; | |
144 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries; | |
145 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance; | |
146 RAISE NOTICE '--------------'; | |
147 | |
148 SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2'); | |
149 | |
150 RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patientKey, result.isNewPatient; | |
151 RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.studyKey, result.isNewStudy; | |
152 RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.seriesKey, result.isNewSeries; | |
153 RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instanceKey, result.isNewInstance; | |
154 RAISE NOTICE '--------------'; | |
155 | |
156 END $$; | |
157 | |
158 -- \set patient_key 'patient_key' | |
159 -- SELECT CreateInstance('patient', 'study', 'series', 'instance', patient_key) ; | |
160 | |
161 -- drop function CreateInstance | |
162 -- select * from "resources"; | |
163 -- delete from "resources"; | |
164 -- INSERT INTO "resources" VALUES (DEFAULT, 0, 'patient', NULL) | |
165 | |
166 | |
167 | |
168 ************************************************************************ | |
169 | |
443 | 170 In debug, no verbose logs, 10 connections |
433
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
171 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 43.957 s |
436
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
172 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 15.744 s |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
173 test_concurrent_anonymize_same_study deletion took: 18.8 s |
435
326f8304daa1
new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents:
434
diff
changeset
|
174 |
433
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
175 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 21.214 s |
436
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
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) |
433
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
177 |
443 | 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 |
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 | |
436
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
180 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
181 With Docker with 10 connections SQL: |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
182 osimis/orthanc:23.11.0: TIMING test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 25.047 s |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
183 osimis/orthanc:current: TIMING test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 8.649 s |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
184 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
185 osimis/orthanc:23.11.0: TIMING test_concurrent_anonymize_same_study deletion took: 11.807 s |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
186 osimis/orthanc:current: TIMING test_concurrent_anonymize_same_study deletion took: 10.620 s |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
187 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
188 osimis/orthanc:23.11.0: TIMING test_concurrent_uploads_same_study with 20 workers and 1x repeat: 10.736 s |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
189 osimis/orthanc:current: TIMING test_concurrent_uploads_same_study with 20 workers and 1x repeat: 3.465 s |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
190 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
191 osimis/orthanc:23.11.0: TIMING test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 11.966 s |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
192 osimis/orthanc:current: TIMING test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 5.092 s |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
193 |
443 | 194 Testing the connecions (note: Orthanc and PG server running on the same server) |
195 10 connections : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 15.744 s | |
196 1 connection : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 21.341 s | |
197 10 connections : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 6.57 s | |
198 1 connection : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 10.223 s | |
199 10 connections : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 7.129 s | |
200 1 connection : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 11.172 s | |
201 | |
431 | 202 |
203 TODO: | |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
436
diff
changeset
|
204 - have a separate "thread" to UpdateStatistics ? |
433
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
205 - check https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor-when-using-mysql-mariadb/3820 |
443 | 206 - implement a downgrade script ? And test it in PotgresUpgrades integ tests |
436
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
435
diff
changeset
|
207 - In Docker images, re-enable MySQL & ODBC plugins + tests |
433
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
208 |
443 | 209 - this error sometimes occur... |
210 ====================================================================== | |
211 FAIL: test_upload_delete_same_study_from_multiple_threads (Concurrency.test_concurrency.TestConcurrency) | |
212 ---------------------------------------------------------------------- | |
213 Traceback (most recent call last): | |
214 File "/home/runner/work/orthanc-builder/orthanc-builder/docker/integration-tests/orthanc-tests-repo-normal/NewTests/Concurrency/test_concurrency.py", line 254, in test_upload_delete_same_study_from_multiple_threads | |
215 self.assertEqual(0, len(self.o.studies.get_all_ids())) | |
216 AssertionError: 0 != 1 | |
217 | |
218 2023-12-21 17:10:04.737 UTC [69] ERROR: deadlock detected | |
219 2023-12-21 17:10:04.737 UTC [69] DETAIL: Process 69 waits for ShareLock on transaction 20657; blocked by process 70. | |
220 Process 70 waits for ShareLock on transaction 18193; blocked by process 69. | |
221 Process 69: SELECT * FROM DeleteResource($1) | |
222 Process 70: SELECT * FROM CreateInstance($1, $2, $3, $4) | |
223 2023-12-21 17:10:04.737 UTC [69] HINT: See server log for query details. | |
224 2023-12-21 17:10:04.737 UTC [69] CONTEXT: while deleting tuple (134,66) in relation "resources" | |
225 SQL statement "DELETE FROM ONLY "public"."resources" WHERE $1 OPERATOR(pg_catalog.=) "parentid"" | |
226 SQL statement "DELETE FROM Resources WHERE internalId = old.parentId | |
227 AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId)" | |
228 PL/pgSQL function resourcedeletedfunc() line 7 at SQL statement | |
229 SQL statement "DELETE FROM Resources WHERE internalId=id RETURNING *" | |
230 PL/pgSQL function deleteresource(bigint) line 26 at SQL statement | |
231 2023-12-21 17:10:04.737 UTC [69] STATEMENT: SELECT * FROM DeleteResource($1) | |
232 | |
433
5964ce6385a5
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents:
432
diff
changeset
|
233 DONE: |
443 | 234 - reenable PatientRecyclingOrder |
432
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
235 - force the create/update DB transaction to be serializable (not needed: this is handled by POSTGRESQL_LOCK_DATABASE_SETUP) |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
436
diff
changeset
|
236 - PatientAddedFunc contains an IF (check if other IF/THEN/ELSE pattern remains) |
443 | 237 - validate upgrade DB from previous Orthanc and from scratch |
238 - check minimal version of PG (9.5 - 9.6 ? for create index if not exists): seems to work with 9.5 cfr PotgresUpgrades integ tests | |
239 - test events generation StableSeries .... (count the NewSeries, NewInstances event and make sure they match the numb) |