+- 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)
RE: MySQL slow requests on tv show seasons - tommyp - 2019-02-09
Interestingly enough, the mysql wiki page has a blurb about mysql / mariadb versions starting from January 2019. I'm no database expert but can follow wikis. Incidentally, the wiki for raspbian is telling me to install something that won't work.
RE: MySQL slow requests on tv show seasons - Klojum - 2019-02-09
(2019-02-08, 23:58)Mungo Wrote: Movies are around 1500, and TV Series are around 300 with 25,500ish episodes.Do you ever watch something?
RE: MySQL slow requests on tv show seasons - Klojum - 2019-02-09
(2019-02-08, 20:21)tommyp Wrote: I'm using a raspbian (raspberry pi) for my database. Turns out it's mysql v 5.5.Raspbian stretch by default installs MariaDB afaik.
I get
MariaDB [mysql]> once I enter the SQL environment.
RE: MySQL slow requests on tv show seasons - Mungo - 2019-02-09
(2019-02-09, 09:17)Klojum Wrote: Do you ever watch something?
Haha, now that it doesn't take me a few minutes to get into an episode - yeah
. Been on a Marvel binge lately - finally making my way through agents of S.H.I.E.L.D 
RE: MySQL slow requests on tv show seasons - tommyp - 2019-02-09
(2019-02-09, 09:22)Klojum Wrote: Raspbian stretch by default installs MariaDB afaik.Yea, you are right. Went to mysql prompt, typed in status and it says MariaDB ver 10.1.37. Database is still slow compared to kodi v17.6.
I getMariaDB [mysql]>once I enter the SQL environment.
RE: MySQL slow requests on tv show seasons - Mungo - 2019-02-09
(2019-02-09, 12:44)tommyp Wrote: MariaDB ver 10.1.37
Just to confirm my full version I'm using is "version 15.1 Distrib 10.3.12". It's the Windows version though, but perhaps there's a newer build to upgrade to for the Pi?
Side note, I noticed at the bottom of the status window it indicates "slow queries: 0". I remember finding the "slow-queries" log file for MySQL when I was looking for the config files to tweak and it was full of stuff. I've been watching stuff all day through this setup across my Windows system and both of my nVidia shields and everything has been blisteringly quick, so I'm glad it's remained as fast as it is.
RE: MySQL slow requests on tv show seasons - Smeulf - 2019-02-12
(2019-02-07, 23:23)Klojum Wrote:Woo, @Klojum thank you so much for pointing this out! You made my day!(2019-02-06, 10:12)Mungo Wrote: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
For the little story, I was running an old setup with MariaDB in a Docker container as previously recomended by LibreELEC team.
But times change, for better solutions ^^
So after dumping my Kodi17 database, update to Kodi18 (LibreELEC 9), remove my old MariaDB docker, install the new LibreELEC MariaDB addon, and tuning my advancedsettings, I have now a system running like a charm, better than ever!
Sounds like my bug was related to the MariaDB config file of the old container.
Sorry Kodi Team for my bad diag at the first time. Kodi 18 rocks!
Cheers.
Smeulf.
RE: MySQL slow requests on tv show seasons - ricocat - 2019-10-30
(2019-02-05, 07:34)Smeulf Wrote: 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.I found this thread as I am experiencing the exact same issue.
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 :
I moved from Kodi 16 to Kodi 18 only to find that there are 10-15 second delays when browsing to a new TV Series. The delay is only when opening a new TV Series title from the list and this is consistent with what you have described above.
The MySQL server is running on an old NAS so cannot be updated, MariaDB also does not seem available for the platform.
As I was thinking that maybe the database migration wasnt completed properly, I dropped the MyVideos DB and let it re-create a fresh one. This is just as slow.
Can you (Smeulf) confirm that changing the group by clause for the view is a viable fix? If so please let me know the code to create this view again using the Kodi 16/17 parameters instead. Many thanks!
RE: MySQL slow requests on tv show seasons - Klojum - 2019-10-30
(2019-10-30, 07:20)ricocat Wrote: The MySQL server is running on an old NAS so cannot be updatedThe age of the NAS is not so important, which MySQL server version is it running?
(2019-10-30, 07:20)ricocat Wrote: Can you (Smeulf) confirm that changing the group by clause for the view is a viable fix?Try doubling all cache memory settings in the my.cnf file (and reboot the MySQL server, or the whole NAS).
RE: MySQL slow requests on tv show seasons - ricocat - 2019-10-30
Thanks, the MySQL version is 5.1.73. There don't seem to be any alternatives for this QNAP TS421 NAS either, although since it all worked fine with earlier Kodi I think this should be fixable.
In response to your suggestion I have doubled all the caches in my.cnf and it made no difference.
Next I have dropped the series_view and recreated it in line with the structure used in Kodi 17.5. I checked the structure from the VideoDatabase.cpp from the git repository. Unfortunately it is still very slow to access the top level view for any TV show.
RE: MySQL slow requests on tv show seasons - fnord12 - 2019-10-31
I've had the same problem since upgrading to 18. In my case, I'm not using a NAS or anything. It's just a single instance of Kodi with a local harddrive on Windows 10, and the database is the default SQLite. But I have the same symptom - very slow going into the season view. And possibly related: when I finish watching an episode, the episode number disappears (it comes back if I toggle watched status or leave the page and come back).
I found this thread a while ago when I first upgraded and tried dropping/replacing my season_view but it didn't help.
Sorry if I shouldn't be replying to this thread, but thought I would add that another person is running into the same (or similar) issue.
RE: MySQL slow requests on tv show seasons - ricocat - 2019-11-01
(2019-10-31, 06:42)fnord12 Wrote: I've had the same problem since upgrading to 18. In my case, I'm not using a NAS or anything. It's just a single instance of Kodi with a local harddrive on Windows 10, and the database is the default SQLite. But I have the same symptom - very slow going into the season view. And possibly related: when I finish watching an episode, the episode number disappears (it comes back if I toggle watched status or leave the page and come back).
I found this thread a while ago when I first upgraded and tried dropping/replacing my season_view but it didn't help.
Sorry if I shouldn't be replying to this thread, but thought I would add that another person is running into the same (or similar) issue.
I'm glad you replied. That's interesting as it suggests that its not my old SQL version that is the bottleneck. Re-creating the views manually in MySQL hasn't helped either, and I wonder how many others are affected.
In the absence of a fix I suppose the only option is to revert to an old Kodi version.
RE: MySQL slow requests on tv show seasons - Ruk12345 - 2019-12-02
(2019-11-01, 08:02)ricocat Wrote:I am also experiencing this from a Kodi v 17 to v 18 update on shield tv. So far reverting to older version seems only viable option.(2019-10-31, 06:42)fnord12 Wrote: I've had the same problem since upgrading to 18. In my case, I'm not using a NAS or anything. It's just a single instance of Kodi with a local harddrive on Windows 10, and the database is the default SQLite. But I have the same symptom - very slow going into the season view. And possibly related: when I finish watching an episode, the episode number disappears (it comes back if I toggle watched status or leave the page and come back).
I found this thread a while ago when I first upgraded and tried dropping/replacing my season_view but it didn't help.
Sorry if I shouldn't be replying to this thread, but thought I would add that another person is running into the same (or similar) issue.
I'm glad you replied. That's interesting as it suggests that its not my old SQL version that is the bottleneck. Re-creating the views manually in MySQL hasn't helped either, and I wonder how many others are affected.
In the absence of a fix I suppose the only option is to revert to an old Kodi version.
RE: MySQL slow requests on tv show seasons - john_es - 2020-03-28
(2019-12-02, 23:43)Ruk12345 Wrote:You guys ever solve this?(2019-11-01, 08:02)ricocat Wrote:I am also experiencing this from a Kodi v 17 to v 18 update on shield tv. So far reverting to older version seems only viable option.(2019-10-31, 06:42)fnord12 Wrote: I've had the same problem since upgrading to 18. In my case, I'm not using a NAS or anything. It's just a single instance of Kodi with a local harddrive on Windows 10, and the database is the default SQLite. But I have the same symptom - very slow going into the season view. And possibly related: when I finish watching an episode, the episode number disappears (it comes back if I toggle watched status or leave the page and come back).
I found this thread a while ago when I first upgraded and tried dropping/replacing my season_view but it didn't help.
Sorry if I shouldn't be replying to this thread, but thought I would add that another person is running into the same (or similar) issue.
I'm glad you replied. That's interesting as it suggests that its not my old SQL version that is the bottleneck. Re-creating the views manually in MySQL hasn't helped either, and I wonder how many others are affected.
In the absence of a fix I suppose the only option is to revert to an old Kodi version.
I upgraded to Maria 10.0.38 running Kodi (18.5 Git:20191116-37f51f6e63) and I am seeing 15 seconds delay when selecting a TV show or running this query:
SELECT * FROM season_view WHERE season_view.idShow = 2045
RE: MySQL slow requests on tv show seasons - Ruk12345 - 2020-03-29
(2020-03-28, 20:23)john_es Wrote:Nope aside from downgrade to v17(2019-12-02, 23:43)Ruk12345 Wrote:You guys ever solve this?(2019-11-01, 08:02)ricocat Wrote: I'm glad you replied. That's interesting as it suggests that its not my old SQL version that is the bottleneck. Re-creating the views manually in MySQL hasn't helped either, and I wonder how many others are affected.I am also experiencing this from a Kodi v 17 to v 18 update on shield tv. So far reverting to older version seems only viable option.
In the absence of a fix I suppose the only option is to revert to an old Kodi version.
I upgraded to Maria 10.0.38 running Kodi (18.5 Git:20191116-37f51f6e63) and I am seeing 15 seconds delay when selecting a TV show or running this query:
SELECT * FROM season_view WHERE season_view.idShow = 2045