Backups: Improve commands to better handle large index dumps

[dsuresh-ap]

It appears that if the SQL db is too large, the backup command silently fails when writing the buffer to string.

I have 2 photoprism containers, the one with a 1.17GB DB fails to output the dump while the 260MB DB dumps successfully.

Looking at the logic and the run time of the command, it looks like this block is not running properly. I suspect its due to the size of my DB and maybe out.String() on either 162 (both file and stdout doesn't work) or 165 is reaching some limitation. Clearly the logic is dropping off somewhere between line 152 and 169...

macOS 13.1 M1
Docker 4.15.0 (4 CPUs, 6GB RAM allocated)
photoprism:latest
MariaDB:10.10

Full context and details in the discussion.
Originally posted by @dsuresh-ap in #772 (comment)

[lastzero]

Can you also verify if this could be a macOS / Apple Silicon specific issue? From what I know, Docker services are running inside a VM there, so the effective "hardware" specs might be different.

[dsuresh-ap]

Yes I tried this on a MacBook Pro running Intel on a dev photoprism build and was able to reproduce. Looks like in this case I do see a log for Killed. Now that I reproduced it maybe I can fix it if Go supports byte streams or something similar.

\u@:\w$ ./photoprism backup -i
INFO[2023-01-23T06:29:55Z] config: case-insensitive file system detected 
DEBU[2023-01-23T06:29:55Z] config: running on 'Intel(R) Core(TM) i7-7920HQ CPU @ 3.10GHz', 4.1 GB memory detected 
DEBU[2023-01-23T06:29:55Z] settings: loaded from /go/src/github.com/photoprism/photoprism/storage/config/settings.yml 
INFO[2023-01-23T06:29:56Z] PhotoPrism® needs your support!              
INFO[2023-01-23T06:29:56Z] Visit https://photoprism.app/membership to learn more. 
DEBU[2023-01-23T06:29:56Z] config: successfully initialized [53.533589ms] 
INFO[2023-01-23T06:29:56Z] writing SQL dump to /go/src/github.com/photoprism/photoprism/storage/backup/mysql/2023-01-23.sql 
Killed

[lastzero]

What is your memory limit for Docker? Note this is a separate setting in Docker Desktop. It's not your total system memory.

[dsuresh-ap]

The Docker memory limit on my Mac mini it was set to 6GB, on this MacBook Pro it was set to 4GB. I raised my MBP limit to 8GB and it worked but still think it's an issue because the plain mysqldump command works fine, the issue is when the output goes through the Golang ByteBuffer.

[lastzero]

It seems possible that the command requires twice the amount of memory due to the conversion or output handling.

[lastzero]

From my understanding, these changes should write the sql dump directly to a file or to stdout, so no buffer is needed.

I've started a new development preview build for you to test this. Any help is greatly appreciated, as it allows our team to work on other issues that our community is waiting for. Thank you very much!