adminExitium 173 Posted March 5 Share Posted March 5 Plex also uses SQLite and there is a similar thread for MySQL support in their forums (closed now due to attacks on the plex team like here) and they have since added a "HubCache" which caches the latest rows, any collections or playlists selected for display on the dashboard and this helps a lot with the timings. The same library loaded in Plex takes <2 seconds to load the dashboard, if cached, and <5 seconds, if uncached, because ideally not everything will get changed at once and some library or the other will be cached anyway. Link to comment Share on other sites More sharing options...
softworkz 3335 Posted March 5 Share Posted March 5 15 minutes ago, adminExitium said: It's probably one of the largest DBs on this forum atm, so I expect it to be quite slow 1 hour ago, adminExitium said: of item lists like the dashboard latest items (which take 10+ seconds on my setup) Here we get to the same crossroads like a few times before already: Are you putting this up as an argument in favor of MySQL or are you primarily interested in improvement. In the latter case, if you can share your database privately with @Luke I think it will be possible to improve this substantially. It's just that (afaik) we have never done optimization with/for large data bases like that. Link to comment Share on other sites More sharing options...
Dibbes 431 Posted March 5 Share Posted March 5 (edited) 24 minutes ago, adminExitium said: It's probably one of the largest DBs on this forum atm, so I expect it to be quite slow The count is 2169299. There are ways to speed up the loading. In my case I've been adding a bunch of NVMe drives and split things out. For example, do not have the pagefile/swapfile, the database and the cache on the same drive and make sure that the pagefile/swapfile and the cache NVMe drives do not use the same PCIe lanes. This is a snapshot from September last year: I'm not currently at home, but over the weekend I'll see what's the count is now. Edited March 5 by Dibbes Link to comment Share on other sites More sharing options...
softworkz 3335 Posted March 5 Share Posted March 5 2 minutes ago, Dibbes said: There are ways to speed up the loading. In my case I've been adding a bunch of NVMe drives and split things out. For example, do not have the pagefile/swapfile, the database and the cache on the same drive and make sure that the pagefile/swapfile drive and the cache NVMe drives do not use the same PCIe lanes. This is a snapshot from September last year: I'm not currently at home, but over the weekend I'll what's the count is now. Wasn't it an 8-figure? I probably misremembered. Link to comment Share on other sites More sharing options...
Dibbes 431 Posted March 5 Share Posted March 5 (edited) Just now, softworkz said: Wasn't it an 8-figure? I probably misremembered. Nope... I copied this image from an earlier chat you and I had Edited March 5 by Dibbes 1 Link to comment Share on other sites More sharing options...
adminExitium 173 Posted March 5 Share Posted March 5 14 minutes ago, softworkz said: in favor of MySQL or are you primarily interested in improvement 100% in improvement of the current timings with SQLite. I am not really a fan of external databases unless you need horizontally scalable apps and Emby doesn't support that anyway. In fact, I just moved a lot of my databases at work the other way (from MySQL to SQLite) to massive improvements in performance. 14 minutes ago, softworkz said: if you can share your database privately I did do that for the 4.8 beta cycle and the speeds did go down to 6-8 seconds on average briefly for a few beta releases but they have increased back to 10+ second timings (still a performance improvement over the ~15 seconds of the 4.7 release). Will hand over another copy in a few days now that the 4.9 beta cycle has started properly. 1 Link to comment Share on other sites More sharing options...
adminExitium 173 Posted March 5 Share Posted March 5 16 minutes ago, Dibbes said: ways to speed up the loading This is currently on an Intel Optane drive in Linux. I have also tested it out on a RAM-backed filesystem, to rule out any I/O issues, to not much improvement so I don't think I can do much hardware-wise to improve the timings. Link to comment Share on other sites More sharing options...
softworkz 3335 Posted March 5 Share Posted March 5 4 minutes ago, adminExitium said: I did do that for the 4.8 beta cycle Ah, great! There are always ways for improvement, it just depends on how far one would want to go (given that it's not the avearage case). But i'm not really familiar with the subject in detail, it's something for @Luketo explain why and what is taking som much time. 1 1 Link to comment Share on other sites More sharing options...
Dibbes 431 Posted March 5 Share Posted March 5 4 hours ago, adminExitium said: This is currently on an Intel Optane drive in Linux. I have also tested it out on a RAM-backed filesystem, to rule out any I/O issues, to not much improvement so I don't think I can do much hardware-wise to improve the timings. You put the cache and database on separate drives or are these still on the same stick? Link to comment Share on other sites More sharing options...
adminExitium 173 Posted March 5 Share Posted March 5 18 minutes ago, Dibbes said: the cache What do you mean by the cache here exactly? The cache folder for Emby? That's also on a RAM-backed temporary filesystem. Link to comment Share on other sites More sharing options...
sfatula 185 Posted March 5 Share Posted March 5 10 hours ago, softworkz said: Yes, foreign key constraints, sometimes coluimn data constraints, indexes, also partial indexes. Triggers only for full-text index updating IIRC. Also curious more than anything, if you have the time. For a given screen or task, Is Emby primarily using lots of small queries to populate a screen, or, is Emby primarily doing a multi-table query to obtain all the results needed? The former would be bad when using external database. Link to comment Share on other sites More sharing options...
softworkz 3335 Posted March 7 Share Posted March 7 6 hours ago, sfatula said: Also curious more than anything, if you have the time. For a given screen or task, Is Emby primarily using lots of small queries to populate a screen, or, is Emby primarily doing a multi-table query to obtain all the results needed? The former would be bad when using external database. It primarily uses complex queries to get all information at once. But the more complex queries get, the less predictable will the execution plan generation get and optimizing becomes hard. Also, certain joins cannot be done economically, or the acquisition of certain data depends on business logic. So, the answer is: both cases exist and when small queries take negligible time with SqLite, there was never a reason to care or worry about those. Another example is image loading: When the client displays a list ot items with images, it retrieves the data from the server first and the client requests images from the server's image service. For each image request, the server queries the database again to where the image paths are stored. Let's assume that the view displays 30 images. In this case, there's not only the db request(s) for the data but possibly also 30 requests for the images. With SqLite, this is nothing to care about, because there is zero latency, but with an external DB, that's whole different story. When you develop for an architecture with an external DB, then you would probably do something like caching of the data from the initial data request, knowing that it's likely that the data will be needed shortly after when the image requests are coming. Countless of such cases exist everywhere which would need to be done differently, and that's what makes this an even more toxic task. 1 Link to comment Share on other sites More sharing options...
Dibbes 431 Posted March 9 Share Posted March 9 (edited) On 3/5/2024 at 9:44 AM, softworkz said: Wasn't it an 8-figure? I probably misremembered. Here's one from today: I'll be cleaning up over the next few weeks though, splitting this into 2 servers... I'm running out of space on my main server and backups are a nightmare Edited March 9 by Dibbes 1 1 Link to comment Share on other sites More sharing options...
bakes82 90 Posted March 10 Share Posted March 10 On 3/5/2024 at 3:37 AM, adminExitium said: Plex also uses SQLite and there is a similar thread for MySQL support in their forums (closed now due to attacks on the plex team like here) and they have since added a "HubCache" which caches the latest rows, any collections or playlists selected for display on the dashboard and this helps a lot with the timings. The same library loaded in Plex takes <2 seconds to load the dashboard, if cached, and <5 seconds, if uncached, because ideally not everything will get changed at once and some library or the other will be cached anyway. Yeah plex does a lot of things better and if you run the plex db optimization script on GitHub that reorders/indexes the db it’s even better. Link to comment Share on other sites More sharing options...
bakes82 90 Posted March 10 Share Posted March 10 On 3/6/2024 at 9:50 PM, softworkz said: data from the server first and the client requests images from the server's image service. For each image request, the server queries the database again to where the image paths are stored. Seems like a poor design and should be returned with the base payload. No reason to be calling the db for every image path when you just called it to get the details of all the items to display. Images should be part of that. Link to comment Share on other sites More sharing options...
TMCsw 122 Posted March 10 Share Posted March 10 So much better... 2 3 Link to comment Share on other sites More sharing options...
softworkz 3335 Posted March 10 Share Posted March 10 1 hour ago, TMCsw said: So much better... 1 1 Link to comment Share on other sites More sharing options...
ebr 14913 Posted March 10 Share Posted March 10 10 hours ago, bakes82 said: Seems like a poor design and should be returned with the base payload. No reason to be calling the db for every image path when you just called it to get the details of all the items to display. Images should be part of that. On the contrary, it is exactly the proper design. 1 Link to comment Share on other sites More sharing options...
jaycedk 382 Posted March 10 Share Posted March 10 12 hours ago, bakes82 said: Yeah plex does a lot of things better and if you run the plex db optimization script on GitHub that reorders/indexes the db it’s even better. 12 hours ago, bakes82 said: Seems like a poor design and should be returned with the base payload. No reason to be calling the db for every image path when you just called it to get the details of all the items to display. Images should be part of that. Just go back to that guy, you sorely miss him. Ohh boy, now i'm feeding the amimal in the cage. 1 Link to comment Share on other sites More sharing options...
Dibbes 431 Posted Tuesday at 06:26 PM Share Posted Tuesday at 06:26 PM On 3/10/2024 at 3:49 PM, jaycedk said: Just go back to that guy, you sorely miss him. Ohh boy, now i'm feeding the amimal in the cage. I see an ice bucket challenge coming up for this specific gremlin... what's the worst that could happen Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now