evaluating PSU - postgresql out of memory

[Ingo.Th]

I'm currently evaluating PSU Server Edition for personal use. PSU runs on a M2pro Mac Mini, Database(Postgres 15.4) on a Linux VM(20GB RAM, 4 CPU, 200GB Disk)
Start with a catalog with around 40000 Images which i want to import from my NAS.

Start an import process which failed after around 2000 images, with broken connection to the database. DB killed with out of memory.
Here I checked what goes on. I use the database for other applications to and run never in this kind of problem.
So I tried a few things to overcome it.
- optimize memory usage in postgres.
- extending memory ( more memory means images imported before db get killed)
- watch the memory consumption on the db server if it goes critical, stop the process in PSU. Restart PSU(to cleanup the session to the DB) continue the process.

nothing really solve the unnatural memory consumption.
If you run the postgresql on windows, the default virtual memory is much larger, so you see here less problems with this. Switching of Swap I think you will get the same behavior as on Linux. Memory on disk means slow performance.

So I checked the statements fired against the database. And then I thought, what goes one here.
Every select-statement I see, is fired as a new prepared statement. Which is complete in memory until is explicitly freed up, or session terminates.
Example of generated query from monitoring the database.

Code: Select all

prepare prepst87755  as select  * from    idMultiUserNotification where   TimeStamp > (to_timestamp(1692187533.798) at time zone 'UTC' ) order   by TimeStamp asc
prepare prepst87764  as select  * from    idMultiUserNotification where   TimeStamp > (to_timestamp(1692187551.609) at time zone 'UTC' ) order   by TimeStamp asc
prepare prepst87781  as select  * from    idMultiUserNotification where   TimeStamp > (to_timestamp(1692187578.326) at time zone 'UTC' ) order   by TimeStamp asc

3 times the same query with one parameter. But 3 prepared statements, no reuse of it, so use of memory with no meaning until db session terminates or manually discarded. This means, if you let PSU long enagou run, it will exhaust the db memory.

Until this problem isn't solved, I don't migrate to PSU. I'm a little bit curious about this, doesn't no one use postgres as database? Or do you simple us so much memory to avoid this? I would call this a bug, that's makes the software in this combination unusable.


Ingo

[HCS]

Hi, i do use Postgres as my database. I'm running it natively on my system (Windows), not in a VM. I'm curious to your config settings for Postgres.

Could you share some of the memory and process settings so we can compare notes?

It may be that on initial import you need to break up the images in batches. After all, in the end, there is an end to the resources

Other than that, i'm hosting well over 150K images in my database and it never so much as blinks an eye on any query or update.

[bimo]

Ingo,

IMO your evaluation is perfectly right.

It is not only a problem when importing but also if syncing large numbers of images. With 64GB RAM I can manage about 4k images on Win before memory exhausts.

Never before did I notice ever growing memory consumption with each and every item being processed like in PSU.

(Most of importing and labeling I did years ago with the SQLite version and can't remember to have had that kind of behaviour. Nowadays I'm dealing primarily with filtering and viewing my 200+k images. That's why I can afford living with the bug...)

[Hert]

If your database consumes too much memory then check the database configuration settings. Lower max connections or work memory or cache memory settings. Google on the subject to find more suggestions for memory issues with PostgreSQL on your platform.

Google on

Code: Select all

postgresql out of memory

Here’s a random hit
https://italux.medium.com/postgresql-ou ... sk%20files.

[RobiWan]

Start an import process which failed after around 2000 images, with broken connection to the database. DB killed with out of memory.

In my opinion, the 20GM RAM alone is not enough for PSU. I have always had problems with PSU in the past. No matter how much RAM the VM got, at some point it was over. PSU is not able to stage the import or update. Say after 1000 images, commit and again 1000 images. It takes memory until it is completely full or import/ Update is done.
My only solution that works for ~2 years - 48GM RAM AND more importand 2TB SWAP Space. In this way PSU or PostgreSQL can swap out used memory.

[Hert]

I run PostgreSQL on a Windows server and never have any memory issues (16GB internal memory and 512GB SSD drive).
All reports that I have received about PG memory issues were all about PG instances that were installed on Virtual Machines with Linux and hence I expect this to be related to that combi.

Another tip that I found during an online search: check the idle_in_transaction_session_timeout setting
https://www.postgresql.org/docs/9.6/run ... ON-TIMEOUT

[RobiWan]

I know. You had already told me that. I cannot confirm it. With my dataset I had 100% same behavior with Linux VM, Windows Server 2019 VM and Windows Server 2019 as dedicated machine.
The database specialists at our company told me that it is normal behavior when the application does not do any cleanup actions in between. It doesn't matter if it's PostgreSQL or MS SQL.

[aaronj]

I had reason to be looking at SQL activity related to a separate issue but I think I noted what is the key issue with Photo Supreme and PostgreSQL specifically.
Note that you don't have to do anything intensive to exhaust memory, you just have to do enough in a single session. If you do something so simple as leave a slideshow running, for example, memory usage creeps up and will unavoidably eventually exhaust the server's memory, how long it takes is just a question of how much memory you have.
The issue is the use of prepared statements without any corresponding deallocation in most cases. Preparing a statement reserves memory related to that prepared statement which is not released until either:
1. The session ends, or
2. it is expressly deallocated
This is clearly and unequivocally stated in PostgreSQL official documentation:
www postgresql org/docs/current/sql-deallocate.html

... so thanks to the lack of deallocations, the number of operations you can do in any session is hard limited by how much memory PostgreSQL has access to because so much ends up tied up by no-longer-needed prepared statements.
In the very small sample of activity I looked at there were 1,309 prepared statements and only 157 of them (about 12%) deallocated despite only 77 of them (6%) actually using parameters, meaning the other 94% 1. had no potential reusability to go against immediately deallocating them and/or 2. if for some reason deallocation is undesirable, they could have been run as direct queries to avoid the need to deallocate them.

Adding in a deallocation for every prepared statement once it no longer has any potential use should resolve the PostgreSQL memory usage issue.

[aaronj]

For the benefit of anyone reading here but not checking release notes, I noted that the PostgreSQL memory utilization improvements in version 2023.2.0.5063 appear to include universal deallocation of prepared statements (very nice).

Syncing/writing to about 6,600 files stayed within 1 GB of the shared buffers allocation at 6.9 GB of RAM consumed (shared buffers are configured at 6 GB). Unfortunately I don't have any ready larger workloads to test on at the moment, but will make observations if I do and no one else has yet provided similar results. I don't recall exact numbers as it has been some time, but I have exhausted 32 GB RAM + 128 GB swap somewhere around 20,000 images into a large import in the past, which is fairly consistent with the 2,000 images for 20 GB RAM in the original post.

It will take some time to get results, but I will test the one thing I noted which I can easily test and which should be a very consistent workload, which is the usage for a continuously running slideshow. I will post results if/when they are clear one way or the other (i.e. memory usage plateaus or continues to climb past amounts the server configuration would appear to permit).

[RobiWan]

Hi,

I will check this in the next weeks, so I have much more time.
Thanks for your info

[Ingo.Th]

So far no problems with memory anymore. Worked through the catalog with 30000 Files and max memory consumption in postgres is stable around 1.5 GB memory as configured in my system.

I think this problem is solved.

Currently I can't use it, because I upgraded to postgresql 16 and PSU did not t start anymore Hopefully we get "support" anytime soon.