analysis of XBMC's use of SQL to load movie wall
#1
I have noticed since moving to SQL that loading the movie wall is very slow. I have about 550 movies and it takes about 8sec. Not great but not horrible. Primary motivation is that I also have an ATV2 on 802.11n 5Ghz where it takes MUCH longer. Almost 30sec. I can imagine for larger datasets, music or larger movie libraries, things could take much longer.

To preface, I have very little skills(almost none) in either SQL or programing so please forgive if I have misinterpreted some of the payload data. What I do have a great deal of skill in is network analysis. I run a Cisco Datacenter network. I have undertaken this effort in hopes that it would encourage the XBMC devs to pick off the low hanging fruit to speed up XBMC's use of SQL and SMB for thumbs. Any gains in efficiency I would hope would have a magnified effect on ATV2 for operations that are not CPU bound.

Test were performed on the following setup.
XBMC Eden beta 3 for OSX, PM3.HD view=thunmbnail, show sets, no hide watched
XBMC Desktop Q6600 @ 3ghz, 4GB RAM, 256GB SSD
SQL server Q6600 @ 3ghz, 8GB RAM, 128GB SSD
Gbit LAN, no jumbo
Procedure: start packet cap for only TCP port 3306, click movies, stop packet cap as fast as possible once movie wall loads. There are still packets going by but I think I have nailed down the end of the packet stream needed to load the movie wall. I also did a few caps with SMB included and comments related to such but packet numbers and timestamps are for cap that only includes SQL.

There is some routine that gets called a lot. Seems to happen every time a SQL connection is opened. Action seems to take about 39 packets and about .015 seconds. While this is not a lot of packets or time keep in mind it is repeated MANY MANY times.
TCP 3WAY
Login Request user=xbmc
Command unknown 66
SET NAMES utf8
Command Ping 14
show databases like 'xbmc_video60'
show tables // response is error as we have not issued USE yet
Use Database xbmc_video60
Command Ping 14
show databases like 'xbmc_video60'
show tables
SELECT idVersion FROM version\n
sometimes do more stuff, sometimes just close connection.

Part 1 // first run at our fav routine
start packet 1
start time 0.00000
Do our fav 39 packet routine and close connection. Nothing else happens here.
end packet 39
end timestamp 0.014939

Part 2 // grab some counts
start packet 40
start timestamp 0.017022
do our fav routine
ask for "select count(1) from movie"
ask for "select count(1) from tvshow"
ask for "select count(1) from musicvideo"
issue quit and close connection
end packet 90
end timestamp 0.039172

Part 3 // part 2 was fun, let's do it again
start packet 91
start timestamp 0.039198
do our fav routine
ask for exact same things as in part 2
quit
close connection
end packet 142
end timestamp 0.136162
big delta in time in for part 2 compared to part 3 is TCP 3WAY takes longer this time(~80ms - server was likely busy). Not XBMC's problem other than why did we do part 3 at all as it's identical to part 2.

Part 4 // find info needed for sets
start packet 143
start timestamp 0.136199
do our fav routine
"SELECT sets.idSet,sets.strSet,COUNT(1) AS c,count(files.playCount) FROM sets JOIN setlinkmovie ON sets.idSet=setlinkmovie.idSet JOIN movie ON setlinkmovie.idMovie=movie.idMovie JOIN files ON files.idFile=movie.idFile GROUP BY sets.idSet HAVING" // truncated by wireshark
select strPath, strFileName from movieview join setlinkmovie on setlinkmovie.idMovie=movieview.idMovie where setlinkmovie.idSet=1
repeat for all movie sets one at a time. it seems to take XBMC 3X as long each time to think of it's next query at SQL server does to answer it.
end packet 236
end timestamp 0.356115
connection still open

Part 5 // bulk of info needed for the wall
start packet 237
start timestamp 0.362995
select * from movieview WHERE movieview.idMovie NOT IN (SELECT idMovie FROM setlinkmovie s1 JOIN(SELECT idSet, COUNT(1) AS c FROM setlinkmovie GROUP BY idSet HAVING c>1) s2 ON s2.idSet=s1.idSet) // Query something along the lines of ask for a dump of info(title, year, plot, file path, etc) for all movies not in a set.
lots of 1500 byte packets as SQL server sends over answer and TCP acks going back
connection still open
end packet 967
end time 0.429138

Part 6 // not really sure why we need stream info to show the wall
start packet 968
start timestamp 0.437753
ask for "SELECT * FROM streamdetails WHERE idFile = <ID>"
we walk through each and every ID as a single query, response, TCP ACK
If also using SMB for thumbs we will issue QUERY_PATH_INFO for each thumb path before moving on to next SQL query. Though I haven't figured out where the thumb path is as I do not see it in any of the SQL responses.
end packet 2329
end timestamp 3.174321

From here on out it appears to be all about loading thumbs.
Part 7 // doing something like file system stat for thumb files…?
There appears to be some inefficiencies here as well as we do a LOT of one by one QUERY_PATH_INFO. There appears to be possible improvement here as well but it's hard for me to be sure as SMB is a much more complicated protocol on the network side than SQL.
takes about 3 sec.

Part 8 // get the thumbs themselves
"Open AndX Request" for the thumbs
takes about 1 sec.


Part 9 // This is only included to show that there are times when XBMC is even MORE inefficient.
Ask for "select timeInSeconds, totalTimeInSeconds from bookmark where idFile=<each ID one at a time> and type=1 order by timeInSeconds"
This appears to be the part where XBMC is calculating the total duration of your media.
In part 9 unlike part 6 instead of just doing each Query one at a time. We open a new connection, run through our fav oft repeated routine, do a query, close the connection, open a new connection again, rinse, wash, repeat. Amazingly inefficient!!

As you can see there are two parts, 6 and 7, which take up the bulk of our time(~90% - half SQL, half SMB) and these appear to be to be spent doing lots of one by one transactions. I'm not even sure 6 and 7 are strictly needed to load the wall…? From a network point of view this is a very inefficient way to go. These kinds of things do not scale well to much larger datasets or, not that it matters on a wired LAN, to networks with higher latency between client and server(wireless).
Reply
#2
Thanks, always good to have stats.

Regarding video thumbs, this is because you have XBMC's thumbnail cache shared, right? XBMC assumes it's local, and thus stat is essentially free. I've told people I don't know how many times that sharing it is silly and will slow things down a whole heap, but I understand why y'all do it (primarily because we haven't made it better yet).

Regarding the resume bookmark stuff, it's done offthread, so it really doesn't matter how inefficient it is. A simple improvement, however, is keeping the db object open to save the versioning hits etc (this can't happen atm due to the way things are setup, but I have local changes that enable it). Reason we do a query per item is the streamdetails stores in multiple rows, so a single query per item is much easier code-wise. Whilst we could get it all at once (repeated rows per item, sorted by item id) in one giant query, as it's done offthread the advantages may not be all that useful. Worth experimenting though, as more stuff like this will be happening in future - see next...

Good news, though: I'm about 95% done moving video thumbs to the thumb cache. Thus, any and all thumbnail shenanigans gets done off the main thread, and the original thumb URLs are stored in the database. Essentially it happens like the resume stuff - offthread, we run through items one by one and fetch the original thumb/fanart/<insert extra art here> from the database. XBMC's texture cache is then used to load a cached version should it be there - we'll also check every 24 hours or so (I'll probably make this configurable) to see whether the original image has changed, and if so pull in the new version.

So you'll see more connections to the sql db, however it won't be much of an issue as it's done off-thread. Much faster initial listings. Further, you don't have to share XBMC's local thumb cache any longer (doing so will ofcourse completely destroy any advantages).

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
#3
Quote:I've told people I don't know how many times that sharing it is silly and will slow things down a whole heap

Understood and to be expected from a simple path substitution that will cause a network file system to be treaded as local. Primary reason for it is I update the library on my desktop, 4 cores 3ghz, where it takes 2 minutes as opposed to the ATV2, 1 core ~600mhz, where it takes something like 20min to scan and update library. However most of my watching is on ATV2 where if item was added to DB by desktop I will have a missing thumb and would have to manually refresh from item info. Even worse, I would have to explain this to the girlfriend while she has that look she gets every time I explain the reasoning behind a sub optimal user interaction due one of my "improvements".

Quote:Reason we do a query per item is the streamdetails stores in multiple rows, so a single query per item is much easier code-wise. Whilst we could get it all at once (repeated rows per item, sorted by item id) in one giant query, as it's done offthread the advantages may not be all that useful. Worth experimenting though, as more stuff like this will be happening in future - see next...

Ok, I'll go back and check but I was 99% sure the wall would not load until this was done. I did some runs at this where I turned off SMB thumb share so I could get a look at it from a pure SQL perspective and it still took ~3sec not the .5 sec I would expect if this was not needed to show the wall. Also this would have a magnified effect on wireless where network latency is, on average, 10 or more times higher. ~2.5ms wireless versus ~.2ms wired, in my setup.

Wild guess, do you need this info to show the "watched" flag? Can't find if there is a place to turn it off in PM3.HD and switching to another skin where I can might cause a change in the dataset needed to load the wall and invalidate the comparison.

Quote:Good news, though: I'm about 95% done moving video thumbs to the thumb cache. Thus, any and all thumbnail shenanigans gets done off the main thread, and the original thumb URLs are stored in the database.

OMG, this is freaking awesome!!! Thank you soo much!! Local cache with central master source would be the best of both worlds. Is there a commit ID or something I can monitor to know when this feature has hit the OSX nightly builds?
Reply
#4
You may be write about some of the single-queries per item being done on-thread. We definitely don't want this in general.

EDIT: Yup, stream details are fetched on app thread. Will add to my list.

I haven't yet pushed my branch up to github - will hopefully finish up (just season thumbs to go, and ofcourse testing it!) this week, clean it up and push it up then.

It won't go in until after Eden is out, ofcourse.

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
#5
Quote:EDIT: Yup, stream details are fetched on app thread. Will add to my list.

Thank you very much for looking into this. If I am right then this alone should vastly speed up the movie wall load time, especially on wireless connections.

I looked at the packet captures further and play count(I assume used for watched flag) appears to be included in the movieview query not streamdetails.
Reply
#6
Yup - playcount is done on the main query. However the resume status (whether the movie is half way through) is done off-thread after the list is retrieved.

If you want to play around and don't care about the stream details stuff (make sure you turn it off in settings, else it'll attempt to regenerate it all the time by the looks) you can just move the GetStreamDetails() line in CVideoDatabase::GetDetailsForMovie() to inside the if (fetchCast) block.

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
analysis of XBMC's use of SQL to load movie wall0