Would a Raspberry PI be worth it for just handling MySQL?
#1
Hi everyone.

I recently migrated my Kodi data to the MySQL server that comes with the Western Digital My Cloud EX4. While it works ok, it has dramatically slowed down the startup of my Kodi clients (2 Fire Sticks and 1 Fire TV 4K), but the benefits of the centralized database are too great to ignore (continue watching from room to room, a single location to update when changes are made as opposed to updating each client individually). I've read up on optimizing MySQL, but it seems that I really can't perform any of the optimizations on the copy of MySQL that ships with the EX4. Apparently it doesn't support using a my.cnf file and for some reason, InnoDB is not available. Considering the EX4 only has 512MB of RAM and a 2 GHz processor (I honestly haven't even been able to tell if it's a single or multi-core), I started looking for something that might be able to handle the database better. Since I can't seem to do much with the EX4 in terms of fine tuning performance, I wanted to think of a cheap alternative, just for running MySQL for Kodi, and I started looking into the PI as a possible reasonable solution. My question is, is this worth the hassle? Would I notice an increase in the responsiveness of the database compared to running MySQL on the EX4, especially since I would be able to fine-tune it, or will the change be barely noticeable (or not noticeable at all)? Has anyone else considered doing this? Is the PI even powerful enough to handle the database? Thanks in advance for any pointers or suggestions.
Reply
#2
(2018-12-30, 05:10)GeorgeT93 Wrote: While it works ok, it has dramatically slowed down the startup of my Kodi clients (2 Fire Sticks and 1 Fire TV 4K),
'Dramatically' for me means like 'coming to a grinding halt'. I don't have Fire Stiick or a Fire TV, so I don't know what's going on. You could look at your own log files (debug enabled) and see where a possible stagnation takes place.

I've tried running a RPi 3B+ with Raspbian and MariaDB on an external USB housing connected SSD drive. The RPi 3B+ can boot directly from USB without an SD card. Recently @vpeter created a separate MariaDB server addon for LibreELEC devices. I haven't tried that yet. I also wanted to use an SSD because I consider them more reliable in the longer run, as an SSD also has TRIM functionality which most SD cards do not have.

I used a cheap USB 3.0 housing, yet it is of course still connected via an RPI's USB 2.0 port. The overall data throughput on the RPi is also not as constant as you hope (there is a steady pulse of a short halting/buffering when seeing the SSD box's led activity), but... it all works and it's pretty stable. I'm guessing that Android boxes with a good eMMC could outperform the whole USB/SSD combo.

I currently run Kodi's database on an SSD on a older Celeron 1037 PC with a gigabit connection. Whether or not the whole MySQL data processing on a RPi3B+ is fast enough is up to you. My RPi3B+setup is cheap, it's totally silent (!), it's certainly slower than a PC/SSD gigabit setup... Is it much slower than a gigabit NAS with rotating disks? Probably. The 100+MB/s gigabit vs a 7-8MB/s RPi network connection bursts certainly makes a difference. There is also the option to replace the disks in your NAS with SSDs, if you want to go for "responsiveness"...

It's not fair to compare a 35 dollar/euro kid's toy with a full-blown PC or NAS. If you want performance, it's going to cost you either way. Whether you find that price tag acceptable is up to you.
Got a Kodi problem? Provide us with a full Debug log (wiki) || Usefull pages: First time user (wiki) || Troubleshooting (wiki) || Free content (wiki) || Forum rules (wiki) || VPN policy (wiki)
Reply
#3
Hi Klojum and thanks for responding. I'll try to better describe what I mean by dramatically and hopefully better convey what I'm hoping to accomplish. I've kept local library databases for each client previously, with all the video files hosted on my EX4. Once loaded into Kodi, the movie listings would show up anywhere from instantly (in the case of the cover art already being stored in the local thumbnail cache) to 20 seconds (in the case of the cover art needing to be reloaded from the EX4, when it is first 'waking up' from standby. Cover loads are virtually instant if the EX4 is already awake). Since moving the database to the NAS, when I first load up Kodi, it takes a little over 1 minute before *anything* shows up on my Movies screen, and instead I just have spinning 'wait' circles, regardless of if the EX4 was awake or not when Kodi is launched from a client.

Naturally, this led me to start reading up on improving database performance, and there were two options that sounded like they could help cut that time down. One was adding a line to the my.cnf to disable reverse name lookup, and the other was converting the tables in the database to InnoDB. What I didn't realize at the time, was that the MySQL on the EX4 is apparently a custom build by WD that is used by (or possibly part of) the OS. So even though the MySQL is version 5.1.56 (which should support InnoDB), InnoDB isn't available and from my research, can't be enabled on the EX4. It apparently doesn't support a my.cnf file in their build either, so not only am I unable to enable InnoDB, but I also can't disable reverse name lookup on this version. Apparently, since this WD fork can't have it's version of MySQL updated to a newer, fully-configurable version, I'm out of luck for attempting either option.

Since that's the case, I decided to start looking for something small that I could throw on the network to host just the Kodi database, that would allow me to try and tune performance of the database. Now I figure that there will always be a minimum of a 20 second response when the NAS first wakes up, so I figured I should find something very low powered that I wouldn't have to worry about disabling any power saving features. Then the device would always be awake and waiting for clients, and in the time it takes for the NAS to 'wake up', the database query to build the Movies home screen could (at least in my mind) be running and possibly even finished by the time the NAS finishes waking up. I honestly can't say I know which device actually has a more powerful CPU, as all I can really say I know are the clock speeds. Other than that, I know the PI is a quad core, and honestly couldn't tell you if the EX4 is a single or dual core. The only other thing I'm aware of is the EX4 is running 512MB of RAM and the PI has 1GB, which I was hoping could help speed up the database.

Ultimately, I'm not concerned with having the fastest response, as that's simply unrealistic on a $35 computer. But I am curious if I may perhaps get better database performance from a PI (specifically the 3 B+) than I do from the NAS, as my hands seem tied in terms of trying to tweak MySQL on the EX4. I love the added functionality of using a centralized database for my Kodi clients, but am hoping to speed things up by eliminating the extra strain and overhead that I'm putting on the EX4's limited resources, and letting something else handle the kodi database that I have more control over. Do you think I may be barking up the wrong tree by considering the PI for this? I mean, I know they're inexpensive, but that's not the price I'm concerned with paying. The price I'm concerned with paying is spending hours configuring, tweaking and getting everything working, only to find out that I need to revert to the old (current) setup because I made things worse! lol!
Reply
#4
In general, a MySQL server is hardly ever the biggest obstacle. Most queries on an sql server will be executed in milliseconds, rather than multiple seconds. Also the result sets of those queries are not the issue. Text data is so much smaller and quicker than for example the file size of a single good-sized movie poster. So a Raspberry Pi doesn't necessarily have to be a slow server. Its slower network port doesn't help though.

The problem area is in processing that data inside Kodi when database output has arrived. Kodi also has one or more internal sorting routines of its own, which doesn't help the overall response time. After that, the subsequent background processing of any art (thumbnails, posters, backgrounds, actor thumbs) kicks in and can hinder things as well.

About waiting periods... When all art is already cached locally, and you still have waiting periods of 20 seconds or more, you may want to check for other unused video/music/photo network or USB sources that are currently unavailable. Each of those will have a time out of some sort, holding up other processes. Also, a NAS having to first wake up is always a problem. My 'NAS' runs 24/7. A spring cleaning of your database may also help: fully export your media collection(s), delete/rename your current database(s), restart Kodi and import/rebuild your database(s). There is also a database scrubbing add-on.

Fire stick and Fire TV are both devices in the low-powered category. As previously said, I have no idea what their performance is, but compared to a RPi, SD cards in general have pretty lousy write speeds (while read speeds are okayish). So building a cache on each of those devices can take up quite some time. Whenever I set up a new Kodi device, I not only set up the links for the MySQL database, I also copy the local cache database (Textures13.db) as well as the whole thumbnail cache folder from another fully cached Kodi device onto the storage of the new Kodi device. It will save the new device so much effort on building up the cache by itself. So when starting the new Kodi device, no background processing for searching/downloading new thumbnails is necessary.

FWIW, I still run the default MySQL 5.5.62 on Ubuntu 14.04... I'm surprised that WD has closed off its software that much, and that it hasn't been updated in like forever. In comparison, Synology does a much better job in keeping their firmware up-to-date. A crippled(?) 5.1 MySQL server on your WD will not help.

But if you simply want more database speed than what the WD NAS currently delivers, any simple/old PC with Ubuntu (Server) and an SSD, along with gigabit network speed, will do IMHO. Then you can also tweak things to your heart's desire. The RPi3B+ may have a faster ethernet port than its predecessors, but it doesn't go beyond approx 220 mbps continuously because of its design and lack of CPU 'umpf'. It's certainly not giving you full gigabit speed.
Got a Kodi problem? Provide us with a full Debug log (wiki) || Usefull pages: First time user (wiki) || Troubleshooting (wiki) || Free content (wiki) || Forum rules (wiki) || VPN policy (wiki)
Reply
 
Thread Rating:
  • 0 Vote(s) - 0 Average



Logout Mark Read Team Forum Stats Members Help
Would a Raspberry PI be worth it for just handling MySQL?00