[ponkotsu]

character-set-server=utf8;I really hope you all used this in MySQL already.  This avoids potential pitfalls with text in exotic codepages.

Hi @HeresJohnny
I've been using your recommendations with my MariaDB database since I first found your post. Thank you for sharing the info.

I was recently sent this by a friend, and I was wondering if it had any relevance here. utf8 vs. utf8mb4 Or is that just for storing emoji in the database?

[HeresJohnny]

I think this is beyond the scope of my little guide. IMHO, one would have to start with asking if is actually a character or a pictogram. Emoticons are usually only used in digital communication, at least I have none on my computer keyboard. I am quite content that utf8 encoding allows me to store european characters, arabian numerals and asian double-byte characters.

But yes, I have seen this particular quirk on my email server database where email message metadata is encoded as utf8mb4_general_ci. Indeed, this works around the problem of having emojis in an email's subject line. Other than that I have never run into trouble with utf8 as database encoding, not even with music album titles that use pictograms like David Bowie's blackstar.

[ponkotsu]

Thanks @HeresJohnny for the reassurance. I was suspecting the same thing -- it may be relevant in other situations, but not so much with Kodi since it's pretty unlikely we'd have to worry about emoticons in our video / music databases.

[jznine]

character-set-server
key-buffer-size
skip-name-resolve

Are those supposed to be hypens '-', or underscores '_' like the rest of the parameters?

[HeresJohnny]

Good catch, thanks. I suspect that those values still take effect with hyphens at least in Windows because of some log messages I saw.

[jznine]

No worries, wasn't sure. I'm running on linux. Keep up the good work, good info and testing in this thread.

[HeresJohnny]

Update for MariaDB 10.2.2.

1. Starting from MariaDB 10.2.2 you can set the InnoDB buffer dynamically. This will save some memory. To calculate the dynamic buffer value, two values need to be specified:
innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances = innodb buffer max size
You should always set the chunk size to 1G and the instances to a sane value for your system memory (I'd recommend half of it, for dedicated database servers up to 70%-80% of total RAM).
Example for a 4GB machine:
innodb_buffer_pool_chunk_size=1G
innodb_buffer_pool_instances=2

2. Starting from MariaDB 10.0 you can dump the InnoDB buffer to a file at shutdown and load it from a file at startup. This will save you the "warmup phase", i.e. having to fill the buffer while calls are made. DB startup will take a little bit longer but operation will be a little bit faster. Use the following statements:
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON

3. Another small speed-up is possible at the cost of safety. By default, MariaDB uses a double buffer to make sure all data is correctly written. You may turn off doublewrite-buffering but be aware that you will lose fail safety if your computer crashes. Not recommended for production systems! To turn off doublewrite-buffering, use
innodb_doublewrite=0

[hugepants]

Many thanks @HeresJohnny for these handy performance tips.

FYI: Since Kodi v18 now uses The Movie Database for scraping TV shows by default, MySQL/MariaDB users will have issues scraping certain TV shows that have a lot of fanart links as discussed here. As suggested in that thread, increasing the size of the c06 column in the tvshow table is one way to fix the issue.

[HeresJohnny]

FYI: Since Kodi v18 now uses The Movie Database for scraping TV shows by default, MySQL/MariaDB users will have issues scraping certain TV shows that have a lot of fanart links as discussed here. As suggested in that thread, increasing the size of the c06 column in the tvshow table is one way to fix the issue.

Thanks for the hint, I was not aware of this issue.

I also don't use TheMovieDB for TV show scraping because after comparing the results with TheTVDB for some months I find it inferior in content. I believe KODI has chosen TMDB as default because the team was fed up with the technical issues that are associated with TheTVDB and its API migration and led to increased support. However, TVDB is essential for me, as for example it handles special episodes which TMDB cannot. But lets not get into comparing both scrapers further in this thread, please.

[HeresJohnny]

I had some time to try out more stuff. Just for reference, this is my config file for MariaDB 10.3:

Code:

[mysqld]
datadir=C:/Program Files/MariaDB 10.3/data
port=3306

# general stuff

# skip_name_resolve=1
optimizer_search_depth=0
character_set_server=utf8
key_buffer_size=16k
host_cache_size=16

# InnoDb optimizations
innodb_buffer_pool_chunk_size=1G
# innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=4
innodb_adaptive_hash_index=OFF
innodb_doublewrite=0
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON

innodb_compression_default=ON
innodb_compression_algorithm=zlib
# innodb_use_trim=ON
# innodb_use_fallocate=ON
innodb_page_cleaners=1

[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.3/lib/plugin

The lines starting with # are either deprecated or don't work (yet). Your mileage may vary. Explanations for new stuff:

Code:

# skip_name_resolve=1

My mail server complained that some stuff could not be resolved so I switched off this line and activated name resolving again. I was quite surprised to find that everything, including KODI, seemed to run faster after as a result. The original hints for setting up the KODI with SQL still apply though, insofar that you should use IP addresses instead of names in advancedsettings.

Code:

innodb_compression_default=ON
innodb_compression_algorithm=zlib
innodb_page_cleaners=1

MariaDB supports compression in INNODB tables as of 10.1. This can potentially reduce wear and tear of your SSDs. However, keep in mind that compression and decompression is done by the CPU so if you use a weak machine like a NAS or an old NUC you should probably stay away from this if you're after the ultimate millisecond in performance.

There is a vast range of compression algorithms available but not on all platforms. ZLIB is the one that is pre-installed on all platforms. For more details on compression, consult the MariaDB reference. If you want to use ZLIB compression you need to access the database with a tool like HeidiSQL (comes with MariaDB) and set each table from ROW FORMAT: DEFAULT to ROW FORMAT: COMPRESSED like so:


This will reduce the database size by 50%!

[HeresJohnny]

I'm at MariaDB 10.4.12. and it really got better and better with each consecutive version, especially where preselected and automatic values are concerned. IMHO, the only real tweak needed with the latest versions is

Code:

optimizer_search_depth=0

or set it at 1 if you're feeling optimistic. Everything else works great out of the box, really.

[hugepants]

I'm at MariaDB 10.4.12. and it really got better and better with each consecutive version, especially where preselected and automatic values are concerned.

By "preselected and automatic values", do you mean the original defaults present in a fresh instance of MariaDB Server*, or a particular distribution's or Docker build's choice of initial values?

*Obtained by:

Code:

mysqld --no-defaults --verbose --help

[HeresJohnny]

I'm a windows guy, none of the stuff you talk about means anything to me

[HeresJohnny]

MariaDB 10.5 has introduced some important changes, unfortunately some of them seem to be incompatible with KODI. I have seen some PRs that try to mitigate this but at the moment I would advise people to stay with 10.4 if possible.

One of the incompatibilities seems to have to do with compression. If compression is enabled on 10.5, Android and Libre/CoreElec installation will either show an empty library (even though sources and files remain accessible) or show a "0" instead of titles. Compression should be off by default but to be really sure you can put the following line in your my.ini / my.cnf:

Code:

[mysqld]
innodb_compression_default=OFF


I would also recommend to remove compression from advancedsettings.xml if set to "true":

Code:


<videodatabase>
<type>mysql</type>
<compression>false</compression>
</videodatabase>
<musicdatabase>
<type>mysql</type>
<compression>true</compression>
</musicdatabase>


Or simply delete the lines with <compression> as it defaults to "false".

And just FYI, this is how my my.ini looks for 10.5 at this point:

Code:

[mysqld]
datadir=C:/Program Files/MariaDB 10.5/data
port=3306

key_buffer_size=16k
skip_name_resolve=1
query_cache_size=32M
query_cache_limit=2M
optimizer_search_depth=1
character-set-server=utf8
innodb_compression_default=OFF
innodb_log_buffer_size=32M

[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.5/lib/plugin


[DaveBlake]

MariaDB 10.5 has introduced some important changes, unfortunately some of them seem to be incompatible with KODI. I have seen some PRs that try to mitigate this but at the moment I would advise people to stay with 10.4 if possible.

One of the incompatibilities seems to have to do with compression. If compression is enabled on 10.5, Android and Libre/CoreElec installation will either show an empty library (even though sources and files remain accessible) or show a "0" instead of titles.

The only issue the team are aware of with using Kodi and MariaDB 10.5 is the display "0" in place of text on library screens. I was certainly unaware it had any connection with compression, or that it effected platforms other than Android

Anyway with Leia 18.8 point release the MariaDB connector version has been bumped which resolves that.
It is also solved in both v19 alpha1. Have no idea how long other distros of Kodi will take to catch up with the Kodi release.