Kodi Community Forum
2 XBMC PCs and 1 Database on the shared NAS - Printable Version

+- 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: Windows (https://forum.kodi.tv/forumdisplay.php?fid=59)
+---- Thread: 2 XBMC PCs and 1 Database on the shared NAS (/showthread.php?tid=70603)

Pages: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

- nitr8 - 2010-09-25

@sladinki007

I dont have hardly as many vids as you however:

By ALTER TABLE movie ADD INDEX ix_idFile(idFile);

BEFORE:
Time for actual SQL query = 99
Time to retrieve movies from dataset = 705

AFTER:
Time for actual SQL query = 29
Time to retrieve movies from dataset = 394

you can check that by tailing the xbmc.log when entering the vids

or cat .xbmc/temp/xbmc.log | grep “Time for actual SQL query” && cat .xbmc/temp/xbmc.log | grep “Time to retrieve movies from dataset”

Let me know if it makes any difference please.

- jodeman - 2010-09-26

nitr8 Wrote:@sladinki007

I dont have hardly as many vids as you however:

By ALTER TABLE movie ADD INDEX ix_idFile(idFile);

BEFORE:
Time for actual SQL query = 99
Time to retrieve movies from dataset = 705

AFTER:
Time for actual SQL query = 29
Time to retrieve movies from dataset = 394

you can check that by tailing the xbmc.log when entering the vids

or cat .xbmc/temp/xbmc.log | grep “Time for actual SQL query” && cat .xbmc/temp/xbmc.log | grep “Time to retrieve movies from dataset”

Let me know if it makes any difference please.

It's still slow, but that did help quite a bit. Thank you!

Would the syntax be the same for the tvshow table?

Code:
ALTER TABLE tvshow ADD INDEX ix_idFile(idFile);


- nitr8 - 2010-09-26

jodeman Wrote:It's still slow, but that did help quite a bit. Thank you!

Would the syntax be the same for the tvshow table?

Code:
ALTER TABLE tvshow ADD INDEX ix_idFile(idFile);

No, tvshow already has a PRIMARY index:

- jodeman - 2010-09-26

nitr8 Wrote:No, tvshow already has a PRIMARY index:

Can you think of any other reasons why it may be slow? Anything else I can test to try to pin down why it'd be so slow?

- jodeman - 2010-09-26

Not sure if this helps at all, but I believe this is the bit of the log file when I select TV Shows:

Code:
12:23:50 T:3042948960 M: 91312128   DEBUG: Load MyVideoNav.xml: 5523.64ms
12:23:50 T:3042948960 M: 91312128   DEBUG: Alloc resources: 5537.16ms (5532.55 ms skin load)
12:23:50 T:3042948960 M: 91312128   DEBUG: CGUIMediaWindow::GetDirectory (videodb://2/2/)
12:23:50 T:3042948960 M: 91312128   DEBUG:   ParentPath = [videodb://2/2/]
12:23:53 T:2825583504 M: 91324416   DEBUG: Thread 2825583504 terminating (autodelete)
12:23:55 T:3042948960 M: 91336704   DEBUG: GetTvShowsByWhere query: SELECT tvshow.*,path.strPath AS strPath,counts.totalcount AS totalCount,counts.watchedcount AS watchedCount,counts.totalcount=counts.watchedcount AS watched FROM tvshow JOIN tvshowlinkpath ON tvshow.idShow=tvshowlinkpath.idShow JOIN path ON path.idpath=tvshowlinkpath.idPath LEFT OUTER join (    SELECT tvshow.idShow AS idShow,count(1) AS totalcount,count(files.playCount) AS watchedcount FROM tvshow     JOIN tvshowlinkepisode ON tvshow.idShow=tvshowlinkepisode.idShow     JOIN episode ON episode.idEpisode=tvshowlinkepisode.idEpisode     JOIN files ON files.idFile=episode.idFile     GROUP BY tvshow.idShow) counts ON tvshow.idShow=counts.idShow
12:23:55 T:3042948960 M: 91336704   DEBUG: Time for actual SQL query = 26
12:23:55 T:3042948960 M: 91348992   DEBUG: Time to retrieve tvshows from dataset = 54

If I run that same SELECT query straight in mysql, I get this:
Code:
74 rows in set (0.01 sec)

So, it almost looks like mysql is quick, but once I'm in xbmc it's slowed down.

- sladinki007 - 2010-09-27

nitr8 Wrote:@sladinki007

I dont have hardly as many vids as you however:

By ALTER TABLE movie ADD INDEX ix_idFile(idFile);

BEFORE:
Time for actual SQL query = 99
Time to retrieve movies from dataset = 705

AFTER:
Time for actual SQL query = 29
Time to retrieve movies from dataset = 394

you can check that by tailing the xbmc.log when entering the vids

or cat .xbmc/temp/xbmc.log | grep “Time for actual SQL query” && cat .xbmc/temp/xbmc.log | grep “Time to retrieve movies from dataset”

Let me know if it makes any difference please.

HI Nitr8

This Helped a lot ....
speed is blazing in comparing with the original tables
I hope the development team is reading up on this thread and considers adapting the tables

Thx for taking the time to looking into this

Greetings
Slad007

- dwizer - 2010-09-27

Hi..

I've gone trough everything in this thread but i can't get XBMC and mysql to run smooth Sad

Problem no.1:

XBMC talking with the database so thats not the problem. When i start XBMC with the advancedsettings.xml file active it takes about 15 seconds before i even get to the main menu. Then if i go into the video directory XBMC loads for a few more seconds and so on.. Everything i do takes alot of time Sad No problem with load times when i use the local database.

Problem no.2:

I can't switch to "Library Mode" and the option to "Scan for new content" isn't there Sad I have set my TV Series dir to retrieve Series information as i always did before with my local database.

Hope that maybe somebody can help me out here!

Some info:

Running a fresh install of latest xbmc-r33778-Dharma_beta2.exe build.
Latest Ubuntu server with MySQL on a local Gbit network with samba share for my movies. So there's no speed problem between the server and my mediapc.

My XBMC log: http://pastebin.com/4e5HkTYi

Did i forgot any important information?

Thanks Wink

- nitr8 - 2010-09-27

dwizer Wrote:Hi..

I've gone trough everything in this thread but i can't get XBMC and mysql to run smooth Sad

Did i forgot any important information?

From your log:
Code:
ERROR: SQL: Undefined MySQL error: Code (1062)
Query: insert into path (idPath, strPath, strContent, strScraper) values (NULL,'T:\\Series\\Stargate Atlantis\\Season 5\\Stargate.Atlantis.S05E01.720p.HDTV.x264.DIRFIX-LOLCATS\\','','')
ERROR: CVideoDatabase::AddPath unable to addpath (insert into path (idPath, strPath, strContent, strScraper) values (NULL,'T:\\Series\\Stargate Atlantis\\Season 5\\Stargate.Atlantis.S05E01.720p.HDTV.x264.DIRFIX-LOLCATS\\','',''))
ERROR: SQL: Undefined MySQL error: Code (1062)
Query: insert into path (idPath, strPath, strContent, strScraper) values (NULL,'T:\\Series\\Stargate Atlantis\\Season 5\\Stargate.Atlantis.S05E01.720p.HDTV.x264.DIRFIX-LOLCATS\\','','')
ERROR: CVideoDatabase::AddPath unable to addpath (insert into path (idPath, strPath, strContent, strScraper) values (NULL,'T:\\Series\\Stargate Atlantis\\Season 5\\Stargate.Atlantis.S05E01.720p.HDTV.x264.DIRFIX-LOLCATS\\','',''))

Clearly you did not follow the instructions your database is not using the right collation:

CREATE DATABASE xbmc_music CHARACTER SET latin1 COLLATE latin1_general_ci;
CREATE DATABASE xbmc_video CHARACTER SET latin1 COLLATE latin1_general_ci;

see here:
http://humphrey.za.net/2010/09/25/xbmc-mysql-multi-room-sync/

- dwizer - 2010-09-27

I did exactly that. Hmm, thats strange! Well, i remove the database and add it again just to be sure Wink

Code:
CREATE DATABASE xbmc_music CHARACTER SET latin1 COLLATE latin1_general_ci;
CREATE DATABASE xbmc_video CHARACTER SET latin1 COLLATE latin1_general_ci;
And phpmyadmin returned

Code:
Your SQL query has been executed successfully
CREATE DATABASE xbmc_music CHARACTER SET latin1 COLLATE latin1_general_ci;# 1 row(s) affected.
CREATE DATABASE xbmc_video CHARACTER SET latin1 COLLATE latin1_general_ci;# 1 row(s) affected.

Same problem as before..

I'm new to this, sorry, but can i check somewhere that the databases REALLY are latin1 latin1_general_ci ?

Thank you for helping nitr8!

- Bommy - 2010-09-27

Thought I'd have a go at this tonight - I have a main HTPC which has all my content stored locally, and have just added an Acer Revo R3610 which I want to run as a client I call TVPC. I installed MySQL on my main HTPC, 192.168.0.30, and I've followed the recent Lifehacker guide to the letter, with the exception of using latin1 for my databases.

HTPC and TVPC are both running Dharma Beta 2 on Windows 7.

Here's how I set up my databases...
Image

And here's my log... http://pastebin.com/kq4zc77z

HTPC runs fine, but when I run XBMC on my TVPC it's initially unresponsive and reporting very high CPU load. It becomes responsive again after an error flashes up in the GUI (something to do with a script to do with recently added items?), then entering my Movies screen causes another long hang, which ends with it showing an empty library.

I can see from my log that TVPC is unable to access the MySQL database but I'm not sure why and/or how to test what's causing that.

A couple of other points which may be of use...
1 - The guides I found are all for when running the database, and storing your media, on a NAS. I'm using one XBMC installation as a server for another one. To this end, I mapped all the shared drives from HTPC on the TVPC with the same letters, so E:/Movies/Die Hard, for example, is at the same address on both machines. I assumed this was necessary so that the mirrored databases on each machine pointed to the same files, but perhaps I'm wrong.
2 - I set a different password (the 8 character one you can see in the screengrab above) for my MySQL root password to the 4 character password 'xbox' that I set on the MySQL database itself. Again, I'm assuming that's correct.

Any ideas what's going on here please?

- Bommy - 2010-09-28

Hmm, turn Windows Firewall off on HTPC and it just works (also disabled the Recently Added script in Home Screen settings while I troubleshoot), so it's just a firewall issue.

Have now allowed mysql.exe through the firewall and still getting issues. Also re-ran the MySQL setup wizard and ticked the "add exception to firewall" setting, but still not working. So I'm obviously not adding the right thing, but I'll get there.

- Bommy - 2010-09-28

Got it! Allowed mysqld.exe to as a firewall exception and now I'm rocking. Now I just need to work out the best way to bring my thumbs etc into TVPC. Any chance someone in the know could give me a rundown of my options? Would be very much appreciated.

It would appear that this post has info on how to use symlink - http://forum.xbmc.org/showpost.php?p=567856&postcount=354
Is that my best option? Won't that put all thumbnails on my remote HTPC and cause my TVPC to be really slow? And how does DDS fit into all of this?

Aaah, XBMC. Every step you crack there's something else to get set up Smile

- Bommy - 2010-09-28

And every problem is just a bit more searching away from solved Smile

http://forum.xbmc.org/showpost.php?p=612485&postcount=4

Working now and not too sluggish, but any advice on tweaks to speed things up, or how DDS caching might play into this, would be most appreciated Smile

I've found this thread - http://forum.xbmc.org/showthread.php?tid=77142 - which is Linux-based, but details sharing your thumbnails folder with another machine - is this essentially the same as the symlink I've set up? If so, all that DDS does is store uncompressed files for the imagery, which I assume are larger.

Over a network, should I still see a boost in performance? My network is wired Gigabit, but presumably latency is the issue here...

- dora - 2010-09-29

Bommy Wrote:And every problem is just a bit more searching away from solved Smile

http://forum.xbmc.org/showpost.php?p=612485&postcount=4

I'm happy that my short instructions helped you.

Can anybody tell me why not using symbolic links for the whole userdata folder instead of using the external MySQL db?
I already asked it in a new thread (http://forum.xbmc.org/showthread.php?p=612485), but this thread is much more popular Wink so I'm sorry for asking agian.
What are the advantages of using MySQL? because, currently, It looks like it has more cons than pros.

- mwkurt - 2010-09-29

Hello,

I have made a symbolic link from my Win 7 PC to my Win 7 server pc and it appears to work, with one caveat.
The thumbnails do not appear until either I
a: do a search for the thumbnail using the "info" menu or
b: start the movie and and stop the movie

The backgrounds all seem to be showing up and are working good. Has anyone else using the sym links ran into this problem? Is there any way to get my thumbnails to show up short of going through each of my movies and doing one of the above mentioned remedies?

Another question...after deleting the "Thumbnails" folder on my HTPC computer, and installing the sym link, will XBMC create another "Thumbnails" directory where the deleted one was?

After doing some more checking, I have come to the conclusion that "I don't know what I am doing".

Could someone help me along?

Here are my computer's paths:

My Server PC:

/192.168.1.6/users/server/AppData/Roaming/XBMC/userdata/Thumbnails

My HTPC:

/users/Mark/Appdata/Roaming/XBMC/userdata/Thumbnails

I am trying to link the Thumbnails folder from my "Server PC" to my "HTPC".

I believe that I tried the following:

mklink /d \users\Mark\AppData\Roaming\XBMC\userdata\Thumbnails \192.168.1.6\users\server\appdata\Roaming\XBMC\userdata\Thumbnails

I tried this in a DOS window. Both computers are Win 7 32 bit and both users directories are on the "C" drive.

Is this the correct format for making the links? If not, how would the format go, please?



Thanks,
Mark