+- 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: v18 MySQL slow requests on tv show seasons (/showthread.php?tid=340340)
MySQL slow requests on tv show seasons - Smeulf - 2019-02-05
Hi everyone,
Yesterday, I tried to update my Raspberry Pi3 (LibreELEC) to LE9/Kodi 18. This RPi also runs a docker image for MySQL to share the database, installed with LibreELEC repositories.
After a few seconds of use, I saw that browsing the tv shows seasons was really slow, no matter the client I use (Rapsberry hosting the database, 2nd Rapsberry client only, or even my PC under Windows).
So I went back to Libreelec 8/Kodi 17 on my Pi, and made a fresh install of Kodi 17 on an another Windows PC, no addons, just Kodi to be sure.
With Kodi 17, I can see in the logs that the request for the seasons takes about 2 seconds, as usual :
- DEBUG: CVideoDatabase::RunQuery took 1152 ms for 4 items query: SELECT * FROM season_view WHERE season_view.idShow = 4
I upgraded Kodi 17 to 18 on the same PC, so it created a new database on the MySQL RPi Server, and for the same query, it's now really slower, and takes 5 to 15 seconds :
- DEBUG: CVideoDatabase::RunQuery took 5332 ms for 4 items query: SELECT * FROM season_view WHERE season_view.idShow = 4
- DEBUG: CVideoDatabase::RunQuery took 14414 ms for 4 items query: SELECT * FROM season_view WHERE season_view.idShow = 4
Can someone confirm that problem, and does anyone have any idea to fix it ? I can't wait to use Kodi 18, because of the new promisses about DRM...
Thanks!
Smeulf.
RE: MySQL slow requests on tv show seasons - Smeulf - 2019-02-05
(2019-02-05, 07:34)Smeulf Wrote: I can see you updated the "season_view" view, and I thins it's related to the "group by" conditions added on the new version of the view.Just for a test, I dropped the view from the MyVideos116 database, and created it again with the same settings as in Kodi 17, and the request now takes around 2 seconds as expected.
So the problem really comes from the extras 'group by' in the season_view definition...
I must confess I have no idea how it can impact Kodi 18 to remove those 'group by', for now i can't even see any reason to have them.
If i'm correct, it want to group by `seasons`.`idSeason` then by `seasons`.`idShow` then by... etc. But as idSeason is a unique id, I can't understand how it can group the records by any other criterion (maybe I'm missing something ?)
Cheers.
Smeulf.
RE: MySQL slow requests on tv show seasons - Mungo - 2019-02-05
Heya Smeulf - awesome troubleshooting. Save me the trouble of recreating a 17.x database to pull the view code from - can you paste what you've reverted the "season_view" view code to, to resolve this issue so I can test it? I've been having the same slowness - specifically navigating around TV series and would love to see if this fixes it before I go pulling logs and making threads.
Tracking the issue to the code being used to generate the view would explain why it occurs across multiple OS systems, and regardless of MySQL being used or the standard database
* Issue is OS independent as it occurs the same way on both my nVidia shields and my Windows box
* Issue is connectivity / share independent - tried across NFS and SMB shares
* Issue is Database type / location independent - tried on my MySQL Server for the database, and rebuilt one using the standard local database - both have the same lag*.
* Should also mentioned I also started from scratch with the latest version of MySQL (went from 5.5 to latest v8) in an attempt to resolve this. Lag still evident across both builds.
*The local database seems to be a bit quicker, but the lag is still evident and noticeably slower than previous builds.
Cheers,
Mungo
RE: MySQL slow requests on tv show seasons - Smeulf - 2019-02-06
Hey @Mungo, thanks for your confirmation of the issue we have.
Sounds like we're in a conflicting position : the view was changed to be SQL ANSI92 compliant (this commit) in order to solve this issue.
But now, we face performances problems...
What I did is just to drop the view and recreate it removing the extra group by after "GROUP BY seasons.idSeason". Sorry if I don't give you the all query, just I perfer not everyone to change it if the Kodi Team doesn't give us a green light...
@Mungo contact me in PM if you have any question.
Cheers.
Smeulf.
RE: MySQL slow requests on tv show seasons - Mungo - 2019-02-06
Thanks Smeulf,
I've loaded up a 17.6 version of kodi to generate the database and have a look around. Unfortunately clean copying the "tvshow_view" and "season_view" queries from 17.6 db v107 to v18 db v116 has had 0 effect on the lag I'm experiencing.
I've reverted back to the default queries for v18 to generate a full debug log. Timings I see from clicking select, to actually seeing the next window (give or take)
Logs are from a flat, clean default install - Windows 10 OS, x64 flavour of Kodi. Only added files are advancedsettings, sources, and mediasources xml's
Debug Log 1
TV Series into TV Season view - 13 seconds
Debug Log 2
TV Series into TV Season view - 22 seconds
Similar lag backing out of these views also.
update: Other slowness, or ... I dunno, "non updating" type behaviour I've noticed is marking things watched/unwatched is just as painfully slow, and even when things are naturally watched, the view doesn't update to indicate, until I've gone back, out of the episode list view and back into the season.
RE: MySQL slow requests on tv show seasons - Mungo - 2019-02-07
I'm surprised we haven't heard from anyone else running a MySQL back-end on this issue, considering how painful it seems to be to navigate. Are we able to get a dev to weigh in on the performance hit an external database configuration seems to have taken?
@Smeulf, what's the size of your library? perhaps it's only evident on large( r ) libraries. My individual TV series count is 298, with around 25.5k episodes.
After continuing to use v18 like this since release, it's beyond painful to continue. After a quick look into it, it appears trying to keep any previous version running and untouched on a shield or anything reporting to the play store is futile, so it looks like I'll need to revert back to local databases just to keep things usable (and the mrs from biting my head off everytime she "just wants to watch her shows") until there's a fix or usable workaround. As mentioned, there's still a noticeable lag in this area on a local db, when compared to v17 - but still completely usable and well above a MySQL configuration.
Edit, apologies - the 2 previously submitted full debug logs did not have the database specific logging enabled. The below one does.
DEBUG + DB Logging
Functionality carried out: entering TV show and then season, marking episodes as watched, and skipping through an episode (and not seeing visual indication in view until backing out of season and back in).
RE: MySQL slow requests on tv show seasons - Klojum - 2019-02-07
(2019-02-06, 10:12)Mungo Wrote: TV Series into TV Season view - 22 secondsFYI... When you enable the Database component in the debugging section of Kodi, you will get a few more SQL details in the kodi.log file.
Running the season_view query, I'm currently needing some 3 seconds for 686 rows on a RPi 3B+ with MariaDB 10.1.37 on an SSD for the 1st time query. Doing the query subsequently results in an instant result with basically a 0,0 second delay.
(2019-02-07, 22:56)Mungo Wrote: I'm surprised we haven't heard from anyone else running a MySQL back-end on this issue, considering how painful it seems to be to navigate.I'm getting an almost instant result on a Celeron 1037 Ubuntu with MySQL 5.5.x. But... it may have cached some data already.
(2019-02-07, 22:56)Mungo Wrote: Are we able to get a dev to weigh in on the performance hit an external database configuration seems to have taken?Previously it was a setting in MariaDB that was causing a slow result. See https://forum.libreelec.tv/thread/13990-mariadb-mysql-server-addon/?postID=108409#post108409
Maybe you also need to increase/double the caching parameter values.
RE: MySQL slow requests on tv show seasons - Mungo - 2019-02-08
Thanks Klojum. I did realise - a little late - that I hadn't enabled the db specific logging. I re-attached a log in my last post with this enabled and performing a few actions through my TV library.
I had previously been using MySQL 5.5, so hadn't updated in a while. 1st thing I did when I noticed these issues was check the wiki to see if 18 needed, or preferred a newer version which now stated 5.7+ so that's the main reason I jumped straight to v8. This was a complete fresh install, fresh database.
My MySQL server resides on a Windows Server 2012 R2 box (VM) with 4GB of RAM and the full capacity of the CPU available (Quad core Intel Xeon 2.5Ghz). Resource usage appears to be fine on the box itself.
I'll give the query cache change a run when I'm back home, unless @Smeulf beats me to it
Thanks for the input.
Cheers,
Mungo
RE: MySQL slow requests on tv show seasons - Mungo - 2019-02-08
(2019-02-07, 23:23)Klojum Wrote: Maybe you also need to increase/double the caching parameter values.
Have had a quick scan around for .cnf files in MySQL, which lead me to official doco, which in turn revealed the following:
Source
query_cache_limit: Do not cache results that are bigger than this. Removed in MySQL 8.0.3.
query_cache_size: The memory allocated to store results from old queries. Removed in MySQL 8.0.3.
I'm currently running 8.0.15, the latest edition. I'll continue digging around for an equivalent adjustment, although from that page as it lists additions too - it really does look like they've just dumped all the query caching options and not introduced anything else to adjust them.
Perhaps @Smeulf, you may be able to give it a crack if you're on an older SQL version...
RE: MySQL slow requests on tv show seasons - Mungo - 2019-02-08
Ok, quick update - I took a snapshot of my SQL VM, killed the MySQL install and rolled up a fresh install of MariaDB v10.3 - the latest stable and kicked off Kodi.....After kicking of a fresh library scan on my TV series source, I could tell just from the speed of scanning items into the library it was miles ahead of the 2 previous MySQL installs I had (5.5 and 8) - and even faster than I'd ever seen it on previous versions of Kodi using a MySQL setup. I started navigating around straight away and it's blistering quick. Instant 0.0 round time going in and out of TV shows, seasons, and episodes. No busy spinner in sight.
NFI what's up with MySQL, but MariaDB seems to be the answer. This responsiveness is apparent on my Windows 10 test box. I haven't confirmed on my nVidia shields just yet, but I expect the same performance there. I'll check and confirm that later tonight once my entire library including movies etc. has successfully re scanned into the MariaDB.
update: Well, as I mentioned - the library scan speed alone was a clear indication MariaDB setup is miles ahead of MySQL. I expected that scan to take hours, but....done. Can confirm speed is the same on my nVidia shields
*takes deep breath*....now I can finally start enjoying v18.
RE: MySQL slow requests on tv show seasons - Klojum - 2019-02-08
(2019-02-08, 12:31)Mungo Wrote: update: Well, as I mentioned - the library scan speed alone was a clear indication MariaDB setup is miles ahead of MySQL.I'm still using a 'stock' installed MySQL 5.5 on my Intel box. Although the initial season_view is slow, the subsequent queries are apparently cached, so perhaps I still need to tweak some settings here.
Overall, I wouldn't say "miles ahead" as MariaDB stumbled in a different way as already pointed out. Default installation configurations can apparently affect performance, something Kodi cannot foresee.
RE: MySQL slow requests on tv show seasons - Mungo - 2019-02-08
Yeah, I can't speak for anyone else, and I've no doubt MariaDB has its own flaws elsewhere - but in this particular instance - I'm blown away by how fast my setup is now. I've always accepted a slight lag using a centralised DB, and as mentioned - rescanning my entire library from scratch has always taken hours. I smashed out a whole movie & TV scan in like half an hour with a break in-between.
The responsiveness of this setup now feels like using a local default database. I have literally 0 lag entering or exiting any views, on a fresh library with nothing browsed to be cached anywhere and it's glorious lol. Even marking things watched etc. is all but instant. The only time I've noticed any sort of wait time of like 2-3 seconds is marking large shows with 200+ episodes.
Maybe @Smeulf can also give the current edition of MariaDB a run and see what sort of performance gain / loss, if any, is evident. For me, for now - MariaDB stays put for the foreseeable future!
Thanks for the input @Klojum. Without the input and supplied links / reading, I probably wouldn't have gone to the effort of giving MariaDB a try.
Cheers,
Mungo
RE: MySQL slow requests on tv show seasons - Klojum - 2019-02-08
(2019-02-08, 15:57)Mungo Wrote: I've always accepted a slight lag using a centralised DB, and as mentioned - rescanning my entire library from scratch has always taken hours.I've been using SSDs with MySQL or MariaDB databases for years, there is no faster substitute ;-)
If you have done a Video Library Export previously, all metadata (and watched status if enabled) is stored in .nfo files along with fanart and so on. Then rescraping your video collection will go in just a couple of minutes, rather than in hours. All depending on the size of your collection of course.
(2019-02-08, 15:57)Mungo Wrote: The only time I've noticed any sort of wait time of like 2-3 seconds is marking large shows with 200+ episodes.Well, Kodi also does some internal sorting routines on top of data retrieval as well, so that is possibly not your database's fault. Things are not perfect yet.
But I'm glad things are working better now.
RE: MySQL slow requests on tv show seasons - tommyp - 2019-02-08
Damn - I have the same problem and have been watching this thread to see what comes up. I'm using a raspbian (raspberry pi) for my database. Turns out it's mysql v 5.5.
RE: MySQL slow requests on tv show seasons - Mungo - 2019-02-08
(2019-02-08, 17:24)Klojum Wrote: I've been using SSDs with MySQL or MariaDB databases for years, there is no faster substitute ;-)Yeah, I hadn't made the jump to SSD due to these type of servers being VM's, and up until recently, decent sized SSD's (terabytes) were just too costly an upgrade. I've been consolidating and simplifying my setup a lot recently and am down to just 2 VM's now on internal hypervisor hosts, rather than a separate NFS box, so I feel the SSD upgrade whisper blowing in the wind lol.
(2019-02-08, 17:24)Klojum Wrote: If you have done a Video Library Export previously, all metadata (and watched status if enabled) is stored in .nfo files along with fanart and so on. Then rescraping your video collection will go in just a couple of minutes, rather than in hours. All depending on the size of your collection of course.My library has always had 100% local metadata. Something I've also been giving a fair bit of thought to recently during all my reconfigurations, but yeah - even with everything stored locally - if I ever had to kill a database and rescan from scratch for whatever reason (mostly testing in cases like these), it's taken hours to complete. Library has continued to grow over the years - then when storage became an issue, I culled a lot, but it's on the move upward again now. Movies are around 1500, and TV Series are around 300 with 25,500ish episodes. It blows me away that with nothing else changed in my environment - a default MariaDB setup performs so drastically better than a MySQL one.....in my case.
(2019-02-08, 20:21)tommyp Wrote: Damn - I have the same problem and have been watching this thread to see what comes up. I'm using a raspbian (raspberry pi) for my database. Turns out it's mysql v 5.5.....I can only speak for my testing and experience here. I / we, still have no idea why MariaDB is performing so much better than MySQL. It may be something in my network / setup that it prefers, or MySQL doesn't. I wouldn't take this resolution as law until you give it a whirl for yourself. Would still love to hear from others like yourself in a similar boat.
Honestly, the only reason I even gave MariaDB a go was Klojum linked his caching fix on the MariaDB config - something MySQL from v8 onward didn't have to tinker with. It was either try MariaDB and have those options to tinker with, or roll back to a version of MySQL that had them to tinker with....as we say in the bizz - fix forward. Going backwards to "fix" something, isn't fixing it, it's denying change.
Cheers,
Mungo