Plexmediaserver on linux just constantly corrupts its own database

[zyvod]

Server Version#: 1.19.5.3112-b23ab3896 (installed via apt repo)

I’m running what I think is the latest plex package, but for the past few months I’ve noticed this issue where plex just corrupts its own database like once a week, and I have to go do the recovery steps listed in the support wiki. once, this corruption was so bad I had to rescan like half my media library, and it lost all my show watched information.

I see plex is using sqlite3 internally for these DBs, which isn’t really a great choice, but should at least be data safe. it seems pretty obvious that plex is doing something unsafe with sqlite. Any chance the devs could replace it with something decent like PostgreSQL, or at least go through the upstream guide on how to avoid db corruption? (https://www.sqlite.org/howtocorrupt.html)

[pshanew]

@zyvod

Any chance the devs could replace it with something decent like PostgreSQL

There’s an existing feature suggestion for this; based on the number of votes it doesn’t seem to be a very popular one. You may want to vote over there though.

[REQUEST] Move from SQLite to MySQL or Postgres

I would like to request that the metadata database com.plexapp.plugins.library.db move from SQLite to MySQL or Postgres ( Or any real external db for that matter).  I feel that this would bring additional stability and repair functionality for large collections.  Additional if this database was available as an external shared resource it would allow for additional clustering and high availability functionality.  

[nokdim]

How many items do you have?

I’ve ran Plex on Linux for years and yeah once a year sometimes twice I get some corruption but mainly when tons of items are added at the same time.

[ChuckPa]

If I may augment here ?

Plex Media Server - SQLite - MySql

I have noticed that when a Plex Server has over 10k content that there is constant issues with the SQLite database. Sometimes it is just lag, but moreover crashes and lockups happen more often. I have tracked down the process cause and it is the database not Plex. When will Plex have an option or switch to MySQL or similar database? This should be a top issue on the developer todo list as it is the main cause of stability issues. I have confirmed the former on over 30 Plex installations. Furt…

[zyvod]

Ahhh chuck/nokdim, I’m getting close to that number for sure. just did a quick check, 2574 video files, 5017 music files. it def feels like a sqlite3 issue, it’s just not appropriate for this kind of workload.

note that this is hosted on a zfs raid10 pool, and it has never once noticed any actual corruption of the files from Linux’s point of view, so this is definitely something that sqlite3/plex is messing up.

[ChuckPa]

I know it may seem like a SQLite3 issue, but it’s not. (see referenced link).

Thanks for sharing that you’re using ZFS. I know know what the problem is.

I presume you have it set to compress (default). This is the root problem.

PMS is in and out of the database in VERY fast & quick transactions.
Simple fact - ZFS can’t keep up with the transactional load in most cases.

Test: Move it to Ext4, XFS, or turn the compression OFF. You’ll instantly see a difference.

Why this is so? ZFS is supposed to know when to give up attempting to compress the data. It doesn’t adapt quickly enough. By that time, it’s too late.

[zyvod]

hmmm, those are very odd blanket statements, given I’ve got a 4 drive raid10 made of SATA SSDs with an NVME SSD in front as a ZIL and L2ARC. I’ve certainly had nothing but fantastic IO performance with everything else on my system, including other databases.

If there were IO speed issues due to having compression enabled, the database should still just queue up writes and finish them as fast as I can. it should never lose data or corrupt itself, unless things are being done very very wrong. I’d just expect user sessions to get a little slower in that case, not have the database destroy itself.

in any case, I’ll make a ZFS mount with compression turned off for the plex DBs, but regardless, I’d suggest you have some serious bugs to fix.

[pl_5309]

Could you clarify if the Plex directory is local to the host? If you are running on a network share then it will still get corrupted as well.

[zyvod]

yep, it’s a local filesystem. and while I’ve been running ZFS for years, this problem has only started occurring the last few months, so it appears to be a relatively recent regression.