+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: OS independent / Other (https://forum.kodi.tv/forumdisplay.php?fid=228)
+---- Thread: External MariaDB > 'too many joins without indexes' etc > Tuning Ideas... (/showthread.php?tid=372958)
External MariaDB > 'too many joins without indexes' etc > Tuning Ideas... - AGLyons - 2023-04-26
Hey all!
I've been using a MariaDB for a while and poking around PHPMyAdmin, I am seeing all the advisor entries for the server.
I'm wondering if there is any optimization that needs to be done to up performance. Could some of these things be related to changes that should be made to the Kodi DB infrastructure (ie, querying the DB, missing foreign keys etc)
Row Sorting:
Joins Without Indexes:
Index Scans:
reading data from a fixed position is high
reading the next table row is high.
temporary tables are being written to disk: These settings I changed to 256MB for both 'heap' and 'tmp'.
MyISAM key buffer
The query cache is not enabled
RE: External MariaDB > 'too many joins without indexes' etc > Tuning Ideas... - wags1 - 2023-04-27
Don’t know if it addresses your specific but have you looked thru this thread?
RE: External MariaDB > 'too many joins without indexes' etc > Tuning Ideas... - AGLyons - 2023-04-27
Hey, thanks for that. I went through it and made some changes. Some are reporting the variable is locked. But the most important one, at least it was alluded to in the post, was not locked and could be set to 1. I've yet to test it out and see the performance. I'll circle back later with an update.
These settings aside, the issue with joins is in the SQL query itself. That's built into Kodi and the DB structure. Ok, sure, all we're talking about here is home entertainment systems. But if we can get a better user experience by tweaking the DB, then why not? Would love it if a dev could offer some insight under the covers.
Hey @Montellese, sorry to tag you directly on this. I saw you were doing work with the importer and thought you would have the best view on this.
RE: External MariaDB > 'too many joins without indexes' etc > Tuning Ideas... - AGLyons - 2023-04-27
As a quick test, I downloaded the Windows64 build and added my media DIR using Local Info Only. So now we are not using MariaDB, and I see the same behaviour.
The status at the top right says "Scanning movies using Local Information only," and it's stuck at 0% and hasn't budged.
Some objects are getting populated, at least in the files view. But nothing is showing in the Movies menu with proper titles. There's something weird with the Local Information scraper.
Maybe I'm reaching here, but I think that scraper would be pretty popular with the large user base of the RRRs creating local info files, like myself. Is there maybe a better nfo scraper that I should be using? Could the existing Local Info scraper be taken a look at for optimizing/tweaking some things?
It would be awesome if there were some support built into it for extras like locally hosted trailers -which Radarr-Extended does automatically. It also DL's all the images (posters, fanart etc) and stores them next to the object. I'm not sure if Kodi does this already, but if it were able to match the thumbnail name in the nfo to a local object instead of grabbing the URL location in the nfo and downloading it again. That would be cool.
RE: External MariaDB > 'too many joins without indexes' etc > Tuning Ideas... - wags1 - 2023-04-27
Glad you found it helpful. Just be aware that unless you setup the correct file to set defaults the changes will not persist when your NAS, or whatever you have your MariaDB running on, reboots/restarts. IIRC I posted instructions on creating the right file, if you are using a Synology NAS, in that thread.
RE: External MariaDB > 'too many joins without indexes' etc > Tuning Ideas... - jbinkley60 - 2023-04-27
(2023-04-27, 15:27)AGLyons Wrote: As a quick test, I downloaded the Windows64 build and added my media DIR using Local Info Only. So now we are not using MariaDB, and I see the same behaviour.
The status at the top right says "Scanning movies using Local Information only," and it's stuck at 0% and hasn't budged.
Some objects are getting populated, at least in the files view. But nothing is showing in the Movies menu with proper titles. There's something weird with the Local Information scraper.
Maybe I'm reaching here, but I think that scraper would be pretty popular with the large user base of the RRRs creating local info files, like myself. Is there maybe a better nfo scraper that I should be using? Could the existing Local Info scraper be taken a look at for optimizing/tweaking some things?
It would be awesome if there were some support built into it for extras like locally hosted trailers -which Radarr-Extended does automatically. It also DL's all the images (posters, fanart etc) and stores them next to the object. I'm not sure if Kodi does this already, but if it were able to match the thumbnail name in the nfo to a local object instead of grabbing the URL location in the nfo and downloading it again. That would be cool.
Ironic that you mention local trailers. That plus much more is supported with Mezzmo and Kodi. If you are interested, here's my post from earlier today. I also have an additional tool which fetches actor artwork information for full Kodi integration. The Mezzmo Kodi addon ties it altogether. The nice thing about these solutions is the level of automation they bring.
I have a new feature under development called Mezzmo Movie Preview which will allow a theater like experience where it will play a predetermined number of trailers ahead of the main movie, allow you to select current year or release year trailers and more. I expect to release it in May and it will leverage local trailers.
Thanks,
Jeff
RE: External MariaDB > 'too many joins without indexes' etc > Tuning Ideas... - AGLyons - 2023-04-27
Hey, @jbinkley60!
This looks cool. How does it play well with Radarr and Sonarr?
I just finished getting my whole operation set up, integrating those into the mix, and it has been a godsend! I did run into an issue that I have filed with Radarr (I suspect Sonarr has the problem too). They don't correctly follow the naming conventions that Kodi is looking for, for extras and metadata in the NFO.
My biggest pet peeve with Kodi is it is so easy to blow away the DB, and when you rebuild it, the added date is the date it was imported into Kodi, aka the current date! Not the date that you actually added the item to the library. I always sort my media by added date. The newest stuff is at the top. That added date history has been blown away a number of times if there is ever a problem with the DB (whether that is internal or even an external server).
Yet, there is a solution sitting in the NFO files, <dateadded>. That field stores the date the whole object was added to the library. It's stored with the object so Kodi can reimport it as often as it wants, but it will still know when it was originally added to the library.
Radarr/Sonarr don't write that, so the problem still stands.
RE: External MariaDB > 'too many joins without indexes' etc > Tuning Ideas... - jbinkley60 - 2023-04-27
(2023-04-27, 17:20)AGLyons Wrote: Hey, @jbinkley60!
This looks cool. How does it play well with Radarr and Sonarr?
I just finished getting my whole operation set up, integrating those into the mix, and it has been a godsend! I did run into an issue that I have filed with Radarr (I suspect Sonarr has the problem too). They don't correctly follow the naming conventions that Kodi is looking for, for extras and metadata in the NFO.
My biggest pet peeve with Kodi is it is so easy to blow away the DB, and when you rebuild it, the added date is the date it was imported into Kodi, aka the current date! Not the date that you actually added the item to the library. I always sort my media by added date. The newest stuff is at the top. That added date history has been blown away a number of times if there is ever a problem with the DB (whether that is internal or even an external server).
Yet, there is a solution sitting in the NFO files, <dateadded>. That field stores the date the whole object was added to the library. It's stored with the object so Kodi can reimport it as often as it wants, but it will still know when it was originally added to the library.
Radarr/Sonarr don't write that, so the problem still stands.
The Mezzmo operating model is a bit different than traditional Kodi approaches and the Kodi database is secondary to the Mezzmo database. Mezzmo becomes the master database and the Mezzmo Kodi addon sync all of the Kodi clients. You can blow away a Kodi database and the addon will rebuild it automatically. In fact the addon completely rebuilds the database every night. You also have the option of doing an immediate client rebuild in case a Kodi client were to blow up. Here's a link which discusses the sync process more with some screen shots.
The Mezzmo server handles all metadata, artwork, trailer and more scraping, not Kodi. Here's a link which describes more about how it works. The Mezzmo artwork checker and trailer checker are just additional tools to help Mezzmo be even better. NFO files aren't really used with Mezzmo. It will import them as part of the initial Mezzmo database build but afterwards they aren't needed. The Mezzmo server handles all resume point, play count, metadata and similar sharing of data. The Mezzmo Kodi addon provides an abstraction layer between the Kodi clients and Mezzmo so you can run Kodi 18, 19, 20 etc.. all together with the same features and no worry about the underling Kodi database. The Mezzmo Kodi addon handles writing to the proper version of the Kodi database. Other peer based sharing solutions often require being on the same version of Kodi to share data due to a dependency for the underlying Kodi database number / version.
With regards to Radarr and Sonarr and such I suspect those would not be needed with Mezzmo and their functionality would be handled automatically by Mezzmo and the Mezzmo Kodi addon. My goal with the Mezzmo Kodi addon is to add major features into it to avoid folks having to run many other addons, adding complexity and creating more support. If a feature or function is requested enough, I'll look to build it into the Mezzmo Kodi addon. The feature list somewhat shows that. On all of my Kodi instances, I typically run just 1 or 2 addons, nothing else. The first being the Mezzmo Kodi addon and the second the HDHomeRun addon for Live OTA PVR support. I know the author of that addon and it wouldn't make sense to incorporate the functionality into the Kodi addon. You might want to take a look at the Library Management page to get an idea of the power of the tools included with Kodi and Mezzmo.
For your question about NFO data importing, I checked and Mezzmo does not import the date added field from the NFO file. It does support bookmarks,/ resume points last played time etc. and it's graphical properties editor makes it easy to modify the date added field. I'll reach out to the Mezzmo developers to see if they would entertain importing the date added field from NFO files.
Thanks,
Jeff
RE: External MariaDB > 'too many joins without indexes' etc > Tuning Ideas... - AGLyons - 2023-04-27
Hey Jeff,
Sounds like a great addition to wrangling multiple Kodi heads, which I do.
But Radarr and Sonarr do much more than just library management. I like where Mezzmo is going, but I don't think it can replace either of those platforms because of the other things they do, which Mezzmo does not.
The other thing is running the Mezzmo 'controller.' Running on a desktop is....OK, in a way. But many of us 'tinkerers' like our home labs. I'm running most of my operations on my Unraid server. If there was a Mezzmo docker container that would do the managing of the multiple Kodi heads DB's, that would be a good goal to shoot for.
RE: External MariaDB > 'too many joins without indexes' etc > Tuning Ideas... - AGLyons - 2023-04-27
Now that I have gone and taken a look at Mezzmo, I see what it is.
It's basically a UPNP media player that Kodi would stream from. It appears to also somewhat take over the entire Kodi instance. UI and all. In that way, it's not much different from the Plex add-on for Kodi. That also replaces the entire UI with a Plex UI.
That's not something I think many people really want. We've mostly all found the Kodi skin that we like and stick to that. Showing anything different to the wife causes future home-lab investments to encounter extreme resistance!
RE: External MariaDB > 'too many joins without indexes' etc > Tuning Ideas... - jbinkley60 - 2023-04-27
(2023-04-27, 21:47)AGLyons Wrote: Now that I have gone and taken a look at Mezzmo, I see what it is.
It's basically a UPNP media player that Kodi would stream from. It appears to also somewhat take over the entire Kodi instance. UI and all. In that way, it's not much different from the Plex add-on for Kodi. That also replaces the entire UI with a Plex UI.
That's not something I think many people really want. We've mostly all found the Kodi skin that we like and stick to that. Showing anything different to the wife causes future home-lab investments to encounter extreme resistance!
I concur that not having a Docker container version of Mezzmo is a limitation for some folks. As for similarities with Plex, Mezzmo is a UPnP server but it is quite a bit more advanced when running with Kodi. The Mezzmo Kodi addon can run in native mode (i.e. normal Kodi skin etc..) and what I call GUi mode (which is basically a content browser leveraging the Kodi UI). The addon can run in both modes at the same time and switch back / forth to get the best of both worlds.
Here's a chart which compares the features with each mode. At the bottom of the main Mezzmo Kodi addon wiki page you can see the various views. Unlike Plex the Mezzmo Kodi addon is fully integrated with the Kodi UI and doesn't create any new Windows and such. That was a key design point. You can run Mezzmo with Kodi and you'd never know it was the back-end. Here's a native mode screenshot . I am actually planning to update the Wiki with screenshots of the Mezzmo Kodi addon running with a number of various Kodi skins.
Thanks,
Jeff
RE: External MariaDB > 'too many joins without indexes' etc > Tuning Ideas... - Klojum - 2023-04-28
(2023-04-27, 22:39)jbinkley60 Wrote: Here's a chart which compares the features with each mode.
He is here to get a MariaDB problem solved. Getting a reply & lecture where just about every sentence has the word 'Mezzmo' in it, does not solve that problem. UPnP != MariaDB.