Mercurial > hg > orthanc-databases

comparison NOTES @ 436:f16faa1fdc46 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
InsertOrUpdateMetadata function + UpdateAndGetStatistics
author Alain Mazy <am@osimis.io>
date Fri, 15 Dec 2023 17:11:26 +0100
parents 326f8304daa1
children d979f25e60cf
comparison
equal deleted inserted replaced
435:326f8304daa1 436:f16faa1fdc46
167 167
168 ************************************************************************ 168 ************************************************************************
169 169
170 In debug, no verbose logs 170 In debug, no verbose logs
171 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 43.957 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
172 Orthanc mainline + PG mainline (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: XX s 172 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 15.744 s
173 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 19.861 s 173 test_concurrent_anonymize_same_study deletion took: 18.8 s
174 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 20.027 s (with temporary tables)
175 test_concurrent_anonymize_same_study deletion took: 28.4 s
176 174
177 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 21.214 s 175 Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 21.214 s
178 Orthanc mainline + PG mainline (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 23.010 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)
179 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 1.71 s (with RemainingAncestor.id)
180 Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 2.97 s (with temporary tables)
181 177
182 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 178 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
183 Orthanc mainline + PG mainline (read-committed mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 8.788 s 179 Orthanc mainline + PG mainline (read-committed mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 7.129 s
184 Orthanc mainline + PG mainline (read-committed mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 11.872 s (with temporary tables) 180
181 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
183 osimis/orthanc:current: TIMING test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 8.649 s
184
185 osimis/orthanc:23.11.0: TIMING test_concurrent_anonymize_same_study deletion took: 11.807 s
186 osimis/orthanc:current: TIMING test_concurrent_anonymize_same_study deletion took: 10.620 s
187
188 osimis/orthanc:23.11.0: TIMING test_concurrent_uploads_same_study with 20 workers and 1x repeat: 10.736 s
189 osimis/orthanc:current: TIMING test_concurrent_uploads_same_study with 20 workers and 1x repeat: 3.465 s
190
191 osimis/orthanc:23.11.0: TIMING test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 11.966 s
192 osimis/orthanc:current: TIMING test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 5.092 s
193
185 194
186 TODO: 195 TODO:
187 - assert((statement.Next(), statement.IsDone())) commented out -> create temporary tables ? 196 - check RETURNS SET OF !
188 try again temporary tables (with different names ? Have a switch in the code ?) 197 CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
198 SELECT * FROM foo WHERE fooid = $1;
199 $$ LANGUAGE SQL;
189 - have a separate "thread" to increment/decrement statistics because everybody is fighting to modify the GlobalIntegers rows 200 - have a separate "thread" to increment/decrement statistics because everybody is fighting to modify the GlobalIntegers rows
201 ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", "i", -> make unique + handle on conflicts
202 countIdentifierTags, identifierTags);
203
204 ExecuteSetResourcesContentTags(manager, "MainDicomTags", "t", -> make unique + handle on conflicts
205 countMainDicomTags, mainDicomTags);
206
207 ExecuteSetResourcesContentMetadata(manager, HasRevisionsSupport(), countMetadata, metadata); handle on conflicts
208
209 CREATE OR REPLACE FUNCTION InsertOrReplaceMetadata(VARIADIC params SETOF metadata)
210 RETURNS VOID AS $$
211 BEGIN
212
213 FOR i IN 1..array_length(my_params, 1) LOOP
214 my_sum := my_sum + my_params[i].my_column;
215 END LOOP;
216 RETURN my_sum;
217 END;
218 $$ LANGUAGE plpgsql;
219
220 #include <postgresql/libpq-fe.h>
221
222 int main() {
223 PGconn *conn = PQconnectdb("dbname=mydb user=myuser password=mypassword");
224 if (PQstatus(conn) != CONNECTION_OK) {
225 std::cerr << "Connection to database failed: " << PQerrorMessage(conn) << std::endl;
226 PQfinish(conn);
227 return 1;
228 }
229
230 const char *function_name = "my_function";
231 int num_params = 3;
232 int params[] = {1, 2, 3};
233
234 char *query = PQescapeLiteral(conn, function_name, strlen(function_name));
235 for (int i = 0; i < num_params; i++) {
236 char *param = PQescapeLiteral(conn, (const char *)&params[i], sizeof(int));
237 query = (char *)realloc(query, strlen(query) + strlen(param) + 1);
238 strcat(query, ",");
239 strcat(query, param);
240 PQfreemem(param);
241 }
242 strcat(query, ")");
243
244 PGresult *res = PQexec(conn, query);
245 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
246 std::cerr << "Function call failed: " << PQerrorMessage(conn) << std::endl;
247 PQfreemem(query);
248 PQclear(res);
249 PQfinish(conn);
250 return 1;
251 }
252
253 int result = atoi(PQgetvalue(res, 0, 0));
254 std::cout << "Result: " << result << std::endl;
255
256 PQfreemem(query);
257 PQclear(res);
258 PQfinish(conn);
259
260
190 - test events generation StableSeries .... 261 - test events generation StableSeries ....
191 - test RemainingAncestor response in integration tests: OK
192 - disable MySQL ODBC plugin
193 - run tests with docker localy + in CI 262 - run tests with docker localy + in CI
194 - check https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor-when-using-mysql-mariadb/3820 263 - check https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor-when-using-mysql-mariadb/3820
195 - PatientAddedFunc contains an IF 264 - PatientAddedFunc contains an IF
196 - validate upgrade DB from previous Orthanc and from scratch 265 - validate upgrade DB from previous Orthanc and from scratch
197 - test with older version of PG 266 - test with older version of PG
267 - In Docker images, re-enable MySQL & ODBC plugins + tests
198 268
199 DONE: 269 DONE:
200 - force the create/update DB transaction to be serializable (not needed: this is handled by POSTGRESQL_LOCK_DATABASE_SETUP) 270 - force the create/update DB transaction to be serializable (not needed: this is handled by POSTGRESQL_LOCK_DATABASE_SETUP)