Windows - Frodo 12.1 / Synology / Very slow common SQL

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
Voldor Offline
Junior Member
Posts: 15
Joined: Jan 2011
Reputation: 0
Post: #31
Are the SQL command to change the engine to InnoDB long to execute ?
(This post was last modified: 2013-10-11 15:25 by Voldor.)
find quote
seth.feinberg Offline
Donor
Posts: 232
Joined: May 2011
Reputation: 2
Post: #32
Just reporting that @Ahuch suggestion worked for as well. changed the engine to innodb for all the tables and no more "freeze" of xbmc whenever I update the Library. Can not thank you enough. THANK YOU!!!
find quote
narvatu Offline
Member
Posts: 87
Joined: Jul 2011
Reputation: 0
Post: #33
Any howto for noobs?
Synology DS411j. What I must install-touch?
find quote
seth.feinberg Offline
Donor
Posts: 232
Joined: May 2011
Reputation: 2
Post: #34
Go to this post by @Ahuch http://forum.xbmc.org/showthread.php?tid...pid1472519 (and give him some props by hitting the +/- buttonSmile and then open PHPMyAdmin. Click on the XBMC databases from the left-hand column and then the SQL tab (I believe its second from the left on the top), then input all the commands in the PHP code box in that post and click Go (or submit or whatever it is). Then wait, it'll take a minute. I then restarted my Synology but dunno if that's necessary and no more freeze! Though PseudoTV still doesn't complete the initial channel load for some reason (a problem I assumed had been related to the db issue)...
find quote
mbu10 Offline
Junior Member
Posts: 46
Joined: Apr 2012
Reputation: 0
Post: #35
thanks for that seth works a treat, dealing with 10K plus items and has sped up no end now and i am running the 1812+ so i expected better, now i have it
find quote
MaxCore Offline
Senior Member
Posts: 243
Joined: May 2011
Reputation: 1
Location: Quebec,Canada
Post: #36
THX YOU so muchhh ! you fixed a long time bug !!! Smile Smile

[Image: all-thin-banner@2x.jpg]
find quote
PH-SYM Offline
Member
Posts: 86
Joined: Feb 2012
Reputation: 2
Post: #37
Thanks or the fix. It solved my 2 minute freeze as well. I made similar changes for the music library and it seems to be more responsive now. Before I had a delay fo 15 seconds when opening an artist folder, now it is 3-5 seconds.
find quote
seth.feinberg Offline
Donor
Posts: 232
Joined: May 2011
Reputation: 2
Post: #38
(2013-11-13 14:53)PH-SYM Wrote:  Thanks or the fix. It solved my 2 minute freeze as well. I made similar changes for the music library and it seems to be more responsive now. Before I had a delay fo 15 seconds when opening an artist folder, now it is 3-5 seconds.

Good to hear, just out of curiosity, what size music db are you accessing?
find quote
PH-SYM Offline
Member
Posts: 86
Joined: Feb 2012
Reputation: 2
Post: #39
My music db is ~95 MB (45000 tracks / 1900 albums)
find quote
nsviper Offline
Senior Member
Posts: 287
Joined: Feb 2011
Reputation: 3
Post: #40
Is this specifically a Synology fix, or would this work on the MySQL install on my QNAP NAS?

2xPi 512mb[XBian][Amber Skin]
QNAP TS410 NAS - 4x2TB Raided with NFS Support & Central MySQL DB
Samsung 46" ES7000 Smart TV & HT-E5550 3D BD Sound System
Solwise 200Mb Powerline adapters

Blog coming soon : XBian/Hyperion/LED Ambient Lights
find quote
seth.feinberg Offline
Donor
Posts: 232
Joined: May 2011
Reputation: 2
Post: #41
(2013-11-14 12:18)PH-SYM Wrote:  My music db is ~95 MB (45000 tracks / 1900 albums)

ahh, thanks, I would really like to use XBMC for music, but in the past I've found XBMC simply cannot handle its size, which is factors of magnitude (about 5000x) larger that what you are experiencing lags with, so I guess I'll continue to stay awaySmile
find quote
PH-SYM Offline
Member
Posts: 86
Joined: Feb 2012
Reputation: 2
Post: #42
Are you really sure about 5000x? in my calculation you must then have over 225 Million (225,000,000) music tracks. Considering my measly 45,000 take up 500GB, your music library must be over 2000 Terabyte! How do you store all that?
(This post was last modified: 2013-11-14 22:50 by PH-SYM.)
find quote
seth.feinberg Offline
Donor
Posts: 232
Joined: May 2011
Reputation: 2
Post: #43
haha no, i must have misread your post....or something, cause I'm looking at it right now, I just saw the 95MB. I have similarly about 500gb, maybe I'll give it another try then...
(This post was last modified: 2013-11-14 22:59 by seth.feinberg.)
find quote
PH-SYM Offline
Member
Posts: 86
Joined: Feb 2012
Reputation: 2
Post: #44
I thought you asked for the size of my music database, that database file is 95MB. The actual music is about 500GB (45000 songs)
With the changes I made, XBMC is certainly able to cope with that so I recommend making those engine changes. Only change the tables with the MyISAM engine. Don't know what happens if you change the "view" tables but I left them alone.

I noticed a definite improvement in opening artists and albums from the library but also things like adding an album to a current playlist is much faster now. Before that took 10 seconds, now its almost instant (1-2 seconds).

I am still looking for the best my.cnf settings regarding InnoDB but sofar it seems working properly and its definitely an improvement over MyISAM.
(This post was last modified: 2013-11-15 12:58 by PH-SYM.)
find quote
seth.feinberg Offline
Donor
Posts: 232
Joined: May 2011
Reputation: 2
Post: #45
(2013-11-15 12:56)PH-SYM Wrote:  I thought you asked for the size of my music database, that database file is 95MB. The actual music is about 500GB (45000 songs)
With the changes I made, XBMC is certainly able to cope with that so I recommend making those engine changes. Only change the tables with the MyISAM engine. Don't know what happens if you change the "view" tables but I left them alone.

I noticed a definite improvement in opening artists and albums from the library but also things like adding an album to a current playlist is much faster now. Before that took 10 seconds, now its almost instant (1-2 seconds).

I am still looking for the best my.cnf settings regarding InnoDB but sofar it seems working properly and its definitely an improvement over MyISAM.

Yes my fault for the misunderstanding. If you find anything regarding the my.cnf settings, please post back if anything helps significantly. Mine is totally usable again, but it lags at random times especially when marking stuff watched (I experienced this recently as I tried to mark all my TV Shows watched, but this was marking whole series as watched and the Synology was concurrently syncing my TV Shows Folder through Bittorrent Sync so I think that might explain most of the slowness).

As far as the my.cnf: before I discovered the innodb "trick" I put this into my my.cnf:
Code:
query_cache_size = 16777216
query_cache_type=1
query_cache_limit=2097152

which for a brief period I thought fixed my "freeze" issue, but it came back...
find quote