Kodi and MariaDB (Part 2)

General Guides & How-tos
1

Preliminaries

This is a general topic about Kodi using a MySQL database, in this case specifically MariaDB. You can find part 1 here:
https://forum.kodi.tv/showthread.php?tid=329046
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.

MySQL

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):

  1. MySQL 5.6
  2. MariaDB 10.2
  3. 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.

Storage Engine

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:
https://mariadb.com/kb/en/library/myrocks/

Migration

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
[mysqld]
rocksdb
default-storage-engine=rocksdb
default-tmp-storage-engine=MyISAM

Restart your server and RocksDB is ready to rock!

Further Optimization

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.

  1. 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
    [mysqld]
    skip-innodb

This will stop allocating memory for InnoDB and unload that storage engine. Remember to restart your server after each change to my.ini.

  1. 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!

3 Likes
2

I have to retract my recommendation to use this in my.ini / my.cnf:

-skip-innodb

Even if your Kodi databases only use MyRocks, some other default databases like schema databases depend on InnoDB so it shouldn’t be disabled.

3

So far I used MySQL (nativelly compiled by vPeter) with Kodi 16.1 on Rpi2.

Now I am trying to work with Kodi 18, MariaDB in Docker @Odroid C2 (which should have significantly more resources than Rpi2), and despite my efforts the user experience are much worse :frowning:

Before optimisations Ive got pretty high CPU around 98-100 when scrapping movies. After some Ive got better CPU, and therefore responsivness (around 40), but the actual queries seems to be slow :frowning:

Not sure if its blame Kodi, or Docker, or MariaDB.

My config is

[mysqld_safe]
nice		= 0

[mysqld]
max_connections		= 100
connect_timeout		= 5
wait_timeout		= 600
max_allowed_packet	= 16M
thread_cache_size       = 128
sort_buffer_size	= 4M
bulk_insert_buffer_size	= 16M
tmp_table_size		= 32M
max_heap_table_size	= 32M
binlog_format=mixed

#My changes, related to fact that DB server is Odroid C2 / Rpi, and I am using it only for Kodi purposes
skip-name-resolve
optimizer_search_depth = 1

query_cache_limit		= 512K
query_cache_size		= 64M

key_buffer_size		= 64K


innodb_buffer_pool_size	= 256M
innodb_adaptive_hash_index = OFF

max_binlog_size         = 10M


#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
#key_buffer_size		= 128M
#open-files-limit	= 2000
table_open_cache	= 400
myisam_sort_buffer_size	= 512M
concurrent_insert	= 2
read_buffer_size	= 2M
read_rnd_buffer_size	= 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
#query_cache_limit		= 128K
#query_cache_size		= 64M
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type		= DEMAND
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /config/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings		= 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log_file	= /config/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit	= 1000
log_slow_verbosity	= query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#report_host		= master1
#auto_increment_increment = 2
#auto_increment_offset	= 1
log_bin			= /config/log/mysql/mariadb-bin
log_bin_index		= /config/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog		= 1
expire_logs_days	= 10
#max_binlog_size         = 100M
# slaves
#relay_log		= /config/log/mysql/relay-bin
#relay_log_index	= /config/log/mysql/relay-bin.index
#relay_log_info_file	= /config/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode		= NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine	= InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size	= 50M
#innodb_buffer_pool_size	= 256M
innodb_log_buffer_size	= 8M
innodb_file_per_table	= 1
innodb_open_files	= 400
innodb_io_capacity	= 400
innodb_flush_method	= O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completion

[isamchk]
key_buffer_size		= 16M

In Rpi2 this config only these changes does the trick just fine :slight_smile:

query_cache_type        = 1
query_cache_size        = 5M
skip-name-resolve
default-storage-engine  = innodb

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /storage/.innodb
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /storage/.innodb

# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 10M
innodb_additional_mem_pool_size = 2M

# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 2M
innodb_log_buffer_size = 8M

#innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
4

At first glance I see this instruction twice

innodb_buffer_pool_size = 256M
innodb_buffer_pool_size = 10M

Get rid of the second one. 256M still seems pretty low. My recommendation would be to get rid of ALL config settings you added except for the ones I recommend in the other article linked above and basically let MariaDB optimize itself. Then you start adding config lines one by one to see if the changes are for the better or not.

5

I can try. But Odroid C2 has only around 700MB memory free. Its not a DB server… Those lowered values I am using with Mysql on even less memory rich Rpi2 without hickups…

Value 10MB is from different config (Rpi2)…