[solved] YASQLI (Yet another SQL issue) --> SLOW!! - Printable Version +- Kodi Community Forum (https://forum.kodi.tv) +-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33) +--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111) +---- Forum: Raspberry Pi (https://forum.kodi.tv/forumdisplay.php?fid=166) +---- Thread: [solved] YASQLI (Yet another SQL issue) --> SLOW!! (/showthread.php?tid=185758) |
[solved] YASQLI (Yet another SQL issue) --> SLOW!! - chefe - 2014-02-09 Hi there Like most of us I want to make the best out of the limited performance of the pi. For this I followed some popular suggestions, i. e.
Everything works really well and the interface is quiet snappy. It is only when I move the database to an external host, that the performance takes a significant hit (see stats below). FYI, this is my network setup:
Here's how long the rpi takes for different tasks, before vs. after moving the SQL database to the local server (times measured subjectively, with each task executed multiple times (except initial boot)).
So as you can see, in my case using an external server for the MySQL database renders the openELEC experience unusable. Notice, that even when absolutely no media share is set-up in the rpi, even then will it take 7-8 seconds to open the (empty) videos folder. Also, there is severe lag when navigating one level back or forth in the videos folder hierarchy. What do you think could be the reason for this dramatic impact? As it stands, I might be keeping the database on the USB drive itself, at least for now. Cheers, chefe RE: YASQLI (Yet another SQL issue) --> SLOW!! - Milhouse - 2014-02-09 1. You're using a very old Gotham build, try a more recent nightly (see the testing thread) 2. You're using an old-ish version of MySQL - why not use v5.5 (although avoid v5.6, assuming that still has problems) 3. NFS is better than SMB 4. debug log (wiki) - how long are your SQL queries taking? RE: YASQLI (Yet another SQL issue) --> SLOW!! - chefe - 2014-02-09 Hi MilhouseVH, thanks for your input. Fair point regarding the newer nightly build, I'll give it a go. I'm using the older MySQL version, since I was recommended to install additional software on my windows server only though the afore mentioned "web platform installer" from MS. I had the choice to install either version 5.1 or 5.5. But when choosing the latter, the installation executable was not found online by the WPI, so I opted for 5.1. If it is not unwise to install software directly on the server os (like I would be doing on my regular desktop workstation), I would roll-back the server to a previous state and reinstall a different MySQL version. As for NFS/SMB, the pi is the only linux-based computer on by network. All my other clients are Windows PCs which derive the access to network shares from the Active Directory and this is working perfectly, I would not want to change anything for these clients. Naturally I chose SMB because that meant network sharing was ready to go out of the box. Is it possible to share the same folders with both SMB and NFS? Of course the NFS folders need to be secured with username and password. I'll post the debug log in an edit to this post. Thanks again, and cheers ########### EDIT ############# xbmc.log @ http://xbmclogs.com/show.php?id=127751 What did I do? (maybe this is helpful in deciphering the log) After a reboot, I ... entered the movie library scrolled 3 movies to the right showed the info for that movie pressed play fast forwarded to ~41 minutes let it play for a while pressed stop went to the home screen re-entered the movie library after that any entries in the file relate to me trying to save the logfile to an usb drive note, that this is still the old version of openelec RE: YASQLI (Yet another SQL issue) --> SLOW!! - chefe - 2014-02-14 Hi again, FYI in the meantime I have tried a newer built. Though this does indeed really help a lot in terms of performance (i.e. opening time of the libaries, etc), I have found navigating the user interface (even without SQL setup) to be more a bit more slow/choppy than on my previous install. So I reverted back and decided to not go down the SQL route. Moreover, opening libaries still takes 6 seconds, which is a huge improvement over my earlier situation but still I can manage 2s from the USB install without SQL. I reconsidered my usage scenario, so I'll not use multiple instances of xbmc. A SQL setup should therefore not be necessary/advantageous. But in the end, although I did not end up using the SQL setup, I still consider my question answered, since using the newer built really does the trick improving the SQL performance. Thanks MilhouseVH! |