Mercurial > hg > orthanc-databases

comparison NOTES @ 433:5964ce6385a5 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
author Alain Mazy <am@osimis.io>
date Wed, 13 Dec 2023 15:48:56 +0100
parents 8b7c1c423367
children 23c7af6f671a
comparison
equal deleted inserted replaced
432:8b7c1c423367 433:5964ce6385a5
1 Resources: 1 Resources:
2 ********* 2 *********
3 - PG transaction modes explained: https://www.postgresql.org/files/developer/concurrency.pdf 3 - PG transaction modes explained: https://www.postgresql.org/files/developer/concurrency.pdf
4 - Isoolation level explained (PG + MySQL): https://amirsoleimani.medium.com/understanding-database-isolation-level-via-examples-mysql-and-postgres-a86b5502d404 4 - Isolation level explained (PG + MySQL): https://amirsoleimani.medium.com/understanding-database-isolation-level-via-examples-mysql-and-postgres-a86b5502d404
5 - Message queuing in PG: https://www.crunchydata.com/blog/message-queuing-using-native-postgresql
6
5 7
6 Create and delete instances Internals: 8 Create and delete instances Internals:
7 ************************************* 9 *************************************
8 10
9 isNewInstance = CreateInstance(...) 11 isNewInstance = CreateInstance(...)
164 166
165 167
166 ************************************************************************ 168 ************************************************************************
167 169
168 In debug, no verbose logs 170 In debug, no verbose logs
169 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 51.703 s 171 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 43.957 s
170 Orthanc mainline + PG maineline (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 51.913 s 172 Orthanc mainline + PG mainline (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: XX s
171 Orthanc mainline + PG maineline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 24.754 s 173 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 19.861 s
174 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 22.500 s (with temporary tables)
172 175
173 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 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 21.214 s
174 Orthanc mainline + PG maineline (serializable mode) : fails: No new instance while overwriting; this should not happen 177 Orthanc mainline + PG mainline (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 23.010 s
175 Orthanc mainline + PG maineline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 1.394 s (with assert((statement.Next(), statement.IsDone())) commented out) 178 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 1.71 s (with RemainingAncestor.id)
179 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 3.26 s (with temporary tables)
180
181 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 23.016 s
182 Orthanc mainline + PG mainline (read-committed mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 8.788 s
183 Orthanc mainline + PG mainline (read-committed mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 17.129 s (with temporary tables)
176 184
177 TODO: 185 TODO:
178 - assert((statement.Next(), statement.IsDone())) commented out -> create temporary tables ? 186 - assert((statement.Next(), statement.IsDone())) commented out -> create temporary tables ?
187 try again temporary tables (with different names ? Have a switch in the code ?)
188 - have a separate "thread" to increment/decrement statistics because everybody is fighting to modify the GlobalIntegers rows
189 - test events generation StableSeries ....
190 - test RemainingAncestor response in integration tests: OK
191 - disable MySQL ODBC plugin
192 - run tests with docker localy + in CI
193 - check https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor-when-using-mysql-mariadb/3820
179 - PatientAddedFunc contains an IF 194 - PatientAddedFunc contains an IF
180 - validate upgrade DB from previous Orthanc and from scratch 195 - validate upgrade DB from previous Orthanc and from scratch
181 - test with older version of PG 196 - test with older version of PG
197
198 DONE:
182 - force the create/update DB transaction to be serializable (not needed: this is handled by POSTGRESQL_LOCK_DATABASE_SETUP) 199 - force the create/update DB transaction to be serializable (not needed: this is handled by POSTGRESQL_LOCK_DATABASE_SETUP)