[michaelstone]

thanks teko, got it figured out yesterday with some help from block134, i had to get rid of the <name> lines....fixed everything

[akevit]

I don’t think this has anything to do with the MySQL library per-say but I’ll mention something I noticed. I found that after upgrading to Eden the default action for playing videos had changed. You can find this setting under Settings → Video → File lists → Default select action.

By default it seems this is set to “Play” where you want it to be “Choose”. This will prompt you to resume videos.

I don't believe this is true, I'm pretty sure it's always been set to play. The real problem seems to be Eden has moved the default ignoreatstart to 3 minutes; specifying in the advancedsettings.xml doesn't seem to matter. If you play a file for more than 3 minutes it should prompt you whether you want to resume or start at beginning.

[kimattree]

Indexes work really well, but when you add a large amount of media into a mysql/xbmc DB, you should optimize the indexes to re-create the table in a proper numerically ascending order.

you can do this with:

mysql> connect xbmc_music;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Connection id: 2597
Current database: xbmc_music

mysql> optimize table song ;
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------+----------+----------+-------------------------------------------------------------------+
| xbmc_music.song | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| xbmc_music.song | optimize | status | OK |
+-----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (3.53 sec)

and also for movies:

mysql> connect xbmc_video;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Connection id: 2599
Current database: xbmc_video

mysql> optimize table movie;
+------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+----------+----------+-------------------------------------------------------------------+
| xbmc_video.movie | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| xbmc_video.movie | optimize | status | OK |
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.65 sec)

Sped up initial connection times to Music Artist/genre/Song/Album as well as the first connection to a 1500 Movie collection.

K

[yapisik]

I have to admit I was not able to go thru all 20 pages in this thread. So, guilty as charged if this case was already discussed.

Is there a way to set mySQL to look for a local copy of the media file (let's say the movie) first and use that copy instead of the network stored one?

I'll give an example. Here is the setup:

1) An always on file server keeps the master copy of all the media files
2) Several XBMC nodes within the house, all using mySQL for sharing the media library hosted on the file server
3) Some of these XBMC nodes also have local storage that partially mirror the media files on the file server, but not any metadata (I'm not gonna detail how it is done, assume somehow specific XBMC nodes are utilized with scripts to sync specific content at intervals)

Here is sample use:
i) User is browsing movie library from XBMC node A, which is pulling info from mySQL.
ii) User selects a movie to play
iii) mySQL has two set of data for this movie: one points to the associated file on the file server, the other points to a location on the local storage
iii) Instead of returning usual file server location for this movie, mySQL checks to see if the same file exists on the local storage using the second set of data.
iv) If the local file exists, mySQL returns the info for the local file; if not, it returns file server location
v) Effectively, XBMC plays whichever location is returned, hence local access if the movie was already synced


I think an SQL-friendly, but sync-hardy version goes like this, in this case the burden is mostly on the syncing scripts:

a) Instead of having a single file location in mySQL DB, there are file location fields for each of the (fixed number of) nodes.
b) When a new entry is created, all these fields contain the exact same entry (in this case the location on the file server)
c) When the sync script runs on node A, it not only makes a local copy of the media file on node A, but also updates the field corresponding to node A on mySQL DB to point to this file.
d) Instead of checking for the "file exists" on step (iii) above, mySQL simply returns the value for the corresponding node


Is either one doable with the current mySQL implementation or minor modifications?

[DasMarx]

I am using mysql for some time now and always had speed problems when opening my movies view. I am pretty sure, mysql has the right settings, because i see a peak and then nothing on mysql administrator. Even xbmc tells me, it is ready after 2 sec.
The time it takes to open the movies list is about 30 - 40 sec and i don't know what xbmc is doing in that time.

Even the log set to debugging does not tell me what it is doing.

http://pastebin.com/RQpPAR82

what is it doing after 12:50:18, after RunQuery?

Code:

12:50:16 T:9228   DEBUG: CGUIMediaWindow::GetDirectory (videodb://1/2/)
12:50:16 T:9228   DEBUG:   ParentPath = [videodb://1/2/]
12:50:16 T:9228   DEBUG: CVideoDatabase::RunQuery took 2 ms for 4 items query: 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 c>1
12:50:16 T:9896   DEBUG: Got rss feed: [url]http://feeds.feedburner.com/xbmc[/url]
12:50:16 T:9896   DEBUG: Thread CRssReader 9896 terminating
12:50:18 T:9228   DEBUG: CVideoDatabase::RunQuery took 2214 ms for 2066 items query: 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)
12:50:44 T:7412   DEBUG: Thread Jobworker 7412 terminating (autodelete)
12:50:44 T:7072   DEBUG: Thread Jobworker 7072 terminating (autodelete)
12:50:48 T:9228   DEBUG: Saving fileitems [videodb://1/2/]
12:50:48 T:9228   DEBUG:   -- items: 2070, sort method: 25, ascending: true

PS: Using eden beta 2


EDIT: OK, what xbmc seems to be doing in that time is somehow connected to Thumbnails. I am using thumbnails on my server aswell and if i change that to local thumbnails, the movies folder will start much faster.

[cmonpellier]

Indexes work really well, but when you add a large amount of media into a mysql/xbmc DB, you should optimize the indexes to re-create the table in a proper numerically ascending order.

Are you suppose to do this on a regular basis or once only ?

[sceaga]

I'd like to know the same thing? Do we need to do the optimizations on a regular basis? Also, do you have to re-build the indexes as well? I've not seen anything about this when they mention creating indexes for speed. I'm assuming they don't auto-update when you add new movies / episodes, but I could be wrong.

[kimattree]

If you're going to add media all the time, I would suggest running it regularly to keep the indexes in top shape, doing so will only speed up library access when using mysql. I Cron the index optimizes daily, like so:

[root@freenas] /mnt/RAID5Z/.custom/mysql# mysql xbmc_music < optmus.sql
Table Op Msg_type Msg_text
xbmc_music.song optimize note Table does not support optimize, doing recreate + analyze instead
xbmc_music.song optimize status OK
xbmc_music.artist optimize note Table does not support optimize, doing recreate + analyze instead
xbmc_music.artist optimize status OK
xbmc_music.album optimize note Table does not support optimize, doing recreate + analyze instead
xbmc_music.album optimize status OK

Where optmus.sql consists of:

[root@freenas] /mnt/RAID5Z/.custom/mysql# cat optmus.sql
optimize table song;
optimize table artist;
optimize table album;

Since mysql lives on my NAS, which is on 24/7, I just run the re-index every day at 4AM in the morning, takes 2 seconds.

[kimattree]

As a quick test, I join the song,artist and album tables, create cross-indexing and run a full query on the entire set of 17000 songs, looking for how long it takes to return the entire query:

[root@freenas] /mnt/RAID5Z/.custom/mysql# time mysql xbmc_music < test2.sql > bleh2.txt
0.458u 0.031s 0:00.48 100.0% 3353+3391k 0+0io 0pf+0w

the query:

[root@freenas] /mnt/RAID5Z/.custom/mysql# cat test2.sql
select * from song inner join artist on song.idArtist=artist.idArtist inner join album on song.idalbum=album.idalbum \G;

So doing a sample join of 3 tables, 17000 songs, and outputting *ALL* data takes 0.031 seconds - indicating a pretty damn good mysql response time.

If there is any minor slowdown when accessing the library, have a heart and understand that just this query, with 17000 songs created 21.5 Mb of data (that needs to be transferred from the mysql to XBMC, and then parsed/sorted/displayed):

[root@freenas] /mnt/RAID5Z/.custom/mysql# ll bleh2.txt
-rw-r--r-- 1 root wheel 21,528,639 Jan 24 09:23 bleh2.txt

Having gigabit ethernet becomes even more important now.

[tripkip]

Hi, I'm running XBMC Eden beta1/2 on all my equipment.
Using central database/movie server.

Configured with mysql, pathsubstitute.

Thumbs, playcount, title, genres, year, runtime, rating all working.
I get no director, writer and plot in every XBMC player even though this information is in the mysql database (I checked with mysql workbench).


What am I missing?

[kimattree]

I'll check my director/writer/plot when I get home....

[kimattree]

Mine all works, granted I'm on Dharma.

SSD Upgrade today.

K

[tripkip]

Ok thx for checking out.

I can't find anything related in the logs.
I did found out that reloading a movie manually does show the plot, direcotor, trailer etc. Also on the other devices... I hope to solve this issue today.

[kimattree]

I hazard to suggest this, but....

...how about cleaning out your library and re-adding it from scratch ?

K

[tripkip]

I hazard to suggest this, but....

...how about cleaning out your library and re-adding it from scratch ?

K

Yes that's possible, it won't harm if I make a backup of my library first.
If I don't find the issue by the end of today, I'll try it.