Slow PosgreSQL

Google Groups archive
1

Hello! My name is Oleg)

I ask for help on PostgreSQL.
Previously I used SQLite. We found that SQLite is not suitable for us, since we have a lot of data over 1 terabyte.

We changed our database to PostgreSQL. Then we noticed that PostgreSQL is much slower than SQLite. I mean about 4-5 times slower. It takes a very long time to search for patients, download studies, open the viewer, everything is very slow.

Why is that? Is there anything in the Orthanc configuration or in the PostgreSQL configuration that needs to be changed for better performance?

Server Configuration:
Windows Server 2016, Intel Xeon CPU E7-8867, RAM-4GB

PostgeSQL Orthanc configuration:
{
/ **

  • Configuration to use PostgreSQL instead of the default SQLite
  • back-end of Orthanc. You will have to install the
  • “orthanc-postgresql” package to take advantage of this feature.
    ** /
    “PostgreSQL”: {
    // Enable the use of PostgreSQL to store the Orthanc index?
    “EnableIndex”: true,

// Enable the use of PostgreSQL to store the DICOM files?
“EnableStorage”: false,

// Option 1: Specify explicit authentication parameters
“Host”: “localhost”,
“Port”: 5432,
“Database”: “orthanc_db”,
“Username”: “postgres”,
“Password”: “postgres”,

// Option 2: Authenticate using PostgreSQL connection URI
// “ConnectionUri”: “postgresql: // orthanc_user: my_password @ localhost: 5432 / orthanc_db”,

// Optional: Disable the locking of the PostgreSQL database
“Lock”: false
}
}

PostgreSQL configuration:
In the attachment

I will be grateful for any help, thanks!

postgresql.conf (23.3 KB)

2

Hi, Oleg!

Would you mind elaborating a little bit? Can you share your logs?

There are a great number of things that contribute to the perceived performance degradation. Logs are specially useful in that regard. Also, don’t forget to run your Orthanc in verbose mode so more information is available; after you’re done collecting the more verbose logs you can turn it off.

One thing that’s specially important is the actual architectural setup. I see your PostgreSQL rum in the same host as Orthanc. That, care must be taken since SQLite does not require a separate process. In other words, you now have more programs in need of CPU time and that can make things unbelievably slow because of this starvation scenario.

Last but not least, the pattern of requests over time, coupled with the other pointjust mentioned.

All in all, with the information I have I’d suggest you either get a couple of cores more if it’s a virtual machine or, better still, have postgresql run on another machine.

HTH,
Luiz