[richardss]

I'm experiencing slow responses using the MySQL databases under XBMC Dharama (~20-30 secs to show the Movies list)

One of the tips that comes up frequently on these forums is to "skip-name-resolve" on the MySQL database.

I've got MySQL running on a Synology NAS, controlled through PhpMyAdmin, and I cannot for the life of me figure out where I'm supposed to add that code.

Can anyone shed some light on this?

Many thanks, Rich

[xgamer99]

A quick Google search for "mysql skip-name-resolve" turned up this:

http://lists.mysql.com/mysql/180963

Don't think you can do it via phpMyAdmin; I would assume you're NAS uses Linux. Not sure how familiar you are with Linux, but you would have to gain root access and modify the MySQL config file which is usually located at /etc/my.cfg or something similar.

[Aenima99x]

You need to add it in /etc/my.cnf
This is how mine the relevant part looks on the my.cnf file on my Synology NAS. HOWEVER....this isn't what solved the slowness for me, it was the following SQL queries below that did it....

Code:

# The MySQL server
[mysqld]
port        = 3306
socket        = /tmp/mysql.sock
skip-name-resolve
skip-external-locking
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
max_allowed_packet = 1M
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
net_buffer_length = 8K
read_rnd_buffer_size = 512K

Run these from phpMyAdmin on the Music DB
1. Fix for Delay of 3 minutes when you click on an Artist in Library mode.

Code:

CREATE INDEX test_idx ON song(idAlbum);

2. Fix for Delay of 20 seconds when you click on "Artists"

Code:

alter table song add index idArtist(idArtist);

3. Fix for Delay of 10 seconds when you click on "Genres"
alter table song add index idGenre(idGenre);

[xgamer99]

Is there any way to see the queries XBMC runs? Is it in the debug log? Do these delays persist when you access the database via localhost and not remotely (where mysql is running)?

[richardss]

Thanks Aenima99x... I am trying to create a my.cnf file like yours but am unable to do this.

I've logged in through ssh (and also tried telnet) through putty. I've then opened up an editor: vi /etc/my.cnf. When I try to save the file however it says permission denied (I've tried this as both 'root' and 'admin')

When I try to open/save files using WinSCP i get:
"Cannot execute SCP to start transfer. Please make sure that SCP is installed on the server and path to it is included in PATH. You may also try SFTP instead of SCP.
Command failed with return code 127." (I've tried SCP, FTP and SFTP to do this)

Any suggestions would be much apprecitaed it. I've trawled Google for what the issues I'm having with modifying files on my Synology server in this way but not finding anything useful.

[paradroid]

Aenima99x - That made a massive difference thanks! I went from 20 second queries to practically instant artist lookup by adding the index.
Would be great to get this into the default build - any reason it shouldn't?

[odt_x]

Thanks for all the info here. Really helped with browsing the music library. One thing though I'm not sure if it's normal, but the following operation still takes 5 seconds even after creating all 3 indexes:


15:49:40 T:2752 M:1990627328 DEBUG: CMusicDatabase::GetArtistsNav query: select * from artist where (idArtist IN (select album.idArtist from album) or idArtist IN (select exartistalbum.idArtist from exartistalbum join album on album.idAlbum = exartistalbum.idAlbum where album.strExtraArtists != '')) and artist.strArtist != ""
15:49:45 T:2752 M:1990705152 DEBUG: Time to retrieve artists from dataset = 5059


Is this normal? There's 1481 items displayed.

[odt_x]

Thanks for all the info here. Really helped with browsing the music library. One thing though I'm not sure if it's normal, but the following operation still takes 5 seconds even after creating all 3 indexes:


15:49:40 T:2752 M:1990627328 DEBUG: CMusicDatabase::GetArtistsNav query: select * from artist where (idArtist IN (select album.idArtist from album) or idArtist IN (select exartistalbum.idArtist from exartistalbum join album on album.idAlbum = exartistalbum.idAlbum where album.strExtraArtists != '')) and artist.strArtist != ""
15:49:45 T:2752 M:1990705152 DEBUG: Time to retrieve artists from dataset = 5059


Is this normal? There's 1481 items displayed.

Looks like adding the following indexes fixes the problem, thanks to Saviq and y from IRC.

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);

[boulala]

Thanks Aenima99x... I am trying to create a my.cnf file like yours but am unable to do this.

I've logged in through ssh (and also tried telnet) through putty. I've then opened up an editor: vi /etc/my.cnf. When I try to save the file however it says permission denied (I've tried this as both 'root' and 'admin')

When I try to open/save files using WinSCP i get:
"Cannot execute SCP to start transfer. Please make sure that SCP is installed on the server and path to it is included in PATH. You may also try SFTP instead of SCP.
Command failed with return code 127." (I've tried SCP, FTP and SFTP to do this)

Any suggestions would be much apprecitaed it. I've trawled Google for what the issues I'm having with modifying files on my Synology server in this way but not finding anything useful.

same problem here. i'm not very telnet/ssh/mysql save so please if someone could write out the steps needed to make those changes to the config.

[makoto42]

So I was hitting an obvious 5 second delay on every MySQL connection. The resolution was MySQL server was timing out on attempting a reverse lookup of the client. I put an entry for client IP in /etc/hosts in my case and DB became instant.

[seth.feinberg]

same problem here. i'm not very telnet/ssh/mysql save so please if someone could write out the steps needed to make those changes to the config.

Did anyone find an easy way to make these changes when MySQL is running on a Nas (like my Synology)? It seems like @Aenima99x implied above that many of these changes can be done in phpMyadmin? Is this true? Or will I have to read up on vi to try out these changes?

Also, this is probably neither here nor there, but I read in a thread that numerous users had reported MySQL performing very slow when, after initial database set up, they IMPORTED their old library, instead of rebuilding from scratch. Does anyone have any idea if this is a legitimate thing to test? I'd like some feedback before I start the process all over again...

Thanks for your help!

[wsnipex]

all changes to /etc/my.cf need to be done on the shell or copied over via scp/ftp etc, beware of windows editors they usually break things(windows line endings).
If you get permission denied try this as root:

Code:

chmod 644 /etc/my.cf

then edit/save again.


All SQL queries can be done from phpmyadmin or any other SQL client.

[seth.feinberg]

all changes to /etc/my.cf need to be done on the shell or copied over via scp/ftp etc, beware of windows editors they usually break things(windows line endings).
If you get permission denied try this as root:

Code:

chmod 644 /etc/my.cf

then edit/save again.


All SQL queries can be done from phpmyadmin or any other SQL client.

Thanks for the tips @wsnipex! I actually just read it as I pulled up the thread to post an update, but the help is nonetheless appreciated.

I actually puttied (can I verb that word?) into my nas and used the vi command (which I learned all by myself, thank you very much ) to create the same /etc/my.cnf file as @Aenima99x posted a few back. I really don't know what all (any) of that does but I didn't encounter a permissions issue. However, I didn't get any noticeable performance improvement on my ATV2 client. The HTPC still works snappy as ever but the bedroom one 30+secs to load the TV Shows window or the Movies window (I haven't gotten my music collection in order yet so haven't tested that). It was also pretty slow once inside my Movie/TV Show collections and as I was slowly scrolling down it crashed a couple times. Unfortunately I'm at work and can't paste my log but I did put the crashreport on pastebin this morning before I left:

http://pastebin.com/wr1DgB25

I'm not entirely sure how to read/post the crashreport but I did notice some memorypressure lines, which I'm sure relate to something in the process stretching the crappy specs of the ATV2. But I've seen multiple users report this working great with an ATV2 so it must be something I'm doing wrong. Anybody got any ideas?

Edit: I also made all of the queries in phpMYadmin (hitting the SQL tab in the upperleft corner) that @Aenima99x suggested and that were suggested in this post (http://forum.xbmc.org/showpost.php?p=646799&postcount=5).

[itsikg]

Hello,
Been having a similar problem - using MySQL running on Synology DS410.
I used telnet for connecting to my NAS, but couldn't find my.cnf file in /etc.

Do I need to create it? What parameters should I put in it?
BTW - what is the command to restart the MySQL service on the NAS (I'm not familiar with Linux commands...)

Thanks!

[seth.feinberg]

So I was hitting an obvious 5 second delay on every MySQL connection. The resolution was MySQL server was timing out on attempting a reverse lookup of the client. I put an entry for client IP in /etc/hosts in my case and DB became instant.

@makoto, could you expand on this a little bit? I've added the my.cnf and basically just copied what @Aenima99x wrote (though admittedly didn't understand much of what I put in there). That and the custom queries sped things up a bit, but I'm still experiencing 5-15sec delays when opening "TV Shows" or "Movies" from the home screen. and am hoping this etc/hosts business is the last hurdle.

@itsikg you indeed will have to create the my.cnf file in /etc/ but as my earlier posts reveal, I really don't know anything about what you add to it. I copied Aenima99x's my.cnf a few posts earlier. I created it by logging into my synology nas with Putty as root (and putting in the password) and using the vi command to create the file. Here's a link on the vi command (http://www.lagmonster.org/docs/vi.html). Mostly you would cd into /etc/ and type vi my.cnf, then hit "i" to enter "insertion mode", enter in what you want to enter and then shift+ZZ to save it. Obviously I've simplified the process a bit and I'm anything but an expert so I'd read the link until you're comfortable with the vi command. Hopefully some of the other, more knowledgeable members on this thread can illuminate some of the things put into the my.cnf file.