MySQL Database: Multiple XBMC HTPC's with 1 Shared Library

czfj5r Wrote:"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe" --user=xbmc --password=xbmc --add-drop-table --all-databases --result-file=C:\Temp\backup-%DATE%.sql --dump-date

Since you're dumping everything in one go, you should probably add --lock-all-tables as well, just to be safe.

I have about 1400 videos in my movie library and about 5000 tv episodes. It's pretty slow to load the initial list of movies/tv shows. Do you guys have any reccos for improving tv/movie performance with indexes to create?

Looking at mysql profiler, some of the following queries look partciularly bad. I'm a mysql noob though, not sure what indexes to make to fix...

Quote:select strPath,noUpdate from path where idPath in (select idPath from files join movie on movie.idFile=files.idFile) and idPath NOT in (select idPath from tvshowlinkpath) and idPath NOT in (select idPath from files where strFileName like 'video_ts.ifo') and idPath NOT in (select idPath from files where strFileName like 'index.bdmv') and strPath NOT like 'multipath://%%' and strContent NOT in ('movies', 'tvshows', 'None') order by strPath

Quote: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)

Quote:select idActor from actors where strActor like 'Brian van Holt'

Quote:select * from episodeview order by idEpisode desc limit 25

craigd Wrote:There are a number of common issues when first setting up I’ll track as many of them here...

...2. Synchronise folders so copies of files are held centrally and copies are cascaded to all other htpc's. Using tools such as synctoy, rsynch, dropbox folders can be set to replicate manually or automatically depending on selected tool.

pros:
guaranteed best performance as files are cached locally
simple to set up

cons:
images not available until sync completes
not all tools automate easily and some cost money

Question about this part of doing Mysql.

I would like to keep my Thumbs local on each of my 3 zboxes, but I don't thing that I add stuff often enough to warrant using and kind of resync. What I do now is to use a Windows vista with XBMC to do all of my backend work like rips and scaping. Then I export the library (separate files), modify and re-scape as needed the new movies on to my zboxes.

Is it just a matter of copying the Thumbs folder from the vista computer to each of my zboxes to get started? And when I want to add a movie, just go to each zbox and in file view, do "i" and then a "refresh" to gain the Thumbs?

you should have a look over the queries and set some indizes, this should speed things up.

episodeview and movieview seem like good candidates.

no matter what I do, it seems that some tables are not created when I try to start it seems that theres a problem with accessing the music db

Hello,

An xbmc newbie here and I'm having trouble getting this to work with two instances of xbmc installed on Windows 7 (my laptop and my desktop) are using the mySQL database, the shared aspect is missing an any noticeable way.

I have checked every setting that I can find in the GUI but for the life of me cannot find how the two machines are sharing any data. I can see that both machines hit the mySQL database using mySQL Workbench. Both machines show connections to the same database when expected (xbmc_video when accessing movies).

The very least of what I expect to see but do not is "watched" status in common between the two machines.

I am also not seeing any of the following:

1. Both machines show the the same sources where I added the source to only one of the two.

2. Paused status for any particular movie on one instance show in some meaningful way on the other.

I suspect there is some fundamental setting that I am missing. Anyone have any tips that might help track down this problem?

Thanks!

-Bob

TiGmsm Wrote:Hello,

An xbmc newbie here and I'm having trouble getting this to work with two instances of xbmc installed on Windows 7 (my laptop and my desktop) are using the mySQL database, the shared aspect is missing an any noticeable way.

I have checked every setting that I can find in the GUI but for the life of me cannot find how the two machines are sharing any data. I can see that both machines hit the mySQL database using mySQL Workbench. Both machines show connections to the same database when expected (xbmc_video when accessing movies).

The very least of what I expect to see but do not is "watched" status in common between the two machines.

I am also not seeing any of the following:

1. Both machines show the the same sources where I added the source to only one of the two.

2. Paused status for any particular movie on one instance show in some meaningful way on the other.

I suspect there is some fundamental setting that I am missing. Anyone have any tips that might help track down this problem?

Thanks!

-Bob

1. You need to add the sources seperately for each machine.

2. Not sure exactly what you mean by paused status. You can't pause on one box and resume on another per se. What you can do is stop the movie in one room and resume from the same point on another box.

You should definitely see synchronized watch status. If you're not getting that, I would say your central db isn;t set up correctly on all machines.

This is a helpful reply. Thank you.

RockDawg Wrote:1. You need to add the sources seperately for each machine.

2. Not sure exactly what you mean by paused status. You can't pause on one box and resume on another per se. What you can do is stop the movie in one room and resume from the same point on another box.

You should definitely see synchronized watch status. If you're not getting that, I would say your central db isn;t set up correctly on all machines.

Not only setting up the sources on each machine separately but also setting the view on each machine to be similar enough that the synchronized status can be noticed.

The subtlety between "paused" and "stopped" alluded me until you mentioned it in the previous post. If I'm watching a video downstairs and 'pause" it then go upstairs to finish it in the bedroom, it would only work if I "resume" playback. This is a well hidden setting that appears in the Settings-Video-Playback screen (I think it was that screen..) It's not easy to resume a movie without discovering this well hidden setting.

I have added the same sources to all four of my xbmc machines (downstairs AppleTV, upstairs ATV, laptop Winddows7, desktop W7). and they all seem to in synch regarding watched,/unwatched status using Dharma and Alask Revisited.

Thank for you help.

-Bob

I would greatly appreciate if someone with good Mysql knowledge could write up a quick guide on how to set up indexing to speed up the SQL XBMC video database.