This is a general topic about Kodi using a MySQL database, in this case specifically MariaDB. You can find part 1 here:
Since I feel some affinity for the CoreElec forums, I’ll generate some traffic by posting my further ramblings here.
I consider this topic advanced/expert level so I will not post general how-tos. If you’re not comfortable with editing a MySQL database with an editor, you should probably start with something easier, like optimizing cache or advancedsettings.xlml in general. There are plenty of good guides about that.
Kodi users who have multi-room setups usually take the next step and switch to a central MySQL database. In that way, they can pause a movie in one room and continue in another, for example. Also, all the information only needs to be scraped into the database once and is instantly available on any Kodi machine that connects to the database.
There are several different MySQL flavours out of which 2 are currently officially supported by Kodi: MySQL (original) and MariaDB which is marketed as a drop-in replacement for MySQL (meaning, you could just use one or the other, although that may not be 100% true, depending on the version used).
MySQL and MariaDB also differ in raw speed. One could rank them like this (in order from faster to slower):
- MySQL 5.6
- MariaDB 10.2
- MySQL 5.7
Both MySQL 5.8 and MariaDB 10.3 are already avaible as beta versions and will be faster than their predecessors, although I haven’t found conclusive speed tests yet.
For this guide, I would recommend you install MariaDB 10.3.current if you start from scratch and 10.2.current if you migrate from MySQL.
MySQL and MariaDB are nothing but trademark names. The real speed optimizations under the hood are done on storage engine level. For more than 7 years now, InnoDB has been the storage engine of choice for data because of its speed, robustness and error tolerance. The speed measurements used for the ranking above have been done with InnoDB.
However, there’s a new player in town called RocksDB. Originally developed for facebook it’s superior to InnoDB in every aspect. Higher compression and fewer writes make for much better throughput. In my tests I was able to reduce my Video9.db from roughly 158MB to 136MB in size. Database size is directly related to load time, so there’s that immediate benefit.
At the time of writing, RocksDB (or MyRocks) may not be installed by default. Here are some general guides that will help you on your way:
To migrate from InnoDB to MyRocks, MariaDB recommends exporting your data to a file and then re-import it. I went a slightly different route. I fired up HeidiSQL (the DBEditor that comes with MariaDB - who comes up with these ridiculous names, btw?), entered maintenance mode, marked the video and music databases and then used the mass table editor to change to storage engine to MyRocks. Straightforward.
Of course, your data is unchanged and not optimized that way. Still in maintenance mode, you should mark those some tables again, then first run ANALYZE on them and subsequently OPTIMIZE. The optimized run might take a while as this is where the reordering and compression is happening.
Finally edit your my.ini and add the following lines under
Restart your server and RocksDB is ready to rock!
At this point, you could just leave everything as it is, MariaDB wil work perfectly fine with your Kodi and you should already notice some nice speed improvements, especially when closing Kodi (that part always tries my patience). The way we have configured it now, MariaDB will process both InnoDB and MyRocks.
MyRocks works in a slightly different way than InnoDB where charactersets are concerned, though. InnoDB is case insensitive, MyRocks is not but it’s tolerant if not further optimized. So we could take some more steps to optimize your server and the storage engine.
- If you are sure that you won’t use InnoDB anymore, you can save memory and processing time by adding the following line to my.ini under
This will stop allocating memory for InnoDB and unload that storage engine. Remember to restart your server after each change to my.ini.
- You can further optimize MyRocks by converting case insensitive (ci) keys to case sensitive (cs) keys, or
“Mem comparable” keys as they call it. When Kodi generates empty databases, by default it uses “utf-8_general_ci” as collation. This needs to be changed to “utf-8_bin”. Fire up your preferred SQL-Editor and do so.
Some further reading concerning keys in MyRocks:
That’s it - hope you had some fun and better loading times!