[sialivi]

innodb_adaptive_hash_index=OFF;For Kodi no benefit is gained from additional indexes. This should only be used with large databases.

What's considered a large database?

[Klojum]

What's considered a large database?

IMO, a database with at least a couple of million records in a table. Kodi is unlikely to have such large tables. But it all comes back to the hardware running the database.
More cpu grunt and faster disk speeds, more internal memory for cache, and things will go smoother.
Databases running on the SD card of a Raspberry Pi is considered a kid's toy to me. SDcards have a p!ss poor quality track record in my book.

[Zoltrix]

Anyone have these settings working on Linux?

I've placed the settings into /etc/mysql/my.cnf

But after a restart, mariadb doesn't load and shows errors such as:

mariadb: unknown variable 'innodb_buffer_pool_instances=2'

my.cnf looks as follows:

Code:

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_file_format = Barracuda
innodb_log_block_size = 4096
innodb_adaptive_hash_index = OFF
character_set_server = utf8
key_buffer_size = 16k
skip_name_resolve = 1
optimizer_search_depth = 0
aria_pagecache_buffer_size = 512M
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_doublewrite = 0

[Klojum]

Which MariaDB server version are you running?
innodb_buffer_pool_instances was deprecated in v10.5.

See https://mariadb.com/docs/reference/mdb/s...instances/

[HeresJohnny]

Try commenting out the line with utf8.

[Zoltrix]

Running version 10.3.25-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

If I comment out every line except the first one, I still get an unknown variable error:

Code:

zoltrix@kodidb:~$ sudo mariadb
mariadb: unknown variable 'innodb_buffer_pool_size=2G'

[HeresJohnny]

Try swapping out [client-server] with [mysqld]

[Zoltrix]

Try swapping out [client-server] with [mysqld]

That did the trick!

The two settings I had to leave commented out though were:

#innodb_log_block_size = 4096
#character_set_server = utf8

Not sure if there are newer variables that replace those?

[teriyaki]

Try swapping out [client-server] with [mysqld]

That did the trick!

The two settings I had to leave commented out though were:

#innodb_log_block_size = 4096
#character_set_server = utf8

Not sure if there are newer variables that replace those?

innodb_log_block_size was removed in 10.3 and to use utf8, see: https://mariadb.com/kb/en/setting-charac...t-to-utf-8

[vinoo]

I have try the query cache, that is suitable to kodi for me.
Add this parameters:
- query_cache_type=ON
- query_cache_size=32M

[HeresJohnny]

Have you seen this https://shatteredsilicon.net/blog/2022/0...ache-lock/ and tested performance with and without query cache?

[vinoo]

Yes i have seen this problem, but with kodi there is not a lot of update on the tables. So if its a dedicated mariadb engine for kodi it's not a problem.
In my context i fell faster, but it's not a rigorous benchmark.

[Jeff721]

Good day all, 

I'm really sorry for the newbie question but here it is: I'm using the MariaDB 10.3.32-1040 on my Synology NAS and would REALLY like to change the value of optimizer_search_depth to 0 or 1 to speed up the queries...  But how do I do this on my Synology NAS 1821+

Thanks for the help.

[jznine]

Do you have phpmyadmin installed from package centre? Under that you can adjust those under the 'Variables' tab.

-- I was wrong, ignore my comments

[Klojum]

Otherwise, you'll have to SSH into your NAS, find MariaDB's config file and edit it manually via the command line.
Which is something you only should do when you know what you're doing.