So I’ve been working on issues with my slow queries with my Plex database. I don’t have any kind of corruption.
System Information:
Running ZFSonLinux 0.8.2. My root pool which also has /var/lib/plexmediaserver dataset on it consists of six 2TB WD Red SA500 SSDs in a raidz2 (think raid6) configuration sitting behind a LSI SAS3 controller.
System has 64GB of ram and 32GB is allocated to ZFS ARC (cache).
The ZFS plexmediaserver dataset has a recordsize of 16K, atime=off, compression=lz4.
In an attempt to optimize the SQLite3 databases, com.plexapp.plugins.library.db, com.plexapp.plugins.library.blobs.db, com.plexapp.dlna.db, I’ve converted them to a page size of 16384 (16K) and increased cache size to be larger than the page count.
Settings and stats for com.plexapp.plugins.library.db:
PRAGMA page_size = 16384
PRAGMA cache_size = 100000
PRAGMA page_count = 18165
select count() from media_streams = 222604
select count() from media_items = 90325
Some of recommended that the problems only went away once they put the database on a ram drive. I think there is some other issue going on. Either the way Plex does it’s queries or something. ChuckPA said it should be related to file system preformance:
I just don’t think that is the case. All the performance tests I’ve been running shows my storage performing excellently. I’ve run many fio tests with performance showing what you would expect with multi drive SSD storage.
My gut feeling is that SQLite3 is the wrong database to use for large Plex installs. I know users have been clamoring for support of other databases such as MariaDB, Postgres, etcetera for years. I think it would only be beneficial for the developers to add support for other databases.
I’ve turned off compression copied the databases to ensure they were not compressed and it made no difference. Still loads of slow query errors during the scanning process.
Are there any official steps that I can go through to request adding other database support such as MariaDB to Plex? Part of me thinks this is bigger problem than what the developers are aware of and once enough people start clamoring for alternative database support the developers might actually take this up. Perhaps a change.org petition?
So I moved my database to a tmpfs ram drive for the last few days to test things out before moving it back to the SSDs. I’m was still seeing slow queries. So file system speed is not the answer. So could something be wrong with the database structure? Is the database missing index(s) it would need to speed up queries?
Yep, I’ve done the DB export, import several times through this process and I’ve still seen the slow queries. Even optimizing the ZFS dataset to align with the page size of sqlite. Even a ram drive was not fast enough.
Well, Debian Stretch really. I put the DB on tmpfs ram drive so that should be magnitudes faster than just about any file system on SSD or HDD. BTW, people on reddit Plex are also having these problems. NVME drives have not made a difference either for those on reddit.
I cannot change my core operating system to Ubuntu. I can if it’s absolutely necessary spin up a virtual machine and install Ubuntu on it and run it on Ext4. Though the KVM VM will be running on top of the current machine.
You can also click on the link I posted above and I have two iostats from ZFS showing almost all file system access being under 1ms during when the system shows slow queries.
So really the issue cannot be the file system. It has to be something else with with how the database is designed against large Plex installations.
How the DB is designed -versus- how much data is indexed ?
I agree. I previously chatted with the actual designer of the database. When I told him the sizes involved, he flat out sold me that “bad things will happen”. I take that to mean that the typical home user , which is what Plex is designed for, was never expected to have that much media.
Can that be explored, sure.
Should it be determined that a full DB overhaul is needed, meaning the code itself needs major overhaul, will they undertake it for select few?
It’s going to come down to “How many users are impacted?” question.
That would be an interesting post to put up on reddit to find out how large some of these installations are. I know most users probably have no idea this is occurring unless they look at their logs. I can still navigate the UI and do things with Plex. Things may not be quite as responsive as it could but, but most people won’t even notice.
I know for me personally, with my large library of physical discs that have been converted over the years, I’m an edge case to actually have this much data:
select count() from media_streams = 222604
select count() from media_items = 90325
Number of video files: 20794
But for those that get their data from other sources, my data size is probably fairly typical to most of the larger users. I’m not even indexing music. Some users music collections would grow into the tens of thousands of songs.
Let me post up on reddit to see if we can get better numbers.
So, from a database standpoint, can we find out what size that developer feels like would be a max size before things start going bad?
If Plex does not want to go the route of using a different db, and they think the size is just too large, then really there should be some documentation around what the max database size is. So that users can account for that by either limiting their data set, or running one Plex Server for TV and one for movies.
Working with Chuck, we think we finally came to the conclusion that I was CPU bound for the queries even though he initially said that the slow query errors are generally related to filesystem performance. Even with putting the database on a RAM drive, I was still getting those slow query errors. My Plex is still responsive and works well. I’ve stopped monitoring the logs at this point as I’ve optimized sqlite as much as I can.
My Xeon E5-2650 v2 server processor is fairly old, so I can see that I probably am CPU bound. I was going to wait until I upgrade to an Epyc to look at at the logs.
I believe what has to be made is some database has to be made the contains how fast the processors are compared to the size of the database.
Chuck also mentioned that Plex has tested databases ten times the size of mine and the database still worked but the design of the Plex database was not meant for libraries that large.
Hmm… I don’t think it’s CPU bound though unless sqlite is running single threaded maybe? Though sqlite could run multithreaded.
My install runs on a Ubuntu VM. It runs on top of ESXi 6.5. The server has dual XeonE5-2620, with a total of 12 cores between both CPU’s. Hyperthreaded ESXi has 24vcpu’s, all of which have been given to to the PlexVM. There are no other VM’s on the box.
When I look at CPU utilization even with heavy use, I never see above 10% utilization since I have a P2000 for hardware encoding.
So if it is cpu bound,I think it would have to do more with sqlite being single threaded and tying up a specific CPU.
I believe that sqlite is single thread. Another reason Plex support mariadb would be a good thing.
I’ve watched my CPU usage as well and I could not correlate the slow queries to it. But the queries are in the milliseconds range. So I probably would not see it in top/atop/etc.
)