Slow MySQL
#16
what makoto is saying is that mysql seems to do DNS queries on the clients that connect to it.
Home networks usually do not have their own DNS server, so those lookups fail.
Instead you can put a line in /etc/hosts(on your NAS/Mysql running server) that tells your system the hostname of your clients.
But this is only a workaround, its probably better to just disable DNS lookups in mysql directly:
just add the "skip-name-resolve" directive in /etc/my.cnf

Code:
[mysqld]
...
...
skip-name-resolve

I'm not sure if this is actually your problem, but worth a try. In case it does not help, you should post a debug.log
Reply
#17
@wsnipex,

Thanks for getting back to me! I added the skip-name-resolve to a newly created my.cnf file in my etc/ directory (along with all the other things @AenimaX99 suggested earlier in this thread). I added the my.cnf file along with all the custom queries I found in this thread and the other main sql thread and they def cut my wait time significantly but there's still a big lag. It used to take me over 30 secs after I clicked on Movies or TV Shows on the Home Window for my Library to pull up. Now its between 12-15 secs. Here's my log (http://pastebin.com/PwPd4Xve). Any help would be greatly appreciated.
Reply
#18
Its not the DB queries:
19:18:35 T:115761152 DEBUG: RunQuery took 565 ms for 178 items query: SELECT * FROM tvshowview
This took 0,5 sec, not the fastest query in the world, but definately not the cause for your lag.

If you take a look at your debug log, you will find that its two things bogging down your system:
1.) The Aeon Nox skin - this one is VERY heavy on resources and probably not a good choice for an ATV. Please try default confluence for a comparison.
2.) Loading thumbs and fanart from smb also regularly takes ~1 sec. Not sure if this is normal, as I don't use smb. NFS might be worth a try, but maybe someone else with more SMB/NFS experience can comment on that.
What network are you using?
Reply
#19
wsnipex Wrote:Its not the DB queries:
19:18:35 T:115761152 DEBUG: RunQuery took 565 ms for 178 items query: SELECT * FROM tvshowview
This took 0,5 sec, not the fastest query in the world, but definately not the cause for your lag.

If you take a look at your debug log, you will find that its two things bogging down your system:
1.) The Aeon Nox skin - this one is VERY heavy on resources and probably not a good choice for an ATV. Please try default confluence for a comparison.
2.) Loading thumbs and fanart from smb also regularly takes ~1 sec. Not sure if this is normal, as I don't use smb. NFS might be worth a try, but maybe someone else with more SMB/NFS experience can comment on that.
What network are you using?

Just the kind of info I wanted, Thanks! I will switch to Confluence and report back. I've read of people having various issues with NFS and the like and am frankly not thrilled with having to redo the whole db, but this is the second time switching to NFS has been suggested to me in so many days, so maybe I should take a hint...Smile
Reply
#20
The DB has nothing to do with NFS/SMB. Its just a way to access content(files) on remote shares.

Anyway, try confluence first and see if it makes a difference. As I already said about NFS, I don't know if it would make a real difference in connection with XBMC, so I leave this to others with more experience with it.

I can only say that for general file serving purposes(company grade file servers), NFS has always performed better for me than SMB.
Reply
#21
wsnipex Wrote:The DB has nothing to do with NFS/SMB. Its just a way to access content(files) on remote shares.

Anyway, try confluence first and see if it makes a difference. As I already said about NFS, I don't know if it would make a real difference in connection with XBMC, so I leave this to others with more experience with it.

I can only say that for general file serving purposes(company grade file servers), NFS has always performed better for me than SMB.

Wouldn't I have to rebuild my MySQL? If I'm changing the sources in sources.xml I just assumed it would have to be re-read into the MySQL db, is that incorrect?
Reply
#22
wsnipex Wrote:The DB has nothing to do with NFS/SMB. Its just a way to access content(files) on remote shares.

Anyway, try confluence first and see if it makes a difference. As I already said about NFS, I don't know if it would make a real difference in connection with XBMC, so I leave this to others with more experience with it.

I can only say that for general file serving purposes(company grade file servers), NFS has always performed better for me than SMB.

One more question about possibly speeding up MySQL w/ NFS (and I realize you've clearly stated that NFS vs SMB isn't your wheelhouse but hopefully at least someone else might chime in):

Since MySQL requires me to use the same path as a source across clients, is NFS not possible with MySQL if you have a set up with, for example, one windows 7 based HTPC and 1 ATV2? This post (http://forum.xbmc.org/showthread.php?tid=105742) and others I've read gave me some conflicting information. Could I just enable NFS on my NAS and change the source in sources.xml from smb://xbmc:[email protected]/Movies to nfs://192.168.1.85/Movies on both ATV2 and Windows installations? Or do I have to use a mapped drive on my windows machine, thus making the 2 clients sources different?
Reply
#23
seth.feinberg Wrote:Wouldn't I have to rebuild my MySQL? If I'm changing the sources in sources.xml I just assumed it would have to be re-read into the MySQL db, is that incorrect?

Hmm yeah, if you mount smb/nfs directly from within xbmc you might have to rebuild the DB.
If you had the shares mounted via the OS (e.g. /etc/fstab on linux) you wouldn't have to.

Did you already test confluence?
Reply
#24
wsnipex Wrote:Hmm yeah, if you mount smb/nfs directly from within xbmc you might have to rebuild the DB.
If you had the shares mounted via the OS (e.g. /etc/fstab on linux) you wouldn't have to.

Did you already test confluence?

Yes Sorry, I was really busy 2 nights ago and didn't get to it. Tested Confluence last night and the lag when entering TV Shows or Movies was 2-3 secs. Thanks so much @wsnipx! I guess I'll put the NFS debate/project on the back burner, but I'm always interested in optimizing so I'm sure it won't be there for long...
Reply
#25
Hi all,

I've got SQL installed on my ReadyNAS Duo, and I've got the database setup and XBMC connecting to it just fine. I've even got the skip-name-resolve in the my.cnf file (both of them that I could find).

It's still horribly slow. Is it the ReadyNAS or something I could sort?


Thanks for any advice Smile


EDIT: In fact, I don't think it's connecting to the SQL database at all, and yet everything is setup right ip address and port). It's still using its local database.
Image
Reply
#26
Hi,

I was having this problem on one 4 mythbuntu machines. I saw this thread and thought that there had to be something more to it. My mysql server worked perfectly well for many other purposes including xbmc on 3 other machines. Once I saw the solutions posted in this thread I took the opposite approach. Instead of stopping the server looking for the client, I thought lets make sure the server finds the client. Installed avahi and it immediately worked! It seems I already had avahi installed on the other clients.

So that works for ubuntu clients, assume bonjour is automatically installed on macs. Not sure what the windows equivalent is?

Hope this helps someone,
Cheers
Phil
Reply
#27
If you happen on this thread and you are still curious how to create a my.cnf file on your synology box, check out post#25 in the following post:

Synology my.cnf
Reply

Logout Mark Read Team Forum Stats Members Help
Slow MySQL1