[SOLVED] Mysql optimization for xbmc question

I read a lot of guides for setting up mysql libraries for xbmc, some were outdated, etc.

 

I finally got it set up.

 

Now, a lot of the guides suggest adding "skip-name-resolve" to my.cnf in mysql because apparently it makes xbmc more responsive.

 

I cannot find info on how to add that to mysql running on unraid. I read that unraid uses my-medium.cnf as the my.cnf, but I do not know how to go about it. Can anyone help me?

 

I think it's accurate to say that I am a total linux newb. My linux knowledge does not extend beyond ls and cd :-)

 

Thanks in advance

I'm planning on setting this up as well. Which guides did you find most useful. Only one I've really looked at was the one on lifehacker.

this is one on XBMC forum itself

 

http://forum.xbmc.org/showthread.php?p=646799&highlight=CHARACTER+SET#post646799

 

There is one here on the forums too ...

 

I have it running but without the "skip name resolve"

tried adding it to my.cnf but the mysql server didn't want to restart with it in it ... so i forgot about it

 

With Eden your advanced settings xml can loose the database name

 

mine looks like this

 

<videodatabase>

<type>mysql</type>

<host>192.168.0.10</host>

<user>xbmc</user>

<pass>xbmc</pass>

</videodatabase>

 

for music database…

<musicdatabase>

<type>mysql</type>

<host>192.168.0.10</host>

<user>xbmc</user>

<pass>xbmc</pass>

</musicdatabase>

 

Xbmc will create the databases and since we are running beta's in eden they already changed tables 2 times

if you make your database and give it a name then you will have issues upgrading ...

see numerous posts in the xbmc eden beta 3 release thread

 

i just upgraded to eden 3 without issues

 

mine is working relatively quick with 32000 tv eps and 4100 movies

i just might put the thumbs/fanart back on the machines and sync it with rsync so the fanart showing in xbmc could be a bit quicker ... not sure if it will help a lot

but it is on my todo list

 

 

I read a lot of guides for setting up mysql libraries for xbmc, some were outdated, etc.

 

I finally got it set up.

 

Now, a lot of the guides suggest adding "skip-name-resolve" to my.cnf in mysql because apparently it makes xbmc more responsive.

 

I cannot find info on how to add that to mysql running on unraid. I read that unraid uses my-medium.cnf as the my.cnf, but I do not know how to go about it. Can anyone help me?

 

I think it's accurate to say that I am a total linux newb. My linux knowledge does not extend beyond ls and cd :-)

 

Thanks in advance

 

What version of unraid are you using?  If you're on a 5.0 beta, are you using the mysql plugin?

I'll assume you're using unRAID 5 and the mysql plugin.  If it doesn't help you, it will probably help others.

 

My setup consists of my unRAID box running b14 and my XBMC clients running Eden beta 3.  If you're not running this same setup, these settings may or may not work for you.  This also assumes that you've already successfully setup and configured your XBMC database.

 

Before you do anything, you should make a backup of your database.  I also like to export my library as well.  There may be better ways to do this, but this is how I do it and it works well for me.

 

Telnet into my unRAID box and type the following (your installation directory may be different):

 

cp -pr /mnt/cache/.apps/mysql/MyVideos60/* /mnt/cache/.apps/db_backup/MyVideo60/

and

cp -pr /mnt/cache/.apps/mysql/MyMusic18/* /mnt/cache/.apps/db_backup/MyMusic18/

 

Then go to XBMC and navigate to System >> Settings >> Video >> Export video library.  When prompted, select "Single File"

Do the same thing for your music by navigating to System >> Settings >> Music >> Export music library.

 

I do this just in case something goes wrong.  It adds a little redundancy to the database backup process.

 

Now, in the Eden betas, you no longer need to specify a database name.  I'm showing this here because throughout the betas, the DB versions are getting updated and rather than updating my advancedsettings.xml file to point to the updated database with each new release, I let XBMC find it automatically.  Here's the portion of my advancedsettings.xml file that connects my clients to my database:

<advancedsettings>
    
     <!--
     #######################################################################################
     Allows advanced customization of the default database settings for video.
     #######################################################################################
     -->
    
     <videodatabase>
       <type>mysql</type>
       <host>###.###.###.###</host>
       <user>xbmc</user>
       <pass>xbmc</pass>
     </videodatabase>
    
     <!--
     #######################################################################################
     Allows advanced customization of the default database settings for music.
     #######################################################################################
     -->
    
     <musicdatabase>
       <type>mysql</type>
       <host>###.###.###.###</host>
       <user>xbmc</user>
       <pass>xbmc</pass>
     </musicdatabase>

     <!--
     #######################################################################################
     Sync the thumbails and fanart for our shared media library.
     #######################################################################################
     -->

     <pathsubstitution>
       <substitute>
         <from>special://masterprofile/Thumbnails/</from>
         <to>smb://###.###.###.###/cache/.apps/xbmc/Thumbnails/</to>
       </substitute>
     </pathsubstitution>
</advancedsettings>

 

Now, to start the optimizations.  You need to edit your my.cnf file for your mysql installation.  This is pretty easy to do if you're using the unRAID 5 plugin. When using the plugin method, the mysql plugin checks the plugin directory every time it is started (at reboot and/or via the enable/disable function in the unRAID settings) for a my.cnf file, if it exists, it will load it rather than create a new one.  With mysql running, telnet in to your unRAID box and type the following:

 

cp -p /etc/my.cnf /boot/config/plugins/mysql

 

This will copy your current my.cnf file to the mysql plugin directory.  From there you can open and edit the file using a text editor from your regular computer (\\tower\flash\config

plugins\mysql\my.cnf).  You need to add skip-name-resolve to the mysqld section.  This portion of my file looks like this:

 

# The MySQL server
[mysqld]
port		= 3306
socket		= /var/run/mysql/mysql.sock
skip-locking
skip-name-resolve
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

 

Save it and restart your mysql server.

 

Now you need to run some code to add/alter the database tables.  Telnet back into the box and type the following to login:

 

mysql -u root -p

when prompted enter your password.

 

Then type the following (edit it to reflect your databases):

 

use MyMusic18;
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 MyMusic18.song ADD INDEX idx_idArtist(idArtist);
ALTER TABLE MyMusic18.song ADD INDEX idx_idGenre(idGenre);
ALTER TABLE MyMusic18.song ADD INDEX idx_idAlbum(idAlbum);

use MyVideos60;
ALTER TABLE movie ADD INDEX idMovie(idMovie);
ALTER TABLE movie ADD INDEX idFile(idFile); 

 

Your setup should now be optimized.  I take no credit for any of the above information.  I've simply compiled it from various sources found via google.

Hi caimakale,

 

I should have mentioned that I am using 4.7

 

Does my.cnf work the same way in 4.7 as well? (I can't check until I get home tonight)

 

By the way, thanks for all the replies guys, I, myself, spent a long, long time figuring out why xbmc would not connect to the mysql database, before I realized I had specified database names in my advancedsettings.xml file which apparently is not needed and worse, not tolerated in eden. Too bad the guides I was following were not updated to point that out.

Hi caimakale,

 

I should have mentioned that I am using 4.7

 

Does my.cnf work the same way in 4.7 as well? (I can't check until I get home tonight)

 

By the way, thanks for all the replies guys, I, myself, spent a long, long time figuring out why xbmc would not connect to the mysql database, before I realized I had specified database names in my advancedsettings.xml file which apparently is not needed and worse, not tolerated in eden. Too bad the guides I was following were not updated to point that out.

I'm making another assumption here, and it's that you're using the unmenu package installer for the mysql database.  I'm not sure if it will recognize a my.cnf file in the package directory at each reboot.  You can always try it.  Stop mysql, put your my.cnf file in the directory, and restart mysql.  Then use vi to look at the loaded my.cnf file.  If the changes are there, then you know it works.  Others may have some insight that I obviously don't.  I just started using unRAID a few weeks ago and am still learning things myself.  I jumped right in to version 5.0b14 so I have no experience with older versions.

Thank you for the tips caimakale

 

I did not get a chance to try this yet, as I have been too busy with setting up VirtualBox.

 

Not to mention, xbmc has been working with no problems (just as well as it did with local db) even without the skip-name-resolve, so I am not really in a rush to figure that out.

 

But I will come back to post my findings once I try

 

Thanks again

alright I finally figured it out.

 

In unmenu, you can go to "Config View/Edit" and select the unmenu-mysql-package

 

In there, you will see a line that goes

 

PACKAGE_INSTALLATION cp /etc/my-medium.cnf /etc/my.cnf

 

What it does is: when mysql is installed from the package, my-medium.cnf is copied to the etc folder. It copies that file to become my.cnf

 

You can't just change my.cnf, because after a reboot, it reverts back. Changing the my-medium.cnf is tricky as well, because then you have to change the whole package and the checksum doesn't match, etc.

 

I found the easiest way to create a custom my-medium.cnf on the flash drive under custom, and I change the conf file to copy that one to my.cnf instead of the one that comes out of the package

 

so I changed that line to:

 

PACKAGE_INSTALLATION cp /boot/custom/my-medium.cnf /etc/my.cnf

 

in order for the changes to take effect, go to package manager, select mysql, click on edit configuration values, do not change anything but just click on save new values and click on reinstall

 

it might give you an error (it did in my case when restarting mysql)

 

you can either manually stop and restart mysql, or just reboot the server, and you should be good to go.

 

Hope it helps others

alright I finally figured it out.

 

In unmenu, you can go to "Config View/Edit" and select the unmenu-mysql-package

 

In there, you will see a line that goes

 

PACKAGE_INSTALLATION cp /etc/my-medium.cnf /etc/my.cnf

 

What it does is: when mysql is installed from the package, my-medium.cnf is copied to the etc folder. It copies that file to become my.cnf

 

You can't just change my.cnf, because after a reboot, it reverts back. Changing the my-medium.cnf is tricky as well, because then you have to change the whole package and the checksum doesn't match, etc.

 

I found the easiest way to create a custom my-medium.cnf on the flash drive under custom, and I change the conf file to copy that one to my.cnf instead of the one that comes out of the package

 

so I changed that line to:

 

PACKAGE_INSTALLATION cp /boot/custom/my-medium.cnf /etc/my.cnf

 

in order for the changes to take effect, go to package manager, select mysql, click on edit configuration values, do not change anything but just click on save new values and click on reinstall

 

it might give you an error (it did in my case when restarting mysql)

 

you can either manually stop and restart mysql, or just reboot the server, and you should be good to go.

 

Hope it helps others

You may want to go back into the editor once more and add one line to the unmenu-mysql-package file you edited.

 

Somewhere in it, add a new line that looks exactly like this:

#AUTO_UPDATE=NO

 

If you do not add that line, the next time you press the "Check for Updates" button in unMENU, it will notice the change you made to the .conf file (the checksum will not match) and it will think the version in google.code needs to be copied over your version.

 

With the added line, it will simply skip the update, although it will download the version from google.code to a temp directory so you can compare them and incorporate any changes if it indeed is an updated version.

 

It was kind of tricky to figure out a way to allow a way to have a self-contained package configuration file, with user-defined variables and still allow people like yourself the ability to make improvements and not have them overwritten.    Glad you figured it out.

 

I don't currently have a central xbmc database here, but probably should.  I've got 4 xbmc instances in the house, and keeping the libraries in sync is a bit of work.  Unfortunately, right now, one is on 11.0rc1, one on 11.0beta2, and  two on 10.1openelec.  Soon they'll all be on the 11.0 version where I can make changes more consistently between them.  When that occurs, I'll be reading these same threads getting tips from others like you who have done the same with their xbmc instances..

 

Joe L.

Thank for the tip Joe L.

 

I went back in and put the auto update line in there. UnMenu is pretty amazing, and as I was browsing the wiki and the forums the other day, I stumbled upon the thread where you first started developing unmenu. It was a fun read. Especially knowing now how useful and crucial it turned out to be for any unraid user. Thanks for all your efforts.

 

Regarding xbmc, you should definitely switch to mysql databases especially with having 4 boxes.

 

The only thing is though (as I am sure you already are aware) 11.0 introduces significant database changes, so with a single mysql database, all the boxes have to be either 11 or 10. But it is nice to update on one box and have everything else be in sync, including watched tags and bookmarks. You can stop playback in one room, and then pick up in another, it doesn't get any better. (Especially in my house, where I constantly get kicked out of the living room by my wife, whenever she takes a nap or has a friend come over to study with, etc. :-) I can just pick up my show in the other room from where I left off.

 

Oh and by the way, in case anyone's interested, the skip-name-resolve did not seem to make a difference for xbmc eden, however it fixed the excruciating slowness of digiKam. Now it is just a little slow, but not too bad. 

This is a great thread when paired with hernandito's original "HowTo" topic (updates from Tight_wad on page 7 for Eden users: http://lime-technology.com/forum/index.php?topic=11473.msg170004#msg170004)

 

Bringing it all together: while since you're creating a custom my-medium.cnf anyway, might as well take the advice here:

 

http://lime-technology.com/forum/index.php?topic=16098.msg159984#msg159984

 

...and add the following lines below the "skip-name-resolve" line:

 

query_cache_size = 8388608
query_cache_type = 1
query_cache_limit= 1048576

 

This will default the cache size to 8 MB. I haven't played with it yet, but I have to believe this is PLENTY for XBMC. Note that "1" ("ON") is the default value for query_cache_type, and "1048576" (1 MB) is the default value for query_cache_limit, so adding the last two lines are redundant. However, having them there will make it easy to tweak if you want to play around.