[r3sno]

Hello,

I recently installed Ubuntu on my AppleTV. I have a mysql server that I use to access show listings, etc and while using the ATV is can be fairly slow when manuvering through items which need the database. Everything else moves along at a zipping speed.

While the broadcom chip improve this? Or are there any other suggestions to improving the lag. I have tried this on my my desktop and have had no issues moving through the those menus.

[Jimmer]

Broadcom CrystalHD will have to effect on your navigation/database problem. It's strictly for decoding content, has no impact on GUI, database, whatever...

It maybe a few things: is mysql a memory hog? I don't use it personally, but that 256 MB of RAM is precious stuff and rapidly runs out.... also have you checked that your swap is active? Maybe you are rapidly swallowing up your RAM with the mysql stuff and then there's nowhere to swap it out to....

Jim

[r3sno]

Are you saying the issues exist on the applet or the mysql server? These are two seperate machines.

Ive been able to access this data without lag from a desktop. Is there any other way to increase swp or to make this action faster?

[Jimmer]

Yeah sorry, I meant on the AppleTV.

Also have you got your max cstate set to 2 on the ATV? Think network performance can suffer without setting this value...

Course these are all just guesses! But at least it's somewhere to start....

Did you do your own install, or use one of the crystalbuntu images from the forum? what does free tell you in a ssh server while your box is under load?

Jim

[r3sno]

Also have you got your max cstate set to 2 on the ATV? Think network performance can suffer without setting this value...

I have done that. The guide I followed instructed that.

Did you do your own install, or use one of the crystalbuntu images from the forum? what does free tell you in a ssh server while your box is under load?

I dont have a large enough usb drive, so I opted for the minimized install and then adding xbmc and other addons etc. When I check top, the appletv never gets that high in cpu usage. It doesnt reach 1.00 for example. So, it seems fairly under utilized. I checked the server to see what happens and right before the appletv reacts the server load jumps and the mysql process jumps also. Im thinking the request it self is delayed a bit, because of the appletv.

Ive considered increasing the priorty of the xbmc.bin process or a few of those ideas. Ive been able to play 720p video ok... (I dont expect great, and thats another story). So, I am curious where the issue is.

[Jimmer]

Did you follow the guide on the xbmc wiki - hardy minimal + xbmc? Does seem like some sort of delay due to the network. I bet if the db was on your internal drive you wouldn't have the issue. Have you thought of making a local copy and testing it with that to eliminate the network?

Jim

[r3sno]

Did you follow the guide on the xbmc wiki - hardy minimal + xbmc?

HOW TO install_a_Miminal_Ubuntu_and_XBMC
This what I followed, I linked to the area I think you are referring to about Cstate and setting up the network.

Which is why I mentioned the bit about being able to play high quality video without much problem, no skips in play, etc.

Does seem like some sort of delay due to the network. I bet if the db was on your internal drive you wouldn't have the issue. Have you thought of making a local copy and testing it with that to eliminate the network?

I have thought about it, but havent done it yet. Are you suggesting I use the mysql version or the builtin in sqlite version?

[Jimmer]

Well that's the thing, I don't use mysql so I can't say. Perhaps someone with a similar setup to you will pitch in. I guess I don't really understand what you're doing. Are you using mysql to keep a centralised database for multiple xbmc's? I suggest making the files local using the same method for access that you are using now on the network, because at least you can eliminate one thing. If it sucks when the db is local, then something is definately up....

Jim

[r3sno]

Are you using mysql to keep a centralised database for multiple xbmc's?
Jim

Ill check that tonight. I am using a centralized database so each machine gets
the same info, ie) what shows were watched, how long watched, etc. And its automatically synced between machines.

Ive asked online, but havent gotten much in response from people. thanks, Ill try it out and report what I find.

[r3sno]

When using the internal database it goes along fine. But when using the mysql one its still fairly slow.

Is anyone using the MySql database or am I the only person in appletv land thats using this combination?

[b00sted4fun]

did you ever find a solution? i tried this and as soon as i pointed it to the mysql db it runs like butt. 1min+ to access menu or video.

[spudbert]

I am having the exact same problem. 3 XBMCs and only the appletv has the slow menu. Playback works fine (once it starts). I have checked the mysql queries and all are < 1 second. I also installed the mysql client ont he appletv and the queries are quick. I don't think it would be a network issue since playback is fine. I would love to solve this.

[spudbert]

More information....

The first mysql DB I setup worked fine with my appletv once the DB was tuned. That computer started becoming flakey, so I migrated the DB to my zotac. Like I mentioned before, the other 2 XBMC systems are fine with the new DB, but the appletv menus went slow again. I checked out the tuning again, but no luck this time.

I can easily switch between the old and new DBs. Using the old is fast navigation, the new one isn't. I put the appletv xbmc into debug mode and started watching the logging. It seems that the DB query times are similar between the old and new DBs. Where the discrepancies occur are on the skin page loading and httpapi calls (saw during startup) when video is involved. Navigating settings menu works nice and fast

Snip-it from log with new DB:

14:34:09 T:3042989920 M:108048384 DEBUG: LIRC: Update - NEW at 82878347:000000
00af50c738 00 KEY_SELECT hitachi (KEY_SELECT)
14:34:09 T:3042989920 M:108048384 DEBUG: OnKey: 11 pressed, action is Select
14:34:09 T:3042989920 M:108048384 DEBUG: ExecuteXBMCAction : Translating Activ
ateWindow(VideoLibrary,movietitles,return)
14:34:09 T:3042989920 M:108048384 DEBUG: ExecuteXBMCAction : To ActivateWindow
(VideoLibrary,movietitles,return)
14:34:09 T:3042989920 M:108048384 DEBUG: Activating window ID: 10025
14:34:09 T:3042989920 M:108048384 DEBUG: Checking if window ID 10024 is locked
.
14:34:09 T:3042989920 M:108048384 DEBUG: ------ Window Deinit (Home.xml) -----
-
14:34:10 T:2916039568 M:108023808 DEBUG: DoWork - took 1070 ms to load special
://masterprofile/Thumbnails/b/b9f58f5f.tbn
14:34:19 T:3042989920 M:108027904 DEBUG: ------ Window Init (MyVideoNav.xml) -
-----
14:34:19 T:3042989920 M:108027904 INFO: Loading skin file: MyVideoNav.xml
14:34:25 T:3042989920 M:108036096 DEBUG: CGUIMediaWindow::GetDirectory (videod
b://1/2/)
14:34:25 T:3042989920 M:108036096 DEBUG: ParentPath = [videodb://1/2/]
14:34:30 T:3042989920 M:108036096 DEBUG: GetSetsNav query: SELECT sets.idSet,s
ets.strSet,COUNT(1) AS c,count(files.playCount) FROM sets JOIN setlinkmovie ON s
ets.idSet=setlinkmovie.idSet JOIN movie ON setlinkmovie.idMovie=movie.idMovie JO
IN files ON files.idFile=movie.idFile GROUP BY sets.idSet HAVING c>1
14:34:30 T:3042989920 M:108036096 DEBUG: GetMoviesByWhere query: select * from
movieview WHERE movieview.idMovie NOT IN (SELECT idMovie FROM setlinkmovie s1 J
OIN(SELECT idSet, COUNT(1) AS c FROM setlinkmovie GROUP BY idSet HAVING c>1) s2
ON s2.idSet=s1.idSet)
14:34:30 T:3042989920 M:107012096 DEBUG: Time for actual SQL query = 180
14:34:32 T:3042989920 M:107008000 DEBUG: Time to retrieve movies from dataset
= 1276
14:34:32 T:3042989920 M:107008000 DEBUG: Saving fileitems [videodb://1/2/]
14:34:32 T:3042989920 M:107008000 DEBUG: -- items: 957, sort method: 25, asc
ending: true
14:34:34 T:2768288656 M:107024384 DEBUG: Thread 2768288656 terminating (autode
lete)
14:34:35 T:2793466768 M:107032576 DEBUG: Thread 2793466768 terminating (autode
lete)
14:34:37 T:2998197136 M:106987520 DEBUG: thread start, auto delete: 0
14:34:37 T:2793466768 M:106741760 DEBUG: thread start, auto delete: 1
14:34:37 T:3042989920 M:106778624 DEBUG: Previous line repeats 1 times.
14:34:37 T:3042989920 M:106778624 DEBUG: ExecuteXBMCAction : Translating SetFo
cus(50)
14:34:37 T:3042989920 M:106840064 DEBUG: ExecuteXBMCAction : To SetFocus(50)

10 seconds lost when loading MyVideoNav.xml, where as on the old DB, the time between Home.xml deinit and MyVideoNav.xml was < 1 second.

Snip-it from log with old DB:

14:39:23 T:3043002208 M:129720320 DEBUG: LIRC: Update - NEW at 63035:00000000a
f50c738 00 KEY_SELECT hitachi (KEY_SELECT)
14:39:23 T:3043002208 M:129720320 DEBUG: OnKey: 11 pressed, action is Select
14:39:23 T:3043002208 M:129720320 DEBUG: ExecuteXBMCAction : Translating Activ
ateWindow(VideoLibrary,movietitles,return)
14:39:23 T:3043002208 M:129720320 DEBUG: ExecuteXBMCAction : To ActivateWindow
(VideoLibrary,movietitles,return)
14:39:23 T:3043002208 M:129720320 DEBUG: Activating window ID: 10025
14:39:23 T:3043002208 M:129720320 DEBUG: Checking if window ID 10024 is locked
.
14:39:23 T:3043002208 M:129720320 DEBUG: ------ Window Deinit (Home.xml) -----
-
14:39:23 T:3043002208 M:129720320 DEBUG: ------ Window Init (MyVideoNav.xml) -
-----
14:39:23 T:3043002208 M:129720320 INFO: Loading skin file: MyVideoNav.xml
14:39:24 T:3043002208 M:129720320 DEBUG: CGUIMediaWindow::GetDirectory (videod
b://1/2/)
14:39:24 T:3043002208 M:129720320 DEBUG: ParentPath = [videodb://1/2/]
14:39:24 T:3043002208 M:129720320 DEBUG: GetSetsNav query: SELECT sets.idSet,s
ets.strSet,COUNT(1) AS c,count(files.playCount) FROM sets JOIN setlinkmovie ON s
ets.idSet=setlinkmovie.idSet JOIN movie ON setlinkmovie.idMovie=movie.idMovie JO
IN files ON files.idFile=movie.idFile GROUP BY sets.idSet HAVING c>1
14:39:24 T:3043002208 M:129720320 DEBUG: GetMoviesByWhere query: select * from
movieview WHERE movieview.idMovie NOT IN (SELECT idMovie FROM setlinkmovie s1 J
OIN(SELECT idSet, COUNT(1) AS c FROM setlinkmovie GROUP BY idSet HAVING c>1) s2
ON s2.idSet=s1.idSet)
14:39:24 T:3043002208 M:128389120 DEBUG: Time for actual SQL query = 160
14:39:25 T:3043002208 M:128393216 DEBUG: Time to retrieve movies from dataset
= 1069
14:39:26 T:3043002208 M:128380928 DEBUG: Saving fileitems [videodb://1/2/]
14:39:26 T:3043002208 M:128397312 DEBUG: -- items: 957, sort method: 25, asc
ending: true
14:39:26 T:2983234448 M:128385024 DEBUG: thread start, auto delete: 0
14:39:26 T:2998209424 M:125620224 DEBUG: DoWork - trying to extract filestream
details from video file /mediaNAS/video/movie/The Backyardigans - Operation Ele
phant Drop (2010)/tboed.iso
14:39:26 T:3043002208 M:125120512 DEBUG: ExecuteXBMCAction : Translating SetFo
cus(50)
14:39:26 T:3043002208 M:125161472 DEBUG: ExecuteXBMCAction : To SetFocus(50)

The only thing that changed when these logs were taken was the advancedsettings.xml. I just changed the IP of the DB... and both DBs are identical.


Here is a snipit of the log during startup with the new DB:

14:53:46 T:3043403616 M:163426304 DEBUG: HttpApi Start command: QueryVideoData
base paras: QueryVideoDatabase; select count(1), count(playCount), movieview.*
from movieview group by lastPlayed
14:53:51 T:3043403616 M:163008512 DEBUG: HttpApi Start command: QueryVideoData
base paras: QueryVideoDatabase; select count(1), count(playCount) from musicvid
eoview
14:53:57 T:3043403616 M:163221504 DEBUG: HttpApi Start command: QueryVideoData
base paras: [not recorded]

All HttpApi calls related to video take 5 seconds, when they took < 1 second on old DB.

I also tried creating a brand new DB on the zotac box. Same behavior even with an empty library. So library size has nothing to do with the issue.

I am running out of ideas to try. Hopefully someone can explain what is going on. BTW, I am using Transparency (also tried switching back to confluence... no improvement)

[spudbert]

I was able to find out what my problem was. It was the mysql DNS. The only difference I found between the old and the new DB was that the hosts file had an entry for my appletv in it, while the new DB didn't. Adding my appletv to my hosts file on my new DB resolved all slowness in the xbmc menu navigation on the appletv.

The following is another way to fix it within mysql itself:

Disable DNS Hostname Lookup

open you /etc/mysql/my.cnf file and use –skip-name-resolve option file looks like below.

[mysqld]
…..
……
skip-name-resolve

When this option is activated, you can only use IP numbers in the MySQL Grant table.

How MySQL uses DNS

When a new thread connects to mysqld, mysqld will spawn a new thread to handle the request. This thread will first check if the hostname is in the hostname cache. If not the thread will call gethostbyaddr_r() and gethostbyname_r() to resolve the hostname.

If the operating system doesn't support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr() and gethostbyname() instead. Note that in this case no other thread can resolve other hostnames that is not in the hostname cache until the first thread is ready.

You can disable DNS host lookup by starting mysqld with --skip-name-resolve. In this case you can however only use IP names in the MySQL privilege tables.

If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookop with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompile mysqld.

You can disable the hostname cache with --skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts.

If you don't want to allow connections over TCP/IP, you can do this by starting mysqld with --skip-networking.