Image insert are too slow. Databse performance too poor when using MySQL/MariaDB

csimon3 · August 22, 2023, 7:11pm

Hi OrthanC community,

Since a couple of weeks when receiving images from the MR are coming up incomplete. Only the first couple of images are registered.

In Osirix, images are received without issue (from MR). Resending images from Osirix to OrthanC shows up same issue.

After investigating we found out that an image takes about 7 seconds to be processed. No resource outage is detected (atop/htop).

Network issue was discarded. We tried to send images connected almost directly to the server and issue persists.

Disks are two 12TB disks (7200 rpm) in RAID1.

Current OrthanC server has about 3.7 millions images, from 9,200 studies (mostly MR with “full details”). Maindicomtags table has about 93 millions records.

Database is Mariadb. Server Dell T140 running OpenSuse Tumbleweed.

I found out that issue is most probably due to bad database queries. These queries will probably be the same no matter the database used (MySQL, Postgres, Oracle…).

For example, on the “create temporary table” the explain plan show that a full table scan is performed on the resource table (>3 millions rows), and various “materialized” tables are created.

The “delete” based on the temporary table cause examined rows of more than 43 millions rows. Lots of IO’s.

Please note I did run the analyze table yesterday, so statistics are fine. I checked on another server, with less data, and explain plan is the same.

Using temporary tables should be avoided at all costs, since creation and deletion of the table consume lots of resources. Additionally, if temporary table has no index, and is used in other query, performance might be awful.

I am disposed to share my experience as DBA with you and help to better the performance of OrthanC if you wish.

In the meantime we will migrate the database to SSD drives, but this is just to reduce (and postpone) the problem. The root cause if the database queries.

Many thanks in advance.

Cedric Simon

Identified queries with lots of examined rows for few affected rows:

rows_examined	db	sql_text	thread_id	rows_affected
12648	orthanc	CALL CreateInstance('0e651a19-8c8a0361-b139d495-dec7442a-f96…	4	15
3714634	orthanc	CREATE TEMPORARY TABLE DeletedResources SELECT * FROM (SELECT internalId, resourceType, publicId FROM Resources WHERE internal….	12	2
43322892	orthanc	DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM DeletedResources)	11	2
55820	orthanc	SELECT studies.publicId, MIN(instances.publicId) FROM (SELECT studies.publicId, studies.internalId FROM Resources AS studies INNER JOIN DicomIdentifiers t0 ON…	4	0

alainmazy · August 23, 2023, 10:57am

Hi Cedric,

Thanks a lot for your precious feedback.

I have planned to work on DB optimizations in 2-3 months from now and I’m keeping a reference on this thread to get back to you once I work on it.

I’ve never observed so bad performances and we have much bigger DBs running on HDD too (However, we are always using PostgreSQL and, it seems there is a difference in the code wrt deleting resources. Only MySQL uses this temporary table: https://hg.orthanc-server.com/orthanc-databases/file/7d2ba3ece4ee/MySQL/Plugins/MySQLIndex.cpp#l378).

I did not write this code so I can not really explain the rationale for this use of temporary tables (according to the comments, it is related to cascading delete in the same table not available in MySQL…)

If you have suggestions for re-writting this deletion code for MySQL to avoid the use of temporary tables (and possibly without changing the DB schema ), I can try it and make some test versions that you could try (even before I start working on DB optimizations). If you provide us with SQL code, I can push it into the plugin.

Best regards,

Alain.

csimon3 · August 23, 2023, 2:22pm

Thank you for your feedback Alain. Will try to find out a way to delete those resources more efficiently. Once I have a possible solution I will let you know.

Is there a way to migrate data from MySQL to PostgreSQL easily?

alainmazy · August 23, 2023, 4:33pm

No, The only current solution is to re-ingest the whole data.

However, the DB structures are very similar so dumping a MySQL DB and reimporting it in PostgreSQL might work with minimal tweaking but I’ve never tried it.

csimon3 · September 6, 2023, 6:09pm

Hello Alain. Continuing with MySQL is not an option for that client, so I am creating a script to migrate the data to Postgres.

Could you please confirm me that :

Table deletedresources and remainingancestor in Postgres can remain empty at import time since they do not exist in MySQL schema.
The field “revision” in tables attachedfiles and metadata can be set to zero at import time.

Once I have the script finalized, tested, and run in production, I will share the details in the event someone else need to migrate later on.

As per my experience, if you expect to store more than 3 millions images in OrthanC, MySQL is NOT an option, use Postgres instead (hoping Postgres will resolve the performance problem).

csimon3 · September 7, 2023, 7:18pm

Hello Alain,

I assume the table “globalproperties” should not be imported, and left with its original values.

Regarding the table “globalintegers”, I suspect this table should also be left aside from the import (has left data for MySQL, and seems to keep a “max” integer for some table?), but what it is used for exactly? Should I update the existing values ? Based on what?

Waiting for your answers before moving the script to production.

If you think about any other worthy information regarding the data migration, please let me know.

Best regards,

Cedric.

csimon3 · September 7, 2023, 10:18pm

Hello Alain,

I am very, very sad.

I made the migration script and all fine, all data is now in Postgres.

We talk about 159 millions of rows migrated (only the OrthanC tables). Only the “resources” table has over 4 millions entries.

I tested it on my local laptop (an Alienware m15 R7 with i7- 12700H processor, 32GB RAM DDR5, and M2 disk WD_BLACK SN850X), running openSuse Linux with OrthanC 1.12.1 on Postgres, and I have exactly the same slow insert issue when inserting a new study (as we have in production now).

I tested inserting a MR of 21 images:

On MySQL (actually MariaDB), with the merged data, insert takes 148 seconds
On MySQL using an empty database, insert takes about 1 second.
On Postgres using an empty database, insert takes about 1 second.
On Postgres, with the merged data, insert takes 124 seconds

Tests were done using exactly the same hardware, same conditions, and same data.

For “small” clients, OrthanC is perfect. I would say up to 3 millions images. But for clients with lots of data (and not wanting to delete them), the database schema is not suited for large data. Also, the images storage (folders) not being “human readable” complicates the backups and visibility of data used in time.

I loved OrthanC, but I now need to look for an alternative. I will probably return to DCM4CHEE in its new version.

I would be happy to share with you (and the community) the scripts for migrating the data from MySQL/Mariadb to Postgres, or do commit of the source code in SourceForge if you wish, as a contribution to the OrthanC community.

But definitely the database is not the problem, the database design is… As always, the database is the most critical part of a software (managing data). Modifying the data structure would be impacting all codes and is not feasible for a change of that magnitude.

In MySQL I already had “denormalized” some data in new tables (maintained using the triggers), for faster performances retrieving study datas, but performance is now at insert time, causing “communication” issues with the equipment. Something I cannot fix aside from the source code.

Best regards,

Cedric Simon.

csimon3 · September 8, 2023, 1:00am

Not giving up with OrthanC (yet)…

I tried to delete “non critical” data from maindicomtags (90% of the data), but it did not help.

The query that kills perfomances (I assume similar process also kill Postgres performances) is the following: DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM DeletedResources);

Explain plan shows that it does not use index, it performs a full table plan.

Doing a loop on DeletedResources and deleting one by one the records (in that case it will use the index) would possibly solve the problem. Separating the steps would possibly also benefit Postgres…

If you fix it for Postgres too (after Mysql, LOL) I am open to test it on my Postgres database.

Implementation should not be complicated, and low risk since we respect the same flow. Is this feasible at short time?

This would possibly benefit all databases and speed up inserts.

csimon3 · September 8, 2023, 1:30am

Even simpler:I created a procedure to delete the records.

You could replace lines 457 to 483 of MySQLIndex.cpp by a “call DeleteResources(${id})”.

If you can provide me with a version to test it, I would be very happy.

I tested it and delete resource for a study of 1600 images took less than one second, while deleting resources from a study of 230 images using delete from (temporary) table took me more than 4 minutes…

CREATE DEFINER = root@localhost PROCEDURE DeleteResources(
        IN p_id BIGINT
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
declare v_internalId bigint;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR
		SELECT internalId FROM Resources WHERE internalId=p_id OR parentId=p_id 
        				OR parentId IN (SELECT internalId FROM Resources WHERE parentId=p_id) 
                        OR parentId IN (SELECT internalId FROM Resources WHERE parentId IN (SELECT internalId FROM Resources WHERE parentId=p_id));
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
set done=0;
OPEN cur1;
  REPEAT
  FETCH cur1 INTO v_internalId;
  IF NOT done THEN
    DELETE FROM Resources WHERE internalId=v_internalId;
  end if;
  UNTIL done END REPEAT;
CLOSE cur1;
commit;
END;

alainmazy · September 9, 2023, 9:27am

Hi Cédric,

Thanks a lot for all these tests and suggestions. Unfortunately, I’m quite overwhelmed by lots of other stuffs these weeks so I have no real time to dig into it.

I had a quick look at your last suggestion of replacing 457 to 483 of MySQLIndex.cpp by a “call DeleteResources(${id})”. The last 2 lines can not be removed since they, amongst others, tell the “file storage” part of Orthanc that files should be removed from the storage. If we remove them, we’ll leak files …

    SignalDeletedResources(output, manager);
    SignalDeletedFiles(output, manager);

The SignalDeletedFiles gets data from the DeletedFiles table that is populated by triggers → it should still work with your procedure.

The SignalDeletedResources gets data from the temporary table DeletedResources. Could you possibly modify your procedure such that it creates and populates this temporary table with the resourceType, publicId of the deleted resources ?

Thanks again for your help and again sorry for not having time to investigate in details right now.

Best regards,

Alain.

csimon3 · September 9, 2023, 2:00pm

Hi Alain,

Then you could just leave the temporary table and only replace the “delete” command with the "call " command. Actually the “delete” is the main issue at this time.

We are trying to compile the MySQL plugin but had no luck this far. This is a critical issue for us.

If you could send us a compiled version of the MySQL plugin, with the “delete” command at line 477 with the “call” command to the new procedure, we would be very happy, an we could test it (and move it to production). The database update (creation of new procedure) would be done manually, until you include it to you version upgrade process.

Many thanks in advance,

Cedric.

csimon3 · September 9, 2023, 2:18pm

Here is the code of the procedure, including (and using) the temporary table.

It is more efficient to create it there so I don’t have to select the data again.

CREATE DEFINER = root@localhost PROCEDURE DeleteResources(
        IN p_id BIGINT
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
declare v_internalId bigint;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR
		SELECT internalId FROM DeletedResources;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
set done=0;
CREATE TEMPORARY TABLE DeletedResources SELECT * FROM (
SELECT internalId, resourceType, publicId FROM Resources WHERE internalId=p_id OR parentId=p_id 
OR parentId IN (SELECT internalId FROM Resources WHERE parentId=p_id) 
OR parentId IN (SELECT internalId FROM Resources WHERE parentId IN (SELECT internalId FROM Resources WHERE parentId=p_id))) AS t;
OPEN cur1;
  REPEAT
  FETCH cur1 INTO v_internalId;
  IF NOT done THEN
    DELETE FROM Resources WHERE internalId=v_internalId;
  end if;
  UNTIL done END REPEAT;
CLOSE cur1;
commit;
END;

alainmazy · September 12, 2023, 7:14am

Hi Cedric,

Here is a procedure to rebuild the osimis/orthanc docker images with a modified MySQL plugin:

run hg clone https://hg.orthanc-server.com/orthanc-databases/ repo locally
modify the code
add this content in a Dockerfile at the root of your cloned repo

FROM osimis/orthanc-builder-base:bullseye-20230814-slim-unstable as build-plugin-mysql


COPY . /sources

WORKDIR /build
RUN cmake -DALLOW_DOWNLOADS=ON -DCMAKE_BUILD_TYPE:STRING=Release -DUSE_SYSTEM_GOOGLE_TEST=ON -DUSE_SYSTEM_ORTHANC_SDK=OFF /sources/MySQL
RUN make -j 4

FROM osimis/orthanc:23.8.2

COPY --from=build-plugin-mysql /build/libOrthancMySQLIndex.so /usr/share/orthanc/plugins-available/
COPY --from=build-plugin-mysql /build/libOrthancMySQLStorage.so /usr/share/orthanc/plugins-available/

run docker build -t osimis/orthanc:test-mysql --progress plain .

you can then use the osimis/orthanc:test-mysql image exactly as you would use the osimis/orthanc image

Hope this helps

Alain

alainmazy · September 13, 2023, 11:40am

Hi Cedric,

I finally found some time to introduce your procedure in the code in a dedicated branch (commit)

And I have just published a test docker image osimis/orthanc:test-mysql.

I’ll let you test and review.

Best regards,

Alain.

csimon3 · September 13, 2023, 9:40pm

Hi Alain,

Merci beaucoup!

We tested it with our database with >4 millions images, and it took less than 4 minutes to load a 2,000 images MR. Previous to the fix we stopped it after waiting for 1 hour…

Since you need the “temporary” table after the procedure has run, for better code readability I propose to leave the temporary table creation inside the “C” code.

Also it seems you removed the DROP TEMPORARY table, causing error at 2nd run (table already exists). We solved it by adding the drop table to the procedure, but I think the best solution would be to leave the create and drop temporary table as before and remove the create from the procedure. So the procedure only replaces the “Delete” statement that was causing the issue.

A similar procedure would probably benefit Postgres as well. I am not a Postgres expert, but it should be easy to do.

We could use the docker container as temporary solution, pointing to the database and files outside the container, but what are we missing to be able to use the updated plugin with openSuse Tumbleweed?

I copied the plugin “.so” files but it lacked dependencies… “Elementary, dear Watson” you will say…

Many thanks for your time Alain.

Cheers from Nicaragua (Central America).

Cedric Simon.

P.S: Je suis un infirmier (belge) tombé dans l’informatique il y a longtemps, aussi j’ai du mal a compiler en C, que je n’ai jamais étudié…

csimon3 · September 14, 2023, 8:01pm

Finally we noticed that setting OverwriteInstance=false allowed to “skip” the delete phase and “resolve” (workaround) the issue.

Since they only resend studies in rare occasions, this will do the trick. I assume most clients have it set to false, and that’s why issue did not show up earlier.

I hope you will implement the use of the SQL procedure (using a cursor) for MySQL and Postgres, to resolve this issue in the event a client want to use OverwriteInstance=true with and OrthanC instance with lots of studies.

If you want us to do testing of that change, feel free to let us know and we will do it.

Thank you very much Alain for your time and dedication. Once fix goes to production we will certainly update OrthanC, and we are happy we could, in some way, help the OrthanC community.

alainmazy · September 18, 2023, 7:38pm

Hi Cédric,

Thanks for this info and suggestions again → I will check them more in details when I go back to these DB stuffs - seems there is no hurry now that I know that you have a workaround !

Alain

PS: Meilleures salutations de Belgique et merci pour les recommandations pour améliorer la santé de notre DB, on sent qu’il reste des traces de l’infirmier

csimon3 · January 15, 2024, 2:43am

Hi Alain,

I upgraded to version 12.2.2 (openSuse Tumbleweed), but I see that the MySQL plugin is still in 5.1 (without the fix).

Since we have setup OverwriteInstances=false, we only have the issue when we need to delete a study (incompletely transferred, that needs to be resent). As of today we need to perform this at night since it take about 30 minutes to delete a single study.

I wonder why other user did not report this earlier (and same issue exists with Postgres)…

Actually we have this issue only with a server storing mainly MR all with full details (about 7 millions images, 170 millions “maindiconmtags” entries, in less than 2 years…).

Do you have a date planned for releasing a new version of MySQL plugin?

If I can help in any way, please let me know.

Many thanks in advance,

Cedric Simon.

alainmazy · January 15, 2024, 11:30am

Hi Cédric,

No, sorry, I still don’t have implemented the fix - it’s still in my todo.

I’m working on DBs right now but more on the PG plugin. I’ll try to provide you with a beta version of MySQL when I do release a beta version of the PG plugin - do not hesitate to ping me if it’s not done in the next 2 weeks.

BTW, I have tested deleting studies on an Orthanc instances with 100 millions images and that was quite responsive (note: it is using PG).

Best regards,

Alain.

alainmazy · January 22, 2024, 2:34pm

Hi @csimon3

Could you try with this image: osimis/orthanc:improve-mysql-delete-unstable.?

Note that you might have to redefine the SQL procedure yourself since your DB schema might already be in version 6.7 since you have already run a beta version previously.

The SQL procedure is available here.

Note that I have also added a debug log at startup to measure the DB latency. This might be interesting to check your value there since, in general, your setup seems much slower than other systems:

Best regards,

Alain