Orthanc and YugabyteDB

General
1

Like that Orthanc user in 2024, I’m trying to use YugaByte instead of postgresql. In fact, I’m already using YB with Orthanc 1.12.6, without problem, albeit it’s much slower. Sadly, trying to update to Orthanc 1.12.10 is not possible.

I understand that this is not officially supported by Orthanc, but you might have the answer to some of my questions. Fyi, YB is postgresql compatible, but one can quickly find incompatibilities when testing with Orthanc. I currently see 3 problems.

First, I had to modify some migrations or to run them manually in the yb shell. Here are some examples:

  • `seq INTEGER PRIMARY KEY AUTOINCREMENT,` is not supported, but `seq SERIAL PRIMARY KEY,` is ok.
  • 2 blocks in `PrepareIndex.sql` where you use `CREATE INDEX` inside of a transaction, which is not supported.

Second, I get this message one time per second:

DB HOUSEKEEPING postgresql-index:/BaseIndexConnectionsPool.cpp:54] Exception during the database housekeeping: Error with the database engine

(I also saw that message with YB + Orthanc 1.12.9.) I understand that this is related to the statistics, but I don’t know the actual problem. There’s no SQL error or anything that I can use to find the cause. Maybe it’s related to the next point.

Third, you optimized the stats last year by using `ctid`. This is not supported by YB. When I load the OE2 page, I get this error 11 times.

HTTP-10 postgresql-index:/DatabaseBackendAdapterV4.cpp:1518] An SQL transaction failed and will likely be retried: ERROR:  system column "ctid" is not supported yet
CONTEXT:  SQL statement "WITH rows_to_delete AS (
    SELECT ctid
    FROM GlobalIntegersChanges
    WHERE GlobalIntegersChanges.key = statistics_key
    LIMIT 10000
),
... 20 more lines ...

The message pretends that `ctid` will be supported “later”, but iiuc `ctid` makes no sense for that kind of DB so I strongly doubt that they will ever support it.

I think some of those are easy to fix. I’m unsure about the others. What is your opinion on this matter?

2

Hello,

Our position is that, unfortunately, we have limited resources. We prefer to focus our efforts on developing new DICOM- and PACS-related features, which means supporting only the most widely used database engines in hospitals (primarily PostgreSQL). It is already challenging for us to keep the official MySQL plugin aligned with the performance of the PostgreSQL plugin.

That said, you are welcome to contribute by maintaining a YugaByte plugin by yourself. As the entire Orthanc ecosystem is free and open-source software, we encourage community contributions. You may also consider hiring a service company to investigate this further, as it appears to be more aligned with an enterprise-level project.

Regards,
Sébastien-

1 Like
3

Perfect, thank you for your answer. I discussed the issue with my boss and we have the same “limited resources“ problem that you have :slight_smile: They will simply forget about the “High Availability” concern that they had and stick with postgresql. Imo, this is much safer, and faster.

4

Hello @jodogne - after reading @alainmazy post about #donations, and seeing this post about the challenges of keeping the official MySQL plugin aligned with the performance of the PostgreSQL plugin, wondering if it might be time to discontinue official support for the MySQL plugin?

5

Dear Olivert,

There are several aspects to consider here.

First, the MySQL plugin is still actively maintained, at least partially, since it shares most of its source code with the PostgreSQL plugin. Our continuous integration server continues to produce nightly binaries. Second, the Orthanc core remains compatible with database plugins that are more than ten years old, including the latest official release (5.2 at the time of writing) of the MySQL plugin for Orthanc. Third, we expect that many users in real-world hospital environments prefer MySQL over PostgreSQL, as MySQL is often the entry point for many system administrators. From my point of view, removing MySQL support from Orthanc would therefore be a mistake.

It is also important to distinguish performance from features. Alain has done extensive work to optimize PostgreSQL to handle terabytes of imaging data. Such very large-scale deployments are not necessarily what users expect from MySQL. The MySQL plugin is probably perfectly adequate in many scenarios without these optimizations.

Regarding features, it is true that the latest official release (5.2) of the MySQL plugin currently lacks two recently introduced extensions in the Orthanc core (key-value stores and message queues). However, Orthanc and most of its plugins remain fully usable without these extensions.

Implementing these missing features would require significantly less effort than performance optimization. If nobody is willing to fund Alain to develop a highly optimized MySQL plugin, I could still work on an updated official release for MySQL. The reason I am not prioritizing this at the moment is that, based on my interactions with the Orthanc community, there are higher-level requests that could support scientific publications (as you know, my primary incentive is research).

If I am mistaken and an urgent MySQL upgrade is required by a research team to support their imaging workflow, please let me know.

I hope this clarifies the status of MySQL in Orthanc.

Kind Regards,
Sébastien

6

Thanks Sebastien for taking the time to write this.

I think I was worried when I saw the #donations message and started thinking of ways to reduce your team’s workload - admittedly selfishly on my part as we only use the PostgreSQL plugin.

It must be difficult to keep up with technical “debt” and deliver product roadmap priorities at the same time - we really appreciate everything the team has been able to do!

1 Like