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

+- Kodi Community Forum (http://forum.kodi.tv)
+-- Forum: Help and Support (/forumdisplay.php?fid=33)
+--- Forum: Kodi General Help and Support (/forumdisplay.php?fid=111)
+---- Forum: Windows support (/forumdisplay.php?fid=59)
+---- Thread: 2 XBMC PCs and 1 Database on the shared NAS (/showthread.php?tid=70603)



- phsyraxion - 2011-08-18 03:15

RockDawg Wrote:I switched back to local Thumbs and I do see that networked is a tad bit slower. When I fast scroll through my movie list the covers disappear and after I stop it takes a fraction of a second for them to populate. It only does this the first time through then they are all cached and it's instantaneous just as if the thumbs were local. I would give the Dropbox method a try, but I run Live and OpenELEC so I wouldn't know where to begin to set it up on those. At the same time, it's not a big deal for me because it's only slower the first time through your list after you boot and I use sleep instead of shutdown and I rarely reboot. So mines instantaneous 90% of the time anyhow.

The Thumbnails folder size could be an issue for some at some point in time with Dropbox, but you my want to try and clean up your Thumbs because XBMC doesn't remove them for media you delete. I have 976 movies, 3932 TV episodes and 3744 songs and my Thumbnails folder is 1.67 GB. Here is a script to remove unused thumbs in case you weren't aware of it:

http://forum.xbmc.org/showthread.php?tid=96097

Yeah my system has:

MUSIC: 11, 000+ Songs, 500+ Albums
TV: 10, 000+ Episodes, 170+ Shows
MOVIES: 900+ Movie Files
PHOTOS: 8000+

This generates a LOT of thumbs... Also when I browse through folders full of icons to set as folder images, these are all cached as well. I might try and clean the thumbs DB but wouldn't want to rely on having to keep the database under 2.5GB just to use Dropbox.

I will test my script in the coming days and see no real issue with its function apart from the delay in the time it would take to scan and sync all machines but this would be less than 5 minutes from when a machine is on if I poll each machine every minute from the server. It would take about 2 minutes to crawl and compare thumb folders and a central DB would always be held on the server


- RockDawg - 2011-08-18 03:20

Yah, I can certainly understand that. 2 GB is certainly a limitation for a lot of people.


Pesky Issues - johnny_swindle - 2011-08-18 19:23

I have 4 XBMC machines and been migrated to use a central MySQL database and all 4 use symbolic link to a centralized store for the thumbnail folder and I have attempted to optomize the database using indexes listed on this forum. It is working out very well except for the following two issues.

Issue #1: when I ask any one of the 4 machines to update the either the TV Series or Movies library, it will freeze. This happens at ramdom with no pattern. All systems are set to prevent spin down of hard drives and none of the system is allow to go into standby or sleep mode.

Issue #2 If any of the systems are idle for prolong periods (12 hours or more) and I attempt to update a library (TV, Movies, Music) the system freezes.

Specs for the systems involved:

4 Zotac Mag systems using Windows 7 Pro/XBMC 10.1
1 WHS with 7 terabytes of storage (all 7200 RPM drives), MySQL 5.1 Community

Any ideas on how to eleminate the freeze ups?


- phsyraxion - 2011-08-19 00:39

What are the sizes of your media collections?

I don't have an answer but I am in the process of setting up this SQL database sharing and want to know if this will be problimatic. Seems a 50/50 so far with people. Some having no issues, some having many issues.


- johnny_swindle - 2011-08-19 01:08

There are many with much larger collections, but mine:

326 movies
44 individual series (2133 total episodes
217 individual music folder (16,000+ songs-----50.8 gigs)

I am pleased with the entire XBMC experience, but the freeze ups are getting a little old and may make me look seriously at Windows Media Center.

phsyraxion Wrote:What are the sizes of your media collections?

I don't have an answer but I am in the process of setting up this SQL database sharing and want to know if this will be problimatic. Seems a 50/50 so far with people. Some having no issues, some having many issues.



- tboooe - 2011-08-19 01:12

phsyraxion Wrote:What are the sizes of your media collections?

I don't have an answer but I am in the process of setting up this SQL database sharing and want to know if this will be problimatic. Seems a 50/50 so far with people. Some having no issues, some having many issues.

I have about 200 movies and 30K songs. Music is my problem. Even on my system where the MYSQL database resides, it takes a while for me to access the music library. The remote systems are even slower to access the library. Movies are no problem.


- phsyraxion - 2011-08-19 02:41

tboooe Wrote:I have about 200 movies and 30K songs. Music is my problem. Even on my system where the MYSQL database resides, it takes a while for me to access the music library. The remote systems are even slower to access the library. Movies are no problem.

Is your music library fully populated (details etc)? It would be some kind of issue with the amount of data that would be loading but being only text I would think that should be pretty quick.


- MrDVD - 2011-08-19 02:45

tboooe Wrote:I have about 200 movies and 30K songs. Music is my problem. Even on my system where the MYSQL database resides, it takes a while for me to access the music library. The remote systems are even slower to access the library. Movies are no problem.

You did this commands at an MySQL Admin tool ? (like Heidisql)

Music:
Code:
CREATE INDEX idAlbum_idx ON song(idAlbum);
CREATE INDEX idArtist_idx ON song(idArtist);
CREATE INDEX idArtist_idx ON album(idArtist);
CREATE INDEX idArtist_idx ON exartistsong(idArtist);
CREATE INDEX idArtist_idx ON exartistalbum(idArtist);
ALTER TABLE xbmc_music.song ADD INDEX idx_idArtist(idArtist);
ALTER TABLE xbmc_music.song ADD INDEX idx_idGenre(idGenre);
ALTER TABLE xbmc_music.song ADD INDEX idx_idAlbum(idAlbum);
Movie:
Code:
ALTER TABLE movie ADD INDEX idMovie(idMovie);
ALTER TABLE movie ADD INDEX idFile(idFile);
Here it helped a lot.

Edit:
You also try to change the cache size that mysql use:
Code:
set global query_cache_size = 10000000;



- tboooe - 2011-08-19 02:47

phsyraxion Wrote:Is your music library fully populated (details etc)? It would be some kind of issue with the amount of data that would be loading but being only text I would think that should be pretty quick.

Yup. The library is fully populated with details. I think perhap not using library mode would help but I love all the eye candy of the fanart and folder jpgs. Maybe using a more powerful processor would help. I am thinking of building an htpc with the i3 chip.


- phsyraxion - 2011-08-19 13:44

I'm a bit concerned about these issues as I will have a large library on all media. My tests are only on small scale at the moment but I will soon start ramping this up and see how it works. I'd hate to get right into it and find the system grinds to a halt. My htpc is a core 2 duo 3.2ghz and so is the server. Have to keep testing me thinks.


- blademansw - 2011-08-19 14:23

I have about 100 movies, 60 TV shows but 16,000 songs with shared thumbs

That works fine, the only thing that is slow is when you go into music by artist view - very slow, however, on the XBMC android remote by artist view, its fast. Work that one out!


- tboooe - 2011-08-19 22:35

MrDVD Wrote:You did this commands at an MySQL Admin tool ? (like Heidisql)

Music:
Code:
CREATE INDEX idAlbum_idx ON song(idAlbum);
CREATE INDEX idArtist_idx ON song(idArtist);
CREATE INDEX idArtist_idx ON album(idArtist);
CREATE INDEX idArtist_idx ON exartistsong(idArtist);
CREATE INDEX idArtist_idx ON exartistalbum(idArtist);
ALTER TABLE xbmc_music.song ADD INDEX idx_idArtist(idArtist);
ALTER TABLE xbmc_music.song ADD INDEX idx_idGenre(idGenre);
ALTER TABLE xbmc_music.song ADD INDEX idx_idAlbum(idAlbum);
Movie:
Code:
ALTER TABLE movie ADD INDEX idMovie(idMovie);
ALTER TABLE movie ADD INDEX idFile(idFile);
Here it helped a lot.

Edit:
You also try to change the cache size that mysql use:
Code:
set global query_cache_size = 10000000;

Yup, I have added all these to my database. For whatever reason, going into Artist view in the music library is sloooow. I wonder if upgrading to a faster processor would help?


- tboooe - 2011-08-19 22:37

blademansw Wrote:I have about 100 movies, 60 TV shows but 16,000 songs with shared thumbs

That works fine, the only thing that is slow is when you go into music by artist view - very slow, however, on the XBMC android remote by artist view, its fast. Work that one out!

+1. Me too! Kind of a bummer. I stopped using XBMC for music since it was so painfully slow. This is a shame because its cool seeing all the artwork, bios, fanart slideshow, etc.

Anyone else with large music library experiencing slow access? Perhaps my PC is too slow? One of my HTPC is an Atom 525 and the other is a Celeron 2300. I am thinking of building an i3-2105 system.


- phsyraxion - 2011-08-22 04:51

Well I have done further testing and started loading more data into XBMC.

My music library is up to 9000 songs (1500 Artists) fully populated with data and images. It takes me 3 seconds from either test machine to open any view (album/artist/genre etc). Both machines access a MYSQL database on my server and I have applied the optimisation queries to this database. Any change on one machine is reflected on the other machine immediately

My TV Show library is 12,000 episodes (170 shows) and again is fully populated. This again only takes 3 seconds to open and browsing is instant.

The script I have been working on keeps thumbs synced perfectly. Any change of a thumb image on one machine is uploaded to the server and sent to connected HTPC machines once they are online. This same script also syncs playlists, favourites and media source paths (all selectable to be synced on/off) by using a central location on the server to send and receive stuff from.

This is all stuff I have wanted working for years and has actually been a little too easy for me to get going which is why I am concerned that this type of setup is going to have a big issue somewhere.

Only 1 thing I have had issues with... The Aeon Nox skin has a really neat "random episode" & "random album" widget that displays on the home page which I love. It seems to have issues working with an SQL database and causes XBMC to disconnect from the database for some reason. This is a small issue but still a shame. Hopefully this can be fixed in time.


- generious - 2011-11-08 13:52

Just after setting this up using the commands below.

Code:
CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';

CREATE DATABASE xbmc_video CHARACTER SET latin1 COLLATE latin1_general_ci;

CREATE DATABASE xbmc_music CHARACTER SET latin1 COLLATE latin1_general_ci;

GRANT ALL ON *.* TO 'xbmc';

GRANT ALL PRIVILEGES ON *.* TO 'xbmc'@'%' IDENTIFIED BY 'xbmc';
Verified everything was okay by doing the below

Code:
SELECT host,user from mysql.user;
SHOW DATABASES;

Modified the my.ini with the details below
Code:
mysqld
port = 3306
bind-address = 192.168.0.24
# skip-networking

Changed the permissions on the xbmc account to DBDesigner

After that I opened the firewall ports using
Code:
netsh advfirewall firewall add rule name="MySQL Server" action=allow protocol=TCP dir=in localport=3306

But it is not working well I can connect to using xbmc10.1/workbench and heidi

11:09:03 T:2820 M:2656104448 ERROR: Error attempting to update the database version!
11:09:03 T:2820 M:2656104448 ERROR: Can't update the database xbmc_video from version 0 to 42
11:09:03 T:2820 M:2656104448 ERROR: SQL: Undefined MySQL error: Code (1050)
Query: CREATE TABLE version (idVersion integer, iCompressCount integer)
11:09:03 T:2820 M:2656104448 ERROR: CVideoDatabase::CreateTables unable to create tables:0
11:09:03 T:2820 M:2656104448 NOTICE: Attempting to update the database xbmc_video from version 0 to 42
11:09:03 T:2820 M:2656104448 ERROR: SQL: The table does not exist
Query: alter table settings add NonLinStretch bool
11:09:03 T:2820 M:2656104448 ERROR: Error attempting to update the database version!
11:09:03 T:2820 M:2656104448 ERROR: Can't update the database xbmc_video from version 0 to 42
11:09:03 T:2820 M:2656104448 ERROR: SQL: Undefined MySQL error: Code (1050)

For the 1050 errors I have already review post

The DB has been dropped, uninstalled MySQL and deleted all the content and registry keys and reinstalled it redid it the above again still the same problem.
MySQL is running on Windows Home Server 2011.

Anyone any ideas on how to fix those darned 1050's?

edit
btw advancedsettings.xml config

Code:
<advancedsettings>
    <videodatabase>
        <type>mysql</type>
        <host>192.168.0.24</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
        <name>xbmc_video</name>
    </videodatabase>

    <musicdatabase>
        <type>mysql</type>
        <host>192.168.0.24</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
        <name>xbmc_music</name>
    </musicdatabase>

<pathsubstitution>
    <substitute>
        <from>special://masterprofile/Thumbnails</from>
        <to>smb://192.168.0.24/Thumbnails/</to>
</substitute>
</pathsubstitution>
    <useddsfanart>true</useddsfanart>
</advancedsettings>


edit

got it all working now, what a pain...