database action on startup takes time
#1
Hi there, love the skin but I'm running into an issue with a delay being caused at startup which is limiting the 'newly added' movies/shows to be showed until sometime afterwards.

I have a setup where my videos are being served up from a centralized database (mysql), and for whatever reason the first query generated by this skin ends up taking an inordinate amount of time, so I'm trying to find out if there's anything in the skin I can disable/remove to help eliminate this large delay on startup.

Enabling the debug logs I see this:

00:02:47 T:140506144818944 DEBUG: RunQuery took 53980 ms for 0 items query: SELECT * FROM tvshowview WHERE (tvshowview.idShow IN (select tvshowview.idShow from tvshowview where (watchedcount > 0 AND watchedcount < totalCount) OR (watchedcount = 0 AND tvshowview.idShow IN (select episodeview.idShow from episodeview WHERE episodeview.idShow = tvshowview.idShow AND episodeview.resumeTimeInSeconds > 0))))


Given that the database is read-only, I'm not entirely concerned about watched/resume points, so if anyone happens to know what I can tweak to remove this, that'd help me out a bunch!

[edit]
I should have mentioned that this is using the Frodo version of the skin, and this behaviour doesn't appear to happen with the Confluence skin.

Cheers,
Derek.

Reply
#2
Hi

It could be related to the new Skin Widgets service that I'm using to display recent, random and recommended media on the home screen. Previously I used a different method (the same that's currently used in Confluence - I think). Could you try with Transparency and see if the issue is still there please? I think ronie is using the Skin Widgets the same way in Transparency so if the problem is there under both skins it might be an issue to bring up in the Skin Widgets thread.

Cheers
Reply
#3
It would appear that the Transparency skin does exhibit the same behaviour (delayed listing of new items.) I will hop over to the Skin Widgets thread and inquire there.

Thanks!
Reply
#4
Cool - I'll keep an eye on it,

Cheers
Reply
#5
There is nothing we can do about. There is something wrong in your database
Read/follow the forum rules.
For troubleshooting and bug reporting, read this first
Interested in seeing some YouTube videos about Kodi? Go here and subscribe
Reply
#6
Oh, alright - I guess I'll have to stick with Confluence then, thanks anyways.
Reply
#7
(2012-12-17, 19:01)derekr Wrote: Oh, alright - I guess I'll have to stick with Confluence then, thanks anyways.

i can only think of something wrong in one of the mysql tables or in xbmc but since no one else has reported this my guess is your database
Read/follow the forum rules.
For troubleshooting and bug reporting, read this first
Interested in seeing some YouTube videos about Kodi? Go here and subscribe
Reply
#8
I do notice a slight difference in how it behaves compared to the old way that Confluence still uses.

Ie under Confluence the recently added movies and episodes are there as soon as you first activate XBMC but under the new Widgets way it takes a few seconds (can be about 20ish) for them to be available. I never notice because I leave my media centre always on and rarely have to re-start it up.
Reply
#9
Mine are near instant without mysql.
Because the normal way uses internal xbmc code which is always faster
Read/follow the forum rules.
For troubleshooting and bug reporting, read this first
Interested in seeing some YouTube videos about Kodi? Go here and subscribe
Reply
#10
As a band-aid if I increase the query cache on my mysql daemon, after the first query the rest of them are <1s.
I'm not sure why this particular query takes so long to perform, as just selecting * from either episodeview (~2.5s) or tvshowview (~0.5s.)

Still though considering it's getting no data at all in my case, it seems like it would be a useful thing to have the ability to turn this query off - perhaps I'll poke around at the Skin Widgets code.
Reply
#11
(2012-12-18, 00:01)derekr Wrote: As a band-aid if I increase the query cache on my mysql daemon, after the first query the rest of them are <1s.
I'm not sure why this particular query takes so long to perform, as just selecting * from either episodeview (~2.5s) or tvshowview (~0.5s.)

Still though considering it's getting no data at all in my case, it seems like it would be a useful thing to have the ability to turn this query off - perhaps I'll poke around at the Skin Widgets code.

Only way is disable the script
Read/follow the forum rules.
For troubleshooting and bug reporting, read this first
Interested in seeing some YouTube videos about Kodi? Go here and subscribe
Reply
#12
(2012-12-18, 00:06)Martijn Wrote:
(2012-12-18, 00:01)derekr Wrote: As a band-aid if I increase the query cache on my mysql daemon, after the first query the rest of them are <1s.
I'm not sure why this particular query takes so long to perform, as just selecting * from either episodeview (~2.5s) or tvshowview (~0.5s.)

Still though considering it's getting no data at all in my case, it seems like it would be a useful thing to have the ability to turn this query off - perhaps I'll poke around at the Skin Widgets code.

Only way is disable the script

Actually, I just changed the call in _fetch_movies/_fetch_tvshows to remove the 'played' filter, and it went from ~50s down to about ~8s.
Good enough for me.
Reply
#13
Ok Smile
Well iirc you could change that in settings

Remember that if script updates you will loose your changes
Read/follow the forum rules.
For troubleshooting and bug reporting, read this first
Interested in seeing some YouTube videos about Kodi? Go here and subscribe
Reply
#14
Thanks for the heads up, I didn't think of checking the add-on settings.
Looks like that'll work for me as well and would be okay across script updates.
Reply
#15
derekr: See if you can figure out why the query is taking so long (i.e. do the same query on mysql directly, try profiling it etc.) It might be missing an index or some such and causing mysql to do something really silly like an effective O(n^2) query.

Cheers,
Jonathan
Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


Image
Reply

Logout Mark Read Team Forum Stats Members Help
database action on startup takes time0