Performance large scale

[bakes82]

How is performance going to be addressed and not be slugglish like Ombi? Can the DB changed out for MYSQL etc?

[sct]

We have people with 14k+ item libraries experiencing no performance issues. If you run into performance issues please let me know!

As for MySQL/other databases; it’s possible. I may expose configuration for it through environment variables in the future for advanced users.

[JamesTeague]

I'd actually like to see that happen for another reason outside of performance. I run all of these things in a Kubernetes cluster (not Overseerr, yet). sqlite does not play well at all over nfs. Which, in a multi-node cluster I would like not to have to tie my instance to a particular node. So, when Ombi allowed me to use a MYSQL db I jumped on it to give the pod the flexibility to be scheduled on any node that seems fit.

[JamesTeague]

I'm sure the performance is fine, and I am willing to request this in the appropriate place, if you point me in the right direction. I just would like to avoid locking issues and be able to schedule the container to run wherever and possibly scale it.

[mmozeiko]

Right. that's a better reason to want different DB backend.

Although - sqlite uses locking only for purposes of controlling multiple process access to same database file. If you're not planning to use multiple overseerr instances at the same time with same database file, then completely disabling locking would work fine when you run sqlite over nfs.

To disable locking calls you need to append ?nolock=1 string to filename. Basically in code which opens database change filename to file://filename?nolock=1 - see https://www.sqlite.org/c3ref/open.html It seems it should be done in ormconfig.js file. Not sure if JS orm library will like it. You need to try it.

[JamesTeague]

I will give this a shot. No harm, no foul. I don’t plan to have multiple instances in the near future. I will spin it up and see if I can change it in the docker container. Although, given the speed at which it is being updated I don’t want to have to do that every time I pull or new image. Or have to maintain a forked version myself. But nonetheless, I will see if it works.

[bakes82]

Well the ARRs all say the same thing ;) but then they also have issues lol but are too far down the road to fix them.

[ezkrg]

@sct any news about exposing mysql configuration? It will be very welcome. I suffered by sqlite slow on glusterfs, mysql would solve my problems. :)

Thanks for overseerr btw! :)

[teambvd]

@sct Between TV and movies, I'm at just under 35k items:

root@<mahServer>:/mnt/user/media# find movies/ -type f | wc -l
2475
root@<mahServer>:/mnt/user/media# find tv_shows/ -type f | wc -l
31867

With music though (something I've not added to any management application yet, but would be nice to have all the metadata for all media in one place someday... maybe lol), that'd put me over 70k items.

I've tuned sqlite to the extent possible for each of my 'arr's (max'd page size, set up recurring vacuum's, etc) once I hit 20k episodes as it was getting painfully slow to audit the history/activity. That brought it back to 'normal' (from ~12-15 seconds per page load, back down to ~1-2), but I've recently started seeing sluggishness just beginning to rear it's ugly head again...

While I know it's not common for folks to have that many items to manage, honestly as the support for pg/maria is already there, I don't see the harm in exposing it right? Unless enabling those environmental variables is for whatever reason non-trivial, which I'd totally understand would de-prioritize it given the limited number of folks who'd benefit, of course!

[mmozeiko]

I have done zero sqlite tuning for my overseerr (running through caddy), and have no performance issues at all. Initial load takes ~1.5 seconds and after that loading individual item pages is pretty instant. And this is for ~46K TV episodes and ~36K Movies. So in total ~82K items:

$ find TV/ -type f -iname '*.m*' | wc -l
46477
$ find Movies/ -type d | wc -l
35816

Here's Network tab from browser dev tools showing initial page load without using cached data in browser:

[teambvd]

@mmozeiko how many users do you have, and how "active" are they, if I might ask?

This is grossly over simplified of course, but any DB load could be poorly estimated as a multiplication of:

• number of active users (at any given moment)
• number of objects
• number of datapoints per object

If you only have a couple users, or if you generally dont have more than a couple users actively using the app at any point, youd probably be fine with sqlite, at least from a performance perspective. But with each additional active user, loads eventually amplify to the point that the user experience begins to degrade.

[mmozeiko]

Right, I understand that. I have around 0 users using the site at the same time. In total ~20 users, but it is being used rarely. I was just pointing out that movie count and tv show count does not matter much.

I will be very surprised if that is sqlite fault to not be able to handle multiple concurrent users. Sqlite has very good performance for cases when code needs just to read db, which is what most active users will do. If there is performance degradation with increased user count then it most likely can be fixed in overseerr code. Just need to profile it to understand where the time is spent. Blindly switching db will not fix completely fix it.

I have written web server software (not in node.js though) using sqlite that has ~20-50 concurrent users, and there was no performance issues.

[bakes82]

This is a discussion no? You tell me what the performance should be, youre making the app lol. Right now I have it on a 32c 128gb box but I assume thats not typical. Also 14k items seems kinda low. I just imported > 12k movies, and 4k tv series. (There also 4k versions but you get the idea). Performance issues dont usually appear on import, they tend to appear over time. The ARRs have DB issues and time outs with bots trying to add to them. They might not be doing good indexes. But its good to hear you can support MySql it will help out for those that want to do a dedicated DB server especially if they have large content/user sizes.

[mmozeiko]

I use Radarr v3 with ~13K items and Sonarr v3 with ~2K and have not seen any issues at all on my PC (4c haswell with 16GB memory). Radarr/Sonarr v2 were a bit unusable with these numbers - but that was their software fault, not database.
Even bumping those numbers up order of magnitude to something like 100K would be no problem to sqlite at all, if software using it is written well. You can always use database in dumb way and then anything will be slow - sqlite, mysql or oracle.
Modern computers are crazy fast. Even if sonarr/radarr would loop over 100K items on every request - that would be plenty fast. For CPU to loop over 100K items is not a big deal. High-end games easily operate real-time on millions per pixels every frame in 16msec or less (not a completely fair comparison because of GPU vs CPU, but you get my point).
I have worked on project that was storing close to half a million items in one sqlite table, and everything was running smoothly. Database was not a bottleneck at all.

[obsidiangroup]

I'd like to see MySQL configuration option exposed for multiple reasons, but the main being that those of us who do use overseerr in a docker swarm and use NFS mounts can run into problems with database locking. And using ?nolock=1 doesn't entirely fix this option. Radarr has, in it's nightly versions, support for an external DMBS (PostgreSQL) and while I'm not a big fan of PostgreSQL, either MySQL or PostgreSQL is better than SQLite.

As I've said in Plex forums, if the DB is handled by an abstract layer, using any RDBMS should be no problem. The problem becomes when applications (like Plex, Emby, Jellyfin) so tightly tie their products to SQLite instead, and refractoring becomes an issue. But looking at how Olaris, for instance is handling database functions, using an abstract layer that isn't tied to any specific DB is the way to go. The default can be SQLite, as for a lot of people this is fine. But for those of us who run our services in a swarm, in a cluster, etc, and using NFS, this makes things just work (tm).

My setup, for instance, runs Overseerr in a 6 member swarm (3/ESXI, 3/Proxmox). My servers have no local storage, outside of the SSD that ESXI/Proxmox boots from, and in two cases, has a datastore just for TrueNAS (which in turn has the HBA passthrough to the VM for complete control). The datastores are all iSCSI for the VMs, and NFS for media/etc. For instance, all my docker VMS have NFS mounts for /NAS/Config, /NAS/Media, and /NAS/Shared. All Docker configs/data directories are reachable via /NAS/Config/ so my containers, regardless of where they are running from, all have the exact same config. So, doesn't matter if 'pildocker02' or 'pildocker05' are down, the container can run on 'pildocker04' using the same mapping.

I do have a few things that are outside of the swarm, because of SQLite and the issues with NFS. Plex, for one (I don't run Plex as a container anyway) but because of issues with file locking, having Plex's data directory on a NFS caused too many issues I'm forced to use custom software to keep, say Plex's DB in sync across multiple nodes.

Needless to say, it's not just the size of ones database, it's more the use of NFS and the problems associated with it.

[evanrich]

FWIW, radarr and prowlarr now support postgres, and sonarr is considering it after v4 comes out, so the tides are shifting. I'd be happy to make a donation if it helps get overseerr to support postgres as well.

[teambvd]

@sct I'd like to re-phrase @evanrich 's statement above from my own view -

Would you be willing to scope Postgres support to fully flesh out how much work it'd take, and could you guess at how much would it cost to complete the scoping portion (either in $ to you, or in time from you if you'd prefer)? If you know it's trivial to scope just based on the code base, that's great, but if it takes you anything more than say, an hour, please don't hesitate to associate a $ value with it.

Then should it eventually get scoped out, could that scope also include a potential cost + way to fund it?

After reading some over the last few months, I'm trying to take a new tact with open source projects I use with how I think about their overall support as well as maint. support - while I pretty much always try throw an anonymous chip in the tip jar for any project I value, I understand even that's almost an anomaly, and shouldn't be expected to be the sole source of funding for all enhancements/feature requests as well as regular maintenance...

Basically where I'm coming from is 'if a feature is so important to folks (including myself of course!), why not ask if we've the possibility to straight up pay for it?' (with the understanding that the response may simply be 'no', due to any number of reasons, time commitments, whatever)

For the record, my preference would be postgres as I'm far more familiar with it... But I don't want that to be a barrier to entry lol

[war59312]

FYI, the docker image on Synology complains about these 2 potential performance issues:

https://nextjs.org/docs/messages/large-page-data

Warning: data for page "/" is 180 kB which exceeds the threshold of 128 kB, this amount of data can reduce performance.

https://nextjs.org/docs/messages/sharp-missing-in-production

Warning: For production Image Optimization with Next.js, the optional 'sharp' package is strongly recommended. Run 'yarn add sharp', and Next.js will use it automatically for Image Optimization.

[teambvd]

+1 for the addition of sharp - I route all my traffic through through it when running on cellular, and the amount of data saved in transit results in significantly improved performance for me.

Should probably be it's own feature request/pr though...?

(Edited to add link to benchmarks - 5x-30x better than alternatives)

[onedr0p]

Opened an issue here #3076

[goldhaxx]

I too am seeing this error using the linuxserver docker image on an Ubuntu host along with very sluggish performance of Overseerr.