Windows - Frodo 12.1 / Synology / Very slow common SQL

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
fmohiy Offline
Junior Member
Posts: 12
Joined: May 2013
Reputation: 0
Post: #16
I've uploaded the complete log
http://xbmclogs.com/show.php?id=20159

lag was at the times below from 20:28 till 20:32 (the screen never showed 20:29)
20:28:15 T:5004 NOTICE: DVDPlayer: finished waiting
20:29:22 T:4524 NOTICE: ES: Client from 127.0.0.1 timed out
20:32:25 T:5004 NOTICE: Previous line repeats 1 times.
20:32:25 T:5004 NOTICE: DVDPlayer: Opening: smb://SYNOLOGY/satashare
find quote
fmohiy Offline
Junior Member
Posts: 12
Joined: May 2013
Reputation: 0
Post: #17
I've uploaded the remaining of the log as the lag occurred again
http://xbmclogs.com/show.php?id=20163
again i never saw 20:50

20:49:01 T:5004 NOTICE: DVDPlayer: finished waiting
20:50:55 T:4524 NOTICE: ES: Client from 127.0.0.1 timed out
20:53:14 T:5004 NOTICE: DVDPlayer: Opening: smb://SYNOLOGY/satashare/TV Shows/La Linea/Season1/La Linea - S01E47 - La Linea 147.avi
find quote
fmohiy Offline
Junior Member
Posts: 12
Joined: May 2013
Reputation: 0
Post: #18
i should mention that I'm using frodo 12.2 not 12.1.

should i create a separate thread?
find quote
Ned Scott Offline
Team-Kodi Wiki Guy
Posts: 21,144
Joined: Jan 2011
Reputation: 276
Location: Arizona, USA
Post: #19
Debugging needs to be enabled in the logs for additional info. See debug log for now to turn this on.

You can make easy links to the XBMC wiki using double brackets around common XBMC words: [[debug log]] = debug log, [[Video library]] = Video library, [[SMB]] = SMB , [[userdata]] = userdata, etc
find quote
ThY91 Offline
Donor
Posts: 84
Joined: Sep 2009
Reputation: 0
Location: Wollongong, Australia
Post: #20
I know this may sound silly but is it quick if you were to exit xbmc once it has finished loading then reopen it immediately?

Is it possible that you have an idle spin down time set on your nas?
find quote
TheGrudge Offline
Junior Member
Posts: 29
Joined: May 2013
Reputation: 0
Post: #21
I have the same problem, after analyzing some SQL queries I came to the conclusion that the view "albumview" is significantly slower than the rest of the views, see http://pastebin.com/wyPhLkAe for the explain statement results.
The view "albumview" took 1,6 seconds to finish, the rest (artistview, songview) is much faster.

If I execute the statement in the view as a standalone SQL query, it is fast:
http://pastebin.com/iWmHSHSE


As you can see, the query is build of differently. Unfortunately I am no database expert so I don't know how to optimize this for local testing purposes.
I read somewhere that maybe the GROUP BY could be a problem.

Ialso removed the LEFT JOIN with the "song" database and it was faster, it took only 0.33 seconds. But I don't know if the
Quote:"min(`song`.`iTimesPlayed`) AS `iTimesPlayed`"
part is needed somewhere in the album view.

Maybe these queries can be optimized? Everything regarding "albums" in XBMC is really slow for me when using MySQL on a Synology DiskStation.
find quote
fmohiy Offline
Junior Member
Posts: 12
Joined: May 2013
Reputation: 0
Post: #22
(2013-05-25 13:51)ThY91 Wrote:  I know this may sound silly but is it quick if you were to exit xbmc once it has finished loading then reopen it immediately?

Is it possible that you have an idle spin down time set on your nas?

my NAS is almost always spun up as i download a lot of torrents directly to it.

but i have xbmc metadata hosted on Mysql on the synology NAS. before that when i have the thumbs and metadat directly on the pc i had no issues with delays or hangups.
find quote
fmohiy Offline
Junior Member
Posts: 12
Joined: May 2013
Reputation: 0
Post: #23
one thing i noticed is that the delay/freeze is always the same duration, about 4.5 minutes.
seems like xbmx is waiting for something to time out.
find quote
fmohiy Offline
Junior Member
Posts: 12
Joined: May 2013
Reputation: 0
Post: #24
i,ve followed the debug log instructions and uploaded log # 23574

the delay occurred here

23:11:46 T:6836 DEBUG: Thread Jobworker 6836 terminating (autodelete)
23:12:17 T:5184 NOTICE: ES: Client from 127.0.0.1 timed out
23:15:43 T:7508 DEBUG: Thread CRemoteControl 7508 terminating
23:16:41 T:14132 DEBUG: CAnnouncementManager - Announcement: OnUpdate from xbmc
find quote
fmohiy Offline
Junior Member
Posts: 12
Joined: May 2013
Reputation: 0
Post: #25
i hope someone can take a look at this.
it occurs almost every time i stop playing one file and start playing another, with a 4.5 minutes delay each time, so its getting very annoying.
find quote
Ahuch Offline
Junior Member
Posts: 17
Joined: Dec 2011
Reputation: 5
Post: #26
(2013-05-25 13:51)ThY91 Wrote:  I know this may sound silly but is it quick if you were to exit xbmc once it has finished loading then reopen it immediately?

Is it possible that you have an idle spin down time set on your nas?

The delay is often a couple of minutes for me - nowhere near the amount of time spinup would take anyways.

Although this has seemed to temporarily alleviate any issues I've been having - after a bit more usage I'll know for sure I guess.

PHP Code:
ALTER TABLE writerlinkmovie ENGINE=InnoDB;
ALTER TABLE writerlinkepisode ENGINE=InnoDB;
ALTER TABLE version ENGINE=InnoDB;
ALTER TABLE tvshowlinkpath ENGINE=InnoDB;
ALTER TABLE tvshow ENGINE=InnoDB;
ALTER TABLE taglinks ENGINE=InnoDB;
ALTER TABLE tag ENGINE=InnoDB;
ALTER TABLE studiolinktvshow ENGINE=InnoDB;
ALTER TABLE studiolinkmusicvideo ENGINE=InnoDB;
ALTER TABLE studiolinkmovie ENGINE=InnoDB;
ALTER TABLE studio ENGINE=InnoDB;
ALTER TABLE streamdetails ENGINE=InnoDB;
ALTER TABLE stacktimes ENGINE=InnoDB;
ALTER TABLE settings ENGINE=InnoDB;
ALTER TABLE sets ENGINE=InnoDB;
ALTER TABLE seasons ENGINE=InnoDB;
ALTER TABLE path ENGINE=InnoDB;
ALTER TABLE musicvideo ENGINE=InnoDB;
ALTER TABLE movielinktvshow ENGINE=InnoDB;
ALTER TABLE movie ENGINE=InnoDB;
ALTER TABLE genrelinktvshow ENGINE=InnoDB;
ALTER TABLE genrelinkmusicvideo ENGINE=InnoDB;
ALTER TABLE genrelinkmovie ENGINE=InnoDB;
ALTER TABLE genre ENGINE=InnoDB;
ALTER TABLE files ENGINE=InnoDB;
ALTER TABLE episode ENGINE=InnoDB;
ALTER TABLE directorlinktvshow ENGINE=InnoDB;
ALTER TABLE directorlinkmusicvideo ENGINE=InnoDB;
ALTER TABLE directorlinkmovie ENGINE=InnoDB;
ALTER TABLE directorlinkepisode ENGINE=InnoDB;
ALTER TABLE countrylinkmovie ENGINE=InnoDB;
ALTER TABLE country ENGINE=InnoDB;
ALTER TABLE bookmark ENGINE=InnoDB;
ALTER TABLE artistlinkmusicvideo ENGINE=InnoDB;
ALTER TABLE art ENGINE=InnoDB;
ALTER TABLE actors ENGINE=InnoDB;
ALTER TABLE actorlinktvshow ENGINE=InnoDB;
ALTER TABLE actorlinkmovie ENGINE=InnoDB;
ALTER TABLE actorlinkepisode ENGINE=InnoDB
(This post was last modified: 2013-07-29 04:14 by Ahuch.)
find quote
fmohiy Offline
Junior Member
Posts: 12
Joined: May 2013
Reputation: 0
Post: #27
i changed the engine to innodb and that seems to have fixed my issues. i will post again to confirm in a couple of days
find quote
KennethC Offline
Donor
Posts: 62
Joined: Dec 2010
Reputation: 1
Post: #28
See http://forum.xbmc.org/showthread.php?tid=168231

I had some significant performance issues with Synology MySQL... never did find out what was happening. Tried all sorts of things that made no difference until throwing my hands up in the air and installing MySQL on a box I had laying around.
find quote
Ahuch Offline
Junior Member
Posts: 17
Joined: Dec 2011
Reputation: 5
Post: #29
(2013-07-30 23:40)fmohiy Wrote:  i changed the engine to innodb and that seems to have fixed my issues. i will post again to confirm in a couple of days

So far so good on my end Cool
find quote
fmohiy Offline
Junior Member
Posts: 12
Joined: May 2013
Reputation: 0
Post: #30
my XBMC setup has been saved!!! i was going to sell my DS410 and buy a DS413 to solve the delay issue.
You Mr Ahuch have saved me no less than 200 USD.

really thanks.


XBMC is working great now and the delays have decreased literally from 5 minutes to 5 seconds.
find quote